Implementing Read‑Write Splitting in Spring Boot with AbstractRoutingDataSource

1. Introduction

Read‑write splitting decides which database (master or slave) should execute a given SQL statement. It can be implemented either by a middleware (e.g., MyCat) or directly in the application. The article chooses the application‑level approach, leveraging Spring’s routing data source and AOP.

2. AbstractRoutingDataSource

Spring’s AbstractRoutingDataSource routes to a specific target data source based on a lookup key. It maintains a map of target data sources and provides a method to determine the current lookup key at runtime.

3. Practice

3.1 Maven Dependencies

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.cjs.example</groupId>
    <artifactId>cjs-datasource-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
    </parent>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

3.2 DataSource Configuration (application.yml)

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://192.168.102.31:3306/test
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
    slave1:
      jdbc-url: jdbc:mysql://192.168.102.56:3306/test
      username: pig   # read‑only account
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
    slave2:
      jdbc-url: jdbc:mysql://192.168.102.36:3306/test
      username: pig   # read‑only account
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver

3.3 Java Configuration

Define a routing data source that aggregates the master and two slave data sources.

package com.cjs.example.config;

import com.cjs.example.bean.MyRoutingDataSource;
import com.cjs.example.enums.DBTypeEnum;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave2")
    public DataSource slave2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slave1DataSource") DataSource slave1DataSource,
                                          @Qualifier("slave2DataSource") DataSource slave2DataSource) {
        Map
targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
        targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
        MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
        myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
        myRoutingDataSource.setTargetDataSources(targetDataSources);
        return myRoutingDataSource;
    }
}

Define the enum representing the three data sources.

package com.cjs.example.enums;

public enum DBTypeEnum {
    MASTER, SLAVE1, SLAVE2;
}

Implement a ThreadLocal holder to store the current routing key.

package com.cjs.example.bean;

import com.cjs.example.enums.DBTypeEnum;
import java.util.concurrent.atomic.AtomicInteger;

public class DBContextHolder {
    private static final ThreadLocal
contextHolder = new ThreadLocal<>();
    private static final AtomicInteger counter = new AtomicInteger(-1);

    public static void set(DBTypeEnum dbType) { contextHolder.set(dbType); }
    public static DBTypeEnum get() { return contextHolder.get(); }
    public static void master() { set(DBTypeEnum.MASTER); System.out.println("切换到master"); }
    public static void slave() {
        int index = counter.getAndIncrement() % 2;
        if (counter.get() > 9999) { counter.set(-1); }
        if (index == 0) { set(DBTypeEnum.SLAVE1); System.out.println("切换到slave1"); }
        else { set(DBTypeEnum.SLAVE2); System.out.println("切换到slave2"); }
    }
}

Extend AbstractRoutingDataSource to obtain the current lookup key.

package com.cjs.example.bean;

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

public class MyRoutingDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DBContextHolder.get();
    }
}

3.4 AOP for Automatic Switching

Use Spring AOP to intercept service methods. Read‑only methods (select/get/find) trigger DBContextHolder.slave() , while write methods (insert/update/delete) trigger DBContextHolder.master() . A custom @Master annotation forces master usage.

package com.cjs.example.aop;

import com.cjs.example.bean.DBContextHolder;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class DataSourceAop {
    @Pointcut("!@annotation(com.cjs.example.annotation.Master) && (execution(* com.cjs.example.service..*.select*(..)) || execution(* com.cjs.example.service..*.get*(..)))")
    public void readPointcut() {}

    @Pointcut("@annotation(com.cjs.example.annotation.Master) || execution(* com.cjs.example.service..*.insert*(..)) || execution(* com.cjs.example.service..*.add*(..)) || execution(* com.cjs.example.service..*.update*(..)) || execution(* com.cjs.example.service..*.edit*(..)) || execution(* com.cjs.example.service..*.delete*(..)) || execution(* com.cjs.example.service..*.remove*(..))")
    public void writePointcut() {}

    @Before("readPointcut()")
    public void read() { DBContextHolder.slave(); }

    @Before("writePointcut()")
    public void write() { DBContextHolder.master(); }
}

Define the @Master annotation.

package com.cjs.example.annotation;

public @interface Master {}

4. Testing

JUnit tests demonstrate write operations (insert), read operations (selectAll) and forced‑master reads (getToken).

package com.cjs.example;

import com.cjs.example.entity.Member;
import com.cjs.example.service.MemberService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest
public class CjsDatasourceDemoApplicationTests {

    @Autowired
    private MemberService memberService;

    @Test
    public void testWrite() {
        Member member = new Member();
        member.setName("zhangsan");
        memberService.insert(member);
    }

    @Test
    public void testRead() {
        for (int i = 0; i < 4; i++) {
            memberService.selectAll();
        }
    }

    @Test
    public void testSave() {
        Member member = new Member();
        member.setName("wangwu");
        memberService.save(member);
    }

    @Test
    public void testReadFromMaster() {
        memberService.getToken("1234");
    }
}

5. Project Structure

The article also shows a diagram of the module layout, illustrating where configuration, enums, beans, AOP, MyBatis, and service implementations reside.

6. References

Links to several Chinese blog posts that discuss read‑write splitting, Spring data access, and related topics.