【实战】基于springboot2.7x集成ShardingSphere5.X的分表及自定义分片策略配置

项目场景：

由于公司项目统一升级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自定义分片配置示例

---