ShardingJDBC分表使用

ju大肠

已于 2024-08-27 18:59:12 修改

阅读量311

点赞数 4

分类专栏： Java 文章标签： java

于 2024-08-27 18:28:31 首次发布

本文链接：https://blog.csdn.net/qq_55019098/article/details/141609686

版权

Java 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1. 导入maven配置


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

2. 配置application.yml

拿四方系统举例，按月分表，配置yml里面的分表策略及分片规则

# spring配置
spring:
  main:
    allow-bean-definition-overriding: true
  redis:
    host: localhost
    port: 6379
    password:
  shardingsphere:
    # 打印sql
    props:
      sql:
        show: true
    datasource:
      druid:
        stat-view-servlet:
          enabled: true
          loginUsername: admin
          loginPassword: 123456
      dynamic:
        druid:
          initial-size: 20
          min-idle: 30
          maxActive: 80
          maxWait: 600000
          timeBetweenEvictionRunsMillis: 60000
          minEvictableIdleTimeMillis: 300000
          validationQuery: SELECT 1 FROM DUAL
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          poolPreparedStatements: true
          maxPoolPreparedStatementPerConnectionSize: 20
          filters: stat,slf4j
          connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
        datasource:
          # 主库数据源
          master:
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://192.168.2.11:3306/ry-cloud?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&connectTimeout=90000&socketTimeout=90000&serverTimezone=GMT%2B8
            username: root
            password: root123
            # 从库数据源
            # slave:
            # username:
            # password:
            # url:
            # driver-class-name:
      names: master
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.2.11:3306/ry-cloud?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: root123
      # 从库数据源
      # slave:
      # username:
      # password:
      # url:
      # driver-class-name:
    sharding:
      # 表策略配置
      tables:
        # t_order 是逻辑表
        t_order:
          # 配置数据节点，这里是按月分表
          # 示例1：时间范围设置在202401 ~ 210012
          actualDataNodes: master.t_order_$->{2024..2100}0$->{1..9},master.t_order_$->{2024..2100}1$->{0..2}
          #actualDataNodes: master.t_order_$->{2024..2032}$->{(1..12).collect{t ->t.toString().padLeft(2,'0')}}
          # 示例2：时间范围设置在202401 ~ 202203
          #actualDataNodes: master.t_order
          tableStrategy:
            # 使用标准分片策略
            standard:
              # 配置分片字段
              shardingColumn: create_time
              # 配置精准分片算法
              preciseAlgorithmClassName: com.ruoyi.operate.sharding.TimeShardingAlgorithm
              # 配置范围分片算法
              rangeAlgorithmClassName: com.ruoyi.operate.sharding.TimeShardingAlgorithm
          # 配置主键及生成算法
          keyGenerator:
            column: id
            type: SNOWFLAKE

# mybatis配置
mybatis:
  # 搜索指定包别名
  typeAliasesPackage: com.ruoyi.operate
  # 配置mapper的扫描，找到所有的mapper.xml映射文件
  mapperLocations: classpath:mapper/**/*.xml

# swagger配置
swagger:
  title: 运营模块接口文档
  license: Powered By ruoyi
  licenseUrl: https://ruoyi.vip

3. TimeShardingAlgorithm 分配算法类

package com.ruoyi.brokerage.sharding;

import cn.hutool.core.date.DateUtil;
import com.google.common.collect.Range;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.spring.SpringUtils;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.Comparator;
import java.util.Date;
import java.util.LinkedHashSet;
import java.util.Optional;
import java.util.Set;
import java.util.function.Function;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.springframework.stereotype.Component;

/**
 * <p> @Title TimeShardingAlgorithm
 * <p> @Description 分片算法，按月分片
 *
 */
@Slf4j
@Component
public class TimeShardingAlgorithm implements PreciseShardingAlgorithm<Date>, RangeShardingAlgorithm<Date> {


    private ShardingAlgorithmTool shardingAlgorithmTool = SpringUtils.getBean(ShardingAlgorithmTool.class);

    /**
     * 分片时间格式
     */
    private static final DateTimeFormatter TABLE_SHARD_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMM");

    /**
     * 完整时间格式
     */
    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd HH:mm:ss");

    private static final String DATE_TIME_FORMATTER_STRING = "yyyyMMdd HH:mm:ss";

    /**
     * 表分片符号，例：t_order_202401 中，分片符号为 "_"
     */
    private final String TABLE_SPLIT_SYMBOL = "_";

    /**
     * 精准分片
     * @param tableNames 对应分片库中所有分片表的集合
     * @param preciseShardingValue 分片键值，其中 logicTableName 为逻辑表，columnName 分片键，value 为从 SQL 中解析出来的分片键的值
     * @return 表名
     */
    @Override
    public String doSharding(Collection<String> tableNames, PreciseShardingValue<Date> preciseShardingValue) {
        String logicTableName = preciseShardingValue.getLogicTableName();
        ShardingTableCacheEnum logicTable = ShardingTableCacheEnum.of(logicTableName);
        if (logicTable == null) {
            log.error("====>【ERROR】数据表类型错误，请稍后重试，logicTableNames：{}，logicTableName:{}",
                    ShardingTableCacheEnum.logicTableNames(), logicTableName);
            throw new IllegalArgumentException("数据表类型错误，请稍后重试");
        }

        log.info("====>【INFO】精确分片，节点配置表名：{}，数据库缓存表名：{}", tableNames, logicTable.resultTableNamesCache());

        Date dateTime = preciseShardingValue.getValue();
        String resultTableName = logicTableName + "_" + DateUtils.parseDateToStr("yyyyMM",dateTime);
        // 检查分表获取的表名是否存在，不存在则自动建表
        return shardingAlgorithmTool.getShardingTableAndCreate(logicTable, resultTableName);
    }


    /**
     * 范围分片
     * @param tableNames 对应分片库中所有分片表的集合
     * @param rangeShardingValue 分片范围
     * @return 表名集合
     */
    @Override
    public Collection<String> doSharding(Collection<String> tableNames, RangeShardingValue<Date> rangeShardingValue) {
        String logicTableName = rangeShardingValue.getLogicTableName();
        ShardingTableCacheEnum logicTable = ShardingTableCacheEnum.of(logicTableName);
        if (logicTable == null) {
            log.error("====> 【ERROR】逻辑表范围异常，请稍后重试，logicTableNames：{}，logicTableName:{}",
                    ShardingTableCacheEnum.logicTableNames(), logicTableName);
            throw new IllegalArgumentException("逻辑表范围异常，请稍后重试");
        }
        log.info("====> 【INFO】范围分片，节点配置表名：{}，数据库缓存表名：{}", tableNames, logicTable.resultTableNamesCache());

        // between and 的起始值
        Range<Date> valueRange = rangeShardingValue.getValueRange();
        boolean hasLowerBound = valueRange.hasLowerBound();
        boolean hasUpperBound = valueRange.hasUpperBound();

        // 获取最大值和最小值
        Set<String> tableNameCache = logicTable.resultTableNamesCache();
        Date min = hasLowerBound ? valueRange.lowerEndpoint() :getLowerEndpoint(tableNameCache);
        Date max = hasUpperBound ? valueRange.upperEndpoint() :getUpperEndpoint(tableNameCache);

        // 循环计算分表范围
        Set<String> resultTableNames = new LinkedHashSet<>();
        while (min.compareTo(max)<0 || min.equals(max)) {
            String tableName = logicTableName + TABLE_SPLIT_SYMBOL + DateUtils.parseDateToStr("yyyyMM",min);
            resultTableNames.add(tableName);
            min = DateUtil.offsetMinute(min,1);
        }
        return shardingAlgorithmTool.getShardingTablesAndCreate(logicTable, resultTableNames);
    }

    // --------------------------------------------------------------------------------------------------------------
    // 私有方法
    // --------------------------------------------------------------------------------------------------------------

    /**
     * 获取 最小分片值
     * @param tableNames 表名集合
     * @return 最小分片值
     */
    private Date getLowerEndpoint(Collection<String> tableNames) {
        Optional<Date> optional = tableNames.stream()
                .map(o -> DateUtils.dateTime(DATE_TIME_FORMATTER_STRING,o.replace(TABLE_SPLIT_SYMBOL, "") + "01 00:00:00"))
                .min(Comparator.comparing(Function.identity()));
        if (optional.isPresent()) {
            return optional.get();
        } else {
            log.error("====> 【ERROR】获取数据最小分表失败，请稍后重试，tableName：{}", tableNames);
            throw new IllegalArgumentException("获取数据最小分表失败，请稍后重试");
        }
    }

    /**
     * 获取 最大分片值
     * @param tableNames 表名集合
     * @return 最大分片值
     */
    private Date getUpperEndpoint(Collection<String> tableNames) {
        Optional<Date> optional = tableNames.stream()
                .map(o ->  DateUtils.dateTime(DATE_TIME_FORMATTER_STRING,o.replace(TABLE_SPLIT_SYMBOL, "") + "01 00:00:00"))
                .max(Comparator.comparing(Function.identity()));
        if (optional.isPresent()) {
            return optional.get();
        } else {
            log.error("====> 【ERROR】获取数据最大分表失败，请稍后重试，tableName：{}", tableNames);
            throw new IllegalArgumentException("获取数据最大分表失败，请稍后重试");
        }
    }
}

4. ShardingAlgorithmTool 分片工具类

package com.ruoyi.brokerage.sharding;

import com.ruoyi.common.utils.StringUtils;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;
import javax.sql.DataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.core.rule.DataNode;
import org.apache.shardingsphere.core.rule.TableRule;
import org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

/**
 * <p> @Title ShardingAlgorithmTool
 * <p> @Description
 */
@Slf4j
@Component
public class ShardingAlgorithmTool {

    /** 表分片符号，例：t_order_202401 中，分片符号为 "_"
     * */
    private static final String TABLE_SPLIT_SYMBOL = "_";
    /** 数据库配置 */
    @Value("${spring.shardingsphere.datasource.druid.master.url}")
    private String DATASOURCE_URL ;

    @Value("${spring.shardingsphere.datasource.druid.master.username}")
    private String DATASOURCE_USERNAME;

    @Value("${spring.shardingsphere.datasource.druid.master.password}")
    private String DATASOURCE_PASSWORD;


    /**
     * 检查分表获取的表名是否存在，不存在则自动建表
     * @param logicTable 逻辑表
     * @param resultTableNames 真实表名，例：t_order_202401
     * @return 存在于数据库中的真实表名集合
     */
    public  Set<String> getShardingTablesAndCreate(ShardingTableCacheEnum logicTable, Collection<String> resultTableNames) {
        return resultTableNames.stream().map(o -> getShardingTableAndCreate(logicTable, o)).collect(Collectors.toSet());
    }

    /**
     * 检查分表获取的表名是否存在，不存在则自动建表
     * @param logicTable 逻辑表
     * @param resultTableName 真实表名，例：t_order_202401
     * @return 确认存在于数据库中的真实表名
     */
    public  String getShardingTableAndCreate(ShardingTableCacheEnum logicTable, String resultTableName) {
        // 缓存中有此表则返回，没有则判断创建
        if (logicTable.resultTableNamesCache().contains(resultTableName)) {
            return resultTableName;
        } else {
            // 未创建的表返回逻辑空表
            boolean isSuccess = createShardingTable(logicTable, resultTableName);
            return isSuccess ? resultTableName : logicTable.logicTableName();
        }
    }

    /**
     * 重载全部缓存
     */
//    public void tableNameCacheReloadAll1() {
//        Arrays.stream(ShardingTableCacheEnum.values()).forEach(ShardingAlgorithmTool::tableNameCacheReload);
//    }

    public void tableNameCacheReloadAll() {
        ShardingTableCacheEnum[] enums = ShardingTableCacheEnum.values();
        for (int i = 0; i < enums.length; i++) {
            tableNameCacheReload(enums[i]);
        }
    }

    /**
     * 重载指定分表缓存
     * @param logicTable 逻辑表，例：t_order
     */
    public void tableNameCacheReload(ShardingTableCacheEnum logicTable) {
        // 读取数据库中|所有表名
        List<String> tableNameList = getAllTableNameBySchema(logicTable);
        // 删除旧的缓存（如果存在）
        logicTable.resultTableNamesCache().clear();
        // 写入新的缓存
        logicTable.resultTableNamesCache().addAll(tableNameList);
        // 动态更新配置 actualDataNodes
        actualDataNodesRefresh(logicTable);
    }

    /**
     * 获取所有表名
     * @return 表名集合
     * @param logicTable 逻辑表
     */
    public List<String> getAllTableNameBySchema(ShardingTableCacheEnum logicTable) {
        List<String> tableNames = new ArrayList<>();
        if (StringUtils.isEmpty(DATASOURCE_URL) || StringUtils.isEmpty(DATASOURCE_USERNAME) || StringUtils.isEmpty(DATASOURCE_PASSWORD)) {
            log.error("====>【ERROR】数据库连接配置有误，请稍后重试，URL:{}, username:{}, password:{}", DATASOURCE_URL, DATASOURCE_USERNAME, DATASOURCE_PASSWORD);
            throw new IllegalArgumentException("数据库连接配置有误，请稍后重试");
        }
        try (Connection conn = DriverManager.getConnection(DATASOURCE_URL, DATASOURCE_USERNAME, DATASOURCE_PASSWORD);
             Statement st = conn.createStatement()) {
            String logicTableName = logicTable.logicTableName();
            try (ResultSet rs = st.executeQuery("show TABLES like '" + logicTableName + TABLE_SPLIT_SYMBOL + "%'")) {
                while (rs.next()) {
                    String tableName = rs.getString(1);
                    // 匹配分表格式 例：^(t\_contract_\d{6})$
                    if (tableName != null && tableName.matches(String.format("^(%s\\d{6})$", logicTableName + TABLE_SPLIT_SYMBOL))) {
                        tableNames.add(rs.getString(1));
                    }
                }
            }
        } catch (SQLException e) {
            log.error("====> 【ERROR】数据库连接失败，请稍后重试，原因：{}", e.getMessage(), e);
            throw new IllegalArgumentException("数据库连接失败，请稍后重试");
        }
        return tableNames;
    }

    /**
     *  动态更新配置 actualDataNodes
     * @param logicTable
     */
    public void actualDataNodesRefresh(ShardingTableCacheEnum logicTable)  {
        try {
            // 获取数据分片节点
            String logicTableName = logicTable.logicTableName();
            Set<String> tableNamesCache = logicTable.resultTableNamesCache();
            log.info("====> 【INFO】更新分表配置，logicTableName:{}，tableNamesCache:{}", logicTableName, tableNamesCache);
            ShardingDataSource dataSource = (ShardingDataSource) SpringUtil.getBean("dataSource", DataSource.class);
            TableRule tableRule = dataSource.getShardingContext().getShardingRule().getTableRule(logicTableName);
            List<DataNode> dataNodes = tableRule.getActualDataNodes();
            String dataSourceName = dataNodes.get(0).getDataSourceName();
            List<DataNode> newDataNodes = tableNamesCache.stream().map(tableName -> new DataNode(dataSourceName + "." + tableName)).collect(Collectors.toList());

            // 更新actualDataNodes
            Field actualDataNodesField = TableRule.class.getDeclaredField("actualDataNodes");
            Field modifiersField = Field.class.getDeclaredField("modifiers");
            modifiersField.setAccessible(true);
            modifiersField.setInt(actualDataNodesField, actualDataNodesField.getModifiers() & ~Modifier.FINAL);
            actualDataNodesField.setAccessible(true);
            actualDataNodesField.set(tableRule, newDataNodes);
        }catch (Exception e){
            log.error("初始化 动态表单失败，原因：{}", e.getMessage(), e);
        }
    }


    // --------------------------------------------------------------------------------------------------------------
    // 私有方法
    // --------------------------------------------------------------------------------------------------------------


    /**
     * 创建分表
     * @param logicTable 逻辑表
     * @param resultTableName 真实表名，例：t_order_202401
     * @return 创建结果（true创建成功，false未创建）
     */
    private boolean createShardingTable(ShardingTableCacheEnum logicTable, String resultTableName) {
        // 根据日期判断，当前月份之后分表不提前创建
        String month = resultTableName.replace(logicTable.logicTableName() + TABLE_SPLIT_SYMBOL,"");
        YearMonth shardingMonth = YearMonth.parse(month, DateTimeFormatter.ofPattern("yyyyMM"));
        if (shardingMonth.isAfter(YearMonth.now())) {
            return false;
        }

        synchronized (logicTable.logicTableName().intern()) {
            // 缓存中有此表 返回
            if (logicTable.resultTableNamesCache().contains(resultTableName)) {
                return false;
            }
            // 缓存中无此表，则建表并添加缓存
            executeSql(Collections.singletonList("CREATE TABLE IF NOT EXISTS `" + resultTableName + "` LIKE `" + logicTable.logicTableName() + "`;"));
            // 缓存重载
            tableNameCacheReload(logicTable);
        }
        return true;
    }

    /**
     * 执行SQL
     * @param sqlList SQL集合
     */
    private  void executeSql(List<String> sqlList) {
        if (StringUtils.isEmpty(DATASOURCE_URL) || StringUtils.isEmpty(DATASOURCE_USERNAME) || StringUtils.isEmpty(DATASOURCE_PASSWORD)) {
            log.error("====> 【ERROR】数据库连接配置有误，请稍后重试，URL:{}, username:{}, password:{}", DATASOURCE_URL, DATASOURCE_USERNAME, DATASOURCE_PASSWORD);
            throw new IllegalArgumentException("数据库连接配置有误，请稍后重试");
        }
        try (Connection conn = DriverManager.getConnection(DATASOURCE_URL, DATASOURCE_USERNAME, DATASOURCE_PASSWORD)) {
            try (Statement st = conn.createStatement()) {
                conn.setAutoCommit(false);
                for (String sql : sqlList) {
                    st.execute(sql);
                }
            } catch (Exception e) {
                conn.rollback();
                log.error("====> 【ERROR】数据表创建执行失败，请稍后重试，原因：{}", e.getMessage(), e);
                throw new IllegalArgumentException("数据表创建执行失败，请稍后重试");
            }
        } catch (SQLException e) {
            log.error("====> 【ERROR】数据库连接失败，请稍后重试，原因：{}", e.getMessage(), e);
            throw new IllegalArgumentException("数据库连接失败，请稍后重试");
        }
    }

}

5. ShardingTableCacheEnum 分片缓存枚举类

说明：你要分那些表，配置几个枚举

package com.ruoyi.brokerage.sharding;

import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

/**
 * <p> @Title ShardingTableCacheEnum
 * <p> @Description 分片表缓存枚举
 */
public enum ShardingTableCacheEnum {

     ORDER("t_order", new HashSet<>());


    /**
     * 逻辑表名
     */
    private final String logicTableName;
    /**
     * 实际表名
     */
    private final Set<String> resultTableNamesCache;

    private static Map<String, ShardingTableCacheEnum> valueMap = new HashMap<>();

    static {
        Arrays.stream(ShardingTableCacheEnum.values()).forEach(o -> valueMap.put(o.logicTableName, o));
    }

    ShardingTableCacheEnum(String logicTableName, Set<String> resultTableNamesCache) {
        this.logicTableName = logicTableName;
        this.resultTableNamesCache = resultTableNamesCache;
    }

    public static ShardingTableCacheEnum of(String value) {
        return valueMap.get(value);
    }

    public String logicTableName() {
        return logicTableName;
    }

    public Set<String> resultTableNamesCache() {
        return resultTableNamesCache;
    }

    public static Set<String> logicTableNames() {
        return valueMap.keySet();
    }

    @Override
    public String toString() {
        return "ShardingTableCacheEnum{" +
                "logicTableName='" + logicTableName + '\'' +
                ", resultTableNamesCache=" + resultTableNamesCache +
                '}';
    }
}

6. SpringUtil 工具类

package com.ruoyi.brokerage.sharding;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;

/**
 * <p> @Title SpringUtil
 * <p> @Description Spring工具类
 */
@Component
public class SpringUtil implements ApplicationContextAware {

    private static ApplicationContext applicationContext = null;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        SpringUtil.applicationContext = applicationContext;
    }

    public static ApplicationContext getApplicationContext() {
        return SpringUtil.applicationContext;
    }

    public static <T> T getBean(Class<T> cla) {
        return applicationContext.getBean(cla);
    }

    public static <T> T getBean(String name, Class<T> cal) {
        return applicationContext.getBean(name, cal);
    }

    public static String getProperty(String key) {
        return applicationContext.getBean(Environment.class).getProperty(key);
    }
}

7. 源码测试

    // 新增
    @Test
    void saveTestOrder() {
        LocalDateTime time1 = LocalDateTime.parse("2024-03-01 00:00:00", DATE_TIME_FORMATTER);
        ZonedDateTime zonedDateTime = time1.atZone(ZoneId.systemDefault());
        Instant instant = zonedDateTime.toInstant();
        Date date = Date.from(instant);
        try {
            Order ddd = new Order();
            ddd.setCreateTime(DateUtils.getNowDate());
            ddd.setPayOrderSn("999");
            orderService.insertOrder(ddd);
        } catch (Exception e){
            e.getMessage();
            e.printStackTrace();
        }
    }

    // 查询
    @Test
    void findByIdTest() {
        List<Order> list = orderService.selectOrderList(new OrderVo());
        System.out.println(">>>>>>>>>> 【Result】<<<<<<<<<< ");
        list.forEach(System.out::println);
    }