连接池技术的思想：
连接复用（高效、安全），避免数据库频繁建立、关闭的开销
--------------------极客学院（参考lulei）
1.配置文件
localmysqldb com.mysql.jdbc.Driver 900000 500000 10 4 select 1 after_transaction
2。工具类
public class ClassUtil { /** * @param c * @return * @Author: * @Description: 返回class文件所在的目录 */ public static String getClassPath(Class c) { return c.getResource("").getPath().replaceAll("%20", " "); } /** * @param c * @return * @Author: * @Description: 返回class文件所在项目的根目录 */ public static String getClassRootPath(Class c) { return c.getResource("/").getPath().replaceAll("%20", " "); } /** * @param c * @param hasName 是否包括class名 * @return * @Author: * @Description: 返回class文件所在的目录 */ public static String getClassPath(Class c, boolean hasName) { String name = c.getSimpleName() + ".class"; String path = c.getResource(name).getPath().replaceAll("%20", " "); if (hasName) { return path; } else { return path.substring(0, path.length() - name.length()); } }} 3.数据库连接池配置加载类
public class DBPool { private String poolPath;//数据库连接池的配置文件路径 private DBPool() { } /** * @return * @Author: * @Description: 返回DBPool对象 */ public static DBPool getDBPool() { return DBPoolDao.dbPool; } /** *@Description: 静态内部类实现单例模式 *@Author: *@Version:1.1.0 */ private static class DBPoolDao{ private static DBPool dbPool = new DBPool(); } public String getPoolPath() { if (poolPath == null) { //如果poolPath为空，赋值为默认值 poolPath = ClassUtil.getClassRootPath(DBPool.class) + "proxool.xml"; } return poolPath; } /** * @param poolPath * @Author: * @Description: 设置数据库连接池的配置文件路径 */ public void setPoolPath(String poolPath) { this.poolPath = poolPath; }} 4.数据库连接池管理类
public class DBManager { private DBManager(){ try { //数据库连接池配置文件 JAXPConfigurator.configure(DBPool.getDBPool().getPoolPath(), false); //数据库加载驱动类 Class.forName("org.logicalcobwebs.proxool.ProxoolDriver"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * @param poolName * @return * @throws SQLException * @Author: * @Description: 获取数据库连接 */ public Connection getConnection(String poolName) throws SQLException { return DriverManager.getConnection(poolName); } /** *@Description: 内部静态类实现单例模式 *@Author: *@Version:1.1.0 */ private static class DBManagerDao { private static DBManager dbManager = new DBManager(); } /** * @return * @Author: * @Description: 返回数据库连接池管理类 */ public static DBManager getDBManager() { return DBManagerDao.dbManager; }} 5. sql语句参数转化
public class DBOperation { private String poolName;//数据库连接池别名 private Connection con = null;//数据库连接 public DBOperation(String poolName) { this.poolName = poolName; } /** * @Author: * @Description:关闭数据库连接 */ public void close() { try { if (this.con != null) { this.con.close(); } } catch (Exception e) { e.printStackTrace(); } } /** * @throws SQLException * @Author:lulei * @Description: 打开数据库连接 */ private void open() throws SQLException { //先关闭后打开，防止数据库连接溢出 close(); this.con = DBManager.getDBManager().getConnection(this.poolName); } /** * @param sql * @param params * @return * @throws SQLException * @throws ClassNotFoundException * @Author: * @Description: sql语句参数转化 */ private PreparedStatement setPres(String sql, HashMap params) throws SQLException, ClassNotFoundException{ if (null == params || params.size() < 1) { return null; } PreparedStatement pres = this.con.prepareStatement(sql); for (int i = 1; i <= params.size(); i++) { if (params.get(i) == null) { pres.setString(i, ""); } else if (params.get(i).getClass() == Class.forName("java.lang.String")) { pres.setString(i, params.get(i).toString()); } else if (params.get(i).getClass() == Class.forName("java.lang.Integer")) { pres.setInt(i, (Integer) params.get(i)); } else if (params.get(i).getClass() == Class.forName("java.lang.Long")) { pres.setLong(i, (Long) params.get(i)); } else if (params.get(i).getClass() == Class.forName("java.lang.Double")) { pres.setDouble(i, (Double) params.get(i)); } else if (params.get(i).getClass() == Class.forName("java.lang.Flaot")) { pres.setFloat(i, (Float) params.get(i)); } else if (params.get(i).getClass() == Class.forName("java.lang.Boolean")) { pres.setBoolean(i, (Boolean) params.get(i)); } else if (params.get(i).getClass() == Class.forName("java.sql.Date")) { pres.setDate(i, java.sql.Date.valueOf(params.get(i).toString())); } else { return null; } } return pres; } /** * @param sql * @return * @throws SQLException * @Author: * @Description: 执行SQL语句，返回影响行数 */ public int executeUpdate(String sql) throws SQLException { this.open(); Statement state = this.con.createStatement(); return state.executeUpdate(sql); } /** * @param sql * @param params * @return * @throws SQLException * @throws ClassNotFoundException * @Author: * @Description: 执行sql语句，返回影响行数 */ public int executeUpdate(String sql, HashMap params) throws SQLException, ClassNotFoundException { this.open(); PreparedStatement pres = setPres(sql, params); if (null == pres) { return 0; } return pres.executeUpdate(); } /** * @param sql * @return * @throws SQLException * @Author: * @Description: 执行sql语句，返回结果集 */ public ResultSet executeQuery(String sql) throws SQLException { this.open(); Statement state = this.con.createStatement(); return state.executeQuery(sql); } /** * @param sql * @param params * @return * @throws SQLException * @throws ClassNotFoundException * @Author: * @Description:执行sql语句，返回结果集 */ public ResultSet executeQuery(String sql, HashMap params) throws SQLException, ClassNotFoundException { this.open(); PreparedStatement pres = setPres(sql, params); if (null == pres) { return null; } return pres.executeQuery(); }} 6.数据基本操作
public class DBServer { private DBOperation dbOperation; public DBServer(String poolName) { dbOperation = new DBOperation(poolName); } /** * @Author: * @Description: 关闭数据库连接 */ public void close() { dbOperation.close(); } /** * @param sql * @return * @throws SQLException * @Author: * @Description: 数据库新增操作 */ public int insert(String sql) throws SQLException { return dbOperation.executeUpdate(sql); } /** * @param tableName * @param columns * @param params * @return * @throws SQLException * @throws ClassNotFoundException * @Author: * @Description: 数据库新增操作 */ public int insert(String tableName, String columns, HashMap params) throws SQLException, ClassNotFoundException { String sql = insertSql(tableName, columns); return dbOperation.executeUpdate(sql, params); } /** * @param sql * @return * @throws SQLException * @Author: * @Description: 数据库删除操作 */ public int delete(String sql) throws SQLException { return dbOperation.executeUpdate(sql); } /** * @param tableName * @param condition * @return * @throws SQLException * @Author:lulei * @Description: 数据库删除操作 */ public int delete(String tableName, String condition) throws SQLException { if (null == tableName) { return 0; } String sql = "delete from " + tableName + " " + condition; return dbOperation.executeUpdate(sql); } /** * @param sql * @return * @throws SQLException * @Author: * @Description: 数据库更新操作 */ public int update(String sql) throws SQLException { return dbOperation.executeUpdate(sql); } /** * @param tableName * @param columns * @param condition * @param params * @return * @throws SQLException * @throws ClassNotFoundException * @Author: * @Description: 数据库更新操作 */ public int update(String tableName, String columns, String condition, HashMap params) throws SQLException, ClassNotFoundException { String sql = updateSql(tableName, columns, condition); return dbOperation.executeUpdate(sql, params); } /** * @param sql * @return * @throws SQLException * @Author: * @Description: 数据库查询操作 */ public ResultSet select(String sql) throws SQLException { return dbOperation.executeQuery(sql); } /** * @param tableName * @param columns * @param condition * @return * @throws SQLException * @Author:lulei * @Description: 数据库查询操作 */ public ResultSet select(String tableName, String columns, String condition) throws SQLException { String sql = "select " + columns + " from " + tableName + " " + condition; return dbOperation.executeQuery(sql); } /** * @param tableName * @param columns * @param condition * @return * @Author: * @Description: 组装 update sql eg: update tableName set column1=?,column2=? condition */ private String updateSql(String tableName, String columns, String condition) { if (tableName == null || columns == null) { return ""; } String[] column = columns.split(","); StringBuilder sb = new StringBuilder(); sb.append("update "); sb.append(tableName); sb.append(" set "); sb.append(column[0]); sb.append("=?"); for (int i = 1; i < column.length; i++) { sb.append(", "); sb.append(column[i]); sb.append("=?"); } sb.append(" "); sb.append(condition); return sb.toString(); } /** * @param tableName * @param columns * @return * @Author: * @Description: 组装 insert sql eg: insert into tableName (column1, column2) values (?,?) */ private String insertSql(String tableName, String columns) { if (tableName == null || columns == null) { return ""; } int n = columns.split(",").length; StringBuilder sb = new StringBuilder(""); sb.append("insert into "); sb.append(tableName); sb.append(" ("); sb.append(columns); sb.append(") values (?"); for (int i = 1; i < n; i++) { sb.append(",?"); } sb.append(")"); return sb.toString(); }}