Springboot 2.2.5 integrates sharding JDBC 4.1.1 custom composite slicing algorithm to divide databases and tables, and configures master-slave separation

Time:2022-5-14

The blog is mainly to record their own learning process, which is convenient for later viewing. Of course, I hope it can help you.

explain

  1. The top name of the sub database and sub table middleware, no more nonsense, official website address:https://shardingsphere.apache.org/
  2. The database used in this article is mysql5 7, and realizes one master and one slave.
  3. The scenario is a sub table of the order table, and it should support only according to the user_ Put the ID of the user to be queried into the primary key of the scene_ ID, so that you can only order according to the primary key_ ID can be queried only by user_ ID to query.
  4. By the way, it supports master-slave separation. This is relatively simple. You can add configuration
  5. The complete code address is at the end!!

Official profile

  1. Positioned as a lightweight Java framework, it provides additional services in the JDBC layer of Java. It uses the client to connect directly to the database and provides services in the form of jar package without additional deployment and dependence. It can be understood as an enhanced version of JDBC Driver and is fully compatible with JDBC and various ORM frameworks.
  2. It is applicable to any ORM framework based on JDBC, such as JPA, hibernate, mybatis, spring JDBC template or directly using JDBC.
  3. Support any third-party database connection pool, such as DBCP, c3p0, bonecp, Druid, hikaricp, etc.
  4. Support any database that implements JDBC specification. At present, it supports mysql, Oracle, sqlserver, PostgreSQL and any database that complies with the SQL92 standard.
Springboot 2.2.5 integrates sharding JDBC 4.1.1 custom composite slicing algorithm to divide databases and tables, and configures master-slave separation

image.png

The first step is in POM Add dependencies to XML as follows

<!--  MySQL driver -- >
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<!--  Mybatisplus core library -- >
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.1</version>
</dependency>
<!-- sharding-jdbc -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>
<!--  Hutool tool -- >
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.5</version>
</dependency>
Note:
  1. The ORM framework in this article uses mybatis plus.
  2. The hutool tool is used to generate custom IDs.

The second step is in application YML configuration shardingsphere, mybatis plus related configuration

spring:
  application:
    name: shardingjdbc-demo-server
  shardingsphere:
    datasource:
      #Data source
      names: master,salve
      master:
        driver-class-name: com.mysql.cj.jdbc.Driver
        password: root
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://xxx:3306/db1
        username: root
      salve:
        driver-class-name: com.mysql.cj.jdbc.Driver
        password: root
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://xxx:3306/db2
        username: root
    sharding:
      #Master slave separation
      master-slave-rules:
        master:
          master-data-source-name: master
          slave-data-source-names: salve
      #Surface segmentation
      tables:
        my_order:
          #Main table partition rule table name
          actual-data-nodes: master.my_order_$->{0..3}
          #Primary key policy
#          key-generator:
#            column: id
#            type: MyShardingKeyGenerator
          table-strategy:
            #Slicing expression
#            inline:
#              algorithm-expression: order_$->{id.longValue() % 4}
#              sharding-column: id
            #Standard slice
#            standard:
#              sharding-column: id
              #Specifies the full path of the custom sharding algorithm class
#              precise-algorithm-class-name: com.jinhx.shardingjdbc.config.MyPreciseShardingAlgorithm
            #Composite slice
            complex:
              #Slice key
              sharding-columns: order_id,user_id
              #Specifies the full path of the custom sharding algorithm class
              algorithm-class-name: com.jinhx.shardingjdbc.config.MyComplexKeysShardingAlgorithm
#          defaultTableStrategy:
    #Open the SQL console and output the log
    props:
      sql:
        show: true

#Mybatis plus related configuration
mybatis-plus:
  #XML scanning, multiple directories are separated by commas or semicolons (tell mapper the corresponding XML file location)
  mapper-locations: classpath:com/jinhx/shardingjdbc/mapper/xml/*.xml
  #Alias package scanning path. Through this attribute, you can register aliases for classes in the package
  type-aliases-package: com.jinhx.shardingjdbc.entity
  configuration:
    #Do not enable L2 cache
    cache-enabled: false
    #Whether to enable automatic hump naming rule mapping: similar mapping from database column name to Java attribute hump naming
    map-underscore-to-camel-case: true
    #If the query result contains a column with null value, mybatis will not map this field when mapping
    call-setters-on-nulls: true
    #This configuration will print out the executed SQL, which can be used during development or testing
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

server:
  port: 8093

Step 3: create the database db1 in the main database and create the order table as follows

explain
  1. This paper does not divide the database, but only divides it into four tables, which are my_ order_ 0,my_ order_ 1,my_ order_ 2,my_ order_ three
sql
CREATE DATABASE db1;

use db1;

create table my_order_0
(
    order_ ID bigint not null comment 'order ID PK'
        primary key,
    user_ ID bigint not null comment 'user ID',
    Money bigint not null comment 'amount'
)
    Comment 'user order table';

##The structure of other tables is the same, which is omitted here

Step 4: create corresponding classes for table operations

  1. Use the code generator of mybatis plus to generate corresponding classes for database tables. For those who don’t understand, please refer to another article-Springboot 2.2.5 integrates the detailed tutorial of mybatis plus 3.3.1, configures multiple data sources and supports transactions, and comes with the tutorial of using code generator
  2. Manually create, including order, iorderservice, orderserviceimpl, etc., as follows
Order
package com.jinhx.shardingjdbc.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.jinhx.shardingjdbc.util.SnowFlakeUtil;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.Objects;

/**
 * Order
 *
 * @author jinhx
 * @since 2021-07-27
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("my_order")
public class Order implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     *The number of sub tables must be to the nth power of 2
     */
    public static final int TABLE_COUNT = 4;

    /**
     *Order ID PK
     */
    @TableId(type = IdType.INPUT)
    private Long orderId;

    /**
     *User ID
     */
    private Long userId;

    /**
     *Amount
     */
    private Long money;

    public void buildOrderId(){
        if (Objects.isNull(this.userId)){
            Throw new runtimeException ("userid is empty, OrderID cannot be generated");
        }
        this.orderId = SnowFlakeUtil.getSnowflakeId(SnowFlakeUtil.getDataCenterId(this.userId) & (TABLE_COUNT - 1));
    }

    public void buildUserId(Integer dataCenterId){
        if (Objects.isNull(dataCenterId)){
            Throw new runtimeException ("datacenter ID is empty, unable to generate userid");
        }
        this.userId = SnowFlakeUtil.getSnowflakeId(dataCenterId & (TABLE_COUNT - 1));
    }

}
IOrderService
package com.jinhx.shardingjdbc.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.jinhx.shardingjdbc.entity.Order;

import java.util.List;

/**
 * IOrderService
 *
 * @author jinhx
 * @since 2021-07-27
 */
public interface IOrderService extends IService<Order> {

    /**
     *Query according to orderids
     *
     * @param orderIds orderIds
     * @return List<Order>
     */
    List<Order> selectByOrderIds(List<Long> orderIds);

    /**
     *Query by userids
     *
     * @param userIds userIds
     * @return List<Order>
     */
    List<Order> selectByUserIds(List<Long> userIds);

    /**
     *Batch insert
     *
     * @param orders orders
     */
    void insertOrders(List<Order> orders);

}
OrderServiceImpl
package com.jinhx.shardingjdbc.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jinhx.shardingjdbc.entity.Order;
import com.jinhx.shardingjdbc.mapper.OrderMapper;
import com.jinhx.shardingjdbc.service.IOrderService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * OrderServiceImpl
 *
 * @author jinhx
 * @since 2021-07-27
 */
@Service
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements IOrderService {

    /**
     *Query according to orderids
     *
     * @param orderIds orderIds
     * @return List<Order>
     */
    @Override
    public List<Order> selectByOrderIds(List<Long> orderIds) {
        return baseMapper.selectBatchIds(orderIds);
    }

    /**
     *Query by userids
     *
     * @param userIds userIds
     * @return List<Order>
     */
    @Override
    public List<Order> selectByUserIds(List<Long> userIds) {
        return baseMapper.selectList(new LambdaQueryWrapper<Order>()
                .in(CollectionUtils.isNotEmpty(userIds), Order::getUserId, userIds));
    }

    /**
     *Batch insert
     *
     * @param orders orders
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void insertOrders(List<Order> orders) {
        if (CollectionUtils.isNotEmpty(orders)){
            if (orders.stream().mapToInt(item -> baseMapper.insert(item)).sum() != orders.size()){
                log. Error ("failed to insert order table in batch, orders = {}" + orders);
                Throw new runtimeException ("failed to insert order table in batch");
            }
        }
    }

}

Step 5: configure mybatisplus as follows

1. Add @ mapperscan annotation in the startup class mybatisplusapplication, which writes the path of mapper in the generated file for scanning mapper files.
package com.jinhx.shardingjdbc;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.jinhx.shardingjdbc.mapper")
@SpringBootApplication
public class ShardingjdbcApplication {

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

}
2. Create the mybatisplus configuration class, mybatisplusconfig, which is mainly used to configure the use of some plug-ins. This step can be omitted
package com.jinhx.shardingjdbc.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 *Mybatis plus configuration class
 *
 * @author jinhx
 * @since 2021-07-27
 */
@EnableTransactionManagement
@Configuration
public class MybatisPlusConfig {

    /**
     *Mybatis plus SQL execution efficiency plug-in [production environment can be closed], set dev test environment to open
     */
//    @Bean
//    @Profile({"dev", "qa"})
//    public PerformanceInterceptor performanceInterceptor() {
//        PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
//        performanceInterceptor.setMaxTime(1000);
//        performanceInterceptor.setFormat(true);
//        return performanceInterceptor;
//    }

    /**
     *Paging plug-in
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        //After setting the requested page to be larger than the maximum page, true will be called back to the home page, false will continue to request, and the default is false
         paginationInterceptor.setOverflow(false);
        //Set the maximum number of single page restrictions, 500 by default, - 1 unlimited
         paginationInterceptor.setLimit(500);
        //Turn on the join optimization of count, only for some left joins
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }
}

Step 6: create a custom composite fragmentation algorithm class mycomplexkeysshardingalgorithm, and pay attention to replacing application Full path in YML

package com.jinhx.shardingjdbc.config;

import com.jinhx.shardingjdbc.util.SnowFlakeUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 *Configuring sharding JDBC composite sharding algorithm
 *According to the ID and age calculation, determine which table to route to
 *Currently, the = and in operations are processed. Other operations, such as >, < and other range operations, are not supported.
 *
 * @author jinhx
 * @since 2021-07-27
 */
@Slf4j
public class MyComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {

    /**
     * orderId
     */
    private static final String COLUMN_ORDER_ID = "order_id";

    /**
     * userId
     */
    private static final String COLUMN_USER_ID = "user_id";

    /**
     *Rewriting composite slicing algorithm
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
        if (!shardingValue.getColumnNameAndRangeValuesMap().isEmpty()) {
            Throw new runtimeException ("all conditions are empty, unable to route to specific tables, and range query is not supported temporarily");
        }

        //Get OrderID
        Collection<Long> orderIds = shardingValue.getColumnNameAndShardingValuesMap().getOrDefault(COLUMN_ORDER_ID, new ArrayList<>(1));
        //Get userid
        Collection<Long> userIds = shardingValue.getColumnNameAndShardingValuesMap().getOrDefault(COLUMN_USER_ID, new ArrayList<>(1));

        if (CollectionUtils.isEmpty(orderIds) && CollectionUtils.isEmpty(userIds)) {
            Throw new runtimeException ("OrderID and userid fields are empty at the same time, unable to route to a specific table, and range query is not supported temporarily");
        }

        //Get the set of suffix sequence numbers of the table to be queried finally. The input parameter order cannot be reversed
        List<Integer> tableNos = getTableNoList(orderIds, userIds);

        return tableNos.stream()
                //Calculate the remainder of the number of available tables to obtain the suffix of the real table
//                .map(idSuffix -> String.valueOf(idSuffix % availableTargetNames.size()))
                //Splice to get the real table
                .map(tableSuffix -> availableTargetNames.stream().filter(targetName -> targetName.endsWith(String.valueOf(tableSuffix))).findFirst().orElse(null))
                .filter(Objects::nonNull)
                .collect(Collectors.toList());
    }

    /**
     *Get the set of suffix sequence numbers of the table to be queried finally
     *
     *@ param orderids OrderID field collection
     *@ param userids userid field collection
     *@ return the set of suffix serial numbers of the final table to query
     */
    private List<Integer> getTableNoList(Collection<Long> orderIds, Collection<Long> userIds) {
        List<Integer> result = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(orderIds)){
            //Get epitope information
            result.addAll(orderIds.stream()
                    .filter(item -> Objects.nonNull(item) && item > 0)
                    .map(item -> (int) SnowFlakeUtil.getDataCenterId(item))
                    .collect(Collectors.toList()));
        }

        if (CollectionUtils.isNotEmpty(userIds)) {
            //Get epitope information
            result.addAll(userIds.stream().filter(item -> Objects.nonNull(item) && item > 0)
                    .map(item -> (int) SnowFlakeUtil.getDataCenterId(item))
                    .collect(Collectors.toList()));
        }

        if (CollectionUtils.isNotEmpty(result)) {
            log. Info ("sharing JDBC successfully parses the suffix of the routing table, redenvelope IDS = {} UIDs = {} routing table suffix list = {}", orderids, userids, result);
            //Merge de duplication
            return result.stream().distinct().collect(Collectors.toList());
        }
        log. Error ("sharing JDBC failed to resolve the suffix of the routing table, redenvelope IDS = {} UIDs = {}", orderids, userids);
        Throw new runtimeException ("OrderID, userid resolution routing table suffix is empty, unable to route to specific table, range query is not supported temporarily");
    }

}

Step 7: write a unit test class, shardingjdbc application tests, and test it

testing procedure
  1. First run the insertorderstest method to insert data into the database. After running, check whether the four tables have data. In theory, the data should be uniform
  2. Pick up the order of several pieces of data randomly from the four tables_ ID, and then run selectbyorderidstest to see if all data can be found. This step is to verify that only order is used_ Whether the routing query with ID is normal
  3. Pick up several pieces of data randomly from the four tables_ ID, and then run selectbyuseridstest to see if the data can be found. This step is to verify that only the user_ Whether the routing query with ID is normal
ShardingjdbcApplicationTests
package com.jinhx.shardingjdbc;

import com.jinhx.shardingjdbc.entity.Order;
import com.jinhx.shardingjdbc.service.IOrderService;
import lombok.extern.slf4j.Slf4j;
import org.assertj.core.util.Lists;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@Slf4j
//Get the startup class, load the configuration, and determine the loading method of loading the spring program. It goes back to find the main configuration startup class (annotated by @ springbootapplication)
@SpringBootTest
class ShardingjdbcApplicationTests {

    @Autowired
    private IOrderService iOrderService;

    @Test
    void selectByOrderIdsTest() {
        List<Long> orderIds = Lists.newArrayList(1443844581547311109L, 1443844581547442181L, 1443844581547573255L, 1443844581547704327L);
        log.info(iOrderService.selectByOrderIds(orderIds).toString());
    }

    @Test
    void selectByUserIdsTest() {
        List<Long> userIds = Lists.newArrayList(1443844581547311108L, 1443844581547311106L, 1443844581547442180L, 1443844581547704326L);
        log.info(iOrderService.selectByUserIds(userIds).toString());
    }

    @Test
    void insertOrdersTest() {
        List<Order> orders = Lists.newArrayList();
        for (int i = 1;i < 100;i++){
            Order order = new Order();
            order.buildUserId(i);
            order.setMoney(i * 1000L);
            order.buildOrderId();
            orders.add(order);
        }
        log.info("orders={}", orders);
        iOrderService.insertOrders(orders);
    }

    @BeforeEach
    void testBefore(){
        log. Info (test start;
    }

    @AfterEach
    void testAfter(){
        log. Info ("test end;
    }

}
Full code address:https://github.com/Jinhx128/springboot-demo
Note: this project contains multiple modules. The codes used in this paper are under the shardingjdbc demo module

Postscript: this sharing is over. My level is limited. It is inevitable that there are mistakes or omissions. I hope you can correct and understand. Please comment and leave a message.