SpringBoot配置多数据源

 

 

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