J2EE工具类：DataBaseUtil.java

package com.worthtech.app.sql; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.Map; import java.util.Properties; public class DataBaseUtil { private String driver; private String url; private String user; private String password; private Connection conn; public DataBaseUtil(String fileName) { loadProperties(fileName); setConnection(); } // handle the properties file to get the informations for connection private void loadProperties(String fileName) { Properties props = new Properties(); try { props.load(new FileInputStream(fileName)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } this.driver = props.getProperty("driver"); this.url = props.getProperty("url"); this.user = props.getProperty("user"); this.password = props.getProperty("password"); } private void setConnection() { try { Class.forName(driver); this.conn = DriverManager.getConnection(url, user, password); } catch (ClassNotFoundException classnotfoundexception) { System.err.println("db: " + classnotfoundexception.getMessage()); } catch (SQLException sqlexception) { System.err.println("db.getconn(): " + sqlexception.getMessage()); } } public Connection getConnection() { try { if (conn != null && !conn.isClosed()) { return this.conn; } else { setConnection(); } } catch (SQLException e) { e.printStackTrace(); } return this.conn; } public void closeConnection() { try { if (st != null) { st.close(); } if (conn != null && !conn.isClosed()) { conn.close(); conn = null; } } catch (SQLException e) { e.printStackTrace(); } } /** * 增加，修改，删除数据 * * @param sql * @param sqlValue * @return * @throws Exception * 用法： sql="UPDATE user SET password = ? WHERE phone= ?"; String * sql="INSERT INTO payee(name,card,openBank,addDate,addPhone) * VALUES(?,?,?,?,?)"; delete ... db.executeUpdate(sql, new * String[]{name,card,openBank,addDate,addPhone}); */ public int doExecute(String sql, String sqlValue[]) { int count = 0; try { PreparedStatement ps = conn.prepareStatement(sql); if (sqlValue != null) { for (int i = 0; i < sqlValue.length; i++) ps.setString(i + 1, sqlValue[i]); } count = ps.executeUpdate(); } catch (Exception e) { } return count; } /** * 提供更通用点的方法 * * @param sql * @return */ public int doExecute(String sql) { return doExecute(sql, null); } /** * 获得查询数据，返回一个ArrayList 得到的Arraylist 可以用 Map解析 用法： Arraylist list = * db.getList("select * from user where ***") for(int i=1;i<list.size;i++) * Map map = (Map)list.get(i); map.get("id"); 就是得到了里面的id了 * */ public ArrayList getList(String sql) { Statement st = null; ArrayList list = new ArrayList(); try { // PreparedStatement ps = conn.prepareStatement(sql); // ResultSet rs=ps.executeQuery(); st = conn.createStatement(); ResultSet rs = st.executeQuery(sql); ResultSetMetaData meta = rs.getMetaData(); int count = meta.getColumnCount(); String cols[] = new String[count]; for (int i = 0; i < cols.length; i++) { if (meta.getColumnName(i + 1) != null) cols[i] = meta.getColumnName(i + 1); else cols[i] = meta.getColumnLabel(i + 1); } HashMap map = null; String fieldValue = null; for (; rs.next(); list.add(map)) { map = new HashMap(); for (int i = 0; i < cols.length; i++) { int iType = meta.getColumnType(i + 1); if (iType == 2 || iType == 3) { if (meta.getScale(i + 1) == 0) fieldValue = String.valueOf(rs.getLong(i + 1)); else fieldValue = rs.getString(i + 1); } else if (iType == 8) fieldValue = String.valueOf(rs.getDouble(i + 1)); else if (iType == 6 || iType == 7) fieldValue = String.valueOf(rs.getFloat(i + 1)); else fieldValue = rs.getString(i + 1); if (fieldValue == null) fieldValue = ""; else fieldValue = fieldValue.trim(); map.put(cols[i], fieldValue);// .toLowerCase() } } } catch (Exception e) { } return list; } /** * 获得记录数 * * @param sql * @return 用法： String sql="select count(*) from bill where ... ; * count=db.executeQuery(sql); */ public int getCount(String sql) { // String sql="select count(*) from table where ..."; ResultSet rs = null; PreparedStatement ps; int count = 0; try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); rs.next(); count = rs.getInt(1); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return count; } /** * 以下4个方法都可以用doExecute(),getList()替代 * * @param args */ private Statement st = null; public void doInsert(String sql) { try { st = conn.createStatement(); int i = st.executeUpdate(sql); } catch (SQLException sqlexception) { System.err.println("db.executeInset:" + sqlexception.getMessage()); } } public void doDelete(String sql) { try { st = conn.createStatement(); int i = st.executeUpdate(sql); } catch (SQLException sqlexception) { System.err.println("db.executeDelete:" + sqlexception.getMessage()); } } public void doUpdate(String sql) { try { st = conn.createStatement(); int i = st.executeUpdate(sql); } catch (SQLException sqlexception) { System.err.println("db.executeUpdate:" + sqlexception.getMessage()); } } public ResultSet doSelect(String sql) { ResultSet rs = null; try { st = conn.createStatement( java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY); rs = st.executeQuery(sql); } catch (SQLException sqlexception) { System.err.println("db.executeQuery: " + sqlexception.getMessage()); } return rs; } /** * 以下3个为保留方法 */ public void beginTransaction() { try { conn.setAutoCommit(false); } catch (SQLException e) { e.printStackTrace(); } } public void commitTransaction() { try { conn.commit(); } catch (SQLException e) { e.printStackTrace(); } } public void rollbackTransaction() { try { conn.rollback(); } catch (SQLException e) { e.printStackTrace(); } } /** * 测试 * * @param args */ public static void main(String[] args) { DataBaseUtil util = new DataBaseUtil("config.properties"); String sql = ""; sql = "INSERT INTO user(userId,password,userName,zone,phone) VALUES(?,?,?,?,?)"; try { util.doExecute(sql, new String[] { "name", "pass", "上海", "021", "61089712" }); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } sql = "select * from user "; ArrayList list = util.getList(sql); System.out.println("size=" + list.size()); for (int i = 0; i < list.size(); i++) { Map map = (Map) list.get(i); System.out.println("===" + map.get("userId")); System.out.println("===" + map.get("password")); System.out.println("===" + map.get("userName")); System.out.println("===" + map.get("zone")); System.out.println("===" + map.get("phone")); } // Connection conn = util.getConnection(); util.closeConnection(); } }