Java数据库连接池

一、概述

核心思想：连接复用，通过建立一个数据库连接池以及一套连接使用、分配、管理策略，使得该连接池中的链接可以得到高效、安全的复用，避免数据库连接的频繁建立、关闭的开销。

组成部分：建立、管理、关闭

　　自己维护一些数据库连接，需要使用的时候直接使用其中一个连接，用完之后不是关闭而是将其归还，等待其他操作使用。

常见技术：proxool/DBCP/C3P0三种常见的连接池技术

Proxool是一种Java数据库连接池技术。sourceforge下的一个开源项目，这个项目提供一个健壮、已用的连接池，最为关键的是这个连接池提供监控的功能，方便已用，便于发现连接泄漏的情况。

二、Java实现数据库连接池

DBPool数据库连接池Bean

public class DBPool {
    private String poolPath;//数据库连接池的配置文件路径

    private DBPool() {
    }

    /**
     * @return
     * @Author:lulei  
     * @Description: 返回DBPool对象
     */
    public static DBPool getDBPool() {
        return DBPoolDao.dbPool;
    }

    /**
     *@Description: 静态内部类实现单例模式
     *@Author:lulei  
     *@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:lulei  
     * @Description: 设置数据库连接池的配置文件路径
     */
    public void setPoolPath(String poolPath) {
        this.poolPath = poolPath;
    }

}

    public static String getClassRootPath(Class<?> c) {
        return c.getResource("/").getPath().replaceAll("%20", " ");
    }

数据库管理类：

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:lulei  
     * @Description: 获取数据库连接
     */
    public Connection getConnection(String poolName) throws SQLException {
        return DriverManager.getConnection(poolName);
    }

    /**
     *@Description: 内部静态类实现单例模式 
     *@Author:lulei  
     *@Version:1.1.0
     */
    private static class DBManagerDao {
        private static DBManager dbManager = new DBManager();
    }

    /**
     * @return
     * @Author:lulei  
     * @Description: 返回数据库连接池管理类
     */
    public static DBManager getDBManager() {
        return DBManagerDao.dbManager;
    }

}

数据库操作类：

public class DBOperation {
    private String poolName;//数据库连接池别名
    private Connection con = null;//数据库连接

    public DBOperation(String poolName) {
        this.poolName = poolName;
    }

    /**
     * @Author:lulei  
     * @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:lulei  
     * @Description: sql语句参数转化
     */
    private PreparedStatement setPres(String sql, HashMap<Integer, Object> 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:lulei  
     * @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:lulei  
     * @Description: 执行sql语句，返回影响行数
     */
    public int executeUpdate(String sql, HashMap<Integer, Object> 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:lulei  
     * @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:lulei  
     * @Description:执行sql语句，返回结果集
     */
    public ResultSet executeQuery(String sql, HashMap<Integer, Object> params) throws SQLException, ClassNotFoundException {
        this.open();
        PreparedStatement pres = setPres(sql, params);
        if (null == pres) {
            return null;
        }
        return pres.executeQuery();
    }

}

数据库服务：

public class DBServer {
    private DBOperation dbOperation;

    public DBServer(String poolName) {
        dbOperation = new DBOperation(poolName);
    }

    /**
     * @Author:lulei  
     * @Description: 关闭数据库连接
     */
    public void close() {
        dbOperation.close();
    }

    /**
     * @param sql
     * @return
     * @throws SQLException
     * @Author:lulei  
     * @Description: 数据库新增操作
     */
    public int insert(String sql) throws SQLException {
        return dbOperation.executeUpdate(sql);
    }

    /**
     * @param tableName
     * @param columns
     * @param params
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     * @Author:lulei  
     * @Description: 数据库新增操作
     */
    public int insert(String tableName, String columns, HashMap<Integer, Object> params) throws SQLException, ClassNotFoundException {
        String sql = insertSql(tableName, columns);
        return dbOperation.executeUpdate(sql, params);
    }

    /**
     * @param sql
     * @return
     * @throws SQLException
     * @Author:lulei  
     * @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:lulei  
     * @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:lulei  
     * @Description: 数据库更新操作
     */
    public int update(String tableName, String columns, String condition, HashMap<Integer, Object> params) throws SQLException, ClassNotFoundException {
        String sql = updateSql(tableName, columns, condition);
        return dbOperation.executeUpdate(sql, params);
    }

    /**
     * @param sql
     * @return
     * @throws SQLException
     * @Author:lulei  
     * @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:lulei  
     * @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:lulei  
     * @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();
    }

}

参考资料：
[极客学院]