SpringBoot配置多数据源

- 日理万妓 2022-01-31 04:01 370阅读 0赞

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