Preface

MySQL master-slave can better reduce the pressure of database. Today, we have a small demo. The read-only interface allows him to query the database and implement it by annotation. Code upload to code cloud:
https://gitee.com/yunup/some-examples

environmental information

Java version 1.8
SpringBoot version 2.2.3.RELEASE
MySQL main database test
From library test2

Write profile

spring:
  datasource:
    url: "jdbc:mysql://localhost:3306/test\?useUnicode=true&characterEncoding=utf-8&useAffectedRows=true\&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai"
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      minimumIdle: 0
      maximum-pool-size: 20
      idle-timeout: 180000
      
dynamic-data-source:
  enabled: true
  slave:
    url: "jdbc:mysql://localhost:3306/test2\?useUnicode=true&characterEncoding=utf-8&useAffectedRows=true\&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai"
    username: root
    password: root

Dynamic data source class

Create the DynamicDataSource class, which inherits from AbstractRoutingDataSource and is used to determine which datasource to use

public class DynamicDataSource extends AbstractRoutingDataSource {

  private static final ThreadLocal<DataSourceType> dataSourceTypeThreadLocal = new ThreadLocal<>();

  public static void removeDataSourceType() {
    dataSourceTypeThreadLocal.remove();
  }

  public static void setDataSourceType(DataSourceType dataSourceType) {
    if (dataSourceTypeThreadLocal.get() == null) {
      dataSourceTypeThreadLocal.set(dataSourceType);
    }
  }

  @Override
  protected Object determineCurrentLookupKey() {
    return dataSourceTypeThreadLocal.get() == null ? DataSourceType.Master : dataSourceTypeThreadLocal.get();
  }
}

Multi data source configuration

First, configure multiple data sources. If the enabled value of dynamic data source in the configuration file is true, it means that the dynamic data source is enabled. Then inject the master data source and the slave data source

  @Bean({"masterDataSource"})
  @ConditionalOnProperty(name = {"dynamic-data-source.enabled"}, havingValue = "true")
  public DataSource masterDataSource() throws CloneNotSupportedException {
    this.log.info("init masterDataSource...");
    // Code for setting parameters is omitted here
    return new HikariDataSource(properties);
  }

  @Bean({"slaverDataSource"})
  @ConditionalOnProperty(name = {"dynamic-data-source.enabled"}, havingValue = "true")
  public DataSource slaverDataSource() throws CloneNotSupportedException {
    this.log.info("init slaverDataSource...");
    // Code for setting parameters is omitted here
    return new HikariDataSource(properties);
  }

  @Bean
  @Primary
  @DependsOn({"masterDataSource", "slaverDataSource"})
  @ConditionalOnProperty(name = {"dynamic-data-source.enabled"}, havingValue = "true")
  public DynamicDataSource dataSource(DataSource masterDataSource, DataSource slaverDataSource) {
    this.log.info("init dynamicDataSource...");
    DynamicDataSource source = new DynamicDataSource();
    Map<Object, Object> map = new HashMap<>();
    map.put(DataSourceType.Master, masterDataSource);
    map.put(DataSourceType.Slave, slaverDataSource);
    source.setTargetDataSources(map);
    source.setDefaultTargetDataSource(masterDataSource);
    return source;
  }

Create annotation class

Create two annotation classes. If it is a query interface, mark DBReadOnly on the interface and ask it to query the slave database

@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DBReadOnly {

}

@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DBWriteable {
  
}

Write tangent code

@Aspect
@Component
@ConditionalOnProperty(name = {"dynamic-data-source.enabled"}, havingValue = "true")
public class DataSourceAspect {

  @Around("@annotation(annotation)")
  public Object processDBReadOnly(ProceedingJoinPoint joinPoint, DBReadOnly annotation) {
    DynamicDataSource.setDataSourceType(DataSourceType.Slave);
    Object var3 = null;
    try {
      var3 = joinPoint.proceed();
    } catch (Throwable throwable) {
      throwable.printStackTrace();
    } finally {
      DynamicDataSource.removeDataSourceType();
    }
    return var3;
  }

  @Around("@annotation(annotation)")
  public Object processDBWriteable(ProceedingJoinPoint joinPoint, DBWriteable annotation) {
    DynamicDataSource.setDataSourceType(DataSourceType.Master);
    Object var3 = null;
    try {
      var3 = joinPoint.proceed();
    } catch (Throwable throwable) {
      throwable.printStackTrace();
    } finally {
      DynamicDataSource.removeDataSourceType();
    }
    return var3;
  }
}

Testing effect

Two requests are marked on the controller. findAll requests to check the test (primary) database and findAllSecond requests to check the test2 (secondary) database.

  @GetMapping("/findAll")
  @DBWriteable
  public List<Users> findAll() {
    List<Users> users = usersService.findAll();
    return users;
  }

  @GetMapping("/findAllSecond")
  @DBReadOnly
  public List<Users> findAllSecond() {
    List<Users> users = usersService.findAll();
    return users;
  }

findAll result: [{"userId": 1, "userName": "Zhang San"}, {"userId": 2, "userName": "Li Si"}, {"userId": 3, "userName": "Wang Wu"}]
findAllSecond result: [{"userId": 1, "userName": "Lucy"}, {"userId": 2, "userName": "Dick"}, {"userId": 3, "userName": "Angel"}]