Master Sharding and Read‑Write Splitting in Spring Boot with ShardingSphere‑JDBC

1. Overview

The guide explains how to solve high‑concurrency and large‑data‑volume problems by splitting a single database into multiple logical data sources (sharding) and separating read and write traffic (read‑write splitting) using ShardingSphere‑JDBC.

2. Prerequisites

Spring Boot 3.5.5

ShardingSphere‑JDBC 5.5.2

Druid 1.2.27 for connection‑pool optimization

MySQL 8.0+ (or any compatible database)

Optional: MyBatis‑Spring‑Boot 3.0.3

3. Maven Dependencies

<dependencies>
  <!-- Spring Boot Web + JDBC -->
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
  </dependency>
  <!-- Druid connection pool -->
  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.27</version>
  </dependency>
  <!-- ShardingSphere‑JDBC core -->
  <dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc</artifactId>
    <version>5.5.2</version>
  </dependency>
  <!-- MySQL driver -->
  <dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
  </dependency>
  <!-- Optional MyBatis -->
  <dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>3.0.3</version>
  </dependency>
</dependencies>

4. Database Preparation

Assume an order system. Create two logical write/read data sources ( ds_write, ds0, ds1) and split the logical table t_order into physical tables t_order_0 and t_order_1. Routing rules:

Write operations → ds_write Read operations → ds0 or ds1 (load‑balanced)

Sharding key: user_id % 2 or order_id % 2 Example SQL to create databases and tables:

-- Master database ds_write
CREATE DATABASE ds_write;
USE ds_write;
CREATE TABLE t_order_0 (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(10,2),
    status VARCHAR(50)
);
CREATE TABLE t_order_1 LIKE t_order_0;

-- Read replica ds0 (same structure)
CREATE DATABASE ds0;
USE ds0;
CREATE TABLE t_order_0 LIKE ds_write.t_order_0;
CREATE TABLE t_order_1 LIKE ds_write.t_order_1;

-- Read replica ds1 (same structure)
CREATE DATABASE ds1;
USE ds1;
CREATE TABLE t_order_0 LIKE ds_write.t_order_0;
CREATE TABLE t_order_1 LIKE ds_write.t_order_1;

5. Configuration Files

5.1 application.yml

server:
  port: 9090

spring:
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:shardingsphere-config.yml

5.2 shardingsphere-config.yml

mode:
  type: Standalone
  repository:
    type: MEMORY

dataSources:
  ds_write:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://10.2.0.230:3306/ds_write?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true&rewriteBatchedStatements=true
    username: root
    password: admin123
  ds0:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://10.2.0.230:3306/ds0?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true&rewriteBatchedStatements=true
    username: root
    password: admin123
  ds1:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://10.2.0.230:3306/ds1?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true&rewriteBatchedStatements=true
    username: root
    password: admin123

rules:
  - !READWRITE_SPLITTING
    dataSourceGroups:
      readwrite_ds:
        writeDataSourceName: ds_write
        readDataSourceNames:
          - ds0
          - ds1
    transactionalReadQueryStrategy: PRIMARY
    loadBalancerName: random
    loadBalancers:
      random:
        type: RANDOM
    sql-show: true
    check-table: false
    default-data-source-name: readwrite_ds

  - !SHARDING
    tables:
      t_order:
        actualDataNodes: ds$->{0..1}.t_order_$->{0..1}
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: database-inline
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: table-inline
    shardingAlgorithms:
      database-inline:
        type: INLINE
        props:
          algorithm-expression: ds$->{user_id % 2}
      table-inline:
        type: INLINE
        props:
          algorithm-expression: t_order_$->{order_id % 2}

  - !SINGLE
    tables:
      - "*.*"
    defaultDataSource: readwrite_ds

6. Application Code (MyBatis Example)

6.1 Entity – Order.java

package com.example.sharding.entity;

import lombok.Data;

@Data
public class Order {
    private Long orderId;
    private Long userId;
    private Double amount;
    private String status;
}

6.2 Mapper – OrderMapper.java

package com.example.sharding.mapper;

import com.example.sharding.entity.Order;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface OrderMapper {
    @Insert("INSERT INTO t_order (order_id, user_id, amount, status) VALUES (#{orderId}, #{userId}, #{amount}, #{status})")
    int insert(Order order);

    @Select("SELECT * FROM t_order WHERE user_id = #{userId}")
    List<Order> selectByUserId(@Param("userId") Long userId);
}

6.3 Service – OrderService.java

package com.example.sharding.service;

import com.example.sharding.entity.Order;
import com.example.sharding.mapper.OrderMapper;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class OrderService {
    private final OrderMapper mapper;

    public OrderService(OrderMapper mapper) {
        this.mapper = mapper;
    }

    public void create(Order order) {
        mapper.insert(order);
    }

    public List<Order> getByUser(Long userId) {
        return mapper.selectByUserId(userId);
    }
}

6.4 Controller – OrderController.java

package com.example.sharding.controller;

import com.example.sharding.entity.Order;
import com.example.sharding.service.OrderService;
import org.springframework.web.bind.annotation.*;
import java.util.concurrent.ThreadLocalRandom;
import java.util.List;

@RestController
@RequestMapping("/orders")
public class OrderController {
    private final OrderService service;

    public OrderController(OrderService service) {
        this.service = service;
    }

    @PostMapping("/create")
    public String create(@RequestParam Long userId) {
        Order order = new Order();
        order.setOrderId(ThreadLocalRandom.current().nextLong(100000, 999999));
        order.setUserId(userId);
        order.setAmount(ThreadLocalRandom.current().nextDouble(10, 1000));
        order.setStatus("CREATED");
        service.create(order);
        return "Order created for user " + userId;
    }

    @GetMapping("/user/{userId}")
    public List<Order> list(@PathVariable Long userId) {
        return service.getByUser(userId);
    }
}

7. Startup and Verification

Start the master database ( ds_write) and the two replicas ( ds0, ds1) ensuring replication from master to replicas.

Run the Spring Boot application (default port 9090).

Insert an order (write routing) e.g.

curl -X POST "http://localhost:9090/orders/create?userId=1"
curl -X POST "http://localhost:9090/orders/create?userId=2"

Check the console; with sql-show: true you will see the actual SQL routed to ds_write.t_order_0 or ds_write.t_order_1.

Query orders for a user (read routing) e.g. curl "http://localhost:9090/orders/user/1" Logs will show the query executed on one of the read replicas, e.g., ds0.t_order_1 or ds1.t_order_1, demonstrating load‑balancing.

8. Summary of Achievements

Integrated Spring Boot 3.5.5 with Druid 1.2.27 for efficient connection pooling.

Implemented database sharding and read‑write splitting using ShardingSphere‑JDBC 5.5.2 without any SQL changes in the business layer.

Achieved automatic routing of write operations to the master and read operations to multiple replicas with load‑balancing.