编写一个简单的数据库连接池

为什么要使用数据库连接池

使用jdbc最大的开销之一就是创建数据库，当我们频繁的创建数据库时，势必影响应用的效率，或者在数据库关闭出现问题时，我们不能马上释放，时间长一些，整个数据库的 资源将会被我们的应用耗尽，这是一个很危险的讯息，怎样将数据库连接创建带来的开销降到最低呢，就是我们实现将一系列创建完毕的连接存放起来，等到使用的时候再来使用，这就是数据库连接池解决的最大问题和存在的最大必要。

---

数据库连接池雏形

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;

public class SimpleDataSource {

    private static LinkedList<Connection> connPool = null;

    static {
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connPool = new LinkedList<Connection>();
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }
    }

    public SimpleDataSource(){
        init();
    }

    private void init(){
        for(int i = 0;i<10;i++){
            try {
                Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "root");
                // 实现了静态代理的Connection类
                Connection proxyConnection = new SimpleConnection(this,conn);
                connPool.addFirst(proxyConnection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public Connection getConnection() throws Exception {
        synchronized (connPool){
            if (connPool.isEmpty()){
                throw new Exception("have no connection now!");
            }
            return connPool.removeLast();
        }
    }

    public void closeConn(Connection conn){
        synchronized (connPool) {
            connPool.addFirst(conn);
        }
    }

    public int getCurrentCount(){
        synchronized (connPool) {
            return connPool.size();
        }
    }

}

数据库连接池优化

• 对连接池加锁，保证多线程并发时获取的连接各不相干，修改其中的代码片段如下

public Connection getConnection(){
        synchronized (connPool) {
            return connPool.removeLast();
        }
}

• 连接不够用时抛出异常

public Connection getConnection() throws Exception{
        synchronized (connPool) {
            if(connPool.isEmpty())
                throw new Exception("have no connection now!");
            return connPool.removeLast();
        }
}

数据库连接池之代理模式

在使用JDBC连接数据库的时候，最后需要用户释放资源，如果使用者按照传统的方式关闭连接，那么我们的连接池就没有存在的意义了，因为每一次使用者都会给关闭掉，导致连接池的连接会是无效的或者越来越少，为了防止这样的事情发生，我们需要保留使用者的使用习惯，也就是说允许使用者通过close方法释放连接，这个时候我们应该如何做既能起到用户的使用习惯，又能在进行关闭的时候不是真的关掉数据库连接，而是直接存放至数据库连接池中

• 静态代理

实现Connection接口，只需要关注将close方法实现为自己的方法就可以

package util;

import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

public class SimpleConnection implements Connection {

    private SimpleDataSource dataSource = null;

    private Connection connection = null;

    public SimpleConnection(SimpleDataSource dataSource,Connection connection) {
        this.dataSource = dataSource;
        this.connection = connection;
    }


    @Override
    public Statement createStatement() throws SQLException {
       return connection.createStatement();
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        return connection.prepareCall(sql);
    }

    @Override
    public String nativeSQL(String sql) throws SQLException {
        return connection.nativeSQL(sql);
    }

    @Override
    public void setAutoCommit(boolean autoCommit) throws SQLException {
        connection.setAutoCommit(autoCommit);
    }

    @Override
    public boolean getAutoCommit() throws SQLException {
        return false;
    }

    @Override
    public void commit() throws SQLException {
        connection.commit();
    }

    @Override
    public void rollback() throws SQLException {
        connection.rollback();
    }

    @Override
    public void close() throws SQLException {
        dataSource.closeConn(this);
    }

    @Override
    public boolean isClosed() throws SQLException {
        return false;
    }

    @Override
    public DatabaseMetaData getMetaData() throws SQLException {
        return connection.getMetaData();
    }

    @Override
    public void setReadOnly(boolean readOnly) throws SQLException {
        connection.setReadOnly(readOnly);
    }

    @Override
    public boolean isReadOnly() throws SQLException {
        return false;
    }

    @Override
    public void setCatalog(String catalog) throws SQLException {
        connection.setCatalog(catalog);
    }

    @Override
    public String getCatalog() throws SQLException {
        return connection.getCatalog();
    }

    @Override
    public void setTransactionIsolation(int level) throws SQLException {
        connection.setTransactionIsolation(level);
    }

    @Override
    public int getTransactionIsolation() throws SQLException {
        return connection.getTransactionIsolation();
    }

    @Override
    public SQLWarning getWarnings() throws SQLException {
        return connection.getWarnings();
    }

    @Override
    public void clearWarnings() throws SQLException {
        connection.clearWarnings();
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
        return connection.createStatement(resultSetType,resultSetConcurrency);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return connection.prepareCall(sql,resultSetType,resultSetConcurrency);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return connection.prepareCall(sql,resultSetType,resultSetConcurrency);
    }

    @Override
    public Map<String, Class<?>> getTypeMap() throws SQLException {
        return connection.getTypeMap();
    }

    @Override
    public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
        connection.setTypeMap(map);
    }

    @Override
    public void setHoldability(int holdability) throws SQLException {
        connection.setHoldability(holdability);
    }

    @Override
    public int getHoldability() throws SQLException {
        return connection.getHoldability();
    }

    @Override
    public Savepoint setSavepoint() throws SQLException {
        return connection.setSavepoint();
    }

    @Override
    public Savepoint setSavepoint(String name) throws SQLException {
        return connection.setSavepoint(name);
    }

    @Override
    public void rollback(Savepoint savepoint) throws SQLException {
        connection.rollback(savepoint);
    }

    @Override
    public void releaseSavepoint(Savepoint savepoint) throws SQLException {
        connection.releaseSavepoint(savepoint);
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return connection.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return connection.prepareCall(sql,resultSetType,resultSetConcurrency);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return connection.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
        return connection.prepareStatement(sql,autoGeneratedKeys);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
        return connection.prepareStatement(sql,columnIndexes);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
        return connection.prepareStatement(sql,columnNames);
    }

    @Override
    public Clob createClob() throws SQLException {
        return connection.createClob();
    }

    @Override
    public Blob createBlob() throws SQLException {
        return connection.createBlob();
    }

    @Override
    public NClob createNClob() throws SQLException {
        return connection.createNClob();
    }

    @Override
    public SQLXML createSQLXML() throws SQLException {
        return connection.createSQLXML();
    }

    @Override
    public boolean isValid(int timeout) throws SQLException {
        return false;
    }

    @Override
    public void setClientInfo(String name, String value) throws SQLClientInfoException {
        connection.setClientInfo(name, value);
    }

    @Override
    public void setClientInfo(Properties properties) throws SQLClientInfoException {
        connection.setClientInfo(properties);
    }

    @Override
    public String getClientInfo(String name) throws SQLException {
        return connection.getClientInfo(name);
    }

    @Override
    public Properties getClientInfo() throws SQLException {
        return connection.getClientInfo();
    }

    @Override
    public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
        return connection.createArrayOf(typeName, elements);
    }

    @Override
    public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
        return connection.createStruct(typeName, attributes);
    }

    @Override
    public void setSchema(String schema) throws SQLException {
        connection.setSchema(schema);
    }

    @Override
    public String getSchema() throws SQLException {
        return connection.getSchema();
    }

    @Override
    public void abort(Executor executor) throws SQLException {
        connection.abort(executor);
    }

    @Override
    public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
        connection.setNetworkTimeout(executor, milliseconds);
    }

    @Override
    public int getNetworkTimeout() throws SQLException {
        return connection.getNetworkTimeout();
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return connection.unwrap(iface);
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }
}

• 动态代理

package util;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;

public class DynSimpleConnection implements InvocationHandler {

    private Connection realConnection = null;// Connection real

    private Connection warpConnection = null;

    private DynSimpleDataSource dataSource = null;

    public DynSimpleConnection(DynSimpleDataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Connection bind(Connection source){
        this.realConnection = source;
        this.warpConnection = (Connection) Proxy.newProxyInstance(source.getClass().getClassLoader(),source.getClass().getInterfaces(),this);
        return warpConnection;
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        String methodName = method.getName();
        Object obj = null;
        if(methodName.equals("close")){
            System.out.println("{{close}}");
            // 实现自己的逻辑
            dataSource.closeConn(warpConnection);
        }else {
            obj = method.invoke(realConnection,args);
        }
        return obj;
    }
}

数据库连接池

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;

public class DynSimpleDataSource {
    private static LinkedList<Connection> connPool = null;

    private static int minCount = 5;

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connPool = new LinkedList<Connection>();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public DynSimpleDataSource(){
        for(int i = 0;i<minCount;i++){
            try {
                Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "root");
                DynSimpleConnection dynSimpleConnection = new DynSimpleConnection(this);
                Connection connWarp=dynSimpleConnection.bind(conn);
                connPool.addFirst(connWarp);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public Connection getConnection() throws Exception{
        synchronized (connPool) {
            if(connPool.isEmpty())
                throw new Exception("have no connection now!");
            return connPool.removeLast();
        }
    }

    void closeConn(Connection conn) throws SQLException{
        synchronized (connPool) {
            connPool.addFirst(conn);
        }
    }

    /** * 获取当前数据库连接数 * @return */
    public int getCurrentCount(){
        synchronized (connPool) {
            return connPool.size();
        }
    }

}

创建Connection的类

public class ConnCreate {

    public static DynSimpleDataSource dataSource = new DynSimpleDataSource();

    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void close(Connection conn, Statement stmt, ResultSet rs)
            throws SQLException {

        if (null != rs) {
            rs.close();
        }
        if (null != stmt) {
            stmt.close();
        }
        if (null != conn) {
            dataSource.closeConn(conn);
        }
    }

}

测试

    @Test
    public void createDataSource2() throws Exception{
        for(int i = 0;i<20;i++){
            Connection conn=ConnCreate.getConnection();
            conn.close();
            System.out.println(conn);
        }
    }