SpringBoot implements switching between multiple data sources

The coder is on the ropes

When the sky is clear, the sky is still blue, at night I will look up at the rare starry sky overlooking the big city of Nuo, but not a lamp is lit for me.

preface

We are in the process of software development, in the beginning because incalculable system later visits and concurrency, so will use a single architecture at the beginning, if late site traffic, concurrent quantitative change is big, then you may be the extension of the architecture for micro service architecture, various micro service corresponding to a database, but the cost is a little big, May only be some modules with more people, few people use some module, if service split and actually didn’t also the necessary, if some modules with more, so we can read and write using separation to reduce stress, so, can to a certain extent, improve the system of the user experience, but that’s just above the I/O of the database solution, If the pressure of the system is very large, then it must do load balancing, we will first say today to achieve the separation of read and write database. We want to achieve the separation of database reading and writing at the code level, so the core is the switch of data sources, this paper based on AOP to achieve the switch of data sources.

Engineering structure

└ ─ com
    └ ─ steak
        └ ─ the transaction
            │  TransactionDemoApplication.java
            │  
            ├ ─ a datasource
            │  │  DatasourceChooser.java
            │  │  DatasourceConfiguration.java
            │  │  DatasourceContext.java
            │  │  DatasourceScope.java
            │  │  DynamicDatasourceAspect.java
            │  │  
            │  └ ─ the properties
            │          DynamicDatasourceProperties.java
            │          MainDatasourceProperties.java
            │          
            ├ ─ the execute
            │      PlaceOrderExecute.java
            │      
            ├ ─ rest
            │      PlaceOrderApi.java
            │      
            ├ ─ the result
            │      R.java
            │      
            └ ─ service
                    IntegralService.java
                    OrderService.java
                    StockService.java
Copy the code

In the following implementation, we have three data sources, one of which is the default. If the specific data source is not specified, the default is used. We switch the data source declaratively, and only need to annotate the specific interface to achieve the switch.

coded

Yml file

The main data source uses the Spring configuration directly, while the other data sources are customized. Here, a map structure is used to define the data source, which is easy to parse. You can add more data sources in the YML file, without changing the code logic level.

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      username: root
      password: 123456
      url: JDBC: mysql: / / 127.0.0.1:3306 / db? useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC
      driver-class-name: com.mysql.cj.jdbc.Driver

dynamic:
  datasource: {
    slave1: {
      username: 'root'.password: '123456'.url: 'url: JDBC: mysql: / / 127.0.0.1:3306 / db? useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC'.driver-class-name: 'com.mysql.cj.jdbc.Driver'
    },
    slave2: {
      username: 'root'.password: '123456'.url: 'url: JDBC: mysql: / / 127.0.0.1:3306 / db? useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC'.driver-class-name: 'com.mysql.cj.jdbc.Driver'}}Copy the code

Main data source MainDatasourceProperties

For the master data source, we’re going to separate it out and put it in a class, but we could have put it in dynamic as well, but with a little bit of manipulation, we’re just going to put a couple of connection properties.

/ * * *@authorLiu brand *@date2022/3/22 0:14 * /
@Component
@ConfigurationProperties(prefix = "spring.datasource.druid")
@Data
public class MainDatasourceProperties {
    private String username;
    private String password;
    private String url;
    private String driverClassName;
}
Copy the code

Other sources DynamicDatasourceProperties

Other data sources use a Map to accept the data source configuration in the YML file

/ * * *@authorLiu brand *@date2022/3/21 3:47 * /
@Component
@ConfigurationProperties(prefix = "dynamic")
@Data
public class DynamicDatasourceProperties {
    private Map<String,Map<String,String>> datasource;
}
Copy the code

DatasourceConfiguration class DatasourceConfiguration

In the configuration class, the DataSource is mainly configured. The connection properties of the master DataSource are defined according to the normal bean, while the connection properties of other data sources are configured in a reflected way. Because the master DataSource generally does not change, but other data sources may change and may add, In this case, if you hardcoded the configuration, you would need to add a configuration for each additional data source, which is obviously not very good, so reflection is used for assignment.

/ * * *@authorLiu brand *@date2022/3/11 1:34 * /
@Configuration
@AllArgsConstructor
public class DatasourceConfiguration {

    final MainDatasourceProperties mainDatasourceProperties;
    final DynamicDatasourceProperties dynamicDatasourceProperties;

    @Bean
    public DataSource datasource(a){
        Map<Object,Object> datasourceMap = new HashMap<>();
        DatasourceChooser datasourceChooser = new DatasourceChooser();
        /** * main database */
        DruidDataSource mainDataSource = new DruidDataSource();
        mainDataSource.setUsername(mainDatasourceProperties.getUsername());
        mainDataSource.setPassword(mainDatasourceProperties.getPassword());
        mainDataSource.setUrl(mainDatasourceProperties.getUrl());
        mainDataSource.setDriverClassName(mainDatasourceProperties.getDriverClassName());
        datasourceMap.put("main",mainDataSource);
        /** * other database */
        Map<String, Map<String, String>> sourceMap = dynamicDatasourceProperties.getDatasource();
        sourceMap.forEach((datasourceName,datasourceMaps) -> {
            DruidDataSource dataSource = new DruidDataSource();
            datasourceMaps.forEach((K,V) -> {
                String setField = "set" + K.substring(0.1).toUpperCase() + K.substring(1);
                // Convert attributes in yML files with the - symbol
                String[] strings = setField.split("");
                StringBuilder newStr = new StringBuilder();
                for (int i = 0; i < strings.length; i++) {
                    if (strings[i].equals("-")) strings[i + 1] = strings[i + 1].toUpperCase();
                    if(! strings[i].equals("-")) newStr.append(strings[i]);
                }
                try {
                    DruidDataSource.class.getMethod(newStr.toString(),String.class).invoke(dataSource,V);
                } catch(Exception e) { e.printStackTrace(); }}); datasourceMap.put(datasourceName,dataSource); });// Set the target data source
        datasourceChooser.setTargetDataSources(datasourceMap);
        // Set the default data source
        datasourceChooser.setDefaultTargetDataSource(mainDataSource);
        returndatasourceChooser; }}Copy the code

The data Source configuration class above uses reflection to set connection properties for other data sources, and then sets the target data source and default data source, with a DatasourceChooser.

DatasourceChooser

DatasourceChooser inherited from AbstractRoutingDataSource AbstractRoutingDataSource can achieve data source switch, It inside determineCurrentLookupKey () method we need to return to a data source name, it will automatically match to us on the data source.

/ * * *@authorLiu brand *@date2022/3/11 2:21 * /
public class DatasourceChooser extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey(a) {
        returnDatasourceContext.getDatasource(); }}Copy the code

The following is part of the AbstractRoutingDataSource source code, we can see the data source is a Map structure, can check it on the data source name to the corresponding data source.

package org.springframework.jdbc.datasource.lookup;

public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
    @Nullable
    private Map<Object, DataSource> resolvedDataSources;
    public AbstractRoutingDataSource(a) {}protected DataSource determineTargetDataSource(a) {
        Object lookupKey = this.determineCurrentLookupKey();
        DataSource dataSource = (DataSource)this.resolvedDataSources.get(lookupKey);
    }

    @Nullable
    protected abstract Object determineCurrentLookupKey(a);
}
Copy the code

DatasourceContext

DatasourceContext is internally a ThreadLocal, which is used to store the data source name for each thread and retrieve the data source name that we used the AOP aspect to retrieve.

/ * * *@authorLiu brand *@date2022/3/11 2:22 * /
public class DatasourceContext {
    private static final ThreadLocal<String> threadLocal = new ThreadLocal<>();
    public static void setDatasource(String key){
        threadLocal.set(key);
    }
    public static String getDatasource(a){
        returnthreadLocal.get(); }}Copy the code

The data source annotation DatasourceScope

The DatasourceScope standard specifies the data source by scope above the method, and does not specify the default main data source.

/ * * *@authorLiu brand *@date2022/3/11 and * /
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DatasourceScope {
    String scope(a) default "main";
}
Copy the code

Datasource facet DynamicDatasourceAspect

When we access each method with the DatasourceScope annotation, we will go through the data source section DynamicDatasourceAspect to obtain the scope value above the annotation, and then set the data source name through the DatasourceContext. The switch to the data source can be realized.

/ * * *@authorLiu brand *@date2022/3/11 1:34 * /
@Aspect
@Component
public class DynamicDatasourceAspect {

    @Pointcut("@annotation(dataSourceScope)")
    public void dynamicPointcut(DatasourceScope dataSourceScope){}

    @Around(value = "dynamicPointcut(dataSourceScope)", argNames = "joinPoint,dataSourceScope")
    public Object dynamicAround(ProceedingJoinPoint joinPoint , DatasourceScope dataSourceScope) throws Throwable {
        String scope = dataSourceScope.scope();
        DatasourceContext.setDatasource(scope);
        returnjoinPoint.proceed(); }}Copy the code

use

Simply annotate the data source annotation @datasourcescope above the concrete method and specify the value of scope to implement the switch. If not, use the master data source.

/ * * *@authorLiu brand *@date2022/3/19:49 * /
@Service
@AllArgsConstructor
public class OrderService {
    
    private JdbcTemplate jdbcTemplate;
    
    @DatasourceScope(scope = "slave1")
    public R saveOrder(Integer userId , Integer commodityId){
        String sql = "INSERT INTO `order`(user_id,commodity_id) VALUES("+userId+","+commodityId+")";
        jdbcTemplate.execute(sql);
        return R.builder().code(200).msg("save order success").build(); }}Copy the code

Thanks for watching. See you next time