1 overview

This article mainly describes how to use ShardingSphere (sharding-JDBC) and MyBatisPlus to divide the database and table, the specific steps include:

  • Preparing the Database Environment
  • Ready to rely on
  • Writing configuration files
  • test

2 Prepare the database environment

2.1 Two databases and six tables

Have two libraries ready:

  • test0
  • test1

Create three tables with the same columns in each library:

  • user0
  • user1
  • user2

The fields are as follows:

That’s two libraries and six tables.

2.2 (Optional)MyBatis Plus GeneratorCode generation table

Because MyBatis Plus the Controller of the Generator to generate code is in accordance with the table name by default, this will generate like User0, User0Controller named, so there is to create a user table, only used for code generation, It does not store any data:

3 New Project

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

  • Druid
  • MyBatis Plus starter
  • MyBaits Plus Generator
  • Velocity core
  • ShardingSphere
  • Yitter(A snowflakeidGenerator)

Maven is as follows:

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.3.1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.5.0</version>
</dependency>
<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity-engine-core</artifactId>
    <version>2.3</version>
</dependency>
<dependency>
    <groupId>org.realityforge.org.jetbrains.annotations</groupId>
    <artifactId>org.jetbrains.annotations</artifactId>
    <version>1.7.0</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>com.github.yitter</groupId>
    <artifactId>yitter-idgenerator</artifactId>
    <version>1.0.6</version>
</dependency>
Copy the code

Gradle is as follows:

implementation 'com. Baomidou: mybatis - plus - the boot - starter: 3.4.3.1'
implementation 'org. Apache. Velocity, velocity - engine - core: 2.3'
implementation 'org.realityforge.org.jetbrains.annotations:org.jetbrains.annotations:1.7.0'
implementation 'com. Alibaba: druid: 1.2.6'
implementation 'org. Apache. Shardingsphere: sharding - JDBC - spring - the boot - starter: 4.4.1'
implementation 'com. Making. Yitter: yitter - idgenerator: 1.0.6'
Copy the code

4 Configuration File

The configuration file can be referred to the ShardingSphere documentation. Here is an example configuration:

spring:
  shardingsphere:
    datasource:
      names: test0,test1                                        The name of the library name is used for convenience
      test0:                                                   
        type: com.alibaba.druid.pool.DruidDataSource            Druid connection pool
        url: JDBC: mysql: / / 127.0.0.1:3306 / test0                  Connect to the url of test0
        username: root
        password: 123456
      test1:
        type: com.alibaba.druid.pool.DruidDataSource            Druid connection pool
        url: JDBC: mysql: / / 127.0.0.1:3306 / test1                  Connect to test1's URL
        username: root
        password: 123456
    sharding:
      default-database-strategy:                                # default library split policy
        inline:  
          sharding-column: age                                  # indicates sorting by age column
          algorithm-expression: test$->{age % 2}                Module 0 indicates library test0, module 1 indicates library test1
      tables:
        user:
          actual-data-nodes: test$->{0.. 1}.user$->{0.. 2}        Test $->{0.. 1} can represent test0 and test1 libraries
                                                                # user$->{0.. 2} represents three libraries user0, user1, and user2
          table-strategy:                                       # table splitting strategy
            inline:
              sharding-column: id                               Select * from 'id'; select * from 'id'
              algorithm-expression: user$->{id%3}               Select * from user0 where id%3 = 0
                                                                # id%3 = 1 and id%3 = 2
    props:
      sql:
        show:
          true                                                  # print SQL
Copy the code

ShardingShphere has built-in sharding algorithm as follows:

  • Automatic sharding algorithm: module sharding, hashing module sharding, scope sharding based on fragment capacity, scope sharding based on fragment boundary, automatic time segment sharding
  • Standard sharding algorithm: line expression sharding, time range sharding
  • Compound row expression sharding
  • HintRow expression sharding

If not, you can customize the sharding algorithm. For details, see the official document.

Test code generation

Use MyBaits Plus Generator to generate the corresponding code. Refer to my previous article for details. Here is the code to generate the class directly:

import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.*;

public class MyBatisPlusGenerator {
    public static void main(String[] args) {
        DataSourceConfig dataSourceConfig = new DataSourceConfig.Builder("jdbc:mysql://localhost:3306/test0"."root"."123456").build();
        String projectPath = System.getProperty("user.dir");
        StrategyConfig strategyConfig = new StrategyConfig.Builder().addInclude("user").build();
        GlobalConfig globalConfig = new GlobalConfig.Builder().outputDir(projectPath + "/src/main/java").openDir(false).build();
        PackageConfig packageConfig = new PackageConfig.Builder().moduleName("user").parent("com.example.demo").serviceImpl("service").build();
        newAutoGenerator(dataSourceConfig).global(globalConfig).packageInfo(packageConfig).strategy(strategyConfig).execute(); }}Copy the code

Change the UserController class as follows:

@RestController
@RequestMapping("/user")
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class UserController {
    private final Random random = new Random();
    private final UserServiceImpl service;
    @GetMapping("/select")
    public List<User> select(a){
        return service.list();
    }

    @GetMapping("/insert")
    public boolean insert(a){
        return service.save(User.builder().age(random.nextInt(80) +20).name("test name").email("[email protected]").build()); }}Copy the code

6 Adding snowflakesidThe generator

Add an @builder annotation to the User class and change the id generation strategy to idtype. ASSIGN_ID:

@Builder
public class User implements Serializable {
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
    / /...
}
Copy the code

Create a new ID generator class:

import com.baomidou.mybatisplus.core.incrementer.IdentifierGenerator;
import com.github.yitter.contract.IdGeneratorOptions;
import com.github.yitter.idgen.YitIdHelper;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;

@Component
public class IdGenerator implements IdentifierGenerator {
    final IdGeneratorOptions options = new IdGeneratorOptions((short) 1);

    @PostConstruct
    public void init(a) {
        YitIdHelper.setIdGenerator(options);
    }

    @Override
    public Long nextId(Object entity) {
        returnYitIdHelper.nextId(); }}Copy the code

When the ID is generated, the nextId() method is automatically called, and the ID generator can be replaced as needed with another snowflake ID generator or distributed ID generator.

Refer to the MyBaits Plus custom ID generator documentation for configuration.

7 test

Insert three data randomly and refresh the following page three times:

localhost:8080/test/user/insert
Copy the code

You can see that three insert operations are performed. The three insert operations are as follows:

  • test0.user2
  • test0.user0
  • test0.user2

Because they all happen to be even ages when they’re tested, and they’re all inserted into table test0.

View data:

http://localhost:8080/user/select
Copy the code

The following output is displayed:

Represents the result of a query on six tables and returns the final result aggregated.

8 Reference Source code

Java version:

  • Github
  • Yards cloud
  • CODE.CHINA

Kotlin version:

  • Github
  • Yards cloud
  • CODE.CHINA