package com.eureka.dataplatform.serviceapi.config.JdbcConnectionConfig;
import com.eureka.dataplatform.common.enums.DWTypeEnum;
import com.eureka.dataplatform.serviceapi.exception.BusinessException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.CommandLineRunner;
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;
/**
 * @author kongjj
 * @date 2021/10/9 16:47
 */
@Component
public class JdbcConnectionManager implements CommandLineRunner {

    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连接池
    public 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;

    @Value("${dw-type}")
    private String dwType;
    /**
     * 定时任务检测连接池里面长时间未使用的连接
     */
    @Override
    public void run(String... args) throws Exception {
        if(DWTypeEnum.valueOf(dwType).equals(DWTypeEnum.SQL_SERVER)) {
            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 dataSourceId
     * @param jdbcUrl
     * @param username
     * @param password
     * @return
     */
    public static ConnectionWapper getConnection(String dataSourceId, String jdbcUrl, String username, String password) {
        try {
            ConnectionWapper connectionWapper = null;
            String key = dataSourceId;
            // 从总的连接池获取
            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(dataSourceId, jdbcUrl, username, password);
                    CONNECTION_NUMLIMIT.put(key, CONNECTION_NUMLIMIT.get(key) + 1);
                }
            }
            return connectionWapper;
        } catch (Exception se) {
            BusinessException.GET_CONNECT_EXCEPTION.doThrow(se, se.getMessage());
        }
        return null;
    }

    /**
     * 创建jdbc连接
     *
     * @param dataSourceId
     * @param jdbcUrl
     * @param username
     * @param password
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */

    private static ConnectionWapper createConnnection(String dataSourceId, String jdbcUrl, String username, String password) throws SQLException, ClassNotFoundException {
        String key = dataSourceId;
        //Class.forName("");
        Properties properties = new Properties();
        properties.put("user", username);
        properties.put("password", password);
        Connection conn = DriverManager.getConnection(jdbcUrl, properties);
        return new ConnectionWapper(conn, JDBC_POOL.get(key));
    }
}
package com.eureka.dataplatform.serviceapi.config.JdbcConnectionConfig;

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;
/**
 * @author yourname
 * @date 2021/10/9 16:48
 */
public class ConnectionWapper implements Connection {
    private static final Logger log = LoggerFactory.getLogger(ConnectionWapper.class);

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

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

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

    public Connection getConnection() {
        return connection;
    }

    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方法，实际执行连接回收操作
     */
    @Override
    public void close() {
        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);
    }
}


使用示例：

ConnectionWapper connectionWapper  = getConnection(datasourceId,jdbcUrl,user,password);
Connection connection = connectionWapper.getConnection();
try (PreparedStatement ps = connection.prepareStatement(sql)) {
    ResultSet rs = ps.executeQuery();
    ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
    int columnCount = md.getColumnCount();
    while (rs.next()) {
        Map<String, Object> rowData = new HashMap<String, Object>();
        for (int i = 1; i <= columnCount; i++) {
            rowData.put(md.getColumnName(i), rs.getObject(i));
        }
        list.add(rowData);
    }
}catch (SQLException se) {
    BusinessException.DW_SQL_EXCEPTION.doThrow(se, se.getMessage());
}finally {
    connectionWapper.close();
}