introduce

With the development of business, in addition to the separation of service modules, database read-write separation is also a common optimization method. Scheme used AbstractRoutingDataSource and mybatis plugin to the selection of dynamic data source the scheme’s main reason is not to need to change the original business code, very friendly

Note: The demo used Mybatis -Plus, and the actual use of Mybatis is the same. The demo used postgres database, and the actual example of any type of primary and secondary backup database is the same. The Demo used Alibaba druid data source, In fact, other types of data sources copy the same codeCopy the code

The environment

First, we need two database instances, one master and one slave. All the writes, we do all the reads on the master node, we do all the reads on the slave node

Note that for a read/write transaction, the read operations within the transaction should not be performed on the slave node, and all operations should be replicated on the master nodeCopy the code

First, run two pg instances, where port 15432 corresponds to the master node and port 15433 corresponds to the slave node:




docker run \
	--name pg-master \
	-p 15432:5432 \
	--env 'PG_PASSWORD=postgres' \
	--env 'REPLICATION_MODE=master' \
	--env 'REPLICATION_USER=repluser' \
   	--env 'REPLICATION_PASS=repluserpass' \
	-d sameersbn/postgresql:10-2

docker run \
	--name pg-slave \
	-p 15433:5432 \
	--link pg-master:master \
	--env 'PG_PASSWORD=postgres' \
	--env 'REPLICATION_MODE=slave' \
	--env 'REPLICATION_SSLMODE=prefer' \
	--env 'REPLICATION_HOST=master' \
	--env 'REPLICATION_PORT=5432' \
	--env 'REPLICATION_USER=repluser' \
   	--env 'REPLICATION_PASS=repluserpass' \
	-d sameersbn/postgresql:10-2

Copy the code

implementation

The whole implementation mainly consists of three parts:

  • Configuring two data sources
  • implementationAbstractRoutingDataSourceTo use the data source dynamically
  • implementationmybatis pluginTo dynamically select the data source

Configuring a Data Source

Configure the database connection information into the application.yml file

spring:
  mvc:
    servlet:
      path: /api

datasource:
  write:
    driver-class-name: org.postgresql.Driver
    url: "${DB_URL_WRITE:jdbc:postgresql://localhost:15432/postgres}"
    username: "${DB_USERNAME_WRITE:postgres}"
    password: "${DB_PASSWORD_WRITE:postgres}"
  read:
    driver-class-name: org.postgresql.Driver
    url: "${DB_URL_READ:jdbc:postgresql://localhost:15433/postgres}"
    username: "${DB_USERNAME_READ:postgres}"
    password: "${DB_PASSWORD_READ:postgres}"


mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true

Copy the code

Write Write data source, corresponding to port 15432 on the master node Read data source, corresponding to port 15433 on the slave node

Inject two data source information as DataSourceProperties:

@Configuration
public class DataSourcePropertiesConfig {

    @Primary
    @Bean("writeDataSourceProperties")
    @ConfigurationProperties("datasource.write")
    public DataSourceProperties writeDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean("readDataSourceProperties")
    @ConfigurationProperties("datasource.read")
    public DataSourceProperties readDataSourceProperties() {
        returnnew DataSourceProperties(); }}Copy the code

Implement AbstractRoutingDataSource

Spring provides AbstractRoutingDataSource, provides the function of dynamic selection of data source, after replacing the original single data source separation can be realized, speaking, reading and writing:

@Component
public class CustomRoutingDataSource extends AbstractRoutingDataSource {

    @Resource(name = "writeDataSourceProperties")
    private DataSourceProperties writeProperties;

    @Resource(name = "readDataSourceProperties")
    private DataSourceProperties readProperties;


    @Override
    public void afterPropertiesSet() {
        DataSource writeDataSource = 
            writeProperties.initializeDataSourceBuilder().type(DruidDataSource.class).build();
        DataSource readDataSource = 
            readProperties.initializeDataSourceBuilder().type(DruidDataSource.class).build();
        
        setDefaultTargetDataSource(writeDataSource);

        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put(WRITE_DATASOURCE, writeDataSource);
        dataSourceMap.put(READ_DATASOURCE, readDataSource);
        setTargetDataSources(dataSourceMap);

        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        String key = DataSourceHolder.getDataSource();

        if (key == null) {
             // default datasource
            return WRITE_DATASOURCE;
        }

        returnkey; }}Copy the code

AbstractRoutingDataSource internal maintains a Map < Object, the Object > Map in the initialization process, we will write, read two data sources to join this call the Map data sources: DetermineCurrentLookupKey () method returns the key of the need to use the data source

The data source key used by the current thread is maintained in the DataSourceHolder class:




public class DataSourceHolder {

    public static final String WRITE_DATASOURCE = "write";
    public static final String READ_DATASOURCE = "read";

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


    public static void putDataSource(String dataSource) {
        local.set(dataSource);
    }

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

    public static void clearDataSource() { local.remove(); }}Copy the code

Implement mybatis plugin

Above mentioned the source of data for the current thread using the corresponding key, the key need to mybatis plugin depending on the type of SQL to determine MybatisDataSourceInterceptor class:

@Component
@Intercepts({
        @Signature(type = Executor.class, method = "update",
                args = {MappedStatement.class, Object.class}),
        @Signature(type = Executor.class, method = "query",
                args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "query",
                args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class,
                        CacheKey.class, BoundSql.class})})
public class MybatisDataSourceInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        boolean synchronizationActive = TransactionSynchronizationManager.isSynchronizationActive();
        if(! synchronizationActive) { Object[] objects = invocation.getArgs(); MappedStatement ms = (MappedStatement) objects[0];if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
                if(! ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) { DataSourceHolder.putDataSource(DataSourceHolder.READ_DATASOURCE);return invocation.proceed();
                }
            }
        }

        DataSourceHolder.putDataSource(DataSourceHolder.WRITE_DATASOURCE);
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
    }
}

Copy the code

Only when not in a transaction, and invoke SQL is the select type, in the heart of the DataSourceHolder data source is set to read other cases, AbstractRoutingDataSource will use the default write data source

At this point, the project can automatically switch between reading and writing data sources without modifying the original business code. Finally, demo is provided to use the dependent version




<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> The < version > 2.1.7. RELEASE < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.2.2</version> </dependency> <dependency> <groupId>com.alibaba</groupId> < artifactId > druid - spring - the boot - starter < / artifactId > < version > 1.1.9 < / version > < / dependency > < the dependency > < the groupId > com. Baomidou < / groupId > < artifactId > mybatisplus - spring - the boot - starter < / artifactId > < version > 1.0.5 < / version > </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus</artifactId> < version > 2.1.9 < / version > < / dependency > < the dependency > < groupId >. IO springfox < / groupId > < artifactId > springfox - swagger2 < / artifactId > < version > 2.8.0 < / version > < / dependency > < the dependency > < the groupId > IO. Springfox < / groupId > < artifactId > springfox swagger - UI < / artifactId > < version > 2.8.0 < / version > < / dependency > < the dependency > < groupId > org. Projectlombok < / groupId > < artifactId > lombok < / artifactId > < version > 1.16.20 < / version > </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId>  <scope>test</scope>
    </dependency>
</dependencies>
Copy the code