SpringBoot整合ShardingSphere 4.x单库 分表

1,pom.xml

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

2,yml

spring:
  shardingsphere:
    datasource:
      names: db0 # 数据源名称
      db0:
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://xxxxxx:3306/zjk_coal?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false
        username: ENC(8r6fF8d+ujG2HXBDoBq3wg==)
        password: ENC(ms9QKba7NE/DtPNukwrykQ==)
        type: com.zaxxer.hikari.HikariDataSource
        hikari:
          minimum-idle: 5
          maximum-pool-size: 15
          auto-commit: true
          idle-timeout: 30000
          pool-name: DatebookHikariCP
          max-lifetime: 1800000
          connection-timeout: 30000
          connection-test-query: SELECT 1
    sharding:
      tables:
        alarm_equipment: # 表名
          actual-data-nodes: db0.alarm_equipment_202009, db0.alarm_equipment_202010, db0.alarm_equipment_202011, db0.alarm_equipment_202011, db0.alarm_equipment_$->{2021..2022}0$->{1..9}, db0.alarm_equipment_$->{2021..2022}$->{10..12}
          table-strategy:
#            inline:
#              sharding-column: id # 分片键
#              algorithm-expression: alarm_equipment$->{id % 3}
            standard:
              sharding-column: date_time # 分片键
              precise-algorithm-class-name: com.chitic.zjkcte.common.common.config.sharding.TableShardingAlgorithm # 实现类的完全限定类名
              range-algorithm-class-name: com.chitic.zjkcte.common.common.config.sharding.TableRangeShardingAlgorithm
    #  打印SQL，不配置   打印的都是逻辑表
    props:
      sql:
        show: true

  jpa:
    hibernate:
    # 生产环境设置成 none，避免程序运行时自动更新数据库结构
      ddl-auto: none
    show-sql: false

3,标准分片策略

精确的分片算法类名称，用于=和IN

package com.chitic.zjkcte.common.common.config.sharding;

import com.chitic.module.core.exception.ChiticException;
import com.chitic.zjkcte.common.common.enums.ResponseCode;
import com.chitic.zjkcte.common.common.util.BaseDateUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.springframework.stereotype.Component;

import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.Date;

/**
 * @Description //TODO 标准分片策略  精确的分片算法类名称，用于=和IN
 * @Author GaoX
 * @Date 2020/11/18 15:56
 */
@Component
public class TableShardingAlgorithm implements PreciseShardingAlgorithm {

    @Override
    public String doSharding(Collection availableTargetNames, PreciseShardingValue preciseShardingValue) {
        // 基本的表名_年份月份  base_202011
        //TODO 此处接收是Integer  不明白为什么
        Object value = preciseShardingValue.getValue();
        final String format = "yyyyMM";
        String targetTable = preciseShardingValue.getLogicTableName() + "_" + BaseDateUtils.getFormatDate(Long.parseLong(value.toString()), format);
        if (availableTargetNames.contains(targetTable)){
            return targetTable;
        }
        throw ChiticException.of(ResponseCode.SYSTEM_ERROR.getCode(), "无效的表名称: " + targetTable);
    }
}

标准分片策略 范围的分片算法类名称，用于BETWEEN

package com.chitic.zjkcte.common.common.config.sharding;

import com.chitic.module.core.exception.ChiticException;
import com.chitic.zjkcte.common.common.enums.ResponseCode;
import com.chitic.zjkcte.common.common.util.BaseDateUtils;
import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.Collection;
/**
 * @Description //TODO 标准分片策略 范围的分片算法类名称，用于BETWEEN
 * @Author GaoX
 * @Date 2020/11/18 16:06
 */
@Component
public class TableRangeShardingAlgorithm implements RangeShardingAlgorithm<Integer> {

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Integer> rangeShardingValue) {
        Collection<String> collect = new ArrayList<>();
        Range valueRange = rangeShardingValue.getValueRange();
        //TODO 这种写法只支持between, 但是效率很高
        //Comparable lowerEndpoint = valueRange.lowerEndpoint();
        //Comparable upperEndpoint = valueRange.upperEndpoint();
        //String lowerEndTable = rangeShardingValue.getLogicTableName() + "_" + BaseDateUtils.getFormatDate(Long.parseLong(lowerEndpoint.toString()), "yyyyMM");
        //String upperEndTable = rangeShardingValue.getLogicTableName() + "_" + BaseDateUtils.getFormatDate(Long.parseLong(upperEndpoint.toString()), "yyyyMM");
        for (String each : collection) {
            Integer shardValue = Integer.parseInt(each.substring(each.lastIndexOf("_") + 1, each.lastIndexOf("_") + 7));
            if (valueRange.hasLowerBound()) {
                String lowerStr = valueRange.lowerEndpoint().toString();
                String date = BaseDateUtils.getFormatDate(Long.parseLong(lowerStr), "yyyyMM");
                Integer start = Integer.parseInt(date);
                if (start - shardValue > 0) {
                    continue;
                }
            }
            if (valueRange.hasUpperBound()) {
                String upperStr = valueRange.upperEndpoint().toString();
                String date = BaseDateUtils.getFormatDate(Long.parseLong(upperStr), "yyyyMM");
                Integer end = Integer.parseInt(date);
                if (end - shardValue < 0) {
                    continue;
                }
            }
            collect.add(each);
        }
        if(CollectionUtils.isEmpty(collect)){
            throw ChiticException.of(ResponseCode.SYSTEM_ERROR.getCode(), "无效的表" );
        }
        return collect;
    }


}

4,数据库表

 

 5，entity和dao

实体类正常写就好，@Table ( name ="alarm_equipment" )    是逻辑表名