Spring Boot demo series (11): ShardingSphereJDBC + MyBatisPlus sub-database sub-table

1 Overview

This article mainly describes how to use ShardingSphereJDBC and MyBatisPlus for sub-database and sub-table. The specific steps include:

• Prepare the database environment
• ready to depend
• write configuration file
• test

2 Environment

• MyBatis Plus 3.5.1
• MyBatis Plus Generator 3.5.2
• Druid 1.2.10
• ShardingSphereJDBC 5.1.1
• MySQL 8.0.29 ( Docker )
• yitter-idgenerator 1.0.6
• Freemarker 2.3.31

3 Prepare the database environment

Prepare two libraries:

• test0
• test1

Create tables with the same three fields in the two libraries:

• user0
• user1
• user2

The fields are as follows:

In this way, two libraries and six tables are prepared.

4 New project

Create a new Spring Boot project and introduce the following dependencies:

• Druid
• MyBatis Plus starter
• MyBaits Plus Generator
• Freemarker
• ShardingSphere
• Yitter (a snowflake id generator)

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'

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>

5 Configuration files

The configuration file can refer to the ShardingSphere documentation , here is an example configuration:

 spring:
  shardingsphere:
    mode:
      type: Memory                                         # 内存模式，元数据保存在当前进程中
    datasource:
      names: test0,test1                                   # 数据源名称，这里有两个
      test0:                                               # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource       # 连接池
        url: jdbc:mysql://127.0.0.1:3306/test0             # 连接url
        username: root
        password: 123456
      test1:                                               # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/test1
        username: root
        password: 123456

    rules:
      sharding:
        tables:
          user:                                            # 这个可以随便取，问题不大
            actual-data-nodes: 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                            # 分片数量
    props:
      sql-show: true                                       # 打印SQL

Most of the configuration instructions are mentioned in the read-write separation article , here are some different configurations.

5.1 Data Node Configuration

spring.shardingsphere.rules.sharding.tables.<table-name>.actual-data-nodes , of which <table-name> can be any value, as long as the meaning is clear, for example, the value here is user .

actual-data-nodes followed by the specific node name, the node name is in the form of 库名.表名 , and multiple tables are separated by , , which supports inline expressions, which is the test$->{0..1}.user$->{0..2} .

test$->{0..1}.user$->{0..2} test0 、 test1 --- ，而表包含user0 、 user1 、 user2 . 文档${} $->{} ，但是前者与Spring配置文件占位符冲突， $->{} 。

5.2 Sub-database and sub-table strategy configuration

They are:

• spring.shardingsphere.rules.sharding.tables.<table-name>.database-strategy
• spring.shardingsphere.rules.sharding.tables.<table-name>.table-strategy

The configurations of the two are similar. Taking database-strategy as an example, the document mentions that the following properties need to be configured:

• sharding-columns : The name of the sharding column. For sharding, it indicates which column is used for sharding, and for sharding, it indicates which column is used for sharding.
• sharding-algorithm-name : The name of the sharding algorithm. The specific sharding algorithm is configured in sharding-algorithms .

5.3 Sharding Algorithm Configuration

spring.shardingsphere.rules.sharding.sharding-algorithms.<sharding-algorithm-name> ， <sharding-algorithm-name>是spring.shardingsphere.rules.sharding.tables.<table-name>.database-strategy.sharding-algorithm-name / spring.shardingsphere.rules.sharding.tables.<table-name>.table-strategy.sharding-algorithm-name名字，这里age-mod id-mod .

For the sharding algorithm, two properties need to be configured:

• type : the type of the sharding algorithm, here is MOD , the modulo algorithm
• props ：根据类型的不同，配置算法的不同属性，比如---22407e699d121c8e5d394d7043afb30f MOD类型可以配置的属性为sharding-count ， sharding-count表示分Number of slices

Built-in sharding algorithms include:

• Automatic sharding algorithm: including modulo sharding, hash modulo sharding, range sharding based on shard capacity, range sharding based on shard boundaries, automatic time segment sharding
• Standard fragmentation algorithm: including row expression fragmentation, time range fragmentation
• Compound sharding algorithm: including compound row expression sharding
• Hint algorithm: including Hint Line expression sharding

There is also a custom sharding algorithm that supports standard, composite, Hint types. Since there are too many algorithms and complex properties, please refer to the official documentation for the specific sharding algorithm.

6 Test code generation

Use MyBaits Plus Generator to generate the corresponding code. For specific use, please refer to the author 's previous article . Here is the code for the generated class:

 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().convertFileName((entityName -> USER_TABLE_NAME))
                                .mapperBuilder().convertMapperFileName((entityName -> USER_TABLE_NAME + "Mapper")).convertXmlFileName((entityName -> USER_TABLE_NAME + "Mapper"))
                                .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();
    }
}

Modify the UserController class as follows:

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

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

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

7 Add snowflake id generator

First modify the User class, add a @Builder annotation, and modify the id generation strategy, use IdType.ASSIGN_ID

 @Getter
@Setter
@Builder
public class User {
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
    private String name;
    private Integer age;
}

New id generator class:

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

In this way, when id 69764edb80fad9b39f5abaf7988f2335--- is generated, the nextId() method will be automatically called, and the id generator can be replaced with other snowflakes id as needed. id Generator or Distributed id Generator.

When configuring, please refer to the MyBaits Plus Custom ID Generator documentation .

8 test

First insert three pieces of data randomly, and refresh the following page three times:

 localhost:8080/user/insert

You can see that three insert operations are performed, and the three tables inserted are:

• test0.user2
• test0.user1
• test1.user0

View data:

 http://localhost:8080/user/select

The log output is as follows:

9 Reference source code

Java version:

• Github
• Code cloud
• GitCode

Kotlin Version:

• Github
• Code cloud
• GitCode