spring本身是支持多数据源动态切换的,AbstractRoutingDataSource这个抽象类就是spring提供的一个数据源路由的一个入口,该抽象类暴露了一个determineCurrentLookupKey()的方法,该方法返回值是Object,该返回值作为key去取Map中的DataSource。
AbstractRoutingDataSource
getConnection()
determineTargetDataSource() 从Map中通过key获取DataSource
determineCurrentLookupKey() 获取key
1.创建一个线程线程安全的Holder来切换Key
public class DynamicDataSourceHolder {
public static ThreadLocal<DataSourceKey> keyThreadLocal = new ThreadLocal<>();
public static void clear(){
keyThreadLocal.remove();
}
public static void set(DataSourceKey key){
keyThreadLocal.set(key);
}
public static DataSourceKey get(){
DataSourceKey key = keyThreadLocal.get();
return null==key?DataSourceKey.DB:key;
}
}
2.继承AbstractRoutingDataSource设置key
public class DynamicRoutingDataSource extends AbstractRoutingDataSource{
@Nullable
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceHolder.get();
}
}
3.编写Config来初始化DataSource
@Configuration
public class DynamicDatasourceConfig {
@Autowired
ApplicationContext applicationContext;
@Bean("druid_db")//必须加上该注解,否则 @ConfigurationProperties无效
@ConfigurationProperties(prefix = "dynamic-datasource.druid-datasources.db")
public DataSource db(StandardEnvironment env){
DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
return common(env,druidDataSource);
}
@Bean("druid_db1")
@ConfigurationProperties(prefix = "dynamic-datasource.druid-datasources.db1")
public DataSource db1(StandardEnvironment env){
DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
return common(env,druidDataSource);
}
@Bean("druid_db2")
@ConfigurationProperties(prefix = "dynamic-datasource.druid-datasources.db2")
public DataSource db2(StandardEnvironment env){
DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
return common(env,druidDataSource);
}
@Bean("dataSource")
public DataSource dynamicDataSource(StandardEnvironment env) {
DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
Map<Object,Object> map = new HashMap<>();
map.put(DataSourceKey.DB,applicationContext.getBean("druid_db"));
map.put(DataSourceKey.DB1,applicationContext.getBean("druid_db1"));
map.put(DataSourceKey.DB2,applicationContext.getBean("druid_db2"));
dynamicRoutingDataSource.setDefaultTargetDataSource(applicationContext.getBean("druid_db"));
dynamicRoutingDataSource.setTargetDataSources(map);
return dynamicRoutingDataSource;
}
public DataSource common(StandardEnvironment env, DruidDataSource druidDataSource){
Properties properties = new Properties();
PropertySource<?> appProperties = env.getPropertySources().get("applicationConfig: [classpath:/application.yml]");
Map<String,Object> source = (Map<String, Object>) appProperties.getSource();
properties.putAll(source);
druidDataSource.configFromPropety(properties);
return druidDataSource;
}
}
4.通过aop动态去切换key
该注解确定的该方法使用哪一个数据源
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface TargetDataSource {
DataSourceKey value() default DataSourceKey.DB;
}
public enum DataSourceKey {
DB,DB1,DB2,DB3,DB4;
}
一个是拦截特定的方法和拦截有TargetDataSource注解的方法去动态切换
@Aspect
@Component
@Order(-100)//提高优先级
public class DynamicDataSourceAop {
@Pointcut(value = "execution(public * com.yzz.boot..*.mapper ..*.*(..))")
public void defaultDataSource(){}
@Before(value = "defaultDataSource()")
public void setDefaultDataSource(){
}
//没有注解,就选择默认的数据源
@Before(value = "@annotation(dataSource)&&defaultDataSource()")
public void setDynamicDataSource(TargetDataSource dataSource){
if (null == dataSource){
System.err.println("设置默认数据源"+DataSourceKey.DB);
DynamicDataSourceHolder.set(DataSourceKey.DB);
}else {
System.err.println("切换数据源"+dataSource.value());
DynamicDataSourceHolder.set(dataSource.value());
}
}
//清除该线程当前的数据
@After(value = "defaultDataSource()&&@annotation(com.yzz.boot.dyConfig.ann.TargetDataSource)")
public void clean(){
System.err.println("清除当前线程的数据源");
DynamicDataSourceHolder.clear();
}
}
5.Mapper通过方法上通过注解去动态选择数据源
@Mapper
public interface TestMapper {
@TargetDataSource(value = DataSourceKey.DB1)
@Select("select * from t_es_test limit 5")
List<HashMap> getAll();
@TargetDataSource(value = DataSourceKey.DB2)
@Select("select * from t_es_test limit 5")
List<HashMap> getAll1();
}
6.程序入口去除默认的连接池的配置类
//去除默认的连接池
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
//扫描响应的包
@MapperScan("com.yzz.boot.*.mapper")
public class BootApplication {
public static void main(String[] args) {
SpringApplication.run(BootApplication.class);
}
}
7.配置文件
spring:
profiles:
active: system
dynamic-datasource:
druid:
filters: stat
maxActive: 20
initialSize: 1
maxWait: 30000
minIdle: 10
maxIdle: 15
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
maxOpenPreparedStatements: 20
removeAbandoned: true
removeAbandonedTimeout: 1800
logAbandoned: true
druid-datasources:
db:
url: jdbc:mysql://192.168.1.12:3306/yzz
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
db1:
url: jdbc:mysql://192.168.1.12:3306/yzz
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
db2:
url: jdbc:mysql://192.168.1.12:3306/yzz
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
jdbc-conn:
url: jdbc:mysql://192.168.1.12:3306/yzz
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
8. 假如需要配置mybatisplus
package com.seawaterbt.ssm.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.MybatisConfiguration;
import com.baomidou.mybatisplus.entity.GlobalConfiguration;
import com.baomidou.mybatisplus.mapper.LogicSqlInjector;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.plugins.PerformanceInterceptor;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import com.seawaterbt.ssm.enums.DataSourceEnum;
import com.seawaterbt.ssm.multiple.MultipleDataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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 org.springframework.context.annotation.Profile;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
@MapperScan("com.seawaterbt.ssm.mapper*")
public class MyBatiesPlusConfiguration {
/*
* 分页插件,自动识别数据库类型
* 多租户,请参考官网【插件扩展】
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
// 开启 PageHelper 的支持
paginationInterceptor.setLocalPage(true);
return paginationInterceptor;
}
/**
* SQL执行效率插件
*/
@Bean
@Profile({"dev","qa"})// 设置 dev test 环境开启
public PerformanceInterceptor performanceInterceptor() {
PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
performanceInterceptor.setMaxTime(1000);
performanceInterceptor.setFormat(true);
return performanceInterceptor;
}
@Bean(name = "db1")
@ConfigurationProperties(prefix = "spring.datasource.druid.db1" )
public DataSource db1() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "db2")
@ConfigurationProperties(prefix = "spring.datasource.druid.db2" )
public DataSource db2() {
return DruidDataSourceBuilder.create().build();
}
/**
* 动态数据源配置
* @return
*/
@Bean
@Primary
public DataSource multipleDataSource(@Qualifier("db1") DataSource db1, @Qualifier("db2") DataSource db2) {
MultipleDataSource multipleDataSource = new MultipleDataSource();
Map< Object, Object > targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceEnum.DB1.getValue(), db1);
targetDataSources.put(DataSourceEnum.DB2.getValue(), db2);
//添加数据源
multipleDataSource.setTargetDataSources(targetDataSources);
//设置默认数据源
multipleDataSource.setDefaultTargetDataSource(db1);
return multipleDataSource;
}
@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(multipleDataSource(db1(),db2()));
//sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*/*Mapper.xml"));
MybatisConfiguration configuration = new MybatisConfiguration();
//configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
sqlSessionFactory.setConfiguration(configuration);
sqlSessionFactory.setPlugins(new Interceptor[]{ //PerformanceInterceptor(),OptimisticLockerInterceptor()
paginationInterceptor() //添加分页功能
});
//sqlSessionFactory.setGlobalConfig(globalConfiguration());
return sqlSessionFactory.getObject();
}
/*@Bean
public GlobalConfiguration globalConfiguration() {
GlobalConfiguration conf = new GlobalConfiguration(new LogicSqlInjector());
conf.setLogicDeleteValue("-1");
conf.setLogicNotDeleteValue("1");
conf.setIdType(0);
//conf.setMetaObjectHandler(new MyMetaObjectHandler());
conf.setDbColumnUnderline(true);
conf.setRefresh(true);
return conf;
}*/
}
MyBatiesPlusConfiguration.java