SpringBoot configures multiple data sources and dynamically switches data sources using customized policies.

In this example, there are two data sources: primary data source, used for data update (Update, INSERT,delete); The data source Replica1 is used for data queries

Based on springBoot2.4.0, the default database connection pool hikari is used

International sports, the influence of the various open source projects have gradually clear the master, salve, blacklist and whitelist terms, such as mysql to use source respectively, up, blocklist, allowlist replacement, so this is also true in this case

  1. application.ymlAdded custom data source configuration
dynamic:
  defaultDsKey: replica1
  datasources:
    source:
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/yuan? useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
      Connection pool configuration
      # Maximum number of connections. Default is 10
      maximum-pool-size: 2
      The default connection pool name is hikaripool-1, hikaripool-2
      pool-name: yuan-source
    replica1:
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/yuanbak? useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
      Connection pool configuration
      maximum-pool-size: 2
      pool-name: yuan-replica1
Copy the code
  1. Define the entity class to receive the above configuration. Many tutorials teach you to customize the entity class corresponding to the data source configuration properties, but I don’t think it’s necessary to reuse spring as it isapplication.ymlIn thespring.datasourceThe entity class corresponding to the configuration isorg.springframework.boot.autoconfigure.jdbc.DataSourceProperties.spring.datasource.hikariThe entity class corresponding to the pool configuration iscom.zaxxer.hikari.HikariDataSource)
import com.zaxxer.hikari.HikariDataSource;
import lombok.Getter;
import lombok.Setter;
import org.springframework.boot.context.properties.ConfigurationProperties;

import java.util.Map;

@ConfigurationProperties(prefix = "dynamic")
@Getter
@Setter
public class DynamicDataSourceProperties {
    private Map<String, HikariDataSource> datasources;
    // Default data source
    private String defaultDsKey;
}

Copy the code
  1. Key points: InheritanceAbstractRoutingDataSourceAbstract the class and register it as a bean
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.Map;

@Slf4j
@Configuration
/ / enabled DynamicDataSourceProperties configuration
@EnableConfigurationProperties(DynamicDataSourceProperties.class)
public class DynamicDataSourceConfig {
    @Resource
    private DynamicDataSourceProperties dynamicDataSourceProperties;

    @Bean
    public AbstractRoutingDataSource dataSource(a) {
        / / implementation AbstractRoutingDataSource determineCurrentLookupKey method, this method returns the current corresponding dsKey data source to use
        AbstractRoutingDataSource abstractRoutingDataSource = new AbstractRoutingDataSource() {
            @Override
            protected Object determineCurrentLookupKey(a) {
                returnDsKeyThreadLocal.getDsKey(); }}; Map<String, HikariDataSource> datasources = dynamicDataSourceProperties.getDatasources(); log.info("all datasource key: {}", datasources.keySet());
        // The default data source must be configured
        String defaultDsKey = dynamicDataSourceProperties.getDefaultDsKey();
        if(! datasources.containsKey(defaultDsKey)) {throw new IllegalArgumentException("must config default datasource");
        }
        log.info("default datasource key: {}", defaultDsKey);
        // Set all data sources
        Map<Object, Object> dataSourceMap = new HashMap<>();
        datasources.forEach((dsKey, hikariDataSource) -> {
            dataSourceMap.put(dsKey, hikariDataSource);
        });
        abstractRoutingDataSource.setTargetDataSources(dataSourceMap);
        The default data source is used when the dsKey cannot find the corresponding data source or no data source is set
        abstractRoutingDataSource.setDefaultTargetDataSource(dataSourceMap.get(defaultDsKey));
        // The afterPropertiesSet() method is called to write the targetDataSources property to resolvedDataSources
        abstractRoutingDataSource.afterPropertiesSet();
        returnabstractRoutingDataSource; }}Copy the code
  1. DsKeyThreadLocalUsed to store the data source used for this session
// Use threadLocal to store the dsKey corresponding to the data source used by the current session
public final class DsKeyThreadLocal {

    private static ThreadLocal<String> DS_KEY = new ThreadLocal<>();

    private DsKeyThreadLocal(a) {}public static void setDsKey(String dsKey) {
        DS_KEY.set(dsKey);
    }

    public static String getDsKey(a) {
        returnDS_KEY.get(); }}Copy the code
  1. Test: create a record with ID 1 in the two data sources, and the other fields are different. If the result of the query is consistent with the corresponding data source record, the query succeeds
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

@SpringBootTest(classes = App.class)
@Slf4j
public class DictDaoTest {
    @Resource
    DictDao dictDao;

    @Test
    public void dynamicDataSourceTest(a) {
        // Use the primary data source
        DsKeyThreadLocal.setDsKey("source");
        Dict dict = dictDao.selectById(1);
        log.info("dict : {}", dict);

        // Use the slave data source
        DsKeyThreadLocal.setDsKey("replica1");
        dict = dictDao.selectById(1);
        log.info("dict : {}", dict); }}Copy the code

  1. Define a strategy for dynamically switching data sources, which can be implemented using filter or AOP, before the business code sets the data source dsKey to be used for this session.

    Here’s a simple strategy: It’s well known that in restful interface design, GET requests are used to GET resources and POST requests are used to update resources. So you can have a simple policy: GET requests use the secondary data source, and POST requests use the master data source

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.web.filter.OncePerRequestFilter;

import javax.annotation.Resource;
import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;


@Slf4j
@Component
public class DsFilter extends OncePerRequestFilter {
    @Resource
    private DynamicDataSourceProperties dynamicDataSourceProperties;

    // This method ensures that each request is called only once in a single thread of requests
    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain filterChain)
            throws ServletException, IOException {
        String method = request.getMethod();
        // GET requests use the secondary library, POST requests use the primary library, and else should be added to cover all cases
        if (method.equals("GET")) {
            DsKeyThreadLocal.setDsKey("replica1");
        } else if (method.equals("POST")) {
            DsKeyThreadLocal.setDsKey("source");
        } else {
            DsKeyThreadLocal.setDsKey(dynamicDataSourceProperties.getDefaultDsKey());
        }
        log.info("datasource route to {}", DsKeyThreadLocal.getDsKey()); filterChain.doFilter(request, response); }}Copy the code

cook

  1. Earlier versions of springBoot configuring custom multi-data sources should first exclude automatic data source configuration

  2. The policy of dynamically switching data sources should override all cases (that is, giving an initial value to the data source of the current session), otherwise the data source used by the current session is the one used by the processing thread in the last session. The DsKeyThreadLocal#getDsKey method that gets the data source could have been designed to be called once to automatically clear the DS_KEY, but given that there are multiple query statements in a session, this is not the case.

  3. If you look at the logs, each data source creates a connection pool

  4. The configuration for a single data source is as follows:

    spring:
  datasource:
    # type: com.zaxxer.hikari.HikariDataSource
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/yuan? useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    hikari:
      maximum-pool-size: 2
      pool-name: yuan-hikari
Copy the code