1 overview

I have written two articles before:

  • ShardingSphere reads and writes are separated
  • ShardingSphere sub-database sub-table

Here, the two are combined to achieve read and write separation + sub-library sub-table function. This article will simplify the configuration of the environment and refer to the previous two articles for details.

2 the environment

  • MySQL 8.0.25(Docker)
  • MyBatis Plus 3.4.3.1
  • MyBatis Plus the Generator 3.5.0
  • Druid 1.2.6
  • ShardingSphere 4.1.1
  • Yitter 1.0.6(A snowflakeidGenerator)

3 Preparing the database environment

Since environment preparation is not the focus of this article, a master and slave replication environment can be set up by referring to this section.

MySQL > select * from MySQL;

  • Name:master
  • Port:3306
  • Database: two libraries (test0,test1)
  • Data tables: six tables, three per library (test0.user0,test0.user1,test0.user2,test1.user0,test1.user1,test1.user2)

From node environment:

  • Name:slave
  • Port:3307
  • Database: two libraries (test0,test1)
  • Data tables: six tables, three per library (test0.user0,test0.user1,test0.user2,test1.user0,test1.user1,test1.user2)

Master library configuration file:

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

Slave library configuration file:

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

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

4 New Project

Create a new project and introduce the following dependencies:

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

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

5 Configuration File

spring:
  shardingsphere:
    datasource:
      names: master-test0,master-test1,slave-test0,slave-test1                 Data source node name
                                                                               Master-test0 specifies the test0 library of the master node. Master-test1 specifies the test1 library of the master node
                                                                               # slave-test0 indicates the test0 library of the slave node, and slave-test1 indicates the test1 library of the slave node
      master-test0:
        type: com.alibaba.druid.pool.DruidDataSource                           # connection pool
        url: JDBC: mysql: / / 127.0.0.1:3306 / test0                                 Test0 library for primary node
        username: root
        password: 123456
      master-test1:
        type: com.alibaba.druid.pool.DruidDataSource
        url: JDBC: mysql: / / 127.0.0.1:3306 / test1                                 Test1 library for primary node
        username: root
        password: 123456
      slave-test0:
        type: com.alibaba.druid.pool.DruidDataSource
        url: JDBC: mysql: / / 127.0.0.1:3307 / test0                                 Test0 library from node, port 3307
        username: root
        password: 123456
      slave-test1:
        type: com.alibaba.druid.pool.DruidDataSource
        url: JDBC: mysql: / / 127.0.0.1:3307 / test1                                 Test1 library from node, port 3307
        username: root
        password: 123456
    sharding:
      default-database-strategy:
        inline:
          sharding-column: age                                                 Which column does the library belong to
          algorithm-expression: master-test$->{age % 2}                        The rule of dividing database is to mold the age
      tables:
        user:
          actual-data-nodes: master-test$->{0.. 1}.user$->{0.. 2}                Table name = [data source. Table name]
          table-strategy:
            inline:
              sharding-column: id                                              Table by which column
              algorithm-expression: user$->{id%3}                              # table rules, modulo id

      master-slave-rules:                                                      # Rules for read/write separation
        master-test0:                                                          # Which primary node
          master-datasource-name: master-test0                                 # specify the primary node name
          slave-data-source-names: slave-test0                                 # specify the secondary node name
        master-test1:
          master-datasource-name: master-test1
          slave-data-source-names: slave-test1
    props:
      sql:
        show:
          true                                                                 # print SQL
Copy the code

6 Prepare test code

Use MyBatis Plus Generator class to generate code:

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

Add @builder to the entity class and set the id type to idtype. ASSIGN_ID:

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

Modify the Controller class:

@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

Add a new snowflake ID generator class (please refer to MyBatis Plus official documentation for specific configuration methods) :

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

7 test

Refresh the insert page several times:

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

As you can see from the output, the inserts are all in the primary node:

And when querying:

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

The output is as follows:

Is queried from the node.

8 Reference Code

Java version:

  • Github
  • Yards cloud
  • CODE CHINA

Kotlin version:

  • Github
  • Yards cloud
  • CODE CHINA