前言
一般项目中都会连接多个数据库，那么程序如何来管理多个数据库的连接呢，一般我们会采用Druid或者基于Spring的AbstractRoutingDataSource来实现多数据源的切换也管理。但是多数据源的原理是什么呢，其实这一切都离不开原生jdbc的支持。下面就使用原生的jdbc来实现一个动态多数据源。
自定义数据源
package com.cz.jdbc;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;
/**
* @program: Reids
* @description: 自定义数据源
* @author: Cheng Zhi
* @create: 2023-05-06 14:39
**/
public class JefDataSource implements DataSource {
private String driver;
private String url;
private String userName;
private String passWord;
public JefDataSource (String driver, String url, String userName, String passWord) {
this.driver = driver;
this.url = url;
this.userName = userName;
this.passWord = passWord;
}
@Override
public Connection getConnection() throws SQLException {
return getConnection(userName, passWord);
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
initDriver();
return DriverManager.getConnection(url,username, password);
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
private void initDriver() {
if (!driver.isEmpty()) {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
System.out.println("加载驱动失败");
e.printStackTrace();
}
}
}
}
自定义动态数据源类
package com.cz.jdbc;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.logging.Logger;
/**
* @program: Reids
* @description: 自定义动态数据源头
* @author: Cheng Zhi
* @create: 2023-05-06 14:48
**/
public class JefDynamicDataSource implements DataSource {
private static Map<String, DataSource> dataSourcePool = new ConcurrentHashMap<String, DataSource>();
public JefDynamicDataSource() {
}
public void addDataSource(String dbKey, DataSource dataSource) {
dataSourcePool.put(dbKey, dataSource);
}
@Override
public Connection getConnection() throws SQLException {
String dbKey = DbKeyHolder.getDbKey();
return dataSourcePool.get(dbKey).getConnection();
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
}
自定义数据源切换类
package com.cz.jdbc;
/**
* @program: Reids
* @description: Dbkey设置类
* @author: Cheng Zhi
* @create: 2023-05-06 14:57
**/
public class DbKeyHolder {
private static ThreadLocal<String> threadLocal = new ThreadLocal();
public static void setDbKey(String dbKey) {
threadLocal.set(dbKey);
}
public static String getDbKey() {
return threadLocal.get();
}
}
自定义数据驱动枚举
package com.cz.jdbc;
/**
* 数据库驱动枚举
*/
public enum DriverType {
Mysql("mysql", "com.mysql.cj.jdbc.Driver"),
Oracle("oracle", "oracle.jdbc.driver.OracleDriver");
private String code;
private String value;
DriverType(String code, String value) {
this.code = code;
this.value = value;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
测试
package com.cz.jdbc;
import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @program: Reids
* @description:
* @author: Cheng Zhi
* @create: 2023-05-06 15:12
**/
public class Main {
public static void main(String[] args) throws SQLException {
Main main = new Main();
DataSource dataSource = main.initDataSource();
DbKeyHolder.setDbKey("oracle");
PreparedStatement preparedStatement = dataSource.getConnection().prepareStatement("select count(1) from AID2.TAXPAYER_PER_GROUP_MAPPING");
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()) {
System.out.println(resultSet.getString("count(1)"));
}
DbKeyHolder.setDbKey("mysql");
PreparedStatement preparedStatement1 = dataSource.getConnection().prepareStatement("select * from test_case where case_name like 'do_queryUsableBalance' or busi_code like 'do_queryUsableBalance' or case_name_zh like 'do_queryUsableBalance'");
ResultSet resultSet1 = preparedStatement1.executeQuery();
int sum = 0;
while(resultSet1.next()) {
sum ++;
}
System.out.println(sum);
}
private DataSource initDataSource() {
String driver1 = DriverType.Mysql.getValue();
String url1 = "jdbc:mysql://127.0.0.1:3306/test?serverTimezone=Asia/Shanghai";
String userName1 = "root";
String passWord1 = "123456";
DataSource dataSource1 = new JefDataSource(driver1, url1, userName1, passWord1);
String driver2 = DriverType.Oracle.getValue();
String url2 = "jdbc:oracle:thin:@127.0.0.1:1521/test";
String userName2 = "root";
String passWord2 = "123456";
DataSource dataSource2 = new JefDataSource(driver2, url2, userName2, passWord2);
JefDynamicDataSource dynamicDataSource = new JefDynamicDataSource();
dynamicDataSource.addDataSource("mysql", dataSource1);
dynamicDataSource.addDataSource("oracle", dataSource2);
return dynamicDataSource;
}
}
java中好多技术都是万变不离其宗的，比如说mybatis和hibernate这类orm框架其核心原理都是基于jdbc去封装实现的。比如mybatis的流式查询和hibernate的迭代器查询模式，底层都是依赖的stmt.setFetchSize(Integer.MIN_VALUE);这个核心方法。框架只是为了提高开发人员的开发效率。所以也不能太迷信框架。而且有些时候使用框架会使得程序的执行效率变得更加低下。
