Spring boot implements the method of database read and write separation

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

background

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

ReadWriteSplitRoutingDataSource.java


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

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

Thread private routing configuration for ReadWriteSplitRoutingDataSource dynamic read configuration

DbContextHolder.java


/**
 * @author songrgg
 * @since 1.0
 */
public class DbContextHolder {
  public enum DbType {
    MASTER,
    SLAVE
  }

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

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

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

  public static void clearDbType() {
    contextHolder.remove();
  }
}

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.

ReadOnlyConnection.java


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})
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadOnlyConnection {
}

ReadOnlyConnectionInterceptor.java


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
 */
@Aspect
@Component
public class ReadOnlyConnectionInterceptor implements Ordered {

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

  @Around("@annotation(readOnlyConnection)")
  public Object proceed(ProceedingJoinPoint proceedingJoinPoint, ReadOnlyConnection readOnlyConnection) throws Throwable {
    try {
      logger.info("set database connection to read only");
      DbContextHolder.setDbType(DbContextHolder.DbType.SLAVE);
      Object result = proceedingJoinPoint.proceed();
      return result;
    } finally {
      DbContextHolder.clearDbType();
      logger.info("restore database connection");
    }
  }

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

UserService.java


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

Configure the Druid database connection pool

build.gradle

compile("com.alibaba:druid:1.0.18")

groovy dependency injection

Configure dataSource as a routable data source

context.groovy


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

** SOME INITIALIZED CODE LOAD PROPERTIES **
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: