SpringBoot3.3.3+shardingsphere-jdbc5.5.0读写分离、自定义生成主键策略

最近在开发项目搭建框架时，考虑后期支付模块的订单数据量可能会比较大，于是使用现在主流的shardingsphere的读写分离、水平分表来解决后期数据量大影响查询效率的问题。

项目技术栈：jdk17+Springboot3.3.3+shardingsphere-jdbc5.5.0+mybatis-plus3.5.7+mybatis-plus-generator3.5.9+mysql8.0.20

1.报错解决方案

shardingsphere的官网上各个版本的配置有一些差异，官网文档的使用也写得不全，全靠看别人blog和查看源码对应去解决。特别注意自定义class所在resources的文件路为META-INF\services\org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm让我花了一天的时间才解决。

若resources下的包名和文件名不是上面标红所示，会报错：

报这个错其实主要是我的自定义生成主键策略类没有加载进来，一起这个错，搞我真死了好多脑细胞。

SPI-00001: No implementation class load from SPI 'org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm' with type 'MY_KEY_GENERATE_ALGORITHM'.

org.apache.shardingsphere.infra.spi.exception.ServiceProviderNotFoundException: SPI-00001: No implementation class load from SPI 'org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm' with type 'MY_KEY_GENERATE_ALGORITHM'.at org.apache.shardingsphere.infra.spi.type.typed.TypedSPILoader.checkService(TypedSPILoader.java:129) ~[shardingsphere-infra-spi-5.5.0.jar:5.5.0]at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.lambda$checkKeyGeneratorAlgorithms$1(ShardingRuleConfigurationChecker.java:76) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183) ~[na:na]at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179) ~[na:na]at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133) ~[na:na]at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1845) ~[na:na]at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509) ~[na:na]at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499) ~[na:na]at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150) ~[na:na]at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173) ~[na:na]at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:na]at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596) ~[na:na]at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.checkKeyGeneratorAlgorithms(ShardingRuleConfigurationChecker.java:76) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.check(ShardingRuleConfigurationChecker.java:60) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.check(ShardingRuleConfigurationChecker.java:55) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.infra.rule.builder.database.DatabaseRulesBuilder.build(DatabaseRulesBuilder.java:67) ~[shardingsphere-infra-common-5.5.0.jar:5.5.0]at org.apache.shardingsphere.infra.metadata.database.ShardingSphereDatabase.create(ShardingSphereDatabase.java:91) ~[shardingsphere-infra-common-5.5.0.jar:5.5.0]at org.apache.shardingsphere.metadata.factory.ExternalMetaDataFactory.createGenericDatabases(ExternalMetaDataFactory.java:85) ~[shardingsphere-metadata-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.metadata.factory.ExternalMetaDataFactory.create(ExternalMetaDataFactory.java:72) ~[shardingsphere-metadata-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.mode.metadata.MetaDataContextsFactory.create(MetaDataContextsFactory.java:98) ~[shardingsphere-mode-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.mode.metadata.MetaDataContextsFactory.create(MetaDataContextsFactory.java:72) ~[shardingsphere-mode-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.mode.manager.standalone.StandaloneContextManagerBuilder.build(StandaloneContextManagerBuilder.java:53) ~[shardingsphere-standalone-mode-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.createContextManager(ShardingSphereDataSource.java:79) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.<init>(ShardingSphereDataSource.java:67) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory.createDataSource(ShardingSphereDataSourceFactory.java:95) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory.createDataSource(YamlShardingSphereDataSourceFactory.java:135) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory.createDataSource(YamlShardingSphereDataSourceFactory.java:70) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.createDataSource(DriverDataSourceCache.java:55) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.lambda$get$0(DriverDataSourceCache.java:48) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at java.base/java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1708) ~[na:na]at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.get(DriverDataSourceCache.java:48) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.ShardingSphereDriver.connect(ShardingSphereDriver.java:56) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1682) ~[druid-1.2.23.jar:na]at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1803) ~[druid-1.2.23.jar:na]at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2914) ~[druid-1.2.23.jar:na]

查看shardingsphere5.5.0官网关于此信息链接如下：

服务器错误码 :: ShardingSphere

官网写得的这个提示没卵用！

后面查看shardingsphere5.5.0的源码发现UUIDKeyGenerateAlgorithm.java和SnowflakeKeyGenerateAlgorithm.java都是继承了KeyGenerateAlgorithm.java，然后根据上面的报错，猜测是我的resources包名和文件名不对，于是改源码中的包名和文件名就OK了。

2.我的项目配置：

3.自定义策略的类 MyKeyGenerateAlgorithm.java

package com.tfq.shardingshperedemo.config;import org.apache.shardingsphere.infra.algorithm.core.context.AlgorithmSQLContext;
import org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.LinkedList;
import java.util.concurrent.ThreadLocalRandom;/*** @Description: 生成支付订单号算法* @Author: tfq* @Date: 2024-12-26 15:33*/public final class MyKeyGenerateAlgorithm implements KeyGenerateAlgorithm {/*** @param context          algorithm SQL context* @param keyGenerateCount key generate count* @return*/@Overridepublic Collection<String> generateKeys(final AlgorithmSQLContext context, final int keyGenerateCount) {Collection<String> result = new LinkedList<>();ThreadLocalRandom threadLocalRandom = ThreadLocalRandom.current();for (int index = 0; index < keyGenerateCount; index++) {result.add(generateKey(threadLocalRandom));}return result;}/*** 订单编号：时间戳+6位随机数，例如："20241127124523"+"123456"，前面是日期时间戳，后面是订单号** @param threadLocalRandom* @return*/private String generateKey(ThreadLocalRandom threadLocalRandom) {String nowDate = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());String randomNum = threadLocalRandom.nextLong(100000, 999999) + "";return nowDate + randomNum;}@Overridepublic String getType() {return "MY_KEY_GENERATE_ALGORITHM";}
}

上面getType返回的String:MY_KEY_GENERATE_ALGORITHM要配置到下shardingsphere-level-table.yaml的keyGenerators(生成主键方法)的type后面。

4.pom.xml的配置如下

<?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>3.3.3</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.tfq</groupId><artifactId>shardingshperedemo</artifactId><version>0.0.1-SNAPSHOT</version><name>shardingshperedemo</name><description>shardingshperedemo</description><properties><java.version>17</java.version><maven.compiler.source>${java.version}</maven.compiler.source><shardingsphere.version>5.5.0</shardingsphere.version><spring.boot.version>3.3.3</spring.boot.version><!-- DB 相关 --><mysql.version>8.0.33</mysql.version><druid.version>1.2.23</druid.version><mybatis-plus.version>3.5.7</mybatis-plus.version><mybatis-plus-generator>3.5.9</mybatis-plus-generator></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-beans</artifactId><version>6.1.14</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>6.1.14</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-expression</artifactId><version>6.1.14</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-core</artifactId><version>6.1.14</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>${mysql.version}</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-3-starter</artifactId><version>${druid.version}</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-spring-boot3-starter</artifactId><version>${mybatis-plus.version}</version></dependency><dependency><groupId>com.github.yulichang</groupId><artifactId>mybatis-plus-join-boot-starter</artifactId><version>1.5.2</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-generator</artifactId><version>${mybatis-plus-generator}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>6.1.14</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc</artifactId><version>${shardingsphere.version}</version><exclusions><exclusion><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-test-util</artifactId></exclusion></exclusions></dependency><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.14.0</version></dependency><dependency><groupId>com.google.protobuf</groupId><artifactId>protobuf-java</artifactId><version>3.25.5</version></dependency><dependency><groupId>com.github.yulichang</groupId><artifactId>mybatis-plus-join</artifactId><version>1.4.13</version></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>org.xmlunit</groupId><artifactId>xmlunit-core</artifactId><version>2.10.0</version><scope>test</scope></dependency><dependency><groupId>org.junit.platform</groupId><artifactId>junit-platform-launcher</artifactId><scope>test</scope></dependency><dependency><!-- 用于生成自定义的 Spring @ConfigurationProperties 配置类的说明文件 --><groupId>org.springframework.boot</groupId><artifactId>spring-boot-configuration-processor</artifactId><version>${spring.boot.version}</version></dependency><!-- swagger--><dependency><groupId>com.github.xiaoymin</groupId><artifactId>knife4j-openapi3-jakarta-spring-boot-starter</artifactId><version>4.5.0</version></dependency><!-- https://mvnrepository.com/artifact/io.github.classgraph/classgraph --><dependency><groupId>io.github.classgraph</groupId><artifactId>classgraph</artifactId><version>4.8.112</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-webmvc</artifactId><version>6.1.13</version></dependency><dependency><groupId>org.apache.tomcat.embed</groupId><artifactId>tomcat-embed-core</artifactId><version>10.1.31</version></dependency><!-- https://mvnrepository.com/artifact/cn.hutool/hutool-core --><dependency><groupId>cn.hutool</groupId><artifactId>hutool-core</artifactId><version>5.8.25</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>

5.application.yaml配置如下

server:port: 8888spring:application:name: shardingshperedemodatasource:driverClassName: org.apache.shardingsphere.driver.ShardingSphereDriverurl: jdbc:shardingsphere:classpath:shardingsphere-level-table.yaml#在shardingshere的配置参考官网：#https://shardingsphere.apache.org/document/5.5.0/cn/user-manual/shardingsphere-jdbc/yaml-config/rules/readwrite-splitting/mybatis-plus:configuration:map-underscore-to-camel-case: true #开启下划线转驼峰mapper-locations: classpath*:/mapper/**/*.xml #Mapper.xml文件地址，默认值type-aliases-package: com.tfq.shardingshperedemo.entity #别名扫描包# springdoc-openapi项目配置
springdoc:swagger-ui:#自定义swagger前端请求路径,输入http:localhost:8080/swagger-ui会自动重定向到swagger页面path: /swagger-uitags-sorter: alphaoperations-sorter: alpha#此项配置解决controller的Getmapping的参数对象展开并能传递对象参数的值到后台default-flat-param-object: true # 参见 https://doc.xiaominfo.com/docs/faq/v4/knife4j-parameterobject-flat-param 文档api-docs:path: /v3/api-docs  #swagger后端请求地址enabled: true   #是否开启文档功能group-configs: #分组配置,可配置多个分组- group: 'default'             #分组名称paths-to-match: '/**'        #配置需要匹配的路径packages-to-scan: com.tfq.shardingshperedemo.controller    #配置要扫描包的路径,一般配置到启动类所在的包名- group: 'admin-api'paths-to-match: '/**'packages-to-scan: com.tfq.shardingshperedemo.controller

6.shardingsphere-level-table.yaml配置如下

#水平分表-数据源配置
dataSources:ds1: #数据源名称-用于水平分表dataSourceClassName: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.56.10:3306/course_db?serverTimeznotallow=GMT%2B8&useSSL=false&rewriteBatchedStatements=trueusername: rootpassword: 123456#数据分片规则配置:指定数据库存表course_1表分布情况,表名是什么
rules:- !SINGLE #不分库分表的表在 ShardingSphere 中叫做单表，可以使用 LOAD 语句或者 SINGLE 规则配置需要加载的单表。tables:- "*.*"# 数据分片- !SHARDINGtables:course: #逻辑表名actualDataNodes: ds1.course_${0..1} #由数据源名 + 表名组成（参考 Inline 语法规则）tableStrategy: #指定分片策略，约定cid的值偶数添加到course_1,cid的值为奇数则添加到course_2standard:shardingColumn: user_idshardingAlgorithmName: t-course-inline # 分片算法名称（必须以中划线命名）keyGenerateStrategy:column: cidkeyGeneratorName: My-PAYORDER-ID shardingAlgorithms: #分片算法配置t-course-inline: # 分片算法名称（必须以中划线命名）type: INLINE # 分片算法类型props: #分片算法属性配置algorithm-expression: course_$->{user_id % 2}keyGenerators:My-PAYORDER-ID:type: MY_KEY_GENERATE_ALGORITHMprops:worker:id: 1  # 可选：传递给自定义算法的配置
props:max.connections.size.per.query: 10acceptor.size: 200  # The default value is available processors count * 2.executor.size: 200  # Infinite by default.query.with.cipher.column: truesql-show: trueallow.range.query.with.inline.sharding: falsecheck.table.metadata.enabled: false

7.测试代码

ShardingshperedemoApplicationTests.java

package com.tfq.shardingshperedemo;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.tfq.shardingshperedemo.dto.UserDto;
import com.tfq.shardingshperedemo.entity.Course;
import com.tfq.shardingshperedemo.entity.Udict;
import com.tfq.shardingshperedemo.entity.User;
import com.tfq.shardingshperedemo.mapper.CourseMapper;
import com.tfq.shardingshperedemo.mapper.UdictMapper;
import com.tfq.shardingshperedemo.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
import java.util.Random;@SpringBootTest
class ShardingshperedemoApplicationTests {@Autowiredprivate CourseMapper courseMapper;@Autowiredprivate UserMapper userMapper;@Autowiredprivate UdictMapper udictMapper;/*** 水平分表* 添加课程方法*/@Testvoid addCourse() throws InterruptedException {for (int i = 0; i < 10; i++) {Course course = new Course();course.setCname("ruby" + i);course.setCstatus("Normal");course.setUserId(103L + i);courseMapper.insert(course);Thread.sleep(1000);}}/*** 水平分表-查询课程的方法*/@Testpublic void findCourse() {QueryWrapper<Course> wrapper = new QueryWrapper<>();wrapper.eq("cid", 1069938186098049024L);Course course = courseMapper.selectOne(wrapper);System.out.println(course);}}

8.运行结果

插入数据库数据：

查询运行结果：

2024-12-30T18:56:13.879+08:00  INFO 31644 --- [shardingshperedemo] [           main] ShardingSphere-SQL                       : Logic SQL: SELECT    cid,cname,user_id,cstatus    FROM  course         WHERE  (cid = ?)
2024-12-30T18:56:13.880+08:00  INFO 31644 --- [shardingshperedemo] [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT    cid,cname,user_id,cstatus    FROM  course_0         WHERE  (cid = ?) UNION ALL SELECT    cid,cname,user_id,cstatus    FROM  course_1         WHERE  (cid = ?) ::: [20241230184613690172, 20241230184613690172]
Course(cid=20241230184613690172, cname=ruby1, userId=104, cstatus=Normal, users=null)

若大家还有问题请留言在解答。

项目下载地址：https://download.csdn.net/download/developerFBI/90205506

参考文档：

FAQ :: ShardingSphere

基础算法 :: ShardingSphere

springboot整合ShardingSphere5.2.1（最新版）_spi-00001: no implementation class load from spi `-CSDN博客

Docker部署Mysql8.0.20并配置主从复制 - C3Stones - 博客园