Implementing Database Sharding with ShardingSphere‑JDBC in Spring Boot

Implementing ShardingSphere‑JDBC for Database Sharding

When a business grows, horizontal sharding (splitting databases and tables) becomes necessary to isolate resources and improve query performance. Two main approaches exist: a centralized proxy that hides the sharding from the application, and a distributed proxy that adds routing logic in the code. ShardingSphere‑JDBC follows the distributed‑proxy model and works only with Java.

1. ShardingSphere Overview

1.1 Overview

Most middleware implement their own SQL parser and optimizer, which adds considerable overhead. ShardingSphere‑JDBC, however, provides an enhanced JDBC driver that requires only configuration; the application code does not need to change and can be used with Spring Boot, MyBatis, JPA, etc.

1.2 Concepts

Logical Table : The abstract name of a horizontally split table, e.g., t_order represents t_order0 and t_order1.

Binding Table : Two tables share the same sharding key, so joins between them do not produce Cartesian products. Example: t_order and t_order_item both use order_id as the sharding column.

select * from t_order0 inner join t_order_item0 on order_id = order_id where order_id in (0, 1);
select * from t_order0 inner join t_order_item1 on order_id = order_id where order_id in (0, 1);
select * from t_order1 inner join t_order_item0 on order_id = order_id where order_id in (0, 1);
select * from t_order1 inner join t_order_item1 on order_id = order_id where order_id in (0, 1);

If a binding relationship is configured, the query can be routed directly to the correct physical table, eliminating the Cartesian product.

Broadcast Table : A small table that does not need sharding (e.g., a province list) and is replicated to every node.

2. Sharding with Spring Boot

Two MySQL instances (listening on 3306 and 3307) simulate two data sources. The logical sharding rule is based on user_id for database routing and order_id for table routing.

2.1 Database and Table Creation

-- logical table, does not really exist
create table t_order (
  order_id bigint not null auto_increment primary key,
  user_id bigint not null,
  name varchar(100)
);

CREATE TABLE `t_order_item` (
  `order_id` bigint(20) NOT NULL,
  `item` varchar(100) DEFAULT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- broadcast table (only one copy needed)
CREATE TABLE `t_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `config` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.2 Spring Boot Configuration (application.properties)

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3307/test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=

# database sharding rule (by user_id)
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.example.demo.sharding.PreciseShardingAlgorithmImpl

# table sharding rule (by order_id)
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}

spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
spring.shardingsphere.sharding.broadcast-tables=t_config
spring.shardingsphere.sharding.tables.t_config.actual-data-nodes=ds$->{0}.t_config

spring.jpa.show-sql=true
server.port=8080

2.3 Entity Classes

Order entity:

package com.example.demo.entity;

import javax.persistence.*;
import java.util.StringJoiner;

@Entity
@Table(name = "t_order")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long orderId;

    @Column(name = "user_id")
    private long userId;

    @Column(name = "name")
    private String name;

    // getters and setters omitted for brevity

    @Override
    public String toString() {
        return new StringJoiner(", ", Order.class.getSimpleName() + "[", "]")
                .add("orderId=" + orderId)
                .add("userId=" + userId)
                .add("name='" + name + "'")
                .toString();
    }
}

OrderItem entity:

package com.example.demo.entity;

import com.google.common.base.MoreObjects;
import javax.persistence.*;

@Entity
@Table(name = "t_order_item")
public class OrderItem {
    @Id
    @Column(name = "order_id")
    private long orderId;

    @Column(name = "user_id")
    private long userId;

    @Column(name = "item")
    private String item;

    // getters and setters omitted for brevity

    @Override
    public String toString() {
        return MoreObjects.toStringHelper(this)
                .add("orderId", orderId)
                .add("userId", userId)
                .add("item", item)
                .toString();
    }
}

Broadcast table entity (TConfig):

package com.example.demo.entity;

import com.google.common.base.MoreObjects;
import javax.persistence.*;

@Entity
@Table(name = "t_config")
public class TConfig {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(name = "user_id")
    private long userId;

    @Column(name = "config")
    private String config;

    // getters and setters omitted for brevity

    @Override
    public String toString() {
        return MoreObjects.toStringHelper(this)
                .add("id", id)
                .add("userId", userId)
                .add("config", config)
                .toString();
    }
}

2.4 DAO Interfaces

package com.example.demo.dao;

import com.example.demo.entity.Order;
import org.springframework.data.jpa.repository.JpaRepository;

public interface OrderDao extends JpaRepository<Order, Long> {}

package com.example.demo.dao;

import com.example.demo.entity.OrderItem;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.Optional;

public interface OrderItemDao extends JpaRepository<OrderItem, Long> {
    @Query("select n from Order t inner join OrderItem n on t.orderId = n.orderId where n.orderId=:orderId")
    Optional<OrderItem> getOrderItemByOrderId(@Param("orderId") Long orderId);
}

package com.example.demo.dao;

import com.example.demo.entity.TConfig;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ConfigDao extends JpaRepository<TConfig, Integer> {}

2.5 Controllers

package com.example.demo.controller;

import com.example.demo.dao.OrderDao;
import com.example.demo.entity.Order;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.Optional;

@RestController
public class OrderController {
    @Autowired
    private OrderDao orderDao;

    @GetMapping("/order")
    public Optional<Order> getOrderById(@RequestParam("id") Long id) {
        return orderDao.findById(id);
    }

    @PostMapping("/order/save")
    public Order saveOrder(@RequestParam("name") String name, @RequestParam("userid") Long userId) {
        Order order = new Order();
        order.setName(name);
        order.setUserId(userId);
        return orderDao.save(order);
    }
}

package com.example.demo.controller;

import com.example.demo.dao.OrderItemDao;
import com.example.demo.entity.OrderItem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.Optional;

@RestController
public class OrderItemController {
    @Autowired
    private OrderItemDao orderItemDao;

    @GetMapping("/orderItem")
    public Optional<OrderItem> getOrderItemById(@RequestParam("id") Long id) {
        return orderItemDao.findById(id);
    }

    @PostMapping("/orderItem/save")
    public OrderItem saveOrderItem(@RequestParam("item") String item,
                                   @RequestParam("userid") Long userId,
                                   @RequestParam("orderid") Long orderId) {
        OrderItem orderItem = new OrderItem();
        orderItem.setUserId(userId);
        orderItem.setItem(item);
        orderItem.setOrderId(orderId);
        return orderItemDao.save(orderItem);
    }

    @GetMapping("/orderItem/query")
    public Optional<OrderItem> getOrderItemByOrderId(@RequestParam("orderid") Long orderId) {
        return orderItemDao.getOrderItemByOrderId(orderId);
    }
}

package com.example.demo.controller;

import com.example.demo.dao.ConfigDao;
import com.example.demo.entity.TConfig;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
public class ConfigController {
    @Autowired
    private ConfigDao configDao;

    @GetMapping("/listConfig")
    public List<TConfig> getConfig() {
        return configDao.findAll();
    }
}

2.6 Custom Sharding Algorithm

package com.example.demo.sharding;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;

public class PreciseShardingAlgorithmImpl implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        String dbName = "ds" + shardingValue.getValue();
        for (String each : availableTargetNames) {
            if (each.equals(dbName)) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }
}

3. Testing the Sharding

Using curl to insert an order (user_id=0) routes the data to datasource ds0. The returned order_id is then used to insert an order_item. Queries that join t_order and t_order_item are correctly routed to the same physical tables, as shown by the screenshots of the data in both databases.

4. Read/Write Splitting

ShardingSphere also supports master‑slave configuration. By defining spring.shardingsphere.masterslave.name=ms and assigning ds0 as master and ds1 as slave, read requests are sent to the slave while write requests go to the master. The article demonstrates this with simple GET and POST curl commands and verifies the data placement in the master and slave databases.

Overall, the article provides a complete, end‑to‑end guide for implementing database sharding and read/write splitting with ShardingSphere‑JDBC in a Spring Boot microservice.