springboot集成shardingjdbc

1、引入POM

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version> </dependency>

2、修改yml配置

spring:
  shardingsphere:
    props:
      sql:
        #打印sql
        show: true
    datasource:
      names: ds1       #指定一个虚拟数据库名称
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://47.99.80.195:3306/bridge_monitor?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&allowPublicKeyRetrieval=true
        username: root
        password: neIIqlwGsjsfsA6uxNqD
    #分表配置, 这里使用主键cid 作为分表字段
    sharding:
      tables:
        plat_ais_track:    #指定一个虚拟表名称
          actual-data-nodes: ds1.plat_ais_track_$->{202403..202404}, ds1.plat_ais_track_$->{202501..202504}
          key-generator:        #主键自动生成策略
            column: point_id
            type: SNOWFLAKE     #使用雪花ID
            props:
              worker:
                id: 1
          table-strategy:       #分表策略
            standard:             #standard策略
              sharding-column: lastdyn      #分表字段
              precise-algorithm-class-name: com.nuorui.framework.sharding.algorithm.QuarterlyShardingAlgorithm    #分表算法，求模取余算法
              range-algorithm-class-name: com.nuorui.framework.sharding.algorithm.QuarterlyRangeShardingAlgorithm

3、分表算法

public class QuarterlyRangeShardingAlgorithm implements RangeShardingAlgorithm<LocalDateTime> {

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<LocalDateTime> shardingValue) {
        Set<String> result = new HashSet<>();
        LocalDateTime startTime = shardingValue.getValueRange().lowerEndpoint();
        LocalDateTime endTime = shardingValue.getValueRange().upperEndpoint();

        LocalDateTime current = startTime;
        while (!current.isAfter(endTime)) {
            int year = current.getYear();
            int quarter = (current.getMonthValue() - 1) / 3 + 1;
            String tableSuffix = String.format("%d0%d", year, quarter);

            availableTargetNames.stream()
                    .filter(tableName -> tableName.endsWith(tableSuffix))
                    .findFirst()
                    .ifPresent(result::add);
            // 正确移动到下一个季度的开始
            current = moveToNextQuarter(current);
        }
        return result;
    }

    private LocalDateTime moveToNextQuarter(LocalDateTime dateTime) {
        // 获取当前季度的最后一天
        LocalDateTime endOfQuarter = dateTime.with(IsoFields.DAY_OF_QUARTER, dateTime.range(IsoFields.DAY_OF_QUARTER).getMaximum());
        // 移动到下一天，即下一季度的第一天
        return endOfQuarter.plusDays(1).withHour(0).withMinute(0).withSecond(0).withNano(0);
    }
}

public class QuarterlyShardingAlgorithm implements PreciseShardingAlgorithm<LocalDateTime> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDateTime> shardingValue) {
        LocalDateTime dateTime = shardingValue.getValue();
        int year = dateTime.getYear();
        int quarter = (dateTime.getMonthValue() - 1) / 3 + 1;

        String tableSuffix = String.format("%d0%d", year, quarter);

        return availableTargetNames.stream()
                .filter(tableName -> tableName.endsWith(tableSuffix))
                .findFirst()
                .orElseThrow(() -> new IllegalStateException("No table found for date: " + dateTime));
    }
}