Spring boot realizes dynamic switching of multiple data sources

I Multi data source switching

1. In the configuration file, configure three different data sources as follows (the project uses Druid database connection pool):

spring:
  datasource:
    druid:
      # 数据库连接1
      datasource1:
        driver-class-name: com.MysqL.cj.jdbc.Driver
        url: jdbc:MysqL://127.0.0.1/datasource_1?autoReconnect=true&roundRobinLoadBalance=true&useUnicode=yes&characterEncoding=utf8&connectionCollation=utf8_general_ci&serverTimezone=GMT%2B8
        username: root
        password: 
        initialSize: 5
        minIdle: 5
        maxActive: 20
        # 配置获取连接等待超时的时间
        maxWait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        timeBetweenEvictionRunsMillis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        minevictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        # 打开PSCache，并且指定每个连接上PSCache的大小
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,wall
        # 通过connectProperties属性来打开mergesql功能；慢sql记录
        connectionProperties: druid.stat.mergesql=true;druid.stat.slowsqlMillis=5000
      
      # 数据库连接2
      datasource2:
        driver-class-name: com.MysqL.cj.jdbc.Driver
        url: jdbc:MysqL://127.0.0.1/datasource_2?autoReconnect=true&roundRobinLoadBalance=true&useUnicode=yes&characterEncoding=utf8&connectionCollation=utf8_general_ci&serverTimezone=GMT%2B8
        username: root
        password: 
        initialSize: 5
        minIdle: 5
        maxActive: 20
        # 配置获取连接等待超时的时间
        maxWait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        timeBetweenEvictionRunsMillis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        minevictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        # 打开PSCache，并且指定每个连接上PSCache的大小
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,wall
        # 通过connectProperties属性来打开mergesql功能；慢sql记录
        connectionProperties: druid.stat.mergesql=true;druid.stat.slowsqlMillis=5000
      
      # # 数据库连接3
      datasource3:
        driver-class-name: com.MysqL.cj.jdbc.Driver
        url: jdbc:MysqL://127.0.0.1/datasource_3?useUnicode=yes&characterEncoding=utf8&connectionCollation=utf8_general_ci&serverTimezone=GMT%2B8
        username: root
        password: 
        initialSize: 5
        minIdle: 5
        maxActive: 20
        # 配置获取连接等待超时的时间
        maxWait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        timeBetweenEvictionRunsMillis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        minevictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        # 打开PSCache，并且指定每个连接上PSCache的大小
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,wall
        # 通过connectProperties属性来打开mergesql功能；慢sql记录
        connectionProperties: druid.stat.mergesql=true;druid.stat.slowsqlMillis=5000

2. The definition class dynamicdatasource inherits abstractroutingdatasource and implements the determinecurrentlookupkey method, which can realize the dynamic switching of the database, as follows:

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSource();
    }
}

3. Define a tool class that can set the variables of the current thread to set the corresponding data source name:

/**
 * @Decription: 动态数据源配置
 **/

@Slf4j
public class DataSourceContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    /** 
     * @Description: 设置数据源类型 
     * @param dbType 数据源名称
     * @return void
     */  
    public static void setDataSource(String dbType){
        log.info("====>切换到 ["+dbType+"] 数据源");
        contextHolder.set(dbType);
    }
    
    /** 
     * @Description: 获取数据源类型
     * @return String
     */
    public static String getDataSource(){
        return contextHolder.get();
    }

    /**
     * @Description: 清除本地线程使用的数据源，使用默认的数据源
     * @return void
     **/
    public static void clearDataSource(){
        contextHolder.remove();
    }
}

4. Create a new configuration class to configure multiple data sources and annotation transactions

/**
 * @Decription: 动态多数据源配置
 **/
@Configuration
public class MutiplyDataSource {
    /**
     * 数据源1
     * @return
     */
    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.druid.datasource1")
    public DataSource dataSource1(){
        return new DruidDataSource();
    }

    /**
     * 数据源2
     * @return
     */
    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource.druid.datasource2")
    public DataSource dataSource2(){
        return new DruidDataSource();
    }
    
    /**
     * 数据源3
     * @return
     */
    @Bean(name = "dataSource3")
    @ConfigurationProperties(prefix = "spring.datasource.druid.datasource3")
    public DataSource dataSource3(){
        return new DruidDataSource();
    }

    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        //配置默认数据源
        dynamicDataSource.setDefaultTargetDataSource(dataSource1());

        //配置多数据源
        HashMap<Object,Object> dataSourceMap = new HashMap();
        dataSourceMap.put(ContextConst.DataSourceType.DATASOURCE_1.name(),dataSource1());
        dataSourceMap.put(ContextConst.DataSourceType.DATASOURCE_2.name(),dataSource2());
        dataSourceMap.put(ContextConst.DataSourceType.DATASOURCE_3.name(),dataSource3());
        dynamicDataSource.setTargetDataSources(dataSourceMap);
        return dynamicDataSource;
    }

    /**
     * 配置@Transactional注解事务
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }
}

Remember to add the transaction configuration annotation @ enabletransactionmanagement in the entry function and exclude the configuration of automatic injection data source:

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
@EnableTransactionManagement
@EnableAsync
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class,args);
    }

}

5. Define an AOP processing class to switch the data source before the database transaction is started, as follows:

/**
 * @Decription: 动态数据源配置 @Order(-10) ：在事务开启前切换数据源
 **/
@Component
@Aspect
@Order(-10)
@Slf4j
public class DynamicDataSourceAspect {
    @Autowired
    private EntityManager entityManager;

    /**
     * 服务类中的方法开始前，如果有@DataSource，会断开之前的连接，关闭之前的事务(一个事务对应一个数据源)
     **/
    @Before("execution(* com.jcy.demo.service.*.*(..)) && @annotation(com.jcy.demo.annotation.DataSource)")
    public void before(JoinPoint point){
        try {
            DataSource annotationOfClass = point.getTarget().getClass().getAnnotation(DataSource.class);
            String methodName = point.getSignature().getName();
            Class[] parameterTypes = ((MethodSignature) point.getSignature()).getParameterTypes();
            Method method = point.getTarget().getClass().getmethod(methodName,parameterTypes);
            DataSource methodAnnotation = method.getAnnotation(DataSource.class);
            methodAnnotation = methodAnnotation == null ? annotationOfClass:methodAnnotation;
            ContextConst.DataSourceType dataSourceType = methodAnnotation != null && methodAnnotation.value() !=null ? methodAnnotation.value() :ContextConst.DataSourceType.DATASOURCE_1;
            DataSourceContextHolder.setDataSource(dataSourceType.name());
            SessionImplementor session = entityManager.unwrap(SessionImplementor.class);
            //最关键的一句代码， 手动断开连接，不用重新设置 ，会自动重新设置连接
            session.disconnect();
        } catch (NoSuchMethodException e) {
            log.error("切换动态数据源发生异常",e);
        } catch(Exception e){
        	log.error("切换动态数据源发生异常",e);
        }
    }

    /**
     * 服务类的方法结束后，会清除数据源，此时会变更为默认的数据源
     **/
    @After("execution(* com.jcy.demo.service.*.*(..)) && @annotation(com.jcy.demo.annotation.DataSource)")
    public void after(JoinPoint point){
        DataSourceContextHolder.clearDataSource();
    }
}

Use the order attribute of AOP to set the execution order, so that the data source can be switched before the transaction is started.

II Business code test implementation

1. The service example of the second data source is as follows:

@Service
@Slf4j
public class FoodService {
    @Autowired
    private FoodRepository foodRepository;
    
    @DataSource(ContextConst.DataSourceType.DATASOURCE_2)
    @Transactional(rollbackFor = Exception.class)
    public Food getFoodByName(String name){
    	log.info("访问第二个数据源，获得零食信息",name);
        return foodRepository.findByFoodName(name);
    }
}

2. Write an interface in the controller to switch three data sources:

@Api(tags = "多数据源demo")
@RestController
@Slf4j
public class DemoController {
	
    @Autowired
    private UserService userService;
	
    @Autowired
    private AddressService addressService;
	
    @Autowired
    private FoodService foodService;

    @ApiOperation("多数据源切换")
    @RequestMapping(value = "/changeDatasource")
    public Ret index() {
        Ret result = Ret.create();
        // 访问默认数据源
        User user = userService.findByAccount("喵");
        // 访问第二个数据源
        Food food = foodService.getFoodByName("鸡腿");
        // 访问第三个数据源
        Address address = addressService.saveAddress(1L);
        log.info("用户为:{},爱吃的零食为:{},更改后的地址为:{}",user.getUsername(),food.getFoodName(),address.getAddress());
        result.setCodeAndMsg(200);
        return result;
    }
}

The code has been uploaded to GIT. The path is:

Multi data source dynamic switching demo code