Hands‑On Sharding: Implementing Database and Table Partitioning with Spring Boot and Sharding‑JDBC

A friend asked how to answer a sharding interview question, so the author prepared a practical walkthrough that can be used directly in an interview.

Premise

The example partitions a tb_user logical table into four databases (ds0‑ds3) and sixteen physical tables per database. The sharding rules are:

Database strategy: id % 4 → ds0‑ds3

Table strategy: id % 16 →

tb_user_0 … tb_user_15

Typical Problems

After sharding, the following issues usually arise:

Distributed ID generation

Distributed transactions

Data migration

Pagination queries

The article focuses on the pagination problem, which becomes non‑trivial once data is spread across multiple databases and tables.

Pagination Scenarios

Unconditional full‑table pagination

Search by user name

Filter by VIP status

Technology Stack

Spring Boot

MyBatis

Sharding‑JDBC (sharding implementation)

Elasticsearch (pagination support)

Redis

MySQL

Database Setup

Four databases are created (user‑0 … user‑3) and each contains sixteen tables named tb_user_0 … tb_user_15. The screenshots in the original article illustrate the SQL scripts used.

Configuration (application.properties)

# Sharding‑JDBC Configuration for Data Source Names
spring.shardingsphere.datasource.names=ds0,ds1,ds2,ds3

# Data source definitions (example for ds0)
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/user-0?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

# ... similar blocks for ds1, ds2, ds3

# Sharding rules
spring.shardingsphere.sharding.tables.tb_user.actual-data-nodes=ds${0..3}.tb_user_${0..15}
spring.shardingsphere.sharding.tables.tb_user.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.tb_user.database-strategy.inline.algorithm-expression=ds${id%4}
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.algorithm-expression=tb_user_${id%16}
spring.shardingsphere.sharding.tables.tb_user.key-generator.column=id
spring.shardingsphere.sharding.tables.tb_user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.tb_user.key-generator.props.worker.id=1

# MyBatis and PageHelper settings
mybatis.type-aliases-package=com.tian.entity
logging.level.com.tian.mapper=debug
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.pageSizeZero=true
pagehelper.params=countSql
mybatis.mapper-locations=classpath:mapper/*.xml

# Redis configuration
spring.redis.database=0
spring.redis.host=localhost
spring.redis.port=6379
spring.redis.timeout=5000
spring.redis.redisson.file=classpath:redisson-single.yaml

# Elasticsearch configuration
elasticsearch.host=elasticsearch
elasticsearch.port=9200
elasticsearch.cluster-nodes=localhost:9200

Entity Definition

@Document(indexName="user3")
@Data
public class UserEs {
    @Id
    private Long id;
    @Field(type = FieldType.Text)
    private String userName;
    @Field(type = FieldType.Text)
    private String userPassword;
    @Field(type = FieldType.Text)
    private Integer age;
    @Field(type = FieldType.Text)
    private Integer vip;
    @Field(type = FieldType.Long)
    private long createTime;
}

Request DTO

public class UserListReqDto extends BasePage {
    private String username;
    private Integer vip;
}

Controller Layer

@RestController
@RequestMapping("/user")
public class UserController {
    @Resource
    private UserService userService;

    @PostMapping("/add")
    public String addUser() {
        for (int i = 0; i < 100; i++) {
            userService.addUser();
        }
        return "success";
    }

    @GetMapping("/get/{id}")
    public User getUser(@PathVariable("id") Long id) {
        return userService.getUserById(id);
    }

    @PostMapping("/list")
    public UserListRespDto page(@RequestBody UserListReqDto userListReqDto) {
        return userService.page(userListReqDto);
    }
}

Service Implementation (Pagination with Elasticsearch)

@Override
public UserListRespDto page(UserListReqDto userListReqDto) {
    Pageable pageable = PageRequest.of(userListReqDto.getPageNum(), userListReqDto.getPageSize());
    FieldSortBuilder createTimeSort = SortBuilders.fieldSort("createTime").order(SortOrder.DESC);
    String keyword = userListReqDto.getUsername();
    Integer vip = userListReqDto.getVip();
    NativeSearchQuery searchQuery;
    if (StringUtils.hasText(keyword) && vip == null) {
        searchQuery = new NativeSearchQueryBuilder()
                .withPageable(pageable)
                .withQuery(QueryBuilders.multiMatchQuery(keyword, "userName"))
                .withSort(createTimeSort)
                .build();
    } else if (vip != null && !StringUtils.hasText(keyword)) {
        searchQuery = new NativeSearchQueryBuilder()
                .withPageable(pageable)
                .withQuery(QueryBuilders.multiMatchQuery(vip, "vip"))
                .withSort(createTimeSort)
                .build();
    } else {
        searchQuery = new NativeSearchQueryBuilder()
                .withPageable(pageable)
                .withSort(createTimeSort)
                .build();
    }
    SearchHits<UserEs> searchHits = elasticsearchRestTemplate.search(searchQuery, UserEs.class);
    long count = searchHits.stream().count();
    List<UserRespDto> userRespDtoList = new ArrayList<>();
    searchHits.forEach(hit -> {
        UserEs userEs = hit.getContent();
        UserRespDto dto = new UserRespDto();
        dto.setId(userEs.getId());
        dto.setUserName(userEs.getUserName());
        dto.setUserPassword(userEs.getUserPassword());
        dto.setAge(userEs.getAge());
        dto.setVip(userEs.getVip());
        dto.setCreateTime(Date.from(Instant.ofEpochMilli(userEs.getCreateTime())));
        userRespDtoList.add(dto);
    });
    UserListRespDto resp = new UserListRespDto();
    resp.setTotalCount(count);
    resp.setUserRespDtoList(userRespDtoList);
    resp.setPageSize(userListReqDto.getPageSize());
    resp.setPageNum(userListReqDto.getPageNum());
    return resp;
}

Testing

Test screenshots (omitted here) show the API returning paginated results correctly across the sharded tables.

Further Discussion

The article lists additional open questions such as the rationale for sharding, vertical vs. horizontal splitting, number of databases/tables, data migration strategies, distributed transaction handling, and unique ID generation. Readers are directed to the author's documentation for deeper coverage.