Spring Master Slave Databas Configuration and Dynamic Datasource Switching Principles

In large applications, it is a common design pattern to configure master-slave databases and use read-write separation. In Spring applications, to implement read-write separation, it is best to not make changes to existing code, but to support it transparently at the bottom.

Spring has a built-in AbstractRoutingDataSource that can configure multiple data sources into a Map and then, depending on the key, return different data sources. Because AbstractRoutingDataSource is also a DataSource interface, the application can set the key first, and the code that accesses the database can get the corresponding real data source from AbstractRoutingDataSource to access the specified database. Its structure looks like this:

   ┌───────────────────────────┐
   │        controller         │
   │  set routing-key = "xxx"  │
   └───────────────────────────┘
                 │
                 ▼
   ┌───────────────────────────┐
   │        logic code         │
   └───────────────────────────┘
                 │
                 ▼
   ┌───────────────────────────┐
   │    routing datasource     │
   └───────────────────────────┘
                 │
       ┌─────────┴─────────┐
       │                   │
       ▼                   ▼
┌─────────────┐     ┌─────────────┐
│ read-write  │     │  read-only  │
│ datasource  │     │ datasource  │
└─────────────┘     └─────────────┘
       │                   │
       ▼                   ▼
┌─────────────┐     ┌─────────────┐
│             │     │             │
│  Master DB  │     │  Slave DB   │
│             │     │             │
└─────────────┘     └─────────────┘

Step 1: Configure multiple data sources

First, we configure two data sources in SpringBoot, the second of which is ro-datasource

spring:
  datasource:
    jdbc-url: jdbc:mysql://localhost/test
    username: rw
    password: rw_password
    driver-class-name: com.mysql.jdbc.Driver
    hikari:
      pool-name: HikariCP
      auto-commit: false
      ...
  ro-datasource:
    jdbc-url: jdbc:mysql://localhost/test
    username: ro
    password: ro_password
    driver-class-name: com.mysql.jdbc.Driver
    hikari:
      pool-name: HikariCP
      auto-commit: false

In the development environment, there is no need to configure a master-slave database. It is only necessary to set two users to the database, one rw with read and write permissions and one ro with only SELECT permissions, which simulates the read and write separation of the master and slave databases in the production environment.

In the SpringBoot configuration code, we initialize two data sources:

@SpringBootApplication
public class MySpringBootApplication {
    /**
     * Master data source.
     */
    @Bean("masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    DataSource masterDataSource() {
       logger.info("create master datasource...");
        return DataSourceBuilder.create().build();
    }

    /**
     * Slave (read only) data source.
     */
    @Bean("slaveDataSource")
    @ConfigurationProperties(prefix = "spring.ro-datasource")
    DataSource slaveDataSource() {
        logger.info("create slave datasource...");
        return DataSourceBuilder.create().build();
    }

    ...
}

Step 2: Write RoutingDataSource

Then, we use Springs built-in RoutingDataSource to proxy two real data sources into one dynamic data source:

public class RoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return "masterDataSource";
    }
}

For this RoutingDataSource, you need to configure it in SpringBoot and set it as the primary data source:

@SpringBootApplication
public class MySpringBootApplication {
    @Bean
    @Primary
    DataSource primaryDataSource(
            @Autowired @Qualifier("masterDataSource") DataSource masterDataSource,
            @Autowired @Qualifier("slaveDataSource") DataSource slaveDataSource
    ) {
        logger.info("create routing datasource...");
        Map<Object, Object> map = new HashMap<>();
        map.put("masterDataSource", masterDataSource);
        map.put("slaveDataSource", slaveDataSource);
        RoutingDataSource routing = new RoutingDataSource();
        routing.setTargetDataSources(map);
        routing.setDefaultTargetDataSource(masterDataSource);
        return routing;
    }
    ...
}

Now, the RoutingDataSource is configured, but the routing is written dead, i.e. it always returns the masterDataSource

Now comes the question: how to store the dynamically selected key and where to set the key?

In the threaded model of Servlet, it is most appropriate to use ThreadLocal to store the key, so we write a RoutingDataSourceContext to set and dynamically store the key

public class RoutingDataSourceContext implements AutoCloseable {

    // holds data source key in thread local:
    static final ThreadLocal<String> threadLocalDataSourceKey = new ThreadLocal<>();

    public static String getDataSourceRoutingKey() {
        String key = threadLocalDataSourceKey.get();
        return key == null ? "masterDataSource" : key;
    }

    public RoutingDataSourceContext(String key) {
        threadLocalDataSourceKey.set(key);
    }

    public void close() {
        threadLocalDataSourceKey.remove();
    }
}

Then, modify the RoutingDataSource to get the key code as follows:

public class RoutingDataSource extends AbstractRoutingDataSource {
    protected Object determineCurrentLookupKey() {
        return RoutingDataSourceContext.getDataSourceRoutingKey();
    }
}

This way, somewhere, for example inside a Controller method, the Key of the DataSource can be set dynamically:

@Controller
public class MyController {
    @Get("/")
    public String index() {
        String key = "slaveDataSource";
        try (RoutingDataSourceContext ctx = new RoutingDataSourceContext(key)) {
            // TODO:
            return "html... www.sobyte.net";
        }
    }
}

Up to this point, we have successfully implemented dynamic routing access to the database.

This method works, but where you need to read from the database, you need to add a big try (RoutingDataSourceContext ctx = ...) {} code, which is very inconvenient to use. Is there a way to simplify it?

Yes!

If we think about it, the declarative transaction management provided by Spring only requires a @Transactional() annotation, placed on a Java method, which automatically has a transaction.

We can also write a similar @RoutingWith("slaveDataSource") annotation and put it on a Controller method, which automatically selects the corresponding data source internally. The code should look like this:

@Controller
public class MyController {
    @Get("/")
    @RoutingWith("slaveDataSource")
    public String index() {
        return "html... www.sobyte.net";
    }
}

This is the easiest way to automatically implement dynamic data source switching without modifying the application logic at all, and only adding annotations where necessary.

To write less code in the application, we have to do a little more work at the bottom: we have to use a mechanism similar to Spring’s implementation of declarative transactions, i.e. dynamic data source switching with AOP.

Implementing this is also very simple, write a RoutingAspect and use AspectJ to implement an Around intercept:

@Aspect
@Component
public class RoutingAspect {
    @Around("@annotation(routingWith)")
    public Object routingWithDataSource(ProceedingJoinPoint joinPoint, RoutingWith routingWith) throws Throwable {
        String key = routingWith.value();
        try (RoutingDataSourceContext ctx = new RoutingDataSourceContext(key)) {
            return joinPoint.proceed();
		}
	}
}

Note that the second parameter of the method RoutingWith is an instance of an annotation passed in by Spring, and we get the configured key according to the value() of the annotation. You need to add a Maven dependency before compiling:

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

At this point, we have implemented the ability to dynamically select data sources with annotations. The last refactoring step is to replace the masterDataSource and slaveDataSource scattered all over the place with string constants:

Usage restrictions

Limited by the Servlet thread model, dynamic data sources cannot be set and then modified within a request, i.e. @RoutingWith cannot be nested. In addition, when @RoutingWith and @Transactional are mixed, the priority of AOP should be set

Reference https://www.liaoxuefeng.com/article/1182502273240832

Table of Contents

Step 1: Configure multiple data sources

Step 2: Write RoutingDataSource

Usage restrictions