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 Generator`Code 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 snowflakes`id`The 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

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

ShardingSphere + MyBatisPlus sub – library sub – table

1 overview

2 Prepare the database environment

2.1 Two databases and six tables

2.2 (Optional)`MyBatis Plus Generator`Code generation table

3 New Project

4 Configuration File

Test code generation

6 Adding snowflakes`id`The generator

7 test

8 Reference Source code

ShardingSphere + MyBatisPlus sub – library sub – table

1 overview

2 Prepare the database environment

2.1 Two databases and six tables

2.2 (Optional)MyBatis Plus GeneratorCode generation table

3 New Project

4 Configuration File

Test code generation

6 Adding snowflakesidThe generator

7 test

8 Reference Source code

Related Posts

High concurrency from zero (8) – simple implementation of RPC framework

Nuggets AMA: listen to old Money, the author of Redis deep Adventure and RPC Deep Understanding, talk about technology export and precipitation

Github has combed through the spring and Autumn recruitment process, sharing thousands of interview questions and answers, the interview process of documentation tutorial!

2.2 (Optional)`MyBatis Plus Generator`Code generation table

6 Adding snowflakes`id`The generator