springboot 动态切换数据源

灰太狼 2023-10-04 19:41 114阅读 0赞

### springboot 动态切换数据源 ###

1.  引入Alibaba的druid组件

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.2.3</version>
            </dependency>
               <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>transmittable-thread-local</artifactId>
                <version>2.2.0</version>
            </dependency>

1.  在spring项目的yml文件中，添加数据库信息，我的项目添加了taos数据库，当然博友们可以去掉即可

spring:
      datasource:
        tdengine-server:
          jdbc-url: jdbc:TAOS://127.0.0.1:6030/valid_time?timezone=Asia/Shanghai&charset=utf-8
          driver-class-name: com.taosdata.jdbc.TSDBDriver
          username: root
          password: taosdata
      druid:
        datasource:
          master:
            jdbc-url: jdbc:mysql://127.0.0.1:3306/valid_project?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
            driver-class-name: com.mysql.jdbc.Driver
            username: root
            password: Shutd0wn
          slave:
            jdbc-url: jdbc:mysql://127.0.0.1:3306/valid_project_test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
            driver-class-name: com.mysql.jdbc.Driver
            username: root
            password: Shutd0wn

1.  创建DataSourceProperties类

@ConfigurationProperties(prefix = "spring.druid.datasource")
    @Component
    @Getter
    @Setter
    public class DataSourceProperties {
        
        private Map<String,String> master;
        private Map<String,String> slave;
    }

1.  创建数据源切换配置类DynamicDataSourceConfig

@Configuration
    @MapperScan(basePackages = {
        "com.sinux.sinuxproject.dao.mysql"})
    public class DynamicDataSourceConfig {
        
        @Resource
        private DataSourceProperties dataSourceProperties;
        @Bean
        public DataSource master(@Autowired DataSourceProperties dataSourceProperties){
        
            DruidDataSource druidDataSource = new DruidDataSource();
            Map<String, String> master = dataSourceProperties.getMaster();
            druidDataSource.setUsername(master.get("username"));
            druidDataSource.setPassword(master.get("password"));
            druidDataSource.setUrl(master.get("jdbc-url"));
            //其他参数配置 省略
            return druidDataSource;
        }
    
        @Bean
        public DataSource slave(@Autowired DataSourceProperties dataSourceProperties){
        
            DruidDataSource druidDataSource = new DruidDataSource();
            Map<String, String> slave = dataSourceProperties.getSlave();
            druidDataSource.setUsername(slave.get("username"));
            druidDataSource.setPassword(slave.get("password"));
            druidDataSource.setUrl(slave.get("jdbc-url"));
            //其他参数配置 省略
            return druidDataSource;
        }
    
        @Bean("dataSource")
        @Primary
        public DynamicDataSource dataSource(DataSource master,DataSource slave){
        
            Map<Object,Object>map = new HashMap<>(4);
            map.put("master",master);
            map.put("slave",slave);
            return new DynamicDataSource(master,map);
        }
        @Primary
        @Bean("sqlSessionFactoryBean")
        public SqlSessionFactory sqlSessionFactoryBean(@Qualifier("dataSource") DynamicDataSource dynamicDataSource) throws Exception {
        
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(dynamicDataSource);
            sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/mysql/**/*Mapper.xml"));
            org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
            configuration.setMapUnderscoreToCamelCase(true);
            configuration.setDefaultFetchSize(100);
            configuration.setDefaultStatementTimeout(30);
            sqlSessionFactoryBean.setConfiguration(configuration);
            return sqlSessionFactoryBean.getObject();
        }
        @Bean(name = "mysqlTransactionManager")
        @Primary
        public DataSourceTransactionManager mysqlTransactionManager(@Qualifier("dataSource")DynamicDataSource dynamicDataSource) {
        
            return new DataSourceTransactionManager(dynamicDataSource);
        }
        @Bean(name = "mysqlSqlSessionTemplate")
        @Primary
        public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("sqlSessionFactoryBean")SqlSessionFactory sqlSessionFactory) throws Exception {
        
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

1.  创建DynamicDataSource继承AbstractRoutingDataSource，重写determineCurrentLookupKey方法，实现数据源的切换

public class DynamicDataSource extends AbstractRoutingDataSource {
        
    	//异步线程变量共享
        private static final TransmittableThreadLocal<String> contextHolder = new TransmittableThreadLocal<>();
    
        public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        
            super.setDefaultTargetDataSource(defaultTargetDataSource);
            super.setTargetDataSources(targetDataSources);
            super.afterPropertiesSet();
        }
    
    
        @Override
        protected Object determineCurrentLookupKey() {
        
            return getDataSource();
        }
    
        public static void setDataSource(String dataSource) {
        
            contextHolder.set(dataSource);
        }
    
        public static String getDataSource() {
        
            System.out.println(contextHolder.get());
            String key = contextHolder.get()==null?"master":contextHolder.get();
            return key;
        }
    
        public static void clearDataSource() {
        
            contextHolder.remove();
        }
    }

1.  排除启动类的自动配置

@SpringBootApplication(exclude= {
        DataSourceAutoConfiguration.class})

1.  使用，只需要在使用前切换数据源即可，我是使用的aop，进行拦截判断

DynamicDataSource.setDataSource("slave");