Sharding-JDBC Demo: Implementing Database Sharding with Spring Boot

Sharding-JDBC is an open‑source lightweight Java framework that works as an enhanced JDBC driver, providing additional services at the JDBC layer while remaining fully compatible with JDBC and various ORM frameworks.

The demo creates a standard Spring Boot project using a three‑layer architecture. The project structure diagram is shown below:

The pom.xml file includes Spring Boot starter dependencies, MySQL connector, MyBatis‑Plus, ShardingSphere JDBC starters, and Lombok:

<?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>
        <relativePath/>
    </parent>
    <groupId>com.macky</groupId>
    <artifactId>spring-boot-shardingjdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spring-boot-shardingjdbc</name>
    <description>Demo project for spring-boot-shardingjdbc</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- MyBatis‑Plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.1</version>
        </dependency>
        <!-- ShardingSphere JDBC starter -->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>3.1.0</version>
        </dependency>
        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

The entity class uses a Book example with Lombok, MyBatis‑Plus, and ShardingSphere annotations:

package com.macky.springbootshardingjdbc.entity;

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

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

The controller exposes two REST endpoints for retrieving and saving books:

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 delegates to MyBatis‑Plus ServiceImpl:

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 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> {}

Database DDL creates three logical databases (db0, db1, db2) each containing two physical tables ( book_0 and book_1) with identical schema:

# Create databases
CREATE DATABASE IF NOT EXISTS `db0`;
USE `db0`;
DROP TABLE IF EXISTS `book_0`;
CREATE TABLE `book_0` (
    `id` INT(11) NOT NULL,
    `name` VARCHAR(255) DEFAULT NULL,
    `count` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `book_1`;
CREATE TABLE `book_1` (
    `id` INT(11) NOT NULL,
    `name` VARCHAR(255) DEFAULT NULL,
    `count` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

-- Repeat the same DDL for `db1` and `db2`

Sharding rules are defined in application.properties. Three data sources are declared, and inline sharding strategies distribute rows based on the id column for databases and the count column 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?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
sharding.jdbc.datasource.db0.username=root
sharding.jdbc.datasource.db0.password=Aa123456

sharding.jdbc.datasource.db1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/db1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
sharding.jdbc.datasource.db1.username=root
sharding.jdbc.datasource.db1.password=Aa123456

sharding.jdbc.datasource.db2.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.db2.jdbc-url=jdbc:mysql://localhost:3306/db2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
sharding.jdbc.datasource.db2.username=root
sharding.jdbc.datasource.db2.password=Aa123456

# Sharding strategy
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..1}
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 % 2}

sharding.jdbc.config.props..sql.show=true
spring.main.allow-bean-definition-overriding=true

API testing can be performed with Postman:

GET request → http://localhost:8080/book POST request → http://localhost:8080/book?id=1&name=Java%20Programming%20Thoughts&count=8

The demo source code is available on GitHub:

https://github.com/Macky-He/spring-boot–shardingsphere-examples

In summary, this article provides a step‑by‑step guide to building a Sharding‑JDBC demo, covering project setup, code implementation, sharding configuration, and testing. For deeper understanding, readers should consult the official ShardingSphere documentation and explore the source code.

Reference: Official ShardingSphere documentation – https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/usage/sharding/