Getting Started with Sharding-JDBC in SpringBoot: A Practical Guide to Database Sharding

1. Why Sharding Is Needed

In large‑scale applications such as e‑commerce platforms, a single order or user table can quickly reach tens of millions or even billions of rows. When a table exceeds 10 million rows, simple queries may take 1–3 seconds; beyond 100 million rows, queries can time out, inserts may fail, and the whole system’s availability suffers. Additionally, a single MySQL instance cannot store hundreds of gigabytes of data without severe storage pressure.

To break both performance and storage bottlenecks, the most practical solution is to split the data across multiple databases and tables – a technique known as sharding (分库分表).

2. What Is Sharding-JDBC?

Sharding-JDBC is the core component of the Apache ShardingSphere ecosystem. It is a lightweight JDBC enhancement that does not require a separate middleware process; you only add a dependency and configure rules, and the framework intercepts SQL, routes it to the appropriate physical tables, executes it, and merges results transparently.

Core Features

Lightweight – no independent server, easy deployment.

Non‑intrusive – business code stays unchanged; only configuration changes.

Supports horizontal sharding, vertical sharding, and combinations of both.

Compatible with MySQL, Oracle, PostgreSQL and works with MyBatis, JPA, etc.

Working Flow

Intercept SQL via the enhanced JDBC driver.

Parse the SQL to extract the logical table name and sharding key.

Apply the sharding rule to determine the target database(s) and table(s).

Execute the routed SQL on the physical nodes.

Merge results (e.g., pagination) and return to the application.

Example: a query SELECT * FROM order WHERE user_id = 100 is intercepted, the sharding key user_id is extracted, 100 % 2 = 0 routes the query to order_0, and the result is returned without the developer needing to know the actual table.

3. Environment Setup – SpringBoot + Sharding-JDBC

We use SpringBoot 2.7.x, Sharding-JDBC 4.1.1, MySQL 8.0, and MyBatis. The steps are:

3.1 Create Databases and Tables

Horizontal partitioning (single‑database, multiple tables) example:

-- Create database
CREATE DATABASE IF NOT EXISTS sharding_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sharding_db;

-- Create order_0
CREATE TABLE IF NOT EXISTS order_0 (
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '订单ID（主键）',
  order_no VARCHAR(32) NOT NULL COMMENT '订单编号（唯一）',
  user_id BIGINT NOT NULL COMMENT '用户ID（分片键）',
  total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总金额',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (id),
  UNIQUE KEY idx_order_no (order_no),
  KEY idx_user_id (user_id) COMMENT '分片键索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表（水平分表0）';

-- Create order_1 (same structure)
CREATE TABLE IF NOT EXISTS order_1 LIKE order_0;

Horizontal sharding across two databases (多库多表) follows the same pattern, creating sharding_db_0 and sharding_db_1 each with order_0 and order_1.

3.2 Maven Dependencies (pom.xml)

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.7.10</version>
</parent>

<dependencies>
    <!-- SpringBoot Web (optional) -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- Sharding-JDBC core -->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.1.1</version>
    </dependency>

    <!-- MyBatis -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.2</version>
    </dependency>

    <!-- MySQL driver -->
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

3.3 Application Configuration (application.yml)

Horizontal sharding (single database) configuration:

spring:
  shardingsphere:
    datasource:
      names: ds0
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharding_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          order:
            actual-data-nodes: ds0.order_${0..1}
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-table-inline
        sharding-algorithms:
          order-table-inline:
            type: INLINE
            props:
              algorithm-expression: order_${user_id%2}
        props:
          sql-show: true
    mybatis:
      mapper-locations: classpath:mapper/*.xml
      type-aliases-package: com.example.sharding.entity
      configuration:
        map-underscore-to-camel-case: true
    logging:
      level:
        root: info
        com.example.sharding.mapper: debug

Horizontal sharding across two databases adds two data sources ( ds0 and ds1) and a database-strategy that also uses user_id % 2 to select the target database.

3.4 Entity, Mapper, and XML

Entity class ( Order.java) defines fields id, orderNo, userId, totalAmount, createTime and uses Lombok @Data for getters/setters.

package com.example.sharding.entity;

import lombok.Data;
import java.math.BigDecimal;
import java.util.Date;

@Data
public class Order {
    private Long id;               // 主键
    private String orderNo;         // 订单编号
    private Long userId;           // 分片键
    private BigDecimal totalAmount;
    private Date createTime;
}

Mapper interface ( OrderMapper.java) provides insert, selectByUserId, and selectAll methods with MyBatis annotations.

package com.example.sharding.mapper;

import com.example.sharding.entity.Order;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface OrderMapper {
    @Insert("INSERT INTO `order` (order_no, user_id, total_amount, create_time) VALUES (#{orderNo}, #{userId}, #{totalAmount}, #{createTime})")
    int insert(Order order);

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

    @Select("SELECT * FROM `order` LIMIT #{pageNum}, #{pageSize}")
    List<Order> selectAll(int pageNum, int pageSize);
}

Corresponding XML ( OrderMapper.xml) contains the same SQL statements for those methods.

3.5 Service Layer

package com.example.sharding.service;

import com.example.sharding.entity.Order;
import com.example.sharding.mapper.OrderMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.UUID;

@Service
public class OrderService {
    @Autowired
    private OrderMapper orderMapper;

    // Add order – Sharding-JDBC will route based on userId
    public int addOrder(Order order) {
        String orderNo = UUID.randomUUID().toString().replace("-", "").substring(0, 32);
        order.setOrderNo(orderNo);
        order.setCreateTime(new Date());
        return orderMapper.insert(order);
    }

    public List<Order> getOrderByUserId(Long userId) {
        return orderMapper.selectByUserId(userId);
    }

    public List<Order> getAllOrder(int pageNum, int pageSize) {
        return orderMapper.selectAll(pageNum, pageSize);
    }
}

3.6 Test Cases

package com.example.sharding;

import com.example.sharding.entity.Order;
import com.example.sharding.service.OrderService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.math.BigDecimal;
import java.util.List;

@SpringBootTest
class ShardingJdbcApplicationTests {
    @Autowired
    private OrderService orderService;

    // Insert 5 orders with userId 100‑104
    @Test
    void testAddOrder() {
        for (int i = 0; i < 5; i++) {
            Order order = new Order();
            order.setUserId(100L + i);
            order.setTotalAmount(new BigDecimal(100 + i * 10));
            orderService.addOrder(order);
        }
    }

    // Query by userId to verify routing
    @Test
    void testGetOrderByUserId() {
        List<Order> list100 = orderService.getOrderByUserId(100L);
        System.out.println("user_id=100 orders: " + list100);
        List<Order> list101 = orderService.getOrderByUserId(101L);
        System.out.println("user_id=101 orders: " + list101);
    }

    // Paginated query – Sharding-JDBC merges results from all tables
    @Test
    void testGetAllOrder() {
        List<Order> all = orderService.getAllOrder(0, 10);
        System.out.println("All orders: " + all);
    }
}

4. Test Result Verification

Running testAddOrder() prints the intercepted SQL. For each user_id:

Even user_id (e.g., 100, 102, 104) routes to order_0 (or ds0.order_0 in the multi‑database case).

Odd user_id (101, 103) routes to order_1 (or ds1.order_1).

Inspecting the MySQL databases confirms the distribution: sharding_db – order_0 contains three rows (user_id 100, 102, 104); order_1 contains two rows (101, 103).

In the two‑database setup, sharding_db_0 holds the even rows, sharding_db_1 holds the odd rows.

The testGetOrderByUserId() method shows that queries are routed only to the relevant physical table, and testGetAllOrder() demonstrates automatic result merging across tables.

5. Advanced Topics

5.1 Sharding Strategies

Inline – expression‑based, simplest (e.g., order_${user_id%2}).

Standard – uses a sharding column and a pluggable algorithm; supports range queries.

Complex – multiple sharding keys (e.g., user_id + order_time) for more sophisticated scenarios.

5.2 Primary‑Key Generation

Auto‑increment IDs cannot be used across shards. Sharding‑JDBC provides generators such as the Snowflake algorithm. Example configuration:

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          order:
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1

After adding this, the id field is automatically filled with a globally unique 64‑bit value.

5.3 Binding Tables

When multiple logical tables share the same sharding key and algorithm (e.g., order and order_item both sharded by user_id), they should be declared as a binding group. This ensures that join queries are routed to the same physical tables, avoiding full‑table scans.

spring:
  shardingsphere:
    rules:
      sharding:
        binding-tables: order,order_item
        tables:
          order:
            actual-data-nodes: ds${0..1}.order_${0..1}
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-table-inline
          order_item:
            actual-data-nodes: ds${0..1}.order_item_${0..1}
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-table-inline

5.4 Broadcast Tables

Tables that must exist in every shard with identical data (e.g., dictionaries, configuration tables) are defined as broadcast tables. Queries against them are executed on a single randomly chosen database, while updates are automatically propagated to all shards.

spring:
  shardingsphere:
    rules:
      sharding:
        broadcast-tables: dict,sys_config
        tables:
          dict:
            actual-data-nodes: ds${0..1}.dict
          sys_config:
            actual-data-nodes: ds${0..1}.sys_config

6. Conclusion

Sharding‑JDBC makes database sharding in SpringBoot projects straightforward: you only need to define logical tables, choose a sharding key, configure the routing algorithm, and the framework handles SQL interception, routing, and result merging. By mastering horizontal sharding, horizontal sharding across multiple databases, key generation, binding tables, and broadcast tables, developers can efficiently scale databases without rewriting business logic.

Hands‑on the steps in this guide, then explore more advanced strategies (range sharding, complex sharding, distributed transactions) to further strengthen your backend architecture.