配置文件:bootstrap.yml

# Tomcat
server:
  port: 9528

# Spring
spring:
  application:
    # 應(yīng)用名稱
    name: jingce-sharding-gz
  profiles:
    # 環(huán)境配置
     active: dev

props:
  sql-show: true

配置文件: bootstrap-dev.yml

# Spring
spring:
  ### 處理連接池沖突 #####
  main:
    allow-bean-definition-overriding: true
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:sharding-dev.yaml

  cloud:
    nacos:
      discovery:
        # 服務(wù)注冊地址
        server-addr: 172.16.7.100:8848
#        server-addr: 127.0.0.1:8848
      config:
        # 配置中心地址
        server-addr: 172.16.7.100:8848
#        server-addr: 127.0.0.1:8848
        # 配置文件格式
        file-extension: yml
        # 共享配置
        shared-dataids: application-dev.${spring.cloud.nacos.config.file-extension}

logging:
  level.root: info
  level.com.jingce: debug

pagehelper:
  helperDialect: postgresql

配置文件sharding.dev.yaml

dataSources:
  abc:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://172.16.7.100:3306/jingce_sharding_abc?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
    username: root
    password: wervn11l_2Low0OZq

  ccb:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://172.16.7.100:3306/jingce_sharding_ccb?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
    username: root
    password: wervn11l_2Low0OZq

rules:
- !SHARDING
  tables:
    case_follow:
      actualDataNodes: abc.case_follow,ccb.case_follow
      tableStrategy:
        standard:
          shardingColumn: create_time
          shardingAlgorithmName: auto-custom
      databaseStrategy:
        standard:
          shardingColumn: sys_code
          shardingAlgorithmName: auto-custom-db

  shardingAlgorithms:
    auto-custom:
      type: CLASS_BASED
      props:
        strategy: standard
        algorithmClassName: com.jingce.sharding.config.TimeShardingAlgorithm
    auto-custom-db:
      type: CLASS_BASED
      props:
        strategy: standard
        algorithmClassName: com.jingce.sharding.config.TimeShardingAlgorithmDb
        createTable:
          jingce_debt_abc:
            dbName: abc
            jdbcUrl: jdbc:mysql://172.16.7.100:3306/jingce_sharding_abc?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
            username: root
            password: wervn11l_2Low0OZq
          jingce_debt_ccb:
            dbName: ccb
            jdbcUrl: jdbc:mysql://172.16.7.100:3306/jingce_sharding_ccb?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
            username: root
            password: wervn11l_2Low0OZq

props:
  sql-show: true

配置文件項(xiàng)目圖：

11.png

實(shí)體類

package com.jingce.sharding.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.time.LocalDateTime;
import java.io.Serializable;
import java.util.Date;

/**
 * <p>
 *
 * </p>
 *
 * @author ywl
 * @since 2023-12-05
 */
@Data
public class CaseFollow {

    /**
     * id
     */
    private Long id;

    /**
     * 部門id
     */
    private Long deptId;


    /**
     * 創(chuàng)建時(shí)間(跟進(jìn)時(shí)間)
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;



    @TableField(exist = false)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date startTime;

    @TableField(exist = false)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date endTime;

}

本地線程工具類:threadLocal,在分庫分表實(shí)際代碼中需要使用

package com.jingce.sharding.config;

import java.util.Map;

public class ThreadLocal {

    /**
     * 構(gòu)造函數(shù)私有
     */
    private ThreadLocal() {
    }

    private static final java.lang.ThreadLocal<Map<String, String>> threadLocal = new java.lang.ThreadLocal<>();

    /**
     * 清除用戶信息
     */
    public static void clear() {
        threadLocal.remove();
    }

    /**
     * 存儲用戶信息
     */
    public static void set(Map<String, String> map) {
        threadLocal.set(map);
    }

    /**
     * 獲取當(dāng)前用戶信息
     */
    public static Map<String, String> get() {
        return threadLocal.get();
    }




}

準(zhǔn)備工作做好，下面開始實(shí)現(xiàn)具體分庫分表操作柒莉，以及按月自動建表操作

分庫實(shí)現(xiàn)類：TimeShardingAlgorithmDb,根據(jù)字段配置中的sysCode實(shí)現(xiàn)分庫

/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.jingce.sharding.config;

import com.jingce.sharding.utils.SpringUtil;
import lombok.Getter;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.sharding.api.sharding.ShardingAutoTableAlgorithm;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import org.springframework.core.env.Environment;

import java.sql.*;
import java.time.format.DateTimeFormatter;
import java.util.*;


/**
 * <p> @Title TimeShardingAlgorithm
 * <p> @Description 分片算法闻坚，按月分片
 *
 * @author ACGkaka
 * @date 2022/12/20 11:33
 */

@Slf4j
public class TimeShardingAlgorithmDb implements StandardShardingAlgorithm<String>, ShardingAutoTableAlgorithm {


    /** 表分片符號，例：t_user_202201 中常柄，分片符號為 "_" */
    private static final String TABLE_SPLIT_SYMBOL = "_";



    /** 配置文件路徑 */
    private static final String CONFIG_FILE = "sharding-tables.yaml";


    /**
     * 分片時(shí)間格式
     */
    private static final DateTimeFormatter TABLE_SHARD_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMM");

    /**
     * 完整時(shí)間格式
     */
    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd HH:mm:ss");


    @Getter
    private Properties props;

    @Getter
    private int autoTablesAmount;

    private static String CREATE_TABLE_DB = "createTable";
    private static String DB_NAME = "dbName";


    @Override
    public void init(final Properties dataSources) {
        this.props = dataSources;
    }


    @SneakyThrows
    @Override
    public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<String> preciseShardingValue) {
        String value = preciseShardingValue.getValue();
        log.info(">>>>>>>>>> 【INFO】精確DB分片鲤氢，節(jié)點(diǎn)配置表名：{}", availableTargetNames);

        Environment env = SpringUtil.getBean(Environment.class);
        String property = env.getProperty("spring.dataSources.abc.username");
        System.out.println(property);


        // 獲取需要?jiǎng)?chuàng)建新表的數(shù)據(jù)庫連接
        String createTable = props.getProperty(CREATE_TABLE_DB);
        Map<String, Map<String, String>> map = splitWithoutBraces(createTable);
        System.out.println(map);

        // 獲取需要?jiǎng)?chuàng)建表的數(shù)據(jù)庫信息
        Map<String, String> dataSourceMap = map.get(value);
        ThreadLocal.set(dataSourceMap);
        String dbName = dataSourceMap.get(DB_NAME);



        for (Map.Entry<String, Map<String, String>> entry : map.entrySet()) {
            Map<String, String> mapValue = entry.getValue();
            String logicTableName = preciseShardingValue.getLogicTableName();
            String jdbcUrl = mapValue.get("jdbcUrl");
            String username = mapValue.get("username");
            String password = mapValue.get("password");
            List<String> allTableNameBySchema = getAllTableNameBySchema(logicTableName, jdbcUrl, username, password);
            availableTargetNames.addAll(allTableNameBySchema);
            String key = entry.getKey();
            if (!availableTargetNames.contains(key)){
                availableTargetNames.add(key);
            }
        }

        return dbName;

    }


    public static Map<String, Map<String, String>> splitWithoutBraces(String input) {
        // 去掉收尾大括號
        input = input.replaceFirst("\\{", "").replaceFirst("\\}$", "");
        List<String> list = new ArrayList<>();
        int braceLevel = 0;
        int start = 0;

        for (int i = 0; i < input.length(); i++) {
            char ch = input.charAt(i);
            if (ch == '{') {
                braceLevel++;
            } else if (ch == '}') {
                braceLevel--;
            } else if (ch == ',' && braceLevel == 0) {
                list.add(input.substring(start, i).trim());
                start = i + 1;
            }
        }
        list.add(input.substring(start).trim());
        System.out.println(list);
        Map<String, Map<String, String>> map = new HashMap<>();
        for (String str : list) {
            String[] sp = str.replaceFirst("=", "#@&").split("#@&");
            String key = sp[0];
            String value = sp[1];
            HashMap<String, String> valueMap = new HashMap<>();

            List<String> valueList = Arrays.asList(value.replaceAll("\\{", "").replaceAll("}", "").split(","));
            for (String s : valueList) {
                String[] split = s.replaceFirst("=", "#@&").split("#@&");
                valueMap.put(split[0].trim(),split[1].trim());
            }
            map.put(key.trim(),valueMap);
        }

        return map;
    }


    @Override
    public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<String> rangeShardingValue) {
        log.info(">>>>>>>>>> 【INFO】范圍DB分片，節(jié)點(diǎn)配置表名：{}", availableTargetNames);

        return null;

    }

    @Override
    public String getType() {
        return "auto-custom-db";
    }


//    @Override
//    public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<String> shardingValue) {
//        List<String> tables = new ArrayList<>();
//        tables.add("ds1");
//        return tables;
//    }


    /**
     * 獲取所有表名
     * @return 表名集合
     * @param logicTableName 邏輯表
     */
    public List<String> getAllTableNameBySchema(String logicTableName,String jdbcUrl,String username,String password) {
        List<String> tableNames = new ArrayList<>();
        if (StringUtils.isEmpty(jdbcUrl) || StringUtils.isEmpty(username) || StringUtils.isEmpty(password)) {
            log.error(">>>>>>>>>> 【ERROR】數(shù)據(jù)庫連接配置有誤西潘，請稍后重試，URL:{}, username:{}, password:{}", jdbcUrl, username, password);
            throw new IllegalArgumentException("數(shù)據(jù)庫連接配置有誤哨颂，請稍后重試");
        }
        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
             Statement st = conn.createStatement()) {
            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】數(shù)據(jù)庫連接失敗喷市，請稍后重試，原因：{}", e.getMessage(), e);
            throw new IllegalArgumentException("數(shù)據(jù)庫連接失敗威恼，請稍后重試");
        }
        return tableNames;
    }

}

分表實(shí)現(xiàn)類：可以根據(jù)實(shí)體類中createTime區(qū)分當(dāng)前數(shù)據(jù)是幾月份并自動按月份建表例如case_follow_202404

/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.jingce.sharding.config;

import cn.hutool.core.util.ObjectUtil;
import com.google.common.collect.Range;
import com.jingce.common.core.utils.DateUtils;
import lombok.Getter;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.sharding.api.sharding.ShardingAutoTableAlgorithm;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.*;

/**
 * <p> @Title TimeShardingAlgorithm
 * <p> @Description 分片算法品姓，按月分片
 *
 * @author ACGkaka
 * @date 2022/12/20 11:33
 */

@Slf4j
public class TimeShardingAlgorithm implements StandardShardingAlgorithm<String>, ShardingAutoTableAlgorithm {


    /** 表分片符號寝并，例：t_user_202201 中，分片符號為 "_" */
    private static final String TABLE_SPLIT_SYMBOL = "_";



    /** 配置文件路徑 */
    private static final String CONFIG_FILE = "sharding-tables.yaml";


    /**
     * 分片時(shí)間格式
     */
    private static final DateTimeFormatter TABLE_SHARD_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMM");

    /**
     * 完整時(shí)間格式
     */
    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd HH:mm:ss");


    @Getter
    private Properties props;

    @Getter
    private int autoTablesAmount;


    @Override
    public void init(final Properties props) {
        this.props = props;
    }


    @SneakyThrows
    @Override
    public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<String> preciseShardingValue) {
        String logicTableName = preciseShardingValue.getLogicTableName();
        log.info(">>>>>>>>>> 【INFO】精確分片腹备，節(jié)點(diǎn)配置表名：{}", availableTargetNames);
        // availableTargetNames.add("case_follow");
        String monthStr = "";

        Object value = preciseShardingValue.getValue();
        if (value instanceof Date){
            System.out.println(111);
            DateFormat dateFormat = new SimpleDateFormat("yyyyMM");
            monthStr = dateFormat.format(value);
        }else if (value instanceof LocalDateTime){
            System.out.println(222);
            LocalDateTime parse = LocalDateTime.parse(value.toString());
            monthStr = parse.format(TABLE_SHARD_TIME_FORMATTER);
        }
        System.out.println(monthStr);
        String resultTableName = logicTableName + "_" + monthStr;

        System.out.println(resultTableName);


        String jdbcUrl = "";
        String username = "";
        String password = "";
        Map<String, String> map = ThreadLocal.get();
        if (ObjectUtil.isNotNull(map)){
            jdbcUrl = map.get("jdbcUrl");
            username = map.get("username");
            password = map.get("password");
            // 用完之后清空ThreadLocal
            ThreadLocal.clear();

            System.out.println(jdbcUrl);
            System.out.println(username);
            System.out.println(password);

            // 查詢催記所有分表節(jié)點(diǎn)
            List<String> allTableNameBySchema = getAllTableNameBySchema(logicTableName,jdbcUrl,username,password);
            System.out.println(allTableNameBySchema);
            for (String table : allTableNameBySchema) {
                if (!availableTargetNames.contains(table)){
                    availableTargetNames.add(table);
                }
            }
//            availableTargetNames.clear();
//            availableTargetNames.addAll(allTableNameBySchema);

            return getShardingTableAndCreate(logicTableName, resultTableName, availableTargetNames, jdbcUrl, username, password);
        }

        return resultTableName;

    }



    @Override
    public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<String> rangeShardingValue) {
        log.info(">>>>>>>>>> 【INFO】范圍分片衬潦，節(jié)點(diǎn)配置表名：{}", availableTargetNames);
        String logicTableName = rangeShardingValue.getLogicTableName();
        List<String> list = new ArrayList<>();
        Range<String> valueRange = rangeShardingValue.getValueRange();
        Object startValue = valueRange.lowerEndpoint();
        Object endValue = valueRange.upperEndpoint();

        String startMonth = "";
        if (startValue instanceof Date){
            DateFormat dateFormat = new SimpleDateFormat("yyyyMM");
            startMonth = dateFormat.format(startValue);
        }else if (startValue instanceof LocalDateTime){
            LocalDateTime parse = LocalDateTime.parse(startValue.toString());
            startMonth = parse.format(TABLE_SHARD_TIME_FORMATTER);
        }

        String endMonth = "";
        if (endValue instanceof Date){
            DateFormat dateFormat = new SimpleDateFormat("yyyyMM");
            endMonth = dateFormat.format(endValue);
        }else if (endValue instanceof LocalDateTime){
            LocalDateTime parse = LocalDateTime.parse(endValue.toString());
            endMonth = parse.format(TABLE_SHARD_TIME_FORMATTER);
        }

        // list.add("case_follow");
        List<String> yearMonthsBetween = DateUtils.getYearMonthsBetween(startMonth, endMonth);
        for (String yearMonth : yearMonthsBetween) {
            String resultTableName =  logicTableName + "_" + yearMonth;
            if (availableTargetNames.contains(resultTableName)){
                list.add(resultTableName);
            }
        }

        return list;

    }

    @Override
    public String getType() {
        return "AUTO_CUSTOM";
    }

    /**
     * 獲取所有表名
     * @return 表名集合
     * @param logicTableName 邏輯表
     */
    public List<String> getAllTableNameBySchema(String logicTableName,String jdbcUrl,String username,String password) {
        List<String> tableNames = new ArrayList<>();
        if (StringUtils.isEmpty(jdbcUrl) || StringUtils.isEmpty(username) || StringUtils.isEmpty(password)) {
            log.error(">>>>>>>>>> 【ERROR】數(shù)據(jù)庫連接配置有誤，請稍后重試植酥，URL:{}, username:{}, password:{}", jdbcUrl, username, password);
            throw new IllegalArgumentException("數(shù)據(jù)庫連接配置有誤镀岛，請稍后重試");
        }
        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
             Statement st = conn.createStatement()) {
            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】數(shù)據(jù)庫連接失敗，請稍后重試友驮，原因：{}", e.getMessage(), e);
            throw new IllegalArgumentException("數(shù)據(jù)庫連接失敗漂羊，請稍后重試");
        }
        return tableNames;
    }

    /**
     * 檢查分表獲取的表名是否存在，不存在則自動建表
     * @param logicTableName   邏輯表
     * @param resultTableName 真實(shí)表名卸留，例：t_user_202201
     * @return 確認(rèn)存在于數(shù)據(jù)庫中的真實(shí)表名
     */
    public String getShardingTableAndCreate(String logicTableName, String resultTableName, Collection<String> availableTargetNames,String jdbcUrl,String username,String password) {
        // 緩存中有此表則返回走越，沒有則判斷創(chuàng)建
        if (availableTargetNames.contains(resultTableName)) {
            return resultTableName;
        } else {
            // 檢查分表獲取的表名不存在，需要自動建表
            boolean isSuccess = createShardingTable(logicTableName, resultTableName,jdbcUrl,username,password);
            if (isSuccess) {
                // 如果建表成功耻瑟，需要更新緩存
                availableTargetNames.add(resultTableName);
                autoTablesAmount++;
                return resultTableName;
            } else {
                // 如果建表失敗旨指，返回邏輯空表
                return logicTableName;
            }
        }
    }

    /**
     * 創(chuàng)建分表2
     * @param logicTableName  邏輯表
     * @param resultTableName 真實(shí)表名，例：t_user_202201
     * @return 創(chuàng)建結(jié)果（true創(chuàng)建成功喳整，false未創(chuàng)建）
     */
    public boolean createShardingTable(String logicTableName, String resultTableName,String jdbcUrl,String username,String password) {
        // 根據(jù)日期判斷淤毛，當(dāng)前月份之后分表不提前創(chuàng)建
        String month = resultTableName.replace(logicTableName + TABLE_SPLIT_SYMBOL,"");
        YearMonth shardingMonth = YearMonth.parse(month, DateTimeFormatter.ofPattern("yyyyMM"));
        if (shardingMonth.isAfter(YearMonth.now())) {
            return false;
        }

        synchronized (logicTableName.intern()) {
            // 緩存中無此表，則建表并添加緩存
            executeSql(Collections.singletonList("CREATE TABLE IF NOT EXISTS `" + resultTableName + "` LIKE `" + logicTableName + "`;"),jdbcUrl,username,password);
        }
        return true;
    }

    /**
     * 執(zhí)行SQL
     * @param sqlList SQL集合
     */
    private void executeSql(List<String> sqlList,String jdbcUrl,String username,String password) {
        if (StringUtils.isEmpty(jdbcUrl) || StringUtils.isEmpty(username) || StringUtils.isEmpty(password)) {
            log.error(">>>>>>>>>> 【ERROR】數(shù)據(jù)庫連接配置有誤算柳，請稍后重試低淡，URL:{}, username:{}, password:{}", jdbcUrl, username, password);
            throw new IllegalArgumentException("數(shù)據(jù)庫連接配置有誤，請稍后重試");
        }
        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
            try (Statement st = conn.createStatement()) {
                conn.setAutoCommit(false);
                for (String sql : sqlList) {
                    st.execute(sql);
                }
            } catch (Exception e) {
                conn.rollback();
                log.error(">>>>>>>>>> 【ERROR】數(shù)據(jù)表創(chuàng)建執(zhí)行失敗瞬项，請稍后重試蔗蹋，原因：{}", e.getMessage(), e);
                throw new IllegalArgumentException("數(shù)據(jù)表創(chuàng)建執(zhí)行失敗，請稍后重試");
            }
        } catch (SQLException e) {
            log.error(">>>>>>>>>> 【ERROR】數(shù)據(jù)庫連接失敗囱淋，請稍后重試猪杭，原因：{}", e.getMessage(), e);
            throw new IllegalArgumentException("數(shù)據(jù)庫連接失敗，請稍后重試");
        }
    }

}

重要：項(xiàng)目啟動后妥衣，需要主動執(zhí)行一次查詢動作皂吮，目的是為了將分庫分表的配置加載到緩存中,
如果內(nèi)存的緩存中沒有分庫分表的配置，查詢會沒有數(shù)據(jù)
詳細(xì)可以參考：https://blog.csdn.net/qq_33204709/article/details/132590731?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-132590731-blog-131208779.235%5Ev38%5Epc_relevant_anti_t3_base&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-132590731-blog-131208779.235%5Ev38%5Epc_relevant_anti_t3_base&utm_relevant_index=1

這個(gè)地址有具體的自動按月分表方案税手，但是我自己的代碼中有根據(jù)項(xiàng)目實(shí)際的分庫并分表存儲數(shù)據(jù)方案蜂筹，可以多多參考達(dá)到自己的目的

package com.jingce.sharding.config;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.jingce.sharding.domain.CaseFollow;
import com.jingce.sharding.service.ICaseFollowService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import java.time.LocalDateTime;
import java.util.List;

/**
 * <p> @Title ShardingTablesLoadRunner
 * <p> @Description 項(xiàng)目啟動后，讀取已有分表芦倒，進(jìn)行緩存
 *
 * @author ACGkaka
 * @date 2022/12/20 15:41
 */
@Slf4j
//@Order(value = 100) // 數(shù)字越小艺挪，越先執(zhí)行
@Component
public class ShardingTablesLoadRunner implements CommandLineRunner {

//    @Autowired
//    private UserService userService;


    @Autowired
    private ICaseFollowService caseFollowService;

    @Override
    public void run(String... args) {
        // 讀取已有分表，進(jìn)行緩存
//        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
//        queryWrapper.eq(User::getCreateTime, LocalDateTime.now()).last("limit 1");
//        List<User> list = userService.list(queryWrapper);
//        System.out.println(list);



        LambdaQueryWrapper<CaseFollow> queryWrapper = new LambdaQueryWrapper<>();
//        queryWrapper.eq(CaseFollow::getCreateTime, LocalDateTime.now()).last("limit 1");
        queryWrapper.eq(CaseFollow::getSysCode,"jingce_debt_abc").eq(CaseFollow::getCreateTime, LocalDateTime.now()).last("limit 1");
        List<CaseFollow> list = caseFollowService.list(queryWrapper);
        System.out.println(list);


        log.info(">>>>>>>>>> 【ShardingTablesLoadRunner】緩存已有分表成功 <<<<<<<<<<");
    }

}

pom文件：改配置中可能存在項(xiàng)目中自己的一些配置兵扬，不需要可以刪除點(diǎn)

<dependencies>

        <!-- SpringCloud Ailibaba Nacos -->
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
        </dependency>

        <!-- SpringCloud Ailibaba Nacos Config -->
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-nacos-config</artifactId>
        </dependency>

        <!-- SpringCloud Ailibaba Sentinel -->
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-sentinel</artifactId>
        </dependency>

        <!-- SpringBoot Actuator -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>

        <!-- Swagger -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>${swagger.fox.version}</version>
        </dependency>

        <!-- Mysql Connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!-- RuoYi Common Datascope -->
        <dependency>
            <groupId>com.jingce</groupId>
            <artifactId>jingce-common-datascope</artifactId>
        </dependency>

        <!-- RuoYi Common Log -->
        <dependency>
            <groupId>com.jingce</groupId>
            <artifactId>jingce-common-log</artifactId>
        </dependency>

        <!-- RuoYi Common Swagger -->
        <dependency>
            <groupId>com.jingce</groupId>
            <artifactId>jingce-common-swagger</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-websocket</artifactId>
            <version>2.0.4.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils</artifactId>
            <version>1.8.3</version>
            <scope>compile</scope>
        </dependency>


        <!-- https://mvnrepository.com/artifact/commons-httpclient/commons-httpclient -->
        <dependency>
            <groupId>commons-httpclient</groupId>
            <artifactId>commons-httpclient</artifactId>
            <version>3.1</version>
        </dependency>

        <!-- Sharding-JDBC -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>5.3.0</version>
        </dependency>

        <!-- Mybatis的分頁插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
        </dependency>

        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>


    </dependencies>

項(xiàng)目架構(gòu)圖：

22.png