问题提出
问题: 多数据源使用jdbc连接数据库，每次连接都是new新连接
for循环多次时new会导致连接创建失败
分析问题
使用数据库连接池技术解决重复创建的问题
具体实现
jdbc连接数据库
package com.keep.account.util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DbUtil {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//
for(int i = 0; i < 3; i++) {
conn = MultiDataSourcePollUtil.getConnection("test");
// 执行查询
stmt = conn.createStatement();
String sql;
sql = "SELECT id, name FROM user WHERE id IN (1,2,3)";
ResultSet rs = stmt.executeQuery(sql);
// 展开结果集数据库
while (rs.next()) {
// 通过字段检索
int id = rs.getInt("id");
String name = rs.getString("name");
// 输出数据
System.out.print("ID: " + id);
System.out.print(", 站点名称: " + name);
System.out.print("\n");
}
// 完成后关闭
rs.close();
}
stmt.close();
conn.close();
}catch(SQLException se){
// 处理 JDBC 错误
se.printStackTrace();
}catch(Exception e){
// 处理 Class.forName 错误
e.printStackTrace();
}finally{
// 关闭资源
try{
if(stmt!=null) stmt.close();
}catch(SQLException se2){
}// 什么都不做
try{
if(conn!=null) conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
}
}
多数据源缓存(其中配置可拿出来作为参数传入)
package com.keep.account.util;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
/**
* 多数据源datasource
*/
public class MultiDataSourcePollUtil {
private static Map dataSources = new ConcurrentHashMap<>();
private static ComboPooledDataSource getDataSource(String connName) {
ComboPooledDataSource dataSource = dataSources.get(connName);
if (dataSource == null) {
synchronized (MultiDataSourcePollUtil.class) {
dataSource = dataSources.get(connName);
if (dataSource == null) {
System.out.println("create datasource");
dataSource = createDataSource();
dataSources.put(connName, dataSource);
}
}
}
return dataSource;
}
private static ComboPooledDataSource createDataSource() {
// TODO: 2019/7/8 考虑配置让初始化类处理
String dbUrl = "jdbc:mysql://localhost:3306/privateTool?serverTimezone=GMT&useUnicode=true&characterEncoding=utf8&useSSL=false";
String dbUser = "***";
String dbPwd = "****";
String driver = "com.mysql.jdbc.Driver";
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setUser(dbUser);
dataSource.setPassword(dbPwd);
dataSource.setJdbcUrl(dbUrl);
try {
dataSource.setDriverClass(driver);
} catch (PropertyVetoException e) {
e.printStackTrace();
}
dataSource.setInitialPoolSize(10);
dataSource.setMinPoolSize(10);
dataSource.setMaxPoolSize(100);
dataSource.setMaxStatements(100);
dataSource.setMaxIdleTime(60);
dataSource.setAcquireIncrement(10);
return dataSource;
}
public static Connection getConnection(String connName) {
try {
ComboPooledDataSource dataSource = getDataSource(connName);
return dataSource.getConnection();
} catch (Exception e) {
throw new RuntimeException("can not get sms database connection ", e);
}
}
}
参考文献
