Springboot+mybatis plus integrates Sharding-JDBC5.1.1 to realize single database and separate tables [the latest in the whole network]


1, Foreword

Xiaobian has been studying about database and table splitting recently. A few days ago, docker installed mycat to implement database and table splitting, but they all said that mycat has many bug s. Many people still prefer shardingsphere. In fact, it is a family bucket, which consists of JDBC, Proxy and Sidecar. Today, Xiaobian will simply integrate it with the simplest JDBC!
Now the latest version is 5.1.1. After a day of research, all problems have been solved and a single database and table have been completed!!

To learn about version 4.0.0, please take a look at what Xiaobian just wrote: Spring Boot+mybatis plus integrates Sharding-JDBC4.0.0 to realize single database and separate tables
If you want to see MYCAT, you can take a look at the article written by Xiaobian before. Ha: Docker installs MYCAT and Mysql for the practice of horizontal database and table classification

2, Trampled pit

1. data source problem

Do not use the Druid spring boot starter dependency. There will be problems during startup


Error message:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userMapper' defined in file 
 Invocation of init method failed; nested exception is 
 java.lang.IllegalArgumentException: Property 'sqlSessionFactory' 
 or 'sqlSessionTemplate' are required


Use a separate druid


It is recommended to use the default data source. Sharding JDBC is also the default data source used. It comes with Xiaobian. If you forget the druid, will there be a problem!!

type: com.zaxxer.hikari.HikariDataSource

2. the insert statement does not support table splitting routing to multiple data nodes

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

Look at the small article: solve the problem that table splitting routing is not supported

3, Import maven dependencies


<!-- lombok -->
<!-- mysql -->

<!-- mybatis-plus -->

4, New table

1. create two tables

Named: user_0,user_ one

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,
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


2. database structure

5, Frame global display

1. User entity class

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

public class UserController {

 private UserMapper userMapper;

 public void insertTest(){
  for (int i = 1 ; i < 10; i++) {
   User test = new User("king"+i,"male","data" + i);

3. mapper

We have omitted the service directly. Let's make it simple!!

public interface UserMapper extends BaseMapper<User> {

4. application.yml configuration

 port: 8089

  type: memory
 # Open or not
  # Data source (logical name)
  names: m1
  # Configure data source
  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
 # Slice configuration
  # Table fragmentation strategy
   # Name of logical table
   # Data node configuration using Groovy expression
   actual-data-nodes: m1.user_$->{0..1}
   # Configure policy
    # Standard sharding scenarios for single sharding keys
    sharding-column: cid
    # Partition algorithm name
    sharding-algorithm-name: user_inline
   key-generate-strategy: # Primary key generation policy
    column: cid # Primary key column
    key-generator-name: snowflake # Policy algorithm name (snowflake algorithm is recommended)
   type: SNOWFLAKE
   type: inline
    algorithm-expression: user_$->{cid % 2}
  # The log shows the specific SQL
  sql-show: true

 com.wang.test.demo: DEBUG

 mapper-locations: classpath:mapper/*.xml
 type-aliases-package: com.example.demo.entity
 #When mapping entities or attributes, remove the underscores in the table names and field names in the database, and map the address according to the hump naming method_ book ---> addressBook
 map-underscore-to-camel-case: true

5. startup class

public class DemoApplication {

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


6, Test insert nine pieces of data

This test strategy is: line expression fragmentation strategy: inline

1. insert data

Input: localhost:8089/test/insertTest


Partition succeeded

2. single query

public void selectOneTest(){

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


At this time, it will automatically obtain data from that table according to cid

3. full query

public void selectListTest(){

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


Since there are no conditions, he will summarize the two tables UNION ALL


4. paging query

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

public class MybatisPlusConfig {

 public MybatisPlusInterceptor mybatisPlusInterceptor() {
  MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
  interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
  return interceptor;
public void selectListPage(){
 IPage<User> page = new Page(1,6);
 IPage<User> userIPage = userMapper.selectPage(page,null);
 List<User> records = userIPage.getRecords();

We user_0 has 5 pieces of data, user_1 there are 4 pieces of data

We found that it will perform a paging query on all tables. If the data of the first table is insufficient, the value obtained from the paging of the other table will be added


When the page size is 3, a user_0 can satisfy the paging condition, and the user will be ignored_ 1.  

5. non partition attribute query

Let's put the user_ Change two gender of table 0 to female, and then query! See if the fields without fragmentation can only go to user_0 to query

public void selectListByGender(){

 List<User> list = userMapper.selectList(Wrappers.<User>lambdaQuery().eq(User::getGender, "female"));

It can be seen from the figure that the query is not a fragmented field query. Go back to the full connection table to query again. The efficiency is the same as that of the non fragmented table!!


6. fragment attribute comes from a table in query

public void selectList(){
 List<User> users = userMapper.selectList(Wrappers.<User>lambdaQuery().in(User::getCid,736989417020850176L,736989418119757824L));

We can find that when we perform an in query based on the fragment field, the sharding JDBC will identify the table from which it comes and improve efficiency. All tables will not be fully connected.


7, Summary

In this way, the simple test of the latest version of sharding JDBC and the solution of some pits have been completed. In general, the configuration is very laborious and there can be no errors!
See here, don't give Xiaobian a key to walk three times, thank you!!


Tags: Java Spring Spring Boot Distribution Back-end Microservices

Posted by tabatsoy on Wed, 01 Jun 2022 05:14:11 +0530