SpringBoot + Mybatis-Plus integrates Sharding-JDBC 5.1.1 to implement single-database table seating

I. Preface

The editor has been studying things about sharding and table sharding recently, and docker installed mycat a few days ago to implement sharding and table-splitting, but they are saying that mycat has many bugs. Many people still tend to shardingsphere, in fact, it is a family bucket, composed of JDBC, Proxy and Sidecar, Xiaobian today with the simplest JDBC to simply integrate!

Now the latest version is 5.1.1, after a day of research to solve all the problems, the single database table has been completed!!

Second, the pit that has been stepped on

1. Data source issues

Do not use druid-spring-boot-starter dependencies, there will be problems with startup

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.21</version>
</dependency>           

Error message:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userMapper' defined in file 
[D:\jiawayun\demo\target\classes\com\example\demo\mapper\UserMapper.class]:
 Invocation of init method failed; nested exception is 
 java.lang.IllegalArgumentException: Property 'sqlSessionFactory' 
 or 'sqlSessionTemplate' are required           

==Solution:==

Use a separate druid

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>           

It is recommended to use ==Default Data Source==, sharding-jdbc is also the default data source used, the editor uses its own data, forget druid will there be a problem later!!

type: com.zaxxer.hikari.HikariDataSource           

2. The insert statement does not support table splitting to route to multiple data nodes

Error message:

Insert statement does not support sharding table routing to multiple data nodes.

Solution:

Solve the problem that table splitting routing is not supported: https://blog.csdn.net/qq_52423918/article/details/125004312

Third, import maven dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
    <exclusions>
        <exclusion>
            <groupId>org.junit.vintage</groupId>
            <artifactId>junit-vintage-engine</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.1.1</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<!-- lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.10</version>
</dependency>
<!--jdbc-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- mysql -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<!-- mybatis-plus -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>           

4. Create a new table

1. Create two tables

Named as: user_0, user_1

CREATE TABLE `user_0`  (
  `cid` bigint(25) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `data` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;           

2. Database structure

5. Global display of the framework

1. User entity class

@Data
public class User implements Serializable {
    private static final long serialVersionUID = 337361630075002456L;

    private Long cid;

    private String name;

    private String gender;

    private String data;

}           

2. controller

@RestController
@RequestMapping("/test")
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @GetMapping("/insertTest")
    public void insertTest(){
        for (int i = 1 ; i < 10; i++) {
            User test = new User("王"+i,"男","数据" + i);
            userMapper.insert(test);
        }
    }
}           

3. mapper

We just omitted the service, simple ha!!

public interface UserMapper extends BaseMapper<User> {
}           

4. application.yml configuration

server:
  port: 8089

spring:
  shardingsphere:
    mode:
      type: memory
    # 是否开启
    datasource:
      # 数据源（逻辑名字）
      names: m1
      # 配置数据源
      m1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test?useSSL=false&autoReconnect=true&characterEncoding=UTF-8&serverTimezone=UTC
        username: root
        password: root
    # 分片的配置
    rules:
      sharding:
        # 表的分片策略
        tables:
          # 逻辑表的名称
          user:
            # 数据节点配置，采用Groovy表达式
            actual-data-nodes: m1.user_$->{0..1}
            # 配置策略
            table-strategy:
              # 用于单分片键的标准分片场景
              standard:
                sharding-column: cid
                # 分片算法名字
                sharding-algorithm-name: user_inline
            key-generate-strategy: # 主键生成策略
              column: cid  # 主键列
              key-generator-name: snowflake  # 策略算法名称(推荐使用雪花算法)
        key-generators:
          snowflake:
            type: SNOWFLAKE
        sharding-algorithms:
          user_inline:
            type: inline
            props:
              algorithm-expression: user_$->{cid % 2}
    props:
      # 日志显示具体的SQL
      sql-show: true


logging:
  level:
    com.wang.test.demo: DEBUG

mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.demo.entity
  configuration:
    #在映射实体或者属性时，将数据库中表名和字段名中的下划线去掉，按照驼峰命名法映射 address_book ---> addressBook
    map-underscore-to-camel-case: true           

5. Start the class

@MapperScan("com.example.demo.mapper")
@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}           

6. Test inserts nine pieces of data

==The test strategy is: row expression sharding strategy: inline==

1. Insert data

Enter: localhost:8089/test/insertTest

==Sharding successful==

2. Single query

@GetMapping("/selectOneTest")
public void selectOneTest(){

    User user = userMapper.selectOne(Wrappers.<User>lambdaQuery().eq(User::getCid,736989417020850176L));
    System.out.println(user);

}           

At this time, he will automatically fetch the data in that table according to the CID

3. Full query

@GetMapping("/selectListTest")
public void selectListTest(){

    List<User> list = userMapper.selectList(null);
    System.out.println(list);

}           

SINCE THERE ARE NO CONDITIONS, HE WILL GO AND SUMMARIZE THE TWO TABLES, UNION ALL

4. Paginated queries

You need to configure the mybatis-plus paging configuration class first:

@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}
@GetMapping("/selectListPage")
public void selectListPage(){
    IPage<User> page = new Page(1,6);
    IPage<User> userIPage = userMapper.selectPage(page,null);
    List<User> records = userIPage.getRecords();
    System.out.println(records);
}           

We have 5 pieces of data user_0 and 4 pieces of data user_1

==We find that it will go to all tables to perform a pagination query, the first table data is not enough, will add the value obtained by the other table pagination ==

==When the paging size is 3, a user_0 can meet the paging condition and ignore the user_1 paging data. ==

5. Non-sharded attribute queries

Let's first change the gender of the user_0 table to two to female, and then make an inquiry! See if fields without shards can be queried just user_0

@GetMapping("/selectListByGender")
public void selectListByGender(){

    List<User> list = userMapper.selectList(Wrappers.<User>lambdaQuery().eq(User::getGender, "女"));
    System.out.println(list);
}           

There is a figure to see: it is not a sharded field query, go back to the fully connected table to query again, the efficiency is the same as not dividing the table! !

6. The shard attribute comes from a table in query

@GetMapping("/selectInList")
public void selectList(){
    List<User> users = userMapper.selectList(Wrappers.<User>lambdaQuery().in(User::getCid,736989417020850176L,736989418119757824L));
    System.out.println(users);
}           

We can find that when we query in based on shard fields, sharding-jdbc will identify which table comes from to improve efficiency, and not all tables will be fully joined.

VII. Summary

This completes the simple testing of the latest version of sharding-jdbc and the solution of some pits, and in general the configuration is very laborious, there can be no certain errors!