多数据源解决方案有很多种类，包括中间件mycat、sharding-jdbc，spring内置的多数据源方案dynamic，以及使用AOP实现的自定义多数据源动态切换方案。
网上对mycat和shrding-jdbc的使用介绍比较多多，这里主要了解一下dynamic的简单使用，以及自定义AOP方案的实现。
- dynamic
- 基于AOP实现,继承AbstractRoutingDataSource实现动态切换、
- pom
- 配置文件
- DruidProperties类
- JdbcContextHolder类
- DataSourceConfig类
- DataSourceAspect切片
- DynamicDataSource类 获取实际目标数据源关键类
- 测试
依赖
com.baomidou dynamic-datasource-spring-boot-starter 3.4.1
配置文件
spring:
datasource:
dynamic:
datasource:
base:
username: root
password: **********
url: jdbc:mysql://127.0.0.1:3306/cloudalibaba?serverTimezone=UTC&autoReconnect=true&useUnicode=true&characterEncoding=UTF8&useSSL=false
driver-class-name: com.mysql.jdbc.Driver
oauth:
username: root
password: **********
url: jdbc:mysql://127.0.0.1:3306/oauth2?serverTimezone=UTC&autoReconnect=true&useUnicode=true&characterEncoding=UTF8&useSSL=false
driver-class-name: com.mysql.jdbc.Driver
使用方法
@Override
@DS("base")
public ResultVo
该模式适合业务简单，数据源比较少的情况，并且不是我们想要的动态切换效果
基于AOP实现,继承AbstractRoutingDataSource实现动态切换、 pom配置文件org.springframework spring-jdbc 5.3.9 com.alibaba druid 1.1.22 mysql mysql-connector-java 5.1.47 com.baomidou mybatis-plus-boot-starter 3.1.1
custom:
datasource:
enable: true
# druid配置
druid:
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 50
minIdle: 100
maxActive: 2000
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 30000
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
filters: stat,wall,slf4j
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 数据源
cloud_base:
isbase: true
url: jdbc:mysql://127.0.0.1:3306/cloudalibaba?serverTimezone=UTC&autoReconnect=true&useUnicode=true&characterEncoding=UTF8&useSSL=false
username: root
password: 888888888
driverClassName: com.mysql.cj.jdbc.Driver
validationQuery: select 'x'
oauth:
url: jdbc:mysql://127.0.0.1:3306/oauth2?serverTimezone=UTC&autoReconnect=true&useUnicode=true&characterEncoding=UTF8&useSSL=false
username: root
password: 8888888888
driverClassName: com.mysql.cj.jdbc.Driver
validationQuery: select 'x'
DruidProperties类
接受配置文件中的druid配置
@Data
@ConfigurationProperties(prefix = "custom.datasource.druid")
public class DruidProperties {
private int initialSize;//启动程序时，在连接池中初始化多少个连接
private int minIdle;//回收空闲连接时，将保证至少有minIdle个连接.
private int maxActive;//连接池中最多支持多少个活动会话
private int maxWait;//链接等待时长，连接超时，认为本次请求失败 单位毫秒，设置-1时表示无限等待
private int timeBetweenEvictionRunsMillis;//检查空闲连接的频率，单位毫秒, 非正整数时表示不进行检查
private int minEvictableIdleTimeMillis;//池中某个连接的空闲时长达到 timeBetweenEvictionRunsMillis 毫秒后, 连接池在下次检查空闲连接时，将回收该连接
private boolean testWhileIdle;//当程序请求连接，池在分配连接时，是否先检查该连接是否有效。
private boolean testOnBorrow;//程序申请连接时，检查连接有效性
private boolean testOnReturn;//程序返还链接时，检查链接有效性
private boolean poolPreparedStatements;
private int maxPoolPreparedStatementPerConnectionSize;//每个连接最多缓存多少个SQL
private String filters;//插件配置
private String connectionProperties;//连接属性
}
JdbcContextHolder类
动态数据源持有类，包含一个本地线程共享对象，并提供set get remove 方法，切换数据源时，数据库别名信息从共享对象获取
public class JdbcContextHolder {
//本地线程共享对象
private final static ThreadLocal local = new ThreadLocal<>();
public static void putDataSource(String name){
local.set(name);
}
public static String getDataSource(){
return local.get();
}
public static void removeDataSource(){
local.remove();
}
}
DataSourceConfig类
这里是获取数据库配置信息的类，
@Log4j2
@Configuration
@ConditionalOnProperty(prefix = "custom.datasource", name = "enable", havingValue = "true")
@EnableConfigurationProperties(DruidProperties.class)
public class DataSourceConfig {
private SpringContextUtils springContextUtils;//spring上下文
private DruidProperties druidProperties;//Durid属性配置
private List dataNames = new ArrayList<>();//数据源名称列表
private Map dataSources = new HashMap<>();//数据源列表
private DataSource defaultDataSouce ;//默认数据库
public DataSourceConfig(SpringContextUtils springContextUtils,DruidProperties druidProperties){
this.springContextUtils =springContextUtils ;
this.druidProperties = druidProperties;
}
@Bean(name = "dynamicDataSource")
@Primary
public DataSource dataSource(){
//获取配置文件中custom.datasource开头的所有属性
List dataStrs = springContextUtils.getPropertyList("custom.datasource");
//解析配置 排除关键字段
dataNames = dataStrs.stream()
.map(e->e.split("custom\.datasource\.")[1])//截取custom.datasource.后面的字符串
.filter(e->!e.startsWith("enable"))//排除enable “是否启用多数据源”配置
.filter(e->!e.startsWith("druid."))//排除druid配置
.map(e->e.split("\.")[0])//根据“.”分割获取数据库的名称属性
.distinct()//去重
.collect(Collectors.toList());
//初始化数据源信息
for (String dataName : dataNames) {
String cloudbaseUrl =getDataProp(dataName,"url");
String cloudbasesername =getDataProp(dataName,"username");
String cloudbasePassword =getDataProp(dataName,"password");
String cloudbaseDriverClassName =getDataProp(dataName,"driverClassName");
String cloudbasevalidationQuery =getDataProp(dataName,"validationQuery");
DataSource dataSource =initDruidDataSource(cloudbaseUrl,cloudbasesername,cloudbasePassword,cloudbaseDriverClassName,cloudbasevalidationQuery);
if(springContextUtils.containProperty("custom.datasource."+dataName+".isbase",true)
&& "true".equals(springContextUtils.getProperty("custom.datasource."+dataName+".isbase").toString())){
defaultDataSouce = dataSource;
}
dataSources.put(dataName,dataSource);
}
DynamicDataSource dynamicDataSource = new DynamicDataSource();
//设置默认数据源
dynamicDataSource.setDefaultTargetDataSource(defaultDataSouce);
//配置多个数据源
dynamicDataSource.setTargetDataSources(dataSources);
return dynamicDataSource;
}
private void setDruidOptions(DruidDataSource datasource){
datasource.setInitialSize(druidProperties.getInitialSize());
datasource.setMinIdle(druidProperties.getMinIdle());
datasource.setMaxActive(druidProperties.getMaxActive());
datasource.setMaxWait(druidProperties.getMaxWait());
datasource.setTimeBetweenEvictionRunsMillis(druidProperties.getTimeBetweenEvictionRunsMillis());
datasource.setMinEvictableIdleTimeMillis(druidProperties.getMinEvictableIdleTimeMillis());
datasource.setTestWhileIdle(druidProperties.isTestWhileIdle());
datasource.setTestOnBorrow(druidProperties.isTestOnBorrow());
datasource.setTestOnReturn(druidProperties.isTestOnReturn());
datasource.setPoolPreparedStatements(druidProperties.isPoolPreparedStatements());
datasource.setMaxPoolPreparedStatementPerConnectionSize(druidProperties.getMaxPoolPreparedStatementPerConnectionSize());
try {
datasource.setFilters(druidProperties.getFilters());
} catch (SQLException e) {
log.error("druid configuration initialization filter Exception", e);
}
datasource.setConnectionProperties(druidProperties.getConnectionProperties());
}
@Bean
public PlatformTransactionManager txManager() {
return new DataSourceTransactionManager(dataSource());
}
@Bean(name="druidServlet")
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid
@Bean(name = "filterRegistrationBean")
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("
private DruidDataSource initDruidDataSource(String url,String username,String password,String driverName,String validationQuery){
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);//数据库地址
datasource.setUsername(username);//用户名
datasource.setPassword(password);//密码
datasource.setDriverClassName(driverName);//设置驱动
datasource.setValidationQuery(validationQuery);//检查语句
setDruidOptions(datasource); // 设置druid数据源的属性
return datasource;
}
private String getDataProp(String dataName,String propName){
try {
//查找指定配置信息
if (springContextUtils.containProperty("custom.datasource."+dataName+"."+propName,true)){
return springContextUtils.getProperty("custom.datasource."+dataName+"."+propName).toString();
}else{
throw new RuntimeException(String.format("数据库 %s 缺少属性 %s ",dataName,propName));
}
}catch (Exception ex){
throw new RuntimeException(String.format("数据库 %s 配置信息获取失败 ：%s",dataName,ex.getMessage()));
}
}
}
DataSourceAspect切片
@Aspect
@Order(2)
@Component
@Log4j2
public class DataSourceAspect {
// 切入点 这里在Service层切入 如果ServiceImpl实现需要切换多个数据源，可以将切点设置在Dao层
@Pointcut("execution(* com.dm.cloud.service.*Service..*(..))")
public void dataSourcePointCut(){
//引入切点
}
@Before("dataSourcePointCut()")
private void before(JoinPoint joinPoint){
Object[] args = joinPoint.getArgs(); // 参数值
String[] argNames = ((MethodSignature)joinPoint.getSignature()).getParameterNames(); // 参数名
//参数名和参数值的位置是一一对应的
int targetPos = -1 ;
for(int i=0;i=0){
//设置当前线程的目标数据库
JdbcContextHolder.putDataSource(args[targetPos].toString());
}else{
//没设置数据源信息 使用默认数据源
log.info(">>> current thread " + Thread.currentThread().getName() + " is working with default database");
}
}catch (Exception e){
log.error("change database error: "+e.getMessage());
}
}
@After("dataSourcePointCut()")
public void after(JoinPoint joinPoint){
log.info(">>> datasource dispose");
JdbcContextHolder.removeDataSource();
}
}
DynamicDataSource类 获取实际目标数据源关键类
代码很简单，继承AbstractRoutingDataSource 并实现determineCurrentLookupKey()方法。
getResolvedDataSources()方法可以获取到已经记录起来的datasSource信息，判断传进来的数据库标志是不是已经存在，并打印切换信息。
@Log4j2
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
if (getResolvedDataSources().containsKey(JdbcContextHolder.getDataSource())) {
log.info(">>> current thread " + Thread.currentThread().getName() + " add database【 " + JdbcContextHolder.getDataSource() + " 】 to ThreadLocal");
} else {
log.info(">>> current thread " + Thread.currentThread().getName() + " is working with default database");
}
//从共享线程中获取数据源名称
return JdbcContextHolder.getDataSource();
}
}
- 根据AbstractRoutingDataSource 源码
public Connection getConnection() throws SQLException { return this.determineTargetDataSource().getConnection(); } public Connection getConnection(String username, String password) throws SQLException { return this.determineTargetDataSource().getConnection(username, password); }我们可以看到，在获得链接的时候，会先调用determineTargetDataSource()方法获得DataSourceprotected DataSource determineTargetDataSource() { Assert.notNull(this.resolvedDataSources, "DataSource router not initialized"); Object lookupKey = this.determineCurrentLookupKey(); DataSource dataSource = (DataSource)this.resolvedDataSources.get(lookupKey); if (dataSource == null && (this.lenientFallback || lookupKey == null)) { dataSource = this.resolvedDefaultDataSource; } if (dataSource == null) { throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]"); } else { return dataSource; } } @Nullable protected abstract Object determineCurrentLookupKey();
在determineTargetDataSource()方法中，调用了抽象方法**determineCurrentLookupKey()**来获取要切换的数据库标志。这里就是我们子类要实现的内容，我们在这里直接返回ThreadLocal中的值，添加简单的日志。
测试Dao
@Mapper public interface TestDao extends baseMapper
接口
public interface ITestService {
ResultVo
实现类
@Service
public class TestServiceImpl implements ITestService {
@Autowired
TestDao testDao;
@Override
public ResultVo
控制器
@Log4j2
@RestController
@RequestMapping("/test")
public class TestController {
@Autowired
ITestService testService;
@GetMapping("/Db/{db}")
public ResultVo
打印结果：
传入参数 oauth
2021-11-16 14:43:31.622 [http-nio-15998-exec-1] INFO com.dm.cloud.datasource.DynamicDataSource - >>> current thread http-nio-15998-exec-1 add database【 oauth 】 to ThreadLocal
2021-11-16 14:43:32.065 [http-nio-15998-exec-1] INFO com.dm.cloud.datasource.DataSourceAspect - >>> datasource dispose
2021-11-16 14:43:32.067 [http-nio-15998-exec-1] INFO com.dm.cloud.controller.TestController - tables [oauth_client_details, users]
传入参数oooo
2021-11-16 14:43:38.818 [http-nio-15998-exec-3] INFO com.dm.cloud.datasource.DynamicDataSource - >>> current thread http-nio-15998-exec-3 is working with default database
2021-11-16 14:43:38.824 [http-nio-15998-exec-3] INFO com.dm.cloud.datasource.DataSourceAspect - >>> datasource dispose
2021-11-16 14:43:38.824 [http-nio-15998-exec-3] INFO com.dm.cloud.controller.TestController - tables [account, branch_table, config_info, config_info_aggr, config_info_beta, config_info_tag, config_tags_relation, distributed_lock, global_table, group_capacity, his_config_info, lock_table, orders, product, roles, tenant_capacity, tenant_info, undo_log, users]
可以通过地址 http://localhost:xxxx/druid/datasource.html 查看连接状态、sql执行记录等信息。
