1,AbstractRoutingDataSource    

Spring boot provides AbstractRoutingDataSource to select the current data source according to user-defined rules, so that we can set the data source before executing the query. The data source that can be dynamically routed is implemented before each database query operation. Its abstract method determineCurrentLookupKey() determines which data source to use.

It roughly means:
The getConnection() method of AbstractRoutingDataSource calls different target data sources according to the lookup key. It is usually implemented through (but not necessarily) the transaction context bound by some threads.

The core logic of multi data source dynamic switching of AbstractRoutingDataSource is: when the program is running, the data source data source is dynamically woven into the program through AbstractRoutingDataSource to switch data sources flexibly.
The dynamic switching of multiple data sources based on AbstractRoutingDataSource can realize the separation of reading and writing. The disadvantage of this is also obvious, and the data sources cannot be added dynamically.

Implementation logic:

Define the DynamicDataSource class, inherit the abstract class AbstractRoutingDataSource, and implement the determineCurrentLookupKey() method.
Multiple configured data sources will be placed in targetDataSources and defaultTargetDataSource of AbstractRoutingDataSource, and then the data sources will be copied to resolvedDataSources and resolvedDefaultDataSource respectively through afterpropertieset() method.
When calling the getConnection() method of AbstractRoutingDataSource, first call the determineTargetDataSource() method to return the DataSource for getConnection().

2. Concrete implementation

2.1. Create a new springboot project and import a pom file

 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!--Swagger-UI API Document production tools-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.7.0</version>
        </dependency>

        <!--Configure the required for reading and writing-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <!--mybatis plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>

    </dependencies>

2.2. Use mybatis plus to generate entity and xml codes
2.3. Add the scan mapper annotation - @ MapperScan("com.cdzs.mapper") on the spring boot boot class
2.4. In the configuration file application Add configuration information of multiple (I'm here two) data sources in YML

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    druid:
      first:
        url: jdbc:mysql://172.16.7.35:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimeZone=Asia/shanghai&allowPublicKeyRetrieval=true
        username: root
        password: 123456
      second:
        url: jdbc:mysql://172.16.7.139:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimeZone=Asia/shanghai&allowPublicKeyRetrieval=true
        username: root
        password: 123456

3. Integrated dynamic data source module

3.1. Create a new annotation CurDataSource and specify the data source to be used

import java.lang.annotation.*;

/**
 * Multi data source annotation
 * <p/>
 * Specify the data source to use
 */
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface CurDataSource {

    String name() default "";

}

3.2. Create a new constant and store it in the acquired data source

/**
 * Add multiple data sources and configure them here
 */
public interface DataSourceNames {

    String FIRST = "first";

    String SECOND = "second";

}

3.3. Create a new class DynamicDataSource

DynamicDataSource extends the AbstractRoutingDataSource abstract class of Spring and overrides the determineCurrentLookupKey() method

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.util.Map;

/**
 * Extend the AbstractRoutingDataSource abstract class of Spring and override the determineCurrentLookupKey method
 * Dynamic data source
 * determineCurrentLookupKey() Method determines which data source to use
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    /**
     * ThreadLocal It is used to provide thread local variables. In a multithreaded environment, it can ensure that the variables in each thread are independent of those in other threads.
     * In other words, ThreadLocal can create a [separate variable copy] for each thread, which is equivalent to the private static type variable of the thread.
     */
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    /**
     * Before deciding which data source to use, you need to configure the information of multiple data sources and the default data source information
     *
     * @param defaultTargetDataSource Default data source
     * @param targetDataSources       Target data source
     */
    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) {
        CONTEXT_HOLDER.set(dataSource);
    }

    public static String getDataSource() {
        return CONTEXT_HOLDER.get();
    }

    public static void clearDataSource() {
        CONTEXT_HOLDER.remove();
    }

}

3.4. Create a new multi data source configuration class

Configure the information of multiple data sources and generate multiple data sources (I have two here, corresponding to the data sources defined in application.properties)

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * Configure multiple data sources
 */
@Configuration
public class DynamicDataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.druid.first")
    public DataSource firstDataSource(){

        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.druid.second")
    public DataSource secondDataSource(){

        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public DynamicDataSource dataSource(DataSource firstDataSource, DataSource secondDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>(5);
        targetDataSources.put(DataSourceNames.FIRST, firstDataSource);
        targetDataSources.put(DataSourceNames.SECOND, secondDataSource);
        return new DynamicDataSource(firstDataSource, targetDataSources);
    }

}

3.5. Adopt aop mode, use annotation mode to switch where the data source needs to be modified, and then modify the content of ThreadLocal in all aspects

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

/**
 * Multiple data sources, facet processing class
 */
@Slf4j
@Aspect
@Component
public class DataSourceAspect implements Ordered {

    @Pointcut("@annotation(com.cdzs.datasource.CurDataSource)")
    public void dataSourcePointCut() {

    }

    @Around("dataSourcePointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();

        CurDataSource ds = method.getAnnotation(CurDataSource.class);
        if (ds == null) {
            DynamicDataSource.setDataSource(DataSourceNames.FIRST);
            log.debug("set datasource is " + DataSourceNames.FIRST);
        } else {
            DynamicDataSource.setDataSource(ds.name());
            log.debug("set datasource is " + ds.name());
        }

        try {
            return point.proceed();
        } finally {
            DynamicDataSource.clearDataSource();
            log.debug("clean datasource");
        }
    }

    @Override
    public int getOrder() {
        return 1;
    }
}

3.6. Add data source configuration on startup class

Because the data source is self generated, the original data source configuration automatically assembled during spring boot startup should be removed.

import com.cdzs.datasource.DynamicDataSourceConfig;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.Import;

@MapperScan("com.cdzs.mapper")
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@Import({DynamicDataSourceConfig.class})
public class MinioProjectApplication {
    public static void main(String[] args) {
        SpringApplication.run(MinioProjectApplication.class,args);
    }
}

3.7 test data source switching

Two queries are defined in service to query two databases respectively:

import com.cdzs.entity.SysUser;
import com.baomidou.mybatisplus.extension.service.IService;

/**
 * <p>
 * System user service class
 * </p>
 *
 * @author xiaohe
 * @since 2019-06-04
 */
public interface SysUserService extends IService<SysUser> {

    SysUser findUserByFirstDb(long id);

    SysUser findUserBySecondDb(long id);

    SysUser selectFindAll();

}

3.8. Implementation class: because the first data source is used by default, no annotation is required. The annotation @ CurDataSource(name = DataSourceNames.SECOND) needs to be added when using data source 2

import com.cdzs.datasource.CurDataSource;
import com.cdzs.datasource.DataSourceNames;
import com.cdzs.entity.SysUser;
import com.cdzs.mapper.SysUserMapper;
import com.cdzs.service.SysUserService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * <p>
 * System user service implementation class
 * </p>
 */
@Service
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements SysUserService {

    @Autowired
    private SysUserMapper sysUserMapper;

    @Override
    public SysUser findUserByFirstDb(long id) {
        return this.baseMapper.selectById(id);
    }

    @CurDataSource(name = DataSourceNames.SECOND)
    @Override
    public SysUser findUserBySecondDb(long id) {
        return this.baseMapper.selectById(id);
    }

    //Configure master library or read data from library
    @CurDataSource(name = DataSourceNames.SECOND)
    @Override
    public SysUser selectFindAll() {
        return this.sysUserMapper.selectFindAll();
    }

}

3.9 testing

import com.cdzs.entity.SysUser;
import com.cdzs.service.SysUserService;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
public class MinioProjectApplicationTests {

    @Autowired
    private SysUserService userService;

    @Test
    public void contextLoads() {
        SysUser user = userService.getById(1);
        log.info(user.toString());
    }

    @Test
    public void test() {
        SysUser user = userService.findUserByFirstDb(1);
        log.info("First database : [{}]", user.toString());
        SysUser user2 = userService.findUserBySecondDb(1);
        log.info("Second database : [{}]", user2.toString());
    }
}

4.0 creating tables and initializing sql:

-- System user
CREATE TABLE `sys_user`
(
  `user_id`        bigint      NOT NULL AUTO_INCREMENT,
  `username`       varchar(50) NOT NULL COMMENT 'user name',
  `password`       varchar(100) COMMENT 'password',
  `salt`           varchar(20) COMMENT 'salt',
  `email`          varchar(100) COMMENT 'mailbox',
  `mobile`         varchar(100) COMMENT 'cell-phone number',
  `status`         tinyint COMMENT 'Status 0: Disabled 1: normal',
  `create_user_id` bigint(20) COMMENT 'creator ID',
  `create_time`    datetime COMMENT 'Creation time',
  PRIMARY KEY (`user_id`),
  UNIQUE INDEX (`username`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 COMMENT ='System user';

-- Initial data 
INSERT INTO `sys_user` (`user_id`, `username`, `password`, `salt`, `email`, `mobile`, `status`, `create_user_id`,
                        `create_time`)
VALUES ('1', 'admin', '9ec9750e709431dad22365cabc5c625482e574c74adaebba7dd02f1129e4ce1d', 'YzcmCZNvbXocrsz9dm8e',
        'root@renren.io', '13612345678', '1', '1', '2016-11-11 11:11:11');

Change the username of library 2 to admin2222

The final test results are printed as follows:

4. About things

AbstractRoutingDataSource only supports single database transactions, that is, switching data sources should be executed before starting transactions. The spring DataSourceTransactionManager manages transactions. When a transaction is started, the data source will be cached in the DataSourceTransactionObject object for subsequent transaction operations such as commit rollback.

The reason for the failure of dynamic switching of multiple data sources is that after the transaction is started, the data sources cannot be switched at will, that is, a transaction corresponds to a data source.

The traditional Spring management transaction is operated in the Service business layer, so the operation of changing the data source should be carried out before this operation. That is, the data source switching operation is placed in the Controller layer, but this operation will cause confusion in the Controller layer code.

Therefore, the solution is to turn on the transaction management in the data persistence (Dao layer) and switch the data source in the business layer, so that the data source switching can be carried out smoothly before the transaction is started, and there will be no switching failure.