1、背景
很多时候,项目中需要实现多数据源切换的需求,本文简单明了的总结如何在项目中实现多数据源的灵活切换
2、自定义多数据源切换注解
/**
* 自定义多数据源切换注解
* <p>
* 优先级:先方法,后类,如果方法覆盖了类上的数据源类型,以方法的为准,否则以类上的为准
*
* @author fangchen
*/
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface DataSource {
/**
* 切换数据源名称
*/
public DataSourceType value() default DataSourceType.ODSDB;
}
3、定义数据源配置属性加载类
package com.sfpay.merchant.query.config.properties;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
/**
* druid 配置属性
*
* @author fangchen
*/
@Configuration
public class DruidProperties {
@Value("${spring.datasource.druid.initial-size}")
private int initialSize;
@Value("${spring.datasource.druid.min-idle}")
private int minIdle;
@Value("${spring.datasource.druid.max-active}")
private int maxActive;
@Value("${spring.datasource.druid.max-wait}")
private int maxWait;
@Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
private int minEvictableIdleTimeMillis;
// @Value("${spring.datasource.druid.maxEvictableIdleTimeMillis}")
//private int maxEvictableIdleTimeMillis;
@Value("${spring.datasource.druid.validation-query}")
private String validationQuery;
@Value("${spring.datasource.druid.test-while-idle}")
private boolean testWhileIdle;
@Value("${spring.datasource.druid.test-on-borrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.druid.test-on-return}")
private boolean testOnReturn;
public DruidDataSource dataSource(DruidDataSource datasource) {
/** 配置初始化大小、最小、最大 */
datasource.setInitialSize(initialSize);
datasource.setMaxActive(maxActive);
datasource.setMinIdle(minIdle);
/** 配置获取连接等待超时的时间 */
datasource.setMaxWait(maxWait);
/** 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 */
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
/** 配置一个连接在池中最小、最大生存的时间,单位是毫秒 */
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
//datasource.setMaxEvictableIdleTimeMillis(maxEvictableIdleTimeMillis);
/**
* 用来检测连接是否有效的sql,要求是一个查询语句,常用select 'x'。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
*/
datasource.setValidationQuery(validationQuery);
/** 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。 */
datasource.setTestWhileIdle(testWhileIdle);
/** 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。 */
datasource.setTestOnBorrow(testOnBorrow);
/** 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。 */
datasource.setTestOnReturn(testOnReturn);
return datasource;
}
}
4、定义动态数据源类
package com.sfpay.merchant.query.config.properties;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
/**
* 动态数据源
*
* @author chen
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSources);
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.getDataSourceType();
}
}
5、数据源切换处理
package com.sfpay.merchant.query.config.properties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 数据源切换处理
*
* @author chen
*/
public class DynamicDataSourceContextHolder {
public static final Logger log = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);
/**
* 使用ThreadLocal维护变量,ThreadLocal为每个使用该变量的线程提供独立的变量副本,
* 所以每一个线程都可以独立地改变自己的副本,而不会影响其它线程所对应的副本。
*/
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
/**
* 设置数据源的变量
*/
public static void setDataSourceType(String dsType) {
log.info("切换到{}数据源", dsType);
CONTEXT_HOLDER.set(dsType);
}
/**
* 获得数据源的变量
*/
public static String getDataSourceType() {
return CONTEXT_HOLDER.get();
}
/**
* 清空数据源变量
*/
public static void clearDataSourceType() {
CONTEXT_HOLDER.remove();
}
}
6、数据源配置处理类
package com.sfpay.merchant.query.config;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.config.ConfigFilter;
import com.alibaba.druid.filter.config.ConfigTools;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.sfpay.merchant.common.util.spring.SpringUtils;
import com.sfpay.merchant.query.common.enums.DataSourceType;
import com.sfpay.merchant.query.config.properties.DruidProperties;
import com.sfpay.merchant.query.config.properties.DynamicDataSource;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.util.*;
/**
* druid 配置多数据源
*
* @author chen
*/
@Configuration
public class DruidConfig {
@Bean
@ConfigurationProperties("spring.datasource.druid.odsdb")
public DataSource odsdbDataSource(DruidProperties druidProperties) {
DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
//注意如果用了druid对数据库密码做了j
dataSource.getConnectProperties().setProperty("config.decrypt","true");
dataSource.getConnectProperties().setProperty("config.decrypt.key","MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIqNTV3TdwQJz4u8AassP5hmC0ppcEvl/U/Mt44N+VI5wnl5ZgemUGuO4Yz1n9xlEZsJKEfudW5nz+270wCCYAsCAwEAAQ==");
ConfigFilter configFilter=new ConfigFilter();
dataSource.setName("dm_alluser");
dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
List<Filter> filters = new ArrayList<>();
filters.add(configFilter);
dataSource.setProxyFilters(filters);
return druidProperties.dataSource(dataSource);
}
@Bean
@ConfigurationProperties("spring.datasource.druid.dorisdb")
@ConditionalOnProperty(prefix = "spring.datasource.druid.dorisdb", name = "enabled", havingValue = "true")
public DataSource dorisdbDataSource(DruidProperties druidProperties) {
DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
dataSource.setName("dm_alluser1");
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
return druidProperties.dataSource(dataSource);
}
@Bean(name = "dynamicDataSource")
@Primary
public DynamicDataSource dataSource(DataSource odsdbDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceType.ODSDB.name(), odsdbDataSource);
setDataSource(targetDataSources, DataSourceType.DORISDB.name(), "dorisdbDataSource");
return new DynamicDataSource(odsdbDataSource, targetDataSources);
}
/**
* 设置数据源
*
* @param targetDataSources 备选数据源集合
* @param sourceName 数据源名称
* @param beanName bean名称
*/
public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName) {
try {
DataSource dataSource = SpringUtils.getBean(beanName);
targetDataSources.put(sourceName, dataSource);
} catch (Exception e) {
}
}
}
7、测试类
package com.sfpay.merchant.query.controller;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.sfpay.merchant.query.common.enums.DataSourceType;
import com.sfpay.merchant.query.config.annotation.DataSource;
import com.sfpay.merchant.query.dal.dao.AccountWaterMapper;
import com.sfpay.merchant.query.dal.entity.AccountWaterDO;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.List;
/**
* @describe:
* @author:01410843
* @Date 2022/11/18 10:27
**/
@Controller
@RequestMapping("/test")
public class TestController {
@Resource
private AccountWaterMapper accountWaterMapper;
@RequestMapping(value = "/odsdb")
@ResponseBody
public List<AccountWaterDO> odsdb(HttpServletRequest request){
QueryWrapper<AccountWaterDO> queryWrapper =new QueryWrapper();
queryWrapper.lambda().eq(AccountWaterDO::getBusinessSn,"83262000002001923301");
List<AccountWaterDO> list=accountWaterMapper.selectList(queryWrapper);
return list;
}
@RequestMapping(value = "/dorisdb")
@ResponseBody
@DataSource(value = DataSourceType.DORISDB)
public List<AccountWaterDO> dorisdb(HttpServletRequest request){
QueryWrapper<AccountWaterDO> queryWrapper =new QueryWrapper();
queryWrapper.lambda().eq(AccountWaterDO::getBusinessSn,"20210915103002159572");
List<AccountWaterDO> list=accountWaterMapper.selectList(queryWrapper);
return list;
}
}
8、apllication.rpoperties配置项
#ods数据源(ORACLE)
spring.datasource.druid.odsdb.url=jdbc:oracle:thin:@//xxx:1521/odsdb
spring.datasource.druid.odsdb.username=dm_alluser
spring.datasource.druid.odsdb.password=QnZ86RsUeM0r3by9SwWJhwWwbDmC3kpPW3YAOtsj7tySxAl5MkcOkFGQaZWMcYHSEpRZFMDs8MO1/fxxh3PihA==
#doris数据源(MYSQL)
spring.datasource.druid.dorisdb.url=jdbc:mysql://xxxx:9030/fis_pay_bdp?useUnicode=true
spring.datasource.druid.dorisdb.username=fis_pay_bdp
spring.datasource.druid.dorisdb.password=fis_pay_bdp@123
spring.datasource.druid.dorisdb.enabled=true
#连接池的设置
spring.datasource.druid.name=dm_alluser
#初始化时建立物理连接的个数
spring.datasource.druid.initial-size=300
#最小连接池数量
spring.datasource.druid.min-idle=300
#最大连接池数量 maxIdle已经不再使用
spring.datasource.druid.max-active=300
#获取连接时最大等待时间,单位毫秒
spring.datasource.druid.max-wait=120000
#申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
spring.datasource.druid.test-while-idle=true
#既作为检测的间隔时间又作为testWhileIdel执行的依据
spring.datasource.druid.time-between-eviction-runs-millis=120000
#销毁线程时检测当前连接的最后活动时间和当前时间差大于该值时,关闭当前连接
spring.datasource.druid.min-evictable-idle-time-millis=120000
#用来检测连接是否有效的sql 必须是一个查询语句
#mysql中为 select 'x'
#oracle中为 select 1 from dual
spring.datasource.druid.validation-query=select 1 from dual
#申请连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
spring.datasource.druid.test-on-borrow=false
#归还连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
spring.datasource.druid.test-on-return=false
#当数据库抛出不可恢复的异常时,抛弃该连接
#spring.datasource.druid.exception-sorter=true
#是否缓存preparedStatement,mysql5.5+建议开启
#spring.datasource.druid.pool-prepared-statements=true
#当值大于0时poolPreparedStatements会自动修改为true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20