Sharding-JDBC Demo with Spring Boot: Database and Table Sharding Guide

Sharding-JDBC is an open‑source lightweight Java middleware that provides sharding capabilities at the JDBC layer, compatible with standard JDBC and ORM frameworks.

The article demonstrates a Spring Boot demo implementing database and table sharding across three MySQL instances. A three‑layer project structure is used, and the required dependencies are declared in pom.xml (Spring Boot starter, MySQL driver, MyBatis‑Plus, Sharding‑Sphere JDBC starter, Lombok, etc.).

<?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>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.6.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.1</version>
        </dependency>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>
</project>

The entity Book is defined with Lombok annotations and mapped to the logical table book :

package com.macky.springbootshardingjdbc.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import lombok.experimental.Accessors;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("book")
public class Book extends Model<Book> {
    private int id;
    private String name;
    private int count;
}

A REST controller exposes /book GET and POST endpoints that delegate to a service layer built on MyBatis‑Plus ServiceImpl :

package com.macky.springbootshardingjdbc.controller;

import com.macky.springbootshardingjdbc.entity.Book;
import com.macky.springbootshardingjdbc.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
public class BookController {
    @Autowired
    BookService bookService;

    @RequestMapping(value = "/book", method = RequestMethod.GET)
    public List<Book> getItems(){
        return bookService.getBookList();
    }

    @RequestMapping(value = "/book", method = RequestMethod.POST)
    public Boolean saveItem(Book book){
        return bookService.save(book);
    }
}

The service implementation uses the generated BookMapper to query and save records:

package com.macky.springbootshardingjdbc.service.impl;

import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.macky.springbootshardingjdbc.entity.Book;
import com.macky.springbootshardingjdbc.mapper.BookMapper;
import com.macky.springbootshardingjdbc.service.BookService;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class BookServiceImpl extends ServiceImpl<BookMapper, Book> implements BookService {
    @Override
    public List<Book> getBookList() {
        return baseMapper.selectList(Wrappers.<Book>lambdaQuery());
    }

    @Override
    public boolean save(Book book) {
        return super.save(book);
    }
}

The mapper interface simply extends MyBatis‑Plus BaseMapper :

package com.macky.springbootshardingjdbc.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.macky.springbootshardingjdbc.entity.Book;

public interface BookMapper extends BaseMapper<Book> {}

SQL scripts create three databases ( db0 , db1 , db2 ) each containing two physical tables ( book_0 , book_1 ).

CREATE DATABASE IF NOT EXISTS `db0`;
USE `db0`;
CREATE TABLE `book_0` ( `id` INT NOT NULL, `name` VARCHAR(255), `count` INT, PRIMARY KEY(`id`) ) ENGINE=INNODB;
CREATE TABLE `book_1` ( `id` INT NOT NULL, `name` VARCHAR(255), `count` INT, PRIMARY KEY(`id`) ) ENGINE=INNODB;
-- repeat for db1 and db2 ...

The sharding strategy is configured in application.properties . Data sources for the three databases are defined, and inline expressions route records based on id % 3 for databases and count % 3 for tables:

# Data sources
sharding.jdbc.datasource.names=db0,db1,db2
sharding.jdbc.datasource.db0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.db0.jdbc-url=jdbc:mysql://localhost:3306/db0?serverTimezone=UTC
sharding.jdbc.datasource.db0.username=root
sharding.jdbc.datasource.db0.password=******
# ... db1 and db2 similar ...

# Sharding rules
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=db$->{id % 3}
sharding.jdbc.config.sharding.tables.book.actual-data-nodes=db$->{0..2}.book_$->{0..2}
sharding.jdbc.config.sharding.tables.book.table-strategy.inline.sharding-column=count
sharding.jdbc.config.sharding.tables.book.table-strategy.inline.algorithm-expression=book_$->{count % 3}
sharding.jdbc.config.props.sql.show=true

After starting the application, the demo can be tested with Postman: a GET request to http://localhost:8080/book retrieves all books, and a POST request to http://localhost:8080/book?id=1&name=Java%20Programming&count=8 inserts a new record, which is automatically routed to the appropriate database and table according to the defined sharding strategy.

The guide concludes that this setup follows the official ShardingSphere documentation and serves as a starting point for deeper exploration of sharding architecture and source code.