Spring boot implements the method of database read and write separation

  • 2020-05-30 20:16:20
  • OfStack


After the database configuration master and slave, how to achieve read-write separation at the code level?

User - defined Settings for database routing

Spring boot provides AbstractRoutingDataSource with the ability to select the current database based on user-defined rules, so that we can set up the read slave library before executing the query, and then restore to the master library when the execution is complete.

Implement a dynamically routable data source that is executed before each database query operation


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

 * @author songrgg
 * @since 1.0
public class ReadWriteSplitRoutingDataSource extends AbstractRoutingDataSource {
  protected Object determineCurrentLookupKey() {
    return DbContextHolder.getDbType();

Thread private routing configuration for ReadWriteSplitRoutingDataSource dynamic read configuration


 * @author songrgg
 * @since 1.0
public class DbContextHolder {
  public enum DbType {

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

  public static void setDbType(DbType dbType) {
    if(dbType == null){
      throw new NullPointerException();

  public static DbType getDbType() {
    return contextHolder.get() == null ? DbType.MASTER : contextHolder.get();

  public static void clearDbType() {

AOP optimized code

AOP is used to detach the operation of setting up the database from the code, where the granularity control is at the method level, so the database transaction involved in this method is marked read-only in the form of annotations, which walk from the library.

Read-only annotations. Database operations used to annotate methods only follow from the library.


package com.wallstreetcn.hatano.config;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

 * Indicates the database operations is bound to the slave database.
 * AOP interceptor will set the database to the slave with this interface.
 * @author songrgg
 * @since 1.0
@Target({ElementType.METHOD, ElementType.TYPE})
public @interface ReadOnlyConnection {


import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;

 * Intercept the database operations, bind database to read-only database as this annotation
 * is applied.
 * @author songrgg
 * @since 1.0
public class ReadOnlyConnectionInterceptor implements Ordered {

  private static final Logger logger = LoggerFactory.getLogger(ReadOnlyConnectionInterceptor.class);

  public Object proceed(ProceedingJoinPoint proceedingJoinPoint, ReadOnlyConnection readOnlyConnection) throws Throwable {
    try {
      logger.info("set database connection to read only");
      Object result = proceedingJoinPoint.proceed();
      return result;
    } finally {
      logger.info("restore database connection");

  public int getOrder() {
    return 0;


public List<User> getUsers(Integer page, Integer limit) {
  return repository.findAll(new PageRequest(page, limit));

Configure the Druid database connection pool



groovy dependency injection

Configure dataSource as a routable data source


import com.alibaba.druid.pool.DruidDataSource
import DbContextHolder
import ReadWriteSplitRoutingDataSource

def dataSourceMaster = new DruidDataSource()
dataSourceMaster.url = properties.get('datasource.master.url')
println("master set to " + dataSourceMaster.url)
dataSourceMaster.username = properties.get('datasource.master.username')
dataSourceMaster.password = properties.get('datasource.master.password')

def dataSourceSlave = new DruidDataSource()
dataSourceSlave.url = properties.get('datasource.slave.url')
println("slave set to " + dataSourceSlave.url)
dataSourceSlave.username = properties.get('datasource.slave.username')
dataSourceSlave.password = properties.get('datasource.slave.password') 
beans {
  dataSource(ReadWriteSplitRoutingDataSource) { bean ->
    targetDataSources = [
        (DbContextHolder.DbType.MASTER): dataSourceMaster,
        (DbContextHolder.DbType.SLAVE): dataSourceSlave

Related articles: