JDBC封装类升级版，支持DBCP、C3P0连接池，Java连接数据库带例子

package com.soft.util; import java.lang.reflect.Method; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.sql.Types; import java.text.NumberFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.ResourceBundle; import org.apache.commons.dbcp2.BasicDataSource; public class ConnectPoolDBCP { /** * 连接池使用 */ private BasicDataSource dataSource; private static ConnectPoolDBCP conn; private String drive; /** * 获取连接池实例 * @return */ public static ConnectPoolDBCP getInstance(){ if(conn == null){ conn = new ConnectPoolDBCP(); } return conn; } /** * 初始化连接池 */ private ConnectPoolDBCP(){ ResourceBundle bund = ResourceBundle.getBundle("jdbc"); drive = bund.getString("jdbc.driverClassName"); dataSource = new BasicDataSource(); // 驱动名称 dataSource.setDriverClassName(drive); // 连接地址 dataSource.setUrl(bund.getString("jdbc.url")); // name dataSource.setUsername(bund.getString("jdbc.username")); // pass dataSource.setPassword(bund.getString("jdbc.password")); // 最大连接数 dataSource.setMaxIdle(15); // 最小空闲连接 dataSource.setMinIdle(2); // 初始化连接 dataSource.setInitialSize(5); // 连接被泄露时是否打印 dataSource.setLogAbandoned(true); // <!--removeAbandonedTimeout: 超时时间(以秒数为单位)--> dataSource.setRemoveAbandonedTimeout(10); // <!--maxWait: 超时等待时间以毫秒为单位 1000等于60秒--> dataSource.setMaxWaitMillis(1000); // <!-- 在空闲连接回收器线程运行期间休眠的时间值,以毫秒为单位. --> dataSource.setTimeBetweenEvictionRunsMillis(10000); // <!-- 在每次空闲连接回收器线程(如果有)运行时检查的连接数量 --> dataSource.setNumTestsPerEvictionRun(10); // <!-- 1000 * 60 * 30 连接在池中保持空闲而不被空闲连接回收器线程--> dataSource.setMinEvictableIdleTimeMillis(10000); dataSource.setValidationQuery("select 1 from INFORMATION_SCHEMA.SYSTEM_USERS"); } /** * 获取连接 */ public Connection getConnection (){ Connection conn = null; try { conn = dataSource.getConnection(); } catch (Exception e) { e.printStackTrace(); } return conn; } /** * 释放连接 * @param con */ public void freeConnection(Connection con) { try { if(null != con){ con.close(); con = null; } } catch (SQLException e) { System.out.println(e.getMessage()); } finally { if (null != con) { try { con.close(); } catch (SQLException e1) { e1.printStackTrace(); } con = null; } } } /** * 释放Statement资源 * @param statement */ public void freeStatement(Statement statement){ try { if(null != statement){ statement.close(); statement = null; } } catch(Exception e) { System.out.println(e.getMessage()); } finally { if (null !=statement) { try { statement.close(); }catch(Exception e) { System.out.println(e.getMessage()); } statement = null; } } } /** * 释放查询结果 * @param statement */ public void freeResultSet(ResultSet rs){ try { if(null != rs){ rs.close(); rs = null; } } catch(Exception e) { System.out.println(e.getMessage()); } finally { if (null !=rs) { try { rs.close(); }catch(Exception e) { System.out.println(e.getMessage()); } rs = null; } } } /** * 关闭hsqldb连接 * @param con * @throws SQLException */ public void freeHsqldb(Connection con) { try{ // hsqldb 需要关闭数据库 if(drive.contains("hsqldb")){ Statement st = con.createStatement(); st.executeUpdate(" SHUTDOWN "); st.close(); st = null; } }catch(SQLException e){ e.printStackTrace(); } } /*** * 设置sql语句中的问号 * @param ps * @param param * @throws SQLException */ private void setParam(PreparedStatement ps,List<Object> param) throws SQLException{ int seq = 1; // 如果为null,则表示没有参数 if (param != null) { for (Object elem : param) { // String 类型 if (elem instanceof String) { ps.setString(seq, (String) elem); // Long 类型 } else if (elem instanceof Long) { ps.setLong(seq, (Long) elem); // Double 类型 } else if (elem instanceof Double) { ps.setDouble(seq, (Double) elem); // sql 日期类型 } else if (elem instanceof Float) { ps.setFloat(seq, (Float) elem); // Integer 整形 } else if (elem instanceof Integer) { ps.setInt(seq, (Integer) elem); // Timestamp 类型 } else if (elem instanceof Timestamp) { ps.setTimestamp(seq, (Timestamp) elem); } else if (elem instanceof Date) { ps.setDate(seq, (Date) elem); // util 日期类型 } else if (elem instanceof java.util.Date) { java.util.Date tmp = (java.util.Date) elem; ps.setDate(seq, new Date(tmp.getTime())); // Float 浮点类型 } else { ps.setString(seq, (String) elem); } seq++; } } } /** * 根据结果集中的数据类型 转换成相应String 类型 * @throws SQLException */ private String parseResultSet(ResultSet rs, ResultSetMetaData rsmd, int type, int i) throws SQLException { String resStr = ""; switch (type) { case Types.VARCHAR: resStr = rs.getString(i + 1); break; case Types.NUMERIC: NumberFormat nf = NumberFormat.getInstance(); nf.setGroupingUsed(false); int scale = rsmd.getScale(i + 1); if (scale == 0) { resStr = nf.format(rs.getLong(i + 1)); } else { nf.setMinimumFractionDigits(2); resStr = nf.format(rs.getDouble(i + 1)); } break; case Types.CHAR: resStr = rs.getString(i + 1); break; case Types.FLOAT: resStr = String.valueOf(rs.getFloat(i + 1)); break; case Types.DOUBLE: resStr = String.valueOf(rs.getDouble(i + 1)); break; case Types.DATE: resStr = DateUtil.getStringDate(rs.getDate(i + 1)); break; case Types.TIMESTAMP: resStr = DateUtil.getStringTimestamp(rs.getTimestamp(i + 1)); break; default: resStr = rs.getString(i + 1); } return resStr; } /** * 返回数组格式 * @param sql * @param param * @return */ public List<String[]> queryForArray(String sql, List<Object> param) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; ResultSetMetaData rsmd = null; List<String[]> list = new ArrayList<String[]>(); try { // 获取一个连接 conn = this.getConnection(); // 执行预备语句 ps = conn.prepareStatement(sql); // 设置sql 语句中的参数 setParam(ps,param); // 查询操作 rs = ps.executeQuery(); // 获取结果集列信息 rsmd = rs.getMetaData(); // 获取总列数 rsmd = rs.getMetaData(); int columCount = rsmd.getColumnCount(); while (rs.next()) { String[] resStr = new String[columCount]; for (int i = 0; i < columCount; i++) { int type = rsmd.getColumnType(i + 1); resStr[i] = parseResultSet(rs,rsmd,type,i); } list.add(resStr); } } catch (SQLException