记录我在项目中sharding分库分表在落地方案：自动分库 自动按年月建表存储与查询

配置文件:bootstrap.yml

# Tomcat
server:
  port: 9528

# Spring
spring:
  application:
    # 应用名称
    name: jingce-sharding-gz
  profiles:
    # 环境配置
     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:
        # 服务注册地址
        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

配置文件项目图：


11.png

实体类

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;


    /**
     * 创建时间(跟进时间)
     */
    @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,在分库分表实际代码中需要使用

package com.jingce.sharding.config;

import java.util.Map;

public class ThreadLocal {

    /**
     * 构造函数私有
     */
    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);
    }

    /**
     * 获取当前用户信息
     */
    public static Map<String, String> get() {
        return threadLocal.get();
    }




}

准备工作做好，下面开始实现具体分库分表操作，以及按月自动建表操作

分库实现类：TimeShardingAlgorithmDb,根据字段配置中的sysCode实现分库

/*
 * 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";


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

    /**
     * 完整时间格式
     */
    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分片，节点配置表名：{}", availableTargetNames);

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


        // 获取需要创建新表的数据库连接
        String createTable = props.getProperty(CREATE_TABLE_DB);
        Map<String, Map<String, String>> map = splitWithoutBraces(createTable);
        System.out.println(map);

        // 获取需要创建表的数据库信息
        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分片，节点配置表名：{}", 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】数据库连接配置有误，请稍后重试，URL:{}, username:{}, password:{}", jdbcUrl, username, password);
            throw new IllegalArgumentException("数据库连接配置有误，请稍后重试");
        }
        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】数据库连接失败，请稍后重试，原因：{}", e.getMessage(), e);
            throw new IllegalArgumentException("数据库连接失败，请稍后重试");
        }
        return tableNames;
    }

}

分表实现类：可以根据实体类中createTime区分当前数据是几月份并自动按月份建表例如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";


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

    /**
     * 完整时间格式
     */
    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】精确分片，节点配置表名：{}", 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);

            // 查询催记所有分表节点
            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】范围分片，节点配置表名：{}", 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】数据库连接配置有误，请稍后重试，URL:{}, username:{}, password:{}", jdbcUrl, username, password);
            throw new IllegalArgumentException("数据库连接配置有误，请稍后重试");
        }
        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】数据库连接失败，请稍后重试，原因：{}", e.getMessage(), e);
            throw new IllegalArgumentException("数据库连接失败，请稍后重试");
        }
        return tableNames;
    }

    /**
     * 检查分表获取的表名是否存在，不存在则自动建表
     * @param logicTableName   逻辑表
     * @param resultTableName 真实表名，例：t_user_202201
     * @return 确认存在于数据库中的真实表名
     */
    public String getShardingTableAndCreate(String logicTableName, String resultTableName, Collection<String> availableTargetNames,String jdbcUrl,String username,String password) {
        // 缓存中有此表则返回，没有则判断创建
        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;
            }
        }
    }

    /**
     * 创建分表2
     * @param logicTableName  逻辑表
     * @param resultTableName 真实表名，例：t_user_202201
     * @return 创建结果（true创建成功，false未创建）
     */
    public boolean createShardingTable(String logicTableName, String resultTableName,String jdbcUrl,String username,String password) {
        // 根据日期判断，当前月份之后分表不提前创建
        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;
    }

    /**
     * 执行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】数据库连接配置有误，请稍后重试，URL:{}, username:{}, password:{}", jdbcUrl, username, password);
            throw new IllegalArgumentException("数据库连接配置有误，请稍后重试");
        }
        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】数据表创建执行失败，请稍后重试，原因：{}", e.getMessage(), e);
                throw new IllegalArgumentException("数据表创建执行失败，请稍后重试");
            }
        } catch (SQLException e) {
            log.error(">>>>>>>>>> 【ERROR】数据库连接失败，请稍后重试，原因：{}", e.getMessage(), e);
            throw new IllegalArgumentException("数据库连接失败，请稍后重试");
        }
    }

}

重要：项目启动后，需要主动执行一次查询动作，目的是为了将分库分表的配置加载到缓存中,
如果内存的缓存中没有分库分表的配置，查询会没有数据
详细可以参考：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

这个地址有具体的自动按月分表方案，但是我自己的代码中有根据项目实际的分库并分表存储数据方案，可以多多参考达到自己的目的

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 项目启动后，读取已有分表，进行缓存
 *
 * @author ACGkaka
 * @date 2022/12/20 15:41
 */
@Slf4j
//@Order(value = 100) // 数字越小，越先执行
@Component
public class ShardingTablesLoadRunner implements CommandLineRunner {

//    @Autowired
//    private UserService userService;


    @Autowired
    private ICaseFollowService caseFollowService;

    @Override
    public void run(String... args) {
        // 读取已有分表，进行缓存
//        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文件：改配置中可能存在项目中自己的一些配置，不需要可以删除点

<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>

项目架构图：


22.png
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,386评论 6赞 479
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,939评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,851评论 0赞 341
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,953评论 1赞 278
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,971评论 5赞 369
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,784评论 1赞 283
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,126评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,765评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,148评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,744评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,858评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,479评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,080评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,053评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,278评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,245评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,590评论 2赞 343

推荐阅读更多精彩内容

  • Android 权限申请不弹框，直接返回-1
    搜了下，一部分是说讲清单文件加个东西：https://blog.csdn.net/u011343735/artic...
    javalong阅读 311评论 0赞 0
  • iOS使用相关技术要点
    1 工程创建和配置 签名配置问题1 由于项目是从uniapp离线打包工程来的，所以有些配置是已经存在的。创建工程时...
    司空洛一阅读 1,369评论 0赞 0
  • 搭建自己的私服仓库
    工作中发现了个私有仓库 是在内网环境，上传了个jar 结果没有成功特来学习以下想用docker的方式进行安装 1....
    请叫我翟先生阅读 337评论 0赞 0
  • SQL学习笔记05
    Task 06 SQL高级处理 Content 窗口函数 GROUPING运算符 存储过程和参数 预处理声明PRE...
    yuiki_0829阅读 314评论 0赞 0
  • Java
    语言技能 JAVA基础 操作符、控制执行流程JAVA的重要特性：自动内存管理机制、异常处理。 ArrayList的...
    emperorxiaomai阅读 447评论 0赞 0