【sharding-jdbc和mybatis-plus的数据分片复合路由】

maven文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>springboot-shardingsphere</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot-shardingsphere</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>

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


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
            <version>8.0.13</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
            <scope>compile</scope>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- for spring boot -->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>
        <!-- for spring namespace -->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>3.1.0</version>
        </dependency>

        <!-- 加载jdbc连接数据库 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

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

        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.75</version>
        </dependency>

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


    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

application.properties文件

server.port=8089


mybatis-plus.mapper-locations=classpath*:mapper/*.xml

# 垂直拆分（不同的表在不同的库中）
sharding.jdbc.datasource.names=db0,db1
# #当遇到同样名字的时候，是否允许覆盖注册
spring.main.allow-bean-definition-overriding=true
# 打印执行的数据库以及语句
sharding.jdbc.config.props..sql.show=true
#配置默认的数据库
sharding.jdbc.config.sharding.default-data-source-name=db0


# 第一个数据库
sharding.jdbc.datasource.db0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.db0.jdbc-url=jdbc:mysql://localhost:3306/ds-01?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
sharding.jdbc.datasource.db0.username=root
sharding.jdbc.datasource.db0.password=admin

# 第二个数据库
sharding.jdbc.datasource.db1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/ds-02?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
sharding.jdbc.datasource.db1.username=root
sharding.jdbc.datasource.db1.password=admin


sharding.jdbc.config.sharding.tables.test_user.actual-data-nodes=db$->{0..1}.test_user
# 分库策略id
sharding.jdbc.config.sharding.tables.test_user.database-strategy.inline.sharding-column=id
# 分库策略
sharding.jdbc.config.sharding.tables.test_user.database-strategy.inline.algorithm-expression=db$->{id%2}
#分表策略id
sharding.jdbc.config.sharding.tables.test_user.table-strategy.inline.sharding-column=id
#分表策略
sharding.jdbc.config.sharding.tables.test_user.table-strategy.inline.algorithm-expression=test_user


# 配置数据节点，这里是按月分表，时间范围设置在202201 ~ 210012 以id
#sharding.jdbc.config.sharding.tables.log.actual-data-nodes=db0.log_$->{2022..2022}0$->{1..9},db0.log_$->{2022..2022}1$->{0..2}
## 使用标准分片策略，配置分片字段
#sharding.jdbc.config.sharding.tables.log.table-strategy.standard.sharding-column=id
#sharding.jdbc.config.sharding.tables.log.key-generator-column-name=id
#sharding.jdbc.config.sharding.tables.log.table-strategy.standard.precise-algorithm-class-name=com.example.springbootshardingsphere.system.config.SnowFlakeLogShard

#单个分片
#sharding.jdbc.config.sharding.tables.log.actual-data-nodes=db0.log_$->{2023..2023}0$->{1..9},db0.log_$->{2023..2023}1$->{0..2}
## 使用标准分片策略，配置分片字段
#sharding.jdbc.config.sharding.tables.log.table-strategy.standard.sharding-column=shardtime
##配置分片字段规则
#sharding.jdbc.config.sharding.tables.log.table-strategy.standard.precise-algorithm-class-name=com.example.springbootshardingsphere.system.config.SnowFlakeTimeShard

#复合分片
sharding.jdbc.config.sharding.tables.log.actual-data-nodes=db0.log_$->{2023..2023}0$->{1..9},db0.log_$->{2023..2023}1$->{0..2}
# 使用标准分片策略，配置分片字段
sharding.jdbc.config.sharding.tables.log.table-strategy.complex.sharding-columns=id,shardtime
sharding.jdbc.config.sharding.tables.log.table-strategy.complex.algorithm-class-name=com.example.springbootshardingsphere.system.config.CustomComplexKeysShardingAlgorithm

#适用于范围
#sharding.jdbc.config.sharding.tables.log.table-strategy.standard.range-algorithm-class-name=com.example.springbootshardingsphere.system.config.TimeShardingAlgorithm


# 配置主键以及生成算法
#sharding.jdbc.config.sharding.tables.log.key-generator-column-name=id
#sharding.jdbc.config.sharding.tables.log.key-generator-class-name=com.example.springbootshardingsphere.system.config.AutoIncrementKeyGenerator

sharding.jdbc.config.sharding.binding-tables=test_user

启动类

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

@SpringBootApplication
@MapperScan("com.example.springbootshardingsphere.**.mapper")
public class SpringbootShardingsphereApplication {

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

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }

}

复合分片配置

package com.example.springbootshardingsphere.system.config;

import com.alibaba.fastjson.JSONObject;
import com.example.springbootshardingsphere.system.utils.SnowFlakeUtil;
import com.google.common.collect.Range;
import io.shardingsphere.api.algorithm.sharding.ListShardingValue;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
import io.shardingsphere.api.algorithm.sharding.ShardingValue;
import io.shardingsphere.api.algorithm.sharding.complex.ComplexKeysShardingAlgorithm;
import lombok.extern.slf4j.Slf4j;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Classname CustomComplexKeysShardingAlgorithm
 * @Description TODO
 * @Date 2023/2/23 17:56
 * @Created by XiongXiong
 * @Author: X.I.O
 */
@Slf4j
public class CustomComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm{

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, Collection<ShardingValue> shardingValues) {
        Collection<String> routTables = new HashSet<>();
        Collection id=getColumnValue(shardingValues,"id");
        Collection shardTime=getColumnValue(shardingValues, "shardtime");
        if(id!=null){
            Object[] objects=id.toArray();
            if(objects.length>0){
                Object val=objects[0];
                SimpleDateFormat dft=new SimpleDateFormat("yyyyMM");
                //获取主键id
                Date date=SnowFlakeUtil.getTimeBySnowFlakeId(Long.parseLong(String.valueOf(val)));
                routTables.add("log_"+dft.format(date));
                log.info("走的是id路由");
                return routTables;
            }
        }
        if(shardTime != null){
            Object[] objects=shardTime.toArray();
            if (objects.length > 0) {
                Object val=objects[0];
                routTables.add("log_"+val);
                log.info("走的是时间分片路由");
                return routTables;
            }
        }
        return routTables;
    }


    private Collection getColumnValue(Collection<ShardingValue> shardingValues,String key){
        if(shardingValues == null){
            return null;
        }
        for (ShardingValue shardingValue : shardingValues) {
            if(shardingValue instanceof ListShardingValue){
                ListShardingValue listShardingValue=(ListShardingValue) shardingValue;
                if(listShardingValue.getColumnName().equals(key)){
                    return listShardingValue.getValues();
                }
            }
        }
        return null;
    }
}

单个分片配置

package com.example.springbootshardingsphere.system.config;

import com.example.springbootshardingsphere.system.utils.SnowFlakeUtil;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

/**
 * @Classname SnowFlakeLogShard
 * @Description TODO
 * @Date 2023/2/23 16:08
 * @Created by XiongXiong
 * @Author: X.I.O
 */
public class SnowFlakeLogShard implements PreciseShardingAlgorithm<Long> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        Long id = (Long) preciseShardingValue.getValue();
        // 如果 id 是 string ，可以 id.hashcode()
        Date date=SnowFlakeUtil.getTimeBySnowFlakeId(id);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        String tableName="log_"+sdf.format(date);
        return tableName;
    }
}

sql脚本

/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 50737
 Source Host           : localhost:3306
 Source Schema         : ds-01

 Target Server Type    : MySQL
 Target Server Version : 50737
 File Encoding         : 65001

 Date: 23/02/2023 20:57:29
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for log_202301
-- ----------------------------
DROP TABLE IF EXISTS `log_202301`;
CREATE TABLE `log_202301`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `shardtime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `createdate` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

ID雪花算法工具类

package com.example.springbootshardingsphere.system.utils;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @Classname SnowFlakeUtil
 * @Description TODO
 * @Date 2023/2/23 15:30
 * @Created by XiongXiong
 * @Author: X.I.O
 */
public class SnowFlakeUtil {

    private static SnowFlakeUtil snowFlakeUtil;
    static {
        snowFlakeUtil = new SnowFlakeUtil();
    }

    // 初始时间戳(纪年)，可用雪花算法服务上线时间戳的值
    // 1650789964886：2022-04-24 16:45:59
    private static final long INIT_EPOCH = 1650789964886L;

    // 时间位取&
    private static final long TIME_BIT = 0b1111111111111111111111111111111111111111110000000000000000000000L;

    // 记录最后使用的毫秒时间戳，主要用于判断是否同一毫秒，以及用于服务器时钟回拨判断
    private long lastTimeMillis = -1L;

    // dataCenterId占用的位数
    private static final long DATA_CENTER_ID_BITS = 5L;

    // dataCenterId占用5个比特位，最大值31
    // 0000000000000000000000000000000000000000000000000000000000011111
    private static final long MAX_DATA_CENTER_ID = ~(-1L << DATA_CENTER_ID_BITS);

    // dataCenterId
    private long dataCenterId;

    // workId占用的位数
    private static final long WORKER_ID_BITS = 5L;

    // workId占用5个比特位，最大值31
    // 0000000000000000000000000000000000000000000000000000000000011111
    private static final long MAX_WORKER_ID = ~(-1L << WORKER_ID_BITS);

    // workId
    private long workerId;

    // 最后12位，代表每毫秒内可产生最大序列号，即 2^12 - 1 = 4095
    private static final long SEQUENCE_BITS = 12L;

    // 掩码（最低12位为1，高位都为0），主要用于与自增后的序列号进行位与，如果值为0，则代表自增后的序列号超过了4095
    // 0000000000000000000000000000000000000000000000000000111111111111
    private static final long SEQUENCE_MASK = ~(-1L << SEQUENCE_BITS);

    // 同一毫秒内的最新序号，最大值可为 2^12 - 1 = 4095
    private long sequence;

    // workId位需要左移的位数 12
    private static final long WORK_ID_SHIFT = SEQUENCE_BITS;

    // dataCenterId位需要左移的位数 12+5
    private static final long DATA_CENTER_ID_SHIFT = SEQUENCE_BITS + WORKER_ID_BITS;

    // 时间戳需要左移的位数 12+5+5
    private static final long TIMESTAMP_SHIFT = SEQUENCE_BITS + WORKER_ID_BITS + DATA_CENTER_ID_BITS;

    /**
     * 无参构造
     */
    public SnowFlakeUtil() {
        this(1, 1);
    }

    /**
     * 有参构造
     * @param dataCenterId
     * @param workerId
     */
    public SnowFlakeUtil(long dataCenterId, long workerId) {
        // 检查dataCenterId的合法值
        if (dataCenterId < 0 || dataCenterId > MAX_DATA_CENTER_ID) {
            throw new IllegalArgumentException(
                    String.format("dataCenterId 值必须大于 0 并且小于 %d", MAX_DATA_CENTER_ID));
        }
        // 检查workId的合法值
        if (workerId < 0 || workerId > MAX_WORKER_ID) {
            throw new IllegalArgumentException(String.format("workId 值必须大于 0 并且小于 %d", MAX_WORKER_ID));
        }
        this.workerId = workerId;
        this.dataCenterId = dataCenterId;
    }

    /**
     * 获取唯一ID
     * @return
     */
    public static Long getSnowFlakeId() {
        long currentTimeMillis = System.currentTimeMillis();
        return snowFlakeUtil.nextId(currentTimeMillis);
    }

    public synchronized long nextId(){
        long currentTimeMillis = System.currentTimeMillis();
        return nextId(currentTimeMillis);
    }

    public synchronized long nextId(Date date){
        if(date == null){
            long currentTimeMillis = System.currentTimeMillis();
            return nextId(currentTimeMillis);
        }else {
          long timeMills=date.getTime();
          return nextId(timeMills);
        }
    }
    /**
     * 通过雪花算法生成下一个id，注意这里使用synchronized同步
     * @return 唯一id
     */
    public synchronized long nextId(Long currentTimeMillis) {

        // 当前时间小于上一次生成id使用的时间，可能出现服务器时钟回拨问题
        if (currentTimeMillis < lastTimeMillis) {
            throw new RuntimeException(
                    String.format("可能出现服务器时钟回拨问题，请检查服务器时间。当前服务器时间戳：%d，上一次使用时间戳：%d", currentTimeMillis,
                            lastTimeMillis));
        }
        if (currentTimeMillis == lastTimeMillis) {
            // 还是在同一毫秒内，则将序列号递增1，序列号最大值为4095
            // 序列号的最大值是4095，使用掩码（最低12位为1，高位都为0）进行位与运行后如果值为0，则自增后的序列号超过了4095
            // 那么就使用新的时间戳
            sequence = (sequence + 1) & SEQUENCE_MASK;
            if (sequence == 0) {
                currentTimeMillis = getNextMillis(lastTimeMillis);
            }
        } else { // 不在同一毫秒内，则序列号重新从0开始，序列号最大值为4095
            sequence = 0;
        }
        // 记录最后一次使用的毫秒时间戳
        lastTimeMillis = currentTimeMillis;
        // 核心算法，将不同部分的数值移动到指定的位置，然后进行或运行
        // <<：左移运算符, 1 << 2 即将二进制的 1 扩大 2^2 倍
        // |：位或运算符, 是把某两个数中, 只要其中一个的某一位为1, 则结果的该位就为1
        // 优先级：<< > |
        return
                // 时间戳部分
                ((currentTimeMillis - INIT_EPOCH) << TIMESTAMP_SHIFT)
                        // 数据中心部分
                        | (dataCenterId << DATA_CENTER_ID_SHIFT)
                        // 机器表示部分
                        | (workerId << WORK_ID_SHIFT)
                        // 序列号部分
                        | sequence;
    }

    /**
     * 获取指定时间戳的接下来的时间戳，也可以说是下一毫秒
     * @param lastTimeMillis 指定毫秒时间戳
     * @return 时间戳
     */
    private long getNextMillis(long lastTimeMillis) {
        long currentTimeMillis = System.currentTimeMillis();
        while (currentTimeMillis <= lastTimeMillis) {
            currentTimeMillis = System.currentTimeMillis();
        }
        return currentTimeMillis;
    }

    /**
     * 获取随机字符串,length=13
     * @return
     */
    public static String getRandomStr() {
        return Long.toString(getSnowFlakeId(), Character.MAX_RADIX);
    }

    /**
     * 从ID中获取时间
     * @param id 由此类生成的ID
     * @return
     */
    public static Date getTimeBySnowFlakeId(long id) {
        return new Date(((TIME_BIT & id) >> 22) + INIT_EPOCH);
    }

}

Log对象

package com.example.springbootshardingsphere.book.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.util.Date;
import lombok.Data;

/**
 * 
 * @TableName log_202301
 */
@TableName(value ="log")
@Data
public class Log implements Serializable {
    /**
     * 
     */
    @TableId
    private String id;

    /**
     * 
     */
    private String name;

    /**
     * 
     */
    private String note;


    private String shardtime;



    /**
     * 
     */
    private Date createdate;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;



    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", id=").append(id);
        sb.append(", name=").append(name);
        sb.append(",shardtime=").append(shardtime);
        sb.append(", note=").append(note);
        sb.append(", createdate=").append(createdate);
        sb.append(", serialVersionUID=").append(serialVersionUID);
        sb.append("]");
        return sb.toString();
    }
}

分片路由测试

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.springbootshardingsphere.book.domain.Log;
import com.example.springbootshardingsphere.book.service.BookService;

import com.example.springbootshardingsphere.book.service.LogService;
import com.example.springbootshardingsphere.system.utils.SnowFlakeUtil;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@SpringBootTest
class SpringbootShardingsphereApplicationTests {

    @Autowired
    private BookService book0Service;


    @Autowired
    private LogService logService;



    @Test
    void test() throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time="2023-03-17 22:22:22";
        Date date=sdf.parse(time);
        SimpleDateFormat sdfMM = new SimpleDateFormat("yyyyMM");
        SnowFlakeUtil snowFlakeUtil = new SnowFlakeUtil();
        Log log=new Log();
        log.setId(String.valueOf(snowFlakeUtil.nextId(date)));
        log.setName("这是文案");
        log.setNote("这是文案");
        log.setShardtime(sdfMM.format(SnowFlakeUtil.getTimeBySnowFlakeId(Long.valueOf(log.getId()))));
        log.setCreatedate(SnowFlakeUtil.getTimeBySnowFlakeId(Long.valueOf(log.getId())));
        logService.save(log);

    }

    @Test
    void get(){
        String id="108076212898893824";
        Log log=logService.getById(id);
        System.out.println(log.toString());
    }

    @Test
    void page() throws ParseException {
        String time="2023-03-15 22:22:22";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat sdfMM = new SimpleDateFormat("yyyyMM");
        LambdaQueryWrapper<Log> lambda=new LambdaQueryWrapper<>();
        lambda.eq(Log::getShardtime,sdfMM.format(sdf.parse(time)));
        IPage<Log> iPage=logService.page(new Page<>(1,10),lambda);
        List<Log> logList=iPage.getRecords();

        for (Log log : logList) {
            System.out.println(log.toString()+" ");
        }
    }

}