项目场景:
由于公司项目统一升级springboot 2.7x版本,导致原有sharding-jdbc 4x版本无法启动,主要涉及自定义分片策略,找遍全网没有发现完整的解决方案,所以历时两天半终于成功上岸,文章最后有完整配置
5.0.0-beta版本(推荐)
1、自定义分表查询多表不会有异常
2、5.0.0-beta版本偏低,可能存在潜在风险
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0-beta</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>2.7.18</version>
</dependency>
5.2.1版本
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>2.7.18</version>
</dependency>
<dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.33</version>
</dependency>
经小编测试,
1、启动提示 Factory method ‘shardingSphereDataSource’ threw exception; nested exception is java.lang.NoSuchMethodError: org.yaml.snakeyaml.representer.Representer: method ()V not found,需要单独引用snakeyaml 1.33版本,
2、当自定义分表查询多表时,会出现Caused by: java.lang.NullPointerException: null
at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.getValueCaseSensitiveFromTables(GroupByMemoryMergedResult.java:135)错误,追踪代码发现schema对象中没有被分片的表,于是我追踪了schema对象,发现在初始化时,schema对象不会加载spring.shardingsphere.rules.sharding.tables下的所以表,初步怀疑可能是配置文件问题,但是官方配置中也没有说明,已经提交issues等待官方解答,如果已经解决的同学可以评论回答一下,感激不尽
遇到的问题
问题1:启动提示java.lang.NullPointerException: The props algorithmClassName
cannot be null when uses class based sharding strategy,这个错误是真的坑,shardingsphere 4.x和5.x部分配置参数名不同,导致启动找不到这个属性,注意5.x的分片配置和4.x的也不同
shardingsphere 4.x 配置自定义分片属性名 algorithm-class-name
sharding:
tables:
# 订单表基础表
t_ent_order:
# 真实表
actualDataNodes: ds$->{0..3}.t_ent_order
# 分库策略
databaseStrategy:
complex:
sharding-columns: id,ent_id
algorithm-class-name: cn.javayong.shardingjdbc4.spring.common.sharding.HashSlotAlgorithm
shardingsphere 5.x 配置自定义分片属性名 shardingAlgorithmName
rules
sharding:
- !SHARDING
tables:
# 订单表基础表
t_ent_order:
# 真实表
actualDataNodes: ds$->{0..3}.t_ent_order
# 分库策略
databaseStrategy:
complex:
shardingColumns: id,ent_id
shardingAlgorithmName: hash-slot-algorithm
问题2:启动提示org.apache.shardingsphere.infra.util.spi.exception.ServiceProviderNotFoundServerException: SPI-00001: No implementation class load from SPI
这个错误消息表示ShardingSphere在尝试加载自定义的分片算法实现类时遇到了问题。错误消息指出没有从SPI(Service Provider Interface)接口,需要在项目目录resources\META-INF\services下创建org.apache.shardingsphere.sharding.spi.ShardingAlgorithm文件,把你自定义的分片类写入文件中
例如:
##多个类写多行
org.apache.shardingsphere.sharding.algorithm.xxx
问题3:项目启动后,自定义分片策略不执行,实现自定义分片ComplexKeysShardingAlgorithm类时,必须重写public String getType()方法,需要给每个类设置一个唯一的string值(CORE.COMPLEX.FIXTURE)并且和配置文件中的shardingAlgorithms.xx.type一样,配置文件参考 自定义分片类参考
package org.apache.shardingsphere.sharding.fixture;
import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingValue;
import java.util.Collection;
public final class CoreComplexKeysShardingAlgorithmFixture implements ComplexKeysShardingAlgorithm<Integer> {
@Override
public Collection<String> doSharding(final Collection<String> availableTargetNames, final ComplexKeysShardingValue<Integer> shardingValue) {
return availableTargetNames;
}
@Override
public String getType() {
return "CORE.COMPLEX.FIXTURE";
}
}
rules:
- !SHARDING
tables:
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: core_standard_fixture
bindingTables:
- t_order, t_order_item
- foo:t_order, t_order_item
shardingAlgorithms:
core_standard_fixture:
type: CORE.STANDARD.FIXTURE
整体配置:
以下是ShardingSphere5.X所有配置,需要yml可以自己转换,实现了StandardShardingAlgorithm、ComplexKeysShardingAlgorithm两个自定义分片
spring.shardingsphere.datasource.names=xxxx
spring.shardingsphere.datasource.cemos.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.cemos.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.cemos.jdbc-url=jdbc:mysql://${datasource.ip}:${datasource.port}/xxxx?serverTimezone=${time_zone:Asia/Shanghai}&useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.cemos.username=${datasource-username}
spring.shardingsphere.datasource.cemos.password=${datasource-password}
spring.shardingsphere.rules.sharding.sharding-algorithms.day-Long-sharding-algorithm.type=DAY_LONG_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.complex-sharding-algorithm.type=COMPLEX_BASED
spring.shardingsphere.rules.sharding.tables.gps_record.actual-data-nodes=cemos.gps_record_$->{2022..2099}$->{(1..12).collect{t ->t.toString().padLeft(2,'0')}}$->{(1..31).collect{t ->t.toString().padLeft(2,'0')}}
spring.shardingsphere.rules.sharding.tables.gps_record.table-strategy.standard.sharding-column=gps_time
spring.shardingsphere.rules.sharding.tables.gps_record.table-strategy.standard.sharding-algorithm-name=day-Long-sharding-algorithm
spring.shardingsphere.rules.sharding.tables.alarm.actual-data-nodes=cemos.alarm_$->{2022..2099}$->{(1..12).collect{t ->t.toString().padLeft(2,'0')}}$->{(1..31).collect{t ->t.toString().padLeft(2,'0')}}
spring.shardingsphere.rules.sharding.tables.alarm.table-strategy.complex.sharding-columns=id,start_time,task_id
spring.shardingsphere.rules.sharding.tables.alarm.table-strategy.complex.sharding-algorithm-name=complex-sharding-algorithm
spring.shardingsphere.rules.sharding.binding-tables[0]=alarm,device_media_task
spring.shardingsphere.rules.sharding.binding-tables[1]=alarm,device_media_file
spring.shardingsphere.rules.sharding.defaultDataSourceName=xxxx
spring.shardingsphere.mode.type=Standalone
spring.shardingsphere.props.sql-show=false
public class DayLongShardingAlgorithm implements StandardShardingAlgorithm<Long> {
private Properties properties;
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
String db_name = preciseShardingValue.getLogicTableName();
// 获取到分片键中的值(time), 重改表名
try {
Date date = DateUtil.getDateByString(DateUtil.getDateByTime(preciseShardingValue.getValue()* NumberConstant.ONE_THOUSAND));
String year = String.format("%tY", date);
String mon = String.format("%tm",date);
String day = String.format("%td",date);
String newTable = db_name+"_"+year+mon+day;
return newTable ;
} catch (Exception e) {
log.error("doSharding error {}",e);
}
throw new IllegalArgumentException("");
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
//需要自己实现逻辑
String db_name = rangeShardingValue.getLogicTableName();
Range<Long> valueRange = rangeShardingValue.getValueRange();
Long startDay = valueRange.lowerEndpoint()* NumberConstant.ONE_THOUSAND;
Long endDay = valueRange.upperEndpoint()* NumberConstant.ONE_THOUSAND;
Set<String> res = new HashSet<>();
// 计算出这【startDay~endDay】日期之间的所有日期,同时转换成对应数据库表名
Calendar calendar = Calendar.getInstance();
while (startDay<=endDay){
String year = String.format("%tY", startDay);
String mon = String.format("%tm",startDay);
String day = String.format("%td",startDay);
res.add(db_name+"_"+year+mon+day);
calendar.setTime(DateUtil.formatDate(DateUtil.getDateByTime(startDay)));
calendar.add(Calendar.DATE, 1);
// 获取增加后的日期
startDay = calendar.getTimeInMillis();
}
return Arrays.asList(db_name);
}
@Override
public String getType() {
return "DAY_LONG_BASED";
}
@Override
public Properties getProps() {
return this.properties;
}
@Override
public void init(Properties properties) {
this.properties = properties;
}
}
@Slf4j
public class ComplexShardingAlgorithm implements ComplexKeysShardingAlgorithm {
private Properties properties;
@Override
public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
// 得到每个分片健对应的值,需要自己实现逻辑
String db_name = complexKeysShardingValue.getLogicTableName();
return Arrays.asList(db_name);
}
@Override
public String getType() {
return "COMPLEX_BASED";
}
@Override
public Properties getProps() {
return this.properties;
}
@Override
public void init(Properties properties) {
this.properties = properties;
}
}
总结:
遇到问题不要怕,多查阅官方提供的demo,不理解的参数直接问AI(chatgpt),世上无难事,只怕有心人
shardingsphere配置参数说明
shardingsphere自定义分片类示例
shardingsphere自定义分片配置示例