自定义多数据源JDBC连接池

背景

公司需要对各个客户的数据库进行统一管理，故涉及到对多个不同数据库进行连接，传统的数据库连接池无法满足需求，故结合网上的自定义数据库连接池，进行的改进，代码如下

注意

由于代码处于公司环境，有直接使用肯定是会有报错，相信这种简单的修补是绝大部分开发同志的基本功，我也就没有单独大环境去调整，重在整体架构

代码一：自定义连接类

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * 连接包装类
 */
public class ConnectionWapper implements Connection {

    private static final Logger log = LoggerFactory.getLogger(ConnectionWapper.class);

    // Connection接口的实现类对象的引用
    private Connection connection;

    // 存放连接包装对象的池子的引用
    private LinkedBlockingQueue<ConnectionWapper> pool;

    ///激活时间（用于判断该连接是否长时间未使用）
    private long activeTime;

    public ConnectionWapper(Connection connection, LinkedBlockingQueue<ConnectionWapper> pool) {
        this.connection = connection;
        this.pool = pool;
        this.activeTime = new Date().getTime();
    }

    /**
     * 检测连接是否超时
     *
     * @return
     */
    public boolean checkTimeOut() {
        if ((new Date().getTime() - this.activeTime) > JdbcConnectionManager.CONN_TIMEOUT) {
            return true;
        }
        return false;
    }

    /**
     * 关闭连接
     *
     * @throws SQLException
     */
    public void closeConnection() throws SQLException {
        this.connection.close();
    }

    /**
     * 重写close方法，实际执行连接回收操作
     *
     * @throws SQLException
     */
    @Override
    public void close() throws SQLException {
        log.info("-------------------------------------开始执行连接回收操作-------------------------------------");
        this.activeTime = new Date().getTime();
        pool.add(this);
        log.info("-------------------------------------连接回收完毕-------------------------------------");
    }

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

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

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

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

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

    @Override
    public boolean getAutoCommit() throws SQLException {
        return this.connection.getAutoCommit();
    }

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

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

    @Override
    public boolean isClosed() throws SQLException {
        return this.connection.isClosed();
    }

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

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

    @Override
    public boolean isReadOnly() throws SQLException {
        return this.connection.isReadOnly();
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    @Override
    public boolean isValid(int timeout) throws SQLException {
        return this.connection.isValid(timeout);
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

代码二：连接池主类

import com.alibaba.druid.util.StringUtils;
import com.cestc.servicedexchange.enums.DbEngineEnum;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.LinkedBlockingQueue;

/**
 * 自定义多数据源连接池
 */
@Component
public class JdbcConnectionManager extends Thread {

    private static final Logger log = LoggerFactory.getLogger(JdbcConnectionManager.class);

    // 5分钟检测一次连接
    private static final long CONN_CHECK_TIME = 5 * 60 * 1000L;

    // jdbc连接超时时间(3分钟未使用中断)
    public static final long CONN_TIMEOUT = 3 * 60 * 1000L;

    //总的jdbc连接池
    private static ConcurrentHashMap<String, LinkedBlockingQueue<ConnectionWapper>> JDBC_POOL = new ConcurrentHashMap<>();

    //一个连接最大线程数
    private static Map<String, Integer> CONNECTION_NUMLIMIT = new HashMap<>();

    //最大连接数
    private static final Integer MAX_CONNECTION_NUM = 10;

    /**
     * 定时任务检测连接池里面长时间未使用的连接
     */
    @Override
    public void run() {
        while (true) {
            try {
                Thread.sleep(CONN_CHECK_TIME);
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
                log.error("sleep error", e);
            }
            int size = 0;
            for (String key : JDBC_POOL.keySet()) {
                LinkedBlockingQueue<ConnectionWapper> queue = JDBC_POOL.get(key);
                log.info("---------------------开始校验连接池中是否有过期连接，size:【{}】 key:【{}】 ---------------------", queue.size(), key);
                synchronized (JdbcConnectionManager.class) {
                    Integer num = 0;
                    // 头部的肯定是时间最早的，只需要判断头部的时间是否超时即可
                    while (queue.peek() != null && queue.peek().checkTimeOut()) {
                        ConnectionWapper connectionWapper = queue.poll();
                        try {
                            log.info("关闭空闲jdbc连接:{}", key);
                            connectionWapper.closeConnection();
                        } catch (SQLException e) {
                            log.error("关闭连接失败", e);
                        }
                        num--;
                    }
                    CONNECTION_NUMLIMIT.put(key, CONNECTION_NUMLIMIT.get(key) + num);
                }
                size += queue.size();
            }
            log.info("----------------------本次校验完毕，连接池总连接数量为【{}】----------------------", size);
        }
    }

    /**
     * 获取jdbc连接
     *
     * @param dbType
     * @param jdbcUrl
     * @param username
     * @param password
     * @return
     */
    public static ConnectionWapper getConnection(String dbType, String jdbcUrl, String username, String password) {
        try {
            ConnectionWapper connectionWapper = null;
            String key = dbType + "-" + jdbcUrl;
            // 从总的连接池获取
            LinkedBlockingQueue<ConnectionWapper> queue = JDBC_POOL.get(key);
            synchronized (JdbcConnectionManager.class) {
                if (queue == null) {
                    log.info("初始化key为【{}】的连接队列", key);
                    queue = new LinkedBlockingQueue<>();
                    JDBC_POOL.put(key, queue);
                    CONNECTION_NUMLIMIT.put(key, 0);
                }
                Integer limitNum = CONNECTION_NUMLIMIT.get(key);
                log.info("key为【{}】对应的limitNum = 【{}】", key, limitNum);
                if (!queue.isEmpty()) {
                    log.info("key为【{}】的连接池中有可用的连接，直接获取", key);
                    return queue.take();
                }
                if (limitNum >= MAX_CONNECTION_NUM) {
                    log.info("key为【{}】超过了最大连接数，进入等待队列", key);
                    connectionWapper = JDBC_POOL.get(key).take();
                } else {
                    log.info("key为【{}】无可用的连接，开始创建连接", key);
                    connectionWapper = createConnnection(dbType, jdbcUrl, username, password);
                    CONNECTION_NUMLIMIT.put(key, CONNECTION_NUMLIMIT.get(key) + 1);
                }
            }
            return connectionWapper;
        } catch (Exception e) {
            log.error("getConnection error", e);
        }
        return null;
    }

    /**
     * 创建jdbc连接
     *
     * @param dbType
     * @param jdbcUrl
     * @param username
     * @param password
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    private static ConnectionWapper createConnnection(String dbType, String jdbcUrl, String username, String password) throws SQLException, ClassNotFoundException {
        String key = dbType + "-" + jdbcUrl;
        DbEngineEnum dbEngineEnum = null;
        for (DbEngineEnum tmp : DbEngineEnum.values()) {
            if (StringUtils.equalsIgnoreCase(dbType, tmp.getName())) {
                dbEngineEnum = tmp;
                break;
            }
        }
        if (dbEngineEnum == null) {
            return null;
        }
        Class.forName(dbEngineEnum.getDriverClass());
        Properties properties = new Properties();
        properties.put("user", username);
        properties.put("password", password);
        properties.put("remarksReporting", "true");
        Connection conn = DriverManager.getConnection(jdbcUrl, properties);
        return new ConnectionWapper(conn, JDBC_POOL.get(key));
    }
}