• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

自定义多数据源连接池

开发技术 开发技术 4小时前 1次浏览
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();
}




程序员灯塔
转载请注明原文链接:自定义多数据源连接池
喜欢 (0)