解决sharding jdbc 不能使用批量更新数据

背景：集成sharding jdbc后，对做了分表的表数据进行批量修改出现以下问题：

第一条修改数据sql的表明由sharding改写为对应的表名，之后的所有sql表名都未改写造成批量修改失败。

百度了相关资料，sharding jdbc未对批量修改改写表名支持并且未来也不会进行支持，会导致分布式表名不一致的问题（可能需要修改多个表中的数据）

解决方案：

1、根据分片配置信息计算出对应的表名

2、SQL中指定表名（请自行实现）

工具类如下：

package com.ruoyi.yjxd.utils.config;

import com.alibaba.fastjson.JSONObject;
import com.ruoyi.common.core.utils.DateUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Component;

import java.util.Date;

@Component
public class ShardingUtil {

    //商品表
    public static String orderxxxxx = "xxxxx";
    //商品sku表
    public static String orderxxxxx = "xxxxx";
    //商品表
    public static String goodsxxxxx = "xxxxx";
    //商品sku表
    public static String goodsxxxxx = "xxxxx";

    //支付记录表
    public static String waitxxxxx = "xxxxx";
    //订单表
    public static String waitxxxxx = "xxxxx";
    //订单具体子商品表
    public static String waitxxxxx = "xxxxx";


    //xxxxx表的xxxxx字段的分片信息（为xxxxx表的主键自增分片）
    public static Long[] orderGoodsFragment;

    //xxxxx表的xxxxx字段的分片信息（为xxxxx表的主键自增分片）
    public static Long[] orderGoodsskuFragment;

    //xxxxx表的xxxxx字段的分片信息（为xxxxx表的主键自增分片）
    public static Long[] goodsSourcsFragment;

    //xxxxx表的goods_sourcs_id字段的分片信息（为xxxxx表的主键自增分片）
    public static Long[] goodsSourcsSkuFragment;

    //xxxxx表的shop_id字段的分片信息（为xxxxx表的主键自增分片）
    public static Long[] goodsRelationStatusFragment;

    //xxxxx表的xxxxx字段的分片信息(日期分片)
    public static String waitPayRecordFragment;

    //xxxxx表的xxxxx字段的分片信息(日期分片)
    public static Integer waitPayRecordAmount;

    //xxxxx表的xxxxx字段的分片信息(日期分片)
    public static String waitPayOrderFragment;

    //xxxxx表的xxxxx字段的分片信息(日期分片)
    public static Integer waitPayOrderAmount;

    //xxxxx表的xxxxx字段的分片信息(日期分片)
    public static String waitPayOrderProductFragment;

    //xxxxx表的xxxxx字段的分片信息(日期分片)
    public static Integer waitPayOrderProductAmount;

    //分片开始时间
    public static String startTime;

    /**
     * 获取分片表名（id自增分片）
     * @param tableName 原始表名 例：ShardingUtil.orderGoodssMessage
     * @param fragmentId 分片字段
     * @param fragment 分片信息 ShardingUtil.orderGoodsFragment
     * @return
     */
    public static String getIdToTableName(String tableName,Long fragmentId,Long[] fragment){
        Long nk = 0L;
        int fg = 0;//0未确定1已确定
        for(int i=0;i<fragment.length;i++){
            if(fragmentId < fragment[i]){
                nk = i+0L;
                fg = 1;
                break;
            }
        }
        if(fg == 0){
            nk = fragment.length+0L;
        }
        return tableName+"_"+nk;
    }

    /**
     * 获取分片表名（时间分片）
     * @param date 数据的时间  例：采购订单的时间（purchase_time字段对应的时间）
     * @param tableName 原始表名 例：ShardingUtil.waitPayRecord
     * @param amount 例如：ShardingUtil.waitPayRecordAmount
     * @param fragment 时间分片单位 例如：ShardingUtil.waitPayRecordFragment
     * @return
     */
    public static String getTimeToTableName(Date date, String tableName, Integer amount, String fragment){
        //String startTime = "2023-01-01 00:00:00";
        String time = "2023";
        JSONObject jsonObject = DateUtils.dayCompare(DateUtils.dateTime(DateUtils.YYYY_MM_DD_HH_MM_SS,startTime),date);//相差年数月数
        if("YEARS".equals(fragment)){//年
            Integer yearNum = jsonObject.getInteger("year");//相差年数
            int nn = yearNum%amount;//取余
            int nj = yearNum/amount;//取倍数(需要增加的年数)
            //获取配置的开始时间的年份
            String yearStr = DateUtils.parseDateToStr(DateUtils.YYYY,DateUtils.dateTime(DateUtils.YYYY_MM_DD_HH_MM_SS,startTime));
            Integer year = Integer.parseInt(yearStr);
            if(nj > 0){//计算获取订单时间对应的表的年份
                year += (amount*nj);
            }
            time = year.toString();
        }else if("MONTHS".equals(fragment)){//月
            Integer monthNum = jsonObject.getInteger("month");//相差月数
            int nn = monthNum%amount;//取余
            int nj = monthNum/amount;//取倍数(需要增加的年数)
            int month = 0;
            if(nj > 0){//计算获取订单时间对应的表的年份
                month += (amount*nj);
            }
            Date data = DateUtils.stepMonth(DateUtils.dateTime(DateUtils.YYYY_MM_DD_HH_MM_SS,startTime),month);
            time = DateUtils.parseDateToStr(DateUtils.YYYY_MM,data);
        }
        return tableName+"_"+time;
    }

    public static void main(String[] args) {
        String startTime = "2025-09-01 00:00:00";
        String tableName = ShardingUtil.getTimeToTableName(DateUtils.dateTime(DateUtils.YYYY_MM_DD_HH_MM_SS,startTime),waitPayRecord,18,"MONTHS");
        System.out.println(tableName);
    }

    public Long[] getOrderGoodsFragment() {
        return orderGoodsFragment;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-radius.props.sharding-ranges}")
    public void setOrderGoodsFragment(Long[] orderGoodsFragment) {
        ShardingUtil.orderGoodsFragment = orderGoodsFragment;
    }

    public Long[] getOrderGoodsskuFragment() {
        return orderGoodsskuFragment;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-radius1.props.sharding-ranges}")
    public void setOrderGoodsskuFragment(Long[] orderGoodsskuFragment) {
        this.orderGoodsskuFragment = orderGoodsskuFragment;
    }

    public Long[] getGoodsSourcsFragment() {
        return goodsSourcsFragment;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-radius2.props.sharding-ranges}")
    public void setGoodsSourcsFragment(Long[] goodsSourcsFragment) {
        ShardingUtil.goodsSourcsFragment = goodsSourcsFragment;
    }

    public Long[] getGoodsSourcsSkuFragment() {
        return goodsSourcsSkuFragment;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-radius3.props.sharding-ranges}")
    public void setGoodsSourcsSkuFragment(Long[] goodsSourcsSkuFragment) {
        ShardingUtil.goodsSourcsSkuFragment = goodsSourcsSkuFragment;
    }

    public Long[] getGoodsRelationStatusFragment() {
        return goodsRelationStatusFragment;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-radius.props.sharding-ranges}")
    public void setGoodsRelationStatusFragment(Long[] goodsRelationStatusFragment) {
        ShardingUtil.goodsRelationStatusFragment = goodsRelationStatusFragment;
    }

    public String getWaitPayRecordFragment() {
        return waitPayRecordFragment;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.datetime-interval-unit}")
    public void setWaitPayRecordFragment(String waitPayRecordFragment) {
        ShardingUtil.waitPayRecordFragment = waitPayRecordFragment;
    }

    public String getWaitPayOrderFragment() {
        return waitPayOrderFragment;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.datetime-interval-unit}")
    public void setWaitPayOrderFragment(String waitPayOrderFragment) {
        ShardingUtil.waitPayOrderFragment = waitPayOrderFragment;
    }

    public String getWaitPayOrderProductFragment() {
        return waitPayOrderProductFragment;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-radius5.props.sharding-ranges}")
    public void setWaitPayOrderProductFragment(String waitPayOrderProductFragment) {
        ShardingUtil.waitPayOrderProductFragment = waitPayOrderProductFragment;
    }

    public Integer getWaitPayRecordAmount() {
        return waitPayRecordAmount;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.datetime-interval-amount}")
    public void setWaitPayRecordAmount(Integer waitPayRecordAmount) {
        ShardingUtil.waitPayRecordAmount = waitPayRecordAmount;
    }

    public Integer getWaitPayOrderAmount() {
        return waitPayOrderAmount;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.datetime-interval-amount}")
    public void setWaitPayOrderAmount(Integer waitPayOrderAmount) {
        ShardingUtil.waitPayOrderAmount = waitPayOrderAmount;
    }

    public Integer getWaitPayOrderProductAmount() {
        return waitPayOrderProductAmount;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.datetime-interval-amount}")
    public void setWaitPayOrderProductAmount(Integer waitPayOrderProductAmount) {
        ShardingUtil.waitPayOrderProductAmount = waitPayOrderProductAmount;
    }

    public String getStartTime() {
        return startTime;
    }

    @Value("${spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.datetime-lower}")
    public void setStartTime(String startTime) {
        ShardingUtil.startTime = startTime;
    }
}