Spring Boot demo series (12): ShardingSphereJDBC sub-database sub-table + read-write separation

1 Overview

I have written two articles before:

• ShardingSphereJDBC read-write separation
• ShardingSphereJDBC sub-database sub-table

Here, the two are combined to realize the functions of read-write separation + sub-database sub-table. This article will simplify the description of the configuration of the environment. For details, please refer to the first two articles.

2 Environment

• MySQL 8.0.29 ( Docker )
• MyBatis Plus 3.5.1
• MyBatis Plus Generator 3.5.2
• Druid 1.2.10
• ShardingSphereJDBC 5.1.1
• Yitter 1.0.6 (a snowflake id generator)

3 Database environment preparation

Since environment preparation is not the focus of this article, the master-slave replication environment of one master and one slave can be built here .

Prepare the environment, start two locally MySQL , the main node environment:

• Name: master
• Port: 3306
• Database: Two libraries ( test0 , test1 )
• 数据表：六个表，每个库三个（ test0.user0 、 test0.user1 、 test0.user2 、 test1.user0 、 test1.user1 , test1.user2 )

From the node environment:

• Name: slave
• Port: 3307
• Database: Two libraries ( test0 , test1 )
• 数据表：六个表，每个库三个（ test0.user0 、 test0.user1 、 test0.user2 、 test1.user0 、 test1.user1 , test1.user2 )

Main library configuration file:

 [mysqld]
server-id=1
binlog-do-db=test0
binlog-do-db=test1

From the library configuration file:

 [mysqld]
server-id=2
replicate-do-db=test0
replicate-do-db=test1

There are a total of 12 master libraries + slave libraries user the libraries are all the same, as shown below:

The complete database script and MySQL configuration file are placed in the source link at the end of the article.

4 New project

Create a new project and introduce the following dependencies:

• Druid
• MyBatis Plus starter
• MyBaits Plus Generator
• Velocity core
• ShardingSphereJDBC
• Yitter

Maven as follows:

 <dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.5.2</version>
</dependency>
<dependency>
    <groupId>org.freemarker</groupId>
    <artifactId>freemarker</artifactId>
    <version>2.3.31</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.10</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>5.1.1</version>
</dependency>
<dependency>
    <groupId>com.github.yitter</groupId>
    <artifactId>yitter-idgenerator</artifactId>
    <version>1.0.6</version>
</dependency>

Gradle as follows:

 implementation 'com.alibaba:druid:1.2.10'
implementation 'com.baomidou:mybatis-plus-boot-starter:3.5.1'
implementation 'org.freemarker:freemarker:2.3.31'
implementation 'com.baomidou:mybatis-plus-generator:3.5.2'
implementation 'org.apache.shardingsphere:shardingsphere-jdbc-core-spring-boot-starter:5.1.1'
implementation 'com.github.yitter:yitter-idgenerator:1.0.6'

5 Configuration files

 spring:
  shardingsphere:
    mode:
      type: Memory                                                   # 内存模式，元数据保存在当前进程中
    datasource:
      names: master-test$->{0..1},slave-test$->{0..1}
      master-test0:                                                  # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource                 # 连接池
        url: jdbc:mysql://127.0.0.1:3306/test0                       # 连接url
        username: root
        password: 123456
      master-test1:                                                  # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/test1
        username: root
        password: 123456
      slave-test0:                                                   # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3307/test0
        username: root
        password: 123456
      slave-test1:                                                   # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3307/test1
        username: root
        password: 123456

    rules:                                                           # 配置分库分表以及读写分离的规则
      sharding:                                                      # 配置分库分表规则
        tables:
          user:                                                      # 这个可以随便取，问题不大
            actual-data-nodes: master-test$->{0..1}.user$->{0..2}    # 实际节点名称，格式为 库名$->{0..n1}.表名$->{0..n2}
                                                                     # 其中n1、n2分别为库数量-1和表数量-1
                                                                     # 也可以使用${0..n1}的形式，但是会与Spring属性文件占位符冲突
                                                                     # 所以使用$->{0..n1}的形式

            database-strategy:                                       # 分库策略
              standard:                                              # 标准分库策略
                sharding-column: age                                 # 分库列名
                sharding-algorithm-name: age-mod                     # 分库算法名字
            table-strategy:                                          # 分表策略
              standard:                                              # 标准分表策略
                sharding-column: id                                  # 分表列名
                sharding-algorithm-name: id-mod                      # 分表算法名字

        sharding-algorithms:                                         # 配置分库和分表的算法
          age-mod:                                                   # 分库算法名字
            type: MOD                                                # 算法类型为取模
            props:                                                   # 算法配置的键名，所有算法配置都需要在props下
              sharding-count: 2                                      # 分片数量
          id-mod:                                                    # 分表算法名字
            type: MOD                                                # 算法类型为取模
            props:                                                   # 算法配置的键名，所有算法配置都需要在props下
              sharding-count: 3                                      # 分片数量

      readwrite-splitting:                                           # 配置读写分离规则
        data-sources:                                                # 数据源
          master-test0:                                              # 这个可以随便取，带有区分意义即可，比如这里表示的是主库test0的规则
            type: Static                                             # 静态类型
            load-balancer-name: round_robin                          # 负载均衡算法名字
            props:                                                   # 具体读写数据源需要配置在props下
              write-data-source-name: master-test0                   # 写数据源
              read-data-source-names: slave-test0                    # 读数据源
          master-test1:
            type: Static                                             # 静态类型
            load-balancer-name: round_robin                          # 负载均衡算法名字
            props:                                                   # 具体读写数据源需要配置在props下
              write-data-source-name: master-test1                   # 写数据源
              read-data-source-names: slave-test1                    # 读数据源
        load-balancers:                                              # 负载均衡配置
          round_robin:                                               # 跟上面负载均衡算法的名字对应
            type: ROUND_ROBIN                                        # 负载均衡算法
    props:
      sql-show: true                                                 # 打印SQL

The configuration file has been accompanied by detailed comments. If you have any doubts, you can check it:

• The author wrote the read- write separation article before, which introduces the configuration file in more detail.
• The sub-database sub-table article written by the author before, which introduces the configuration file in more detail
• Official website documentation

6 Prepare the test code

Use the MyBatis Plus Generator generator class to generate the code:

 public class CodeGenerator {
    public static void main(String[] args) {
        final String USER_TABLE_NAME = "User";
        FastAutoGenerator.create("jdbc:mysql://localhost:3306/test0", "root", "123456")
                .globalConfig(builder ->
                        builder.author("author").outputDir(System.getProperty("user.dir") + "/src/main/java").build())
                .packageConfig(builder ->
                        builder.parent("com.example.demo").moduleName("user").build())
                .strategyConfig(builder ->
                        builder.addInclude("user0")
                                .entityBuilder().enableLombok().disableSerialVersionUID().idType(IdType.ASSIGN_ID).convertFileName((entityName -> USER_TABLE_NAME))
                                .mapperBuilder().convertMapperFileName((entityName -> USER_TABLE_NAME + "Mapper")).convertXmlFileName((entityName -> USER_TABLE_NAME + "Mapper")).enableMapperAnnotation()
                                .serviceBuilder().convertServiceFileName((entityName -> "I" + USER_TABLE_NAME + "Service")).convertServiceImplFileName((entityName -> USER_TABLE_NAME + "Service"))
                                .controllerBuilder().enableRestStyle().convertFileName(entityName -> USER_TABLE_NAME + "Controller"))
                .templateEngine(new FreemarkerTemplateEngine())
                .execute();
    }
}

Entity class plus @Builder :

 //...
@Builder
public class User {
    //...
}

Modified UserController class:

 @RestController
@RequestMapping("/user")
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class UserController {
    private final IUserService userService;
    private final Random random = new Random();

    @GetMapping("/insert")
    public boolean insert() {
        return userService.save(User.builder().name("name").age(random.nextInt(100) + 1).build());
    }

    @GetMapping("/select")
    public List<User> select() {
        return userService.list();
    }
}

At the same time, a new snowflake id generator class is added (for the specific configuration method, please refer to MyBatis Plus official document ):

 public class IdGenerator {
    @Bean
    public IdentifierGenerator identifierGenerator() {
        return entity -> YitIdHelper.nextId();
    }
}

7 Test

Refresh the insert page a few times:

 http://localhost:8080/user/insert

You can see from the output that the inserts are all done in the master node:

And when querying:

 http://localhost:8080/user/select

The output is as follows:

is being queried from the node.

8 Reference code

Java Version:

• Github
• Code cloud
• GitCode

Kotlin Version:

• Github
• Code cloud
• GitCode