Mastering Read/Write Splitting in Spring Boot with Custom Routing DataSource

1. Introduction

Read/write splitting decides which database a SQL statement should be executed on. It can be done by middleware (e.g., MyCat) or by the application itself. This article chooses the latter, using Spring’s routing datasource and AOP.

Doing it at the application layer is simple but cannot dynamically add nodes because data sources are defined in configuration and require a restart.

2. AbstractRoutingDataSource

Spring’s AbstractRoutingDataSource routes to a specific target datasource based on a lookup key. It maintains a map of target datasources and provides a method to determine the current key.

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>
        <relativePath/>
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <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>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </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 Data source 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
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
    slave2:
      jdbc-url: jdbc:mysql://192.168.102.36:3306/test
      username: pig
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver

Four datasources are defined: one master, two slaves, and a routing datasource that combines them.

3.3 Routing key and datasource lookup

Define an enum to represent the three datasources.

package com.cjs.example.enums;
public enum DBTypeEnum {
    MASTER, SLAVE1, SLAVE2;
}

Use ThreadLocal in DBContextHolder to store the current DB type and provide methods to switch to master or round‑robin slaves.

package com.cjs.example.bean;
import com.cjs.example.enums.DBTypeEnum;
import java.util.concurrent.atomic.AtomicInteger;
public class DBContextHolder {
    private static final ThreadLocal<DBTypeEnum> 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"); }
    }
}

Implement MyRoutingDataSource extending AbstractRoutingDataSource to return 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();
    }
}

Define an AOP aspect DataSourceAop that switches to slave for read methods and to master for write methods, with an optional @Master annotation to force master.

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 {}

Service implementation shows normal CRUD methods, a method annotated with @Master to force master, and a method that reads a token from master.

package com.cjs.example.service.impl;
import com.cjs.example.annotation.Master;
import com.cjs.example.entity.Member;
import com.cjs.example.entity.MemberExample;
import com.cjs.example.mapper.MemberMapper;
import com.cjs.example.service.MemberService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class MemberServiceImpl implements MemberService {
    @Autowired
    private MemberMapper memberMapper;
    @Transactional
    @Override
    public int insert(Member member) { return memberMapper.insert(member); }
    @Master
    @Override
    public int save(Member member) { return memberMapper.insert(member); }
    @Override
    public List<Member> selectAll() { return memberMapper.selectByExample(new MemberExample()); }
    @Master
    @Override
    public String getToken(String appId) { return null; }
}

4. Tests

JUnit tests demonstrate write, read, save (master‑forced), and read‑from‑master scenarios.

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");
    }
}

Console output shows which datasource is used for each operation.

5. Project structure