java jdbc 多数据源_多数据源使用jdbc连接数据库

问题提出

问题: 多数据源使用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);

}

}

}

参考文献