Sharding-JDBC(九)5.3.0版本,实现按月分表、自动建表、自动刷新节点

目录

    • 一、简介
    • 二、Maven依赖
    • 三、配置文件
      • application.yml
      • sharding.yaml
    • 四、代码实现
      • 1.自动建表、自动刷新节点思路
      • 2.创建表结构
      • 3.TimeShardingAlgorithm.java 分片算法类
      • 4.ShardingAlgorithmTool.java 分片工具类
      • 5.ShardingTablesLoadRunner.java 初始化缓存类
      • 6.SpringUtil.java Spring工具类
    • 五、测试
      • 1.测试代码
      • 2.测试结果
    • 六、代码地址

在这里插入图片描述

一、简介

背景: 之前使用 ShardingSphere-JDBC 的 5.1.0 版本进行数据分片,后来开源组件扫描发现 5.1.0 版本存在如下漏洞,需要升级到 5.3.0 及以上版本

  • Apache ShardingSphere远程代码执行漏洞(CVE-2020-1947): Apache ShardingSphere存在YAML解析远程代码执行漏洞,开发人员直接使用unmarshal方法对输入的YAML直接进行解析,没有做校验,攻击者在相应漏洞触发点输入payload即可完成攻击。

升级到 5.3.0 改动还是很大的,新版本移除了 Spring 全部依赖和配置支持,引入了 ShardingSphereDriver 数据库驱动,也就意味着需要使用新的配置方式

注意:由于 Druid 集成过程中遇到些问题,本文集成内容未使用 Druid 连接池,如果后续集成完毕,会再补充一篇文章说明。

二、Maven依赖

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

三、配置文件

application.yml

server:
  port: 8081

spring:
  ### 处理连接池冲突 #####
  main:
    allow-bean-definition-overriding: true
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:sharding.yaml

# mybatis-plus
mybatis-plus:
  mapper-locations: classpath*:/mapper/*Mapper.xml
  # 实体扫描,多个package用逗号或者分号分隔
  typeAliasesPackage: com.demo.*.entity
  # 测试环境打印sql
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

pagehelper:
  helperDialect: postgresql

my:
  sharding:
    create-table:
      url: jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
      username: root
      password: root

sharding.yaml

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

dataSources:
  ds:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    username: root
    password: root

rules:
- !SHARDING
  tables:
    t_user:
      actualDataNodes: ds.t_user
      tableStrategy:
        standard:
          shardingColumn: create_time
          shardingAlgorithmName: auto-custom

  shardingAlgorithms:
    auto-custom:
      type: CLASS_BASED
      props:
        strategy: standard
        algorithmClassName: com.demo.module.config.sharding.TimeShardingAlgorithm
#    auto-interval:
#      type: AUTO_INTERVAL
#      props:
#        datetime-lower: '2023-01-01 00:00:00'
#        datetime-upper: '2023-12-01 00:00:00'
#        # 60 * 60 * 24 * 30=30天
#        sharding-seconds: '2592000'
#    interval:
#      type: INTERVAL
#      props:
#        datetime-pattern: 'yyyy-MM'
#        datetime-lower: '2023-01'
#        datetime-upper: '2023-12'
#        sharding-suffix-pattern: 'yyyyMM'
#        # 间隔大小
#        datetime-interval-amount: 1
#        datetime-interval-unit: 'Months'

props:
  sql-show: false

四、代码实现

1.自动建表、自动刷新节点思路

大概思路为:

  • 配置: 编写配置的时候,我们只编写逻辑表 t_user。
  • 启动初始化: 在启动后执行脚本 LoadRunner.run() 的时候,触发精确分片来初始化数据库中表名到配置中。
  • 建表并刷新配置: 如果初始化后的配置中不存在表名,则创建表并刷新分表配置。

在这里插入图片描述

2.创建表结构

-- ------------------------------
-- 用户表
-- ------------------------------
CREATE TABLE `t_user` (
  `id` bigint(16) NOT NULL COMMENT '主键',
  `username` varchar(64) NOT NULL COMMENT '用户名',
  `password` varchar(64) NOT NULL COMMENT '密码',
  `age` int(8) NOT NULL COMMENT '年龄',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

-- ------------------------------
-- 用户表202201
-- ------------------------------
CREATE TABLE `t_user_202201` (
  `id` bigint(16) NOT NULL COMMENT '主键',
  `username` varchar(64) NOT NULL COMMENT '用户名',
  `password` varchar(64) NOT NULL COMMENT '密码',
  `age` int(8) NOT NULL COMMENT '年龄',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表202201';

3.TimeShardingAlgorithm.java 分片算法类

/*
 * 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.demo.module.config.sharding;

import com.google.common.collect.Range;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
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.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * <p> @Title TimeShardingAlgorithm
 * <p> @Description 分片算法,按月分片
 *
 * @author ACGkaka
 * @date 2022/12/20 11:33
 */
@Slf4j
public final class TimeShardingAlgorithm implements StandardShardingAlgorithm<LocalDateTime>, ShardingAutoTableAlgorithm {

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

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

    /**
     * 表分片符号,例:t_contract_202201 中,分片符号为 "_"
     */
    private final String TABLE_SPLIT_SYMBOL = "_";

    @Getter
    private Properties props;

    @Getter
    private int autoTablesAmount;

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

    @Override
    public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<LocalDateTime> preciseShardingValue) {
        String logicTableName = preciseShardingValue.getLogicTableName();

        /// 打印分片信息
        log.info(">>>>>>>>>> 【INFO】精确分片,节点配置表名:{}", availableTargetNames);

        LocalDateTime dateTime = preciseShardingValue.getValue();
        String resultTableName = logicTableName + "_" + dateTime.format(TABLE_SHARD_TIME_FORMATTER);

        // 检查是否需要初始化
        if (availableTargetNames.size() == 1) {
            // 如果只有一个表,说明需要获取所有表名
            List<String> allTableNameBySchema = ShardingAlgorithmTool.getAllTableNameBySchema(logicTableName);
            availableTargetNames.clear();
            availableTargetNames.addAll(allTableNameBySchema);
            autoTablesAmount = allTableNameBySchema.size();
            return resultTableName;
        }

        return getShardingTableAndCreate(logicTableName, resultTableName, availableTargetNames);
    }

    @Override
    public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<LocalDateTime> rangeShardingValue) {
        String logicTableName = rangeShardingValue.getLogicTableName();

        /// 打印分片信息
        log.info(">>>>>>>>>> 【INFO】范围分片,节点配置表名:{}", availableTargetNames);

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

        // 获取最大值和最小值
        LocalDateTime min = hasLowerBound ? valueRange.lowerEndpoint() :getLowerEndpoint(availableTargetNames);
        LocalDateTime max = hasUpperBound ? valueRange.upperEndpoint() :getUpperEndpoint(availableTargetNames);

        // 循环计算分表范围
        Set<String> resultTableNames = new LinkedHashSet<>();
        while (min.isBefore(max) || min.equals(max)) {
            String tableName = logicTableName + TABLE_SPLIT_SYMBOL + min.format(TABLE_SHARD_TIME_FORMATTER);
            resultTableNames.add(tableName);
            min = min.plusMinutes(1);
        }
        return getShardingTablesAndCreate(logicTableName, resultTableNames, availableTargetNames);
    }

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

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


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

    /**
     * 检查分表获取的表名是否存在,不存在则自动建表
     * @param logicTableName   逻辑表
     * @param resultTableName 真实表名,例:t_user_202201
     * @return 确认存在于数据库中的真实表名
     */
    private String getShardingTableAndCreate(String logicTableName, String resultTableName, Collection<String> availableTargetNames) {
        // 缓存中有此表则返回,没有则判断创建
        if (availableTargetNames.contains(resultTableName)) {
            return resultTableName;
        } else {
            // 检查分表获取的表名不存在,需要自动建表
            boolean isSuccess = ShardingAlgorithmTool.createShardingTable(logicTableName, resultTableName);
            if (isSuccess) {
                // 如果建表成功,需要更新缓存
                availableTargetNames.add(resultTableName);
                autoTablesAmount++;
                return resultTableName;
            } else {
                // 如果建表失败,返回逻辑空表
                return logicTableName;
            }
        }
    }

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

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

4.ShardingAlgorithmTool.java 分片工具类

package com.demo.module.config.sharding;

import com.demo.module.utils.SpringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource;
import org.apache.shardingsphere.infra.config.rule.RuleConfiguration;
import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
import org.apache.shardingsphere.mode.manager.ContextManager;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.springframework.core.env.Environment;
import org.springframework.util.ReflectionUtils;

import java.io.File;
import java.lang.reflect.Field;
import java.sql.*;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * <p> @Title ShardingAlgorithmTool
 * <p> @Description 按月分片算法工具
 *
 * @author ACGkaka
 * @date 2022/12/20 14:03
 */
@Slf4j
public class ShardingAlgorithmTool {

    /** 表分片符号,例:t_user_202201 中,分片符号为 "_" */
    private static final String TABLE_SPLIT_SYMBOL = "_";

    /** 数据库配置 */
    private static final Environment ENV = SpringUtil.getApplicationContext().getEnvironment();
    private static final String DATASOURCE_URL = ENV.getProperty("my.sharding.create-table.url");
    private static final String DATASOURCE_USERNAME = ENV.getProperty("my.sharding.create-table.username");
    private static final String DATASOURCE_PASSWORD = ENV.getProperty("my.sharding.create-table.password");

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

    /**
     * 获取所有表名
     * @return 表名集合
     * @param logicTableName 逻辑表
     */
    public static List<String> getAllTableNameBySchema(String logicTableName) {
        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()) {
            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;
    }


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

    /**
     * 获取数据源
     */
//    private static Map<String, DataSource> getDataSourceMap() {
//        return getActualDataSources().entrySet().stream().filter(entry -> ACTUAL_DATA_SOURCE_NAMES.contains(entry.getKey())).collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
//    }

    /**
     * 获取数据源配置
     */
    private static File getShardingYAMLFile() {
        return new File(Objects.requireNonNull(
                ShardingAlgorithmTool.class.getClassLoader().getResource(CONFIG_FILE), String.format("File `%s` is not existed.", CONFIG_FILE)).getFile());
    }

    /**
     * 刷新ActualDataNodes
     */
    private static void updateShardRuleActualDataNodes(ShardingSphereDataSource dataSource, String logicTableName, String newActualDataNodes) throws NoSuchFieldException {
        // Context manager.
        Field contextManagerField = dataSource.getClass().getDeclaredField("contextManager");
        ContextManager contextManager = (ContextManager) ReflectionUtils.getField(contextManagerField, dataSource);

        // Rule configuration.
        ShardingSphereMetaData shardingSphereMetaData = contextManager
                .getMetaDataContexts()
                .getMetaData();
        Collection<RuleConfiguration> newRuleConfigList = new LinkedList<>();
        Collection<RuleConfiguration> oldRuleConfigList = shardingSphereMetaData
                .getGlobalRuleMetaData()
                .getConfigurations();

        for (RuleConfiguration oldRuleConfig : oldRuleConfigList) {
            if (oldRuleConfig instanceof ShardingRuleConfiguration) {

                // Algorithm provided sharding rule configuration
                ShardingRuleConfiguration oldAlgorithmConfig = (ShardingRuleConfiguration) oldRuleConfig;
                ShardingRuleConfiguration newAlgorithmConfig = new ShardingRuleConfiguration();

                // Sharding table rule configuration Collection
                Collection<ShardingTableRuleConfiguration> newTableRuleConfigList = new LinkedList<>();
                Collection<ShardingTableRuleConfiguration> oldTableRuleConfigList = oldAlgorithmConfig.getTables();

                oldTableRuleConfigList.forEach(oldTableRuleConfig -> {
                    if (logicTableName.equals(oldTableRuleConfig.getLogicTable())) {
                        ShardingTableRuleConfiguration newTableRuleConfig = new ShardingTableRuleConfiguration(oldTableRuleConfig.getLogicTable(), newActualDataNodes);
                        newTableRuleConfig.setTableShardingStrategy(oldTableRuleConfig.getTableShardingStrategy());
                        newTableRuleConfig.setDatabaseShardingStrategy(oldTableRuleConfig.getDatabaseShardingStrategy());
                        newTableRuleConfig.setKeyGenerateStrategy(oldTableRuleConfig.getKeyGenerateStrategy());

                        newTableRuleConfigList.add(newTableRuleConfig);
                    } else {
                        newTableRuleConfigList.add(oldTableRuleConfig);
                    }
                });

                newAlgorithmConfig.setTables(newTableRuleConfigList);
                newAlgorithmConfig.setAutoTables(oldAlgorithmConfig.getAutoTables());
                newAlgorithmConfig.setBindingTableGroups(oldAlgorithmConfig.getBindingTableGroups());
                newAlgorithmConfig.setBroadcastTables(oldAlgorithmConfig.getBroadcastTables());
                newAlgorithmConfig.setDefaultDatabaseShardingStrategy(oldAlgorithmConfig.getDefaultDatabaseShardingStrategy());
                newAlgorithmConfig.setDefaultTableShardingStrategy(oldAlgorithmConfig.getDefaultTableShardingStrategy());
                newAlgorithmConfig.setDefaultKeyGenerateStrategy(oldAlgorithmConfig.getDefaultKeyGenerateStrategy());
                newAlgorithmConfig.setDefaultShardingColumn(oldAlgorithmConfig.getDefaultShardingColumn());
                newAlgorithmConfig.setShardingAlgorithms(oldAlgorithmConfig.getShardingAlgorithms());
                newAlgorithmConfig.setKeyGenerators(oldAlgorithmConfig.getKeyGenerators());

                newRuleConfigList.add(newAlgorithmConfig);
            }
        }

        // update context
        String schemaName = "logic_db";
        contextManager.alterRuleConfiguration(schemaName, newRuleConfigList);
    }

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

    /**
     * 执行SQL
     * @param sqlList SQL集合
     */
    private static 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.ShardingTablesLoadRunner.java 初始化缓存类

package com.demo.module.config.sharding;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.demo.module.entity.User;
import com.demo.module.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.time.LocalDateTime;

/**
 * <p> @Title ShardingTablesLoadRunner
 * <p> @Description 项目启动后,读取已有分表,进行缓存
 *
 * @author ACGkaka
 * @date 2022/12/20 15:41
 */
@Slf4j
@Order(value = 1) // 数字越小,越先执行
@Component
public class ShardingTablesLoadRunner implements CommandLineRunner {

    @Resource
    private UserService userService;

    @Override
    public void run(String... args) {
        // 读取已有分表,进行缓存
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(User::getCreateTime, LocalDateTime.now()).last("limit 1");
        userService.list(queryWrapper);

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

6.SpringUtil.java Spring工具类

package com.demo.module.utils;

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工具类
 *
 * @author ACGkaka
 * @date 2022/12/20 14:39
 */
@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);
    }
}

五、测试

1.测试代码

package com.demo;

import com.demo.module.entity.User;
import com.demo.module.service.UserService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@SpringBootTest
class SpringbootDemoApplicationTests {

    private final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @Autowired
    private UserService userService;

    @Test
    void saveTest() {
        List<User> user1s = new ArrayList<>(3);
        LocalDateTime time1 = LocalDateTime.parse("2022-01-01 00:00:00", DATE_TIME_FORMATTER);
        LocalDateTime time2 = LocalDateTime.parse("2022-02-01 00:00:00", DATE_TIME_FORMATTER);
        user1s.add(new User("ACGkaka_1", "123456", 10, time1, time1));
        user1s.add(new User("ACGkaka_2", "123456", 11, time2, time2));
        userService.saveBatch(user1s);
    }

    @Test
    void listTest() {
        PageHelper.startPage(1, 1);
        List<User> user1s = userService.list();
        PageInfo<User> pageInfo = new PageInfo<>(user1s);
        System.out.println(">>>>>>>>>> 【Result】<<<<<<<<<< ");
        System.out.println(pageInfo);
    }
}

2.测试结果

在这里插入图片描述

新增和查询可以正常分页查询,测试成功。

六、代码地址

地址: https://gitee.com/acgkaka/SpringBootExamples/tree/master/springboot-sharding-jdbc-month-5.3.0

整理完毕,完结撒花~ 🌻





参考地址:

1.ShardingSphere 5.3 系列升级解读:Spring 配置升级指南,https://blog.csdn.net/ShardingSphere/article/details/128910559

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/97075.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

postgresql-子查询

postgresql-子查询 简介派生表IN 操作符ALL 操作符ANY 操作符关联子查询横向子查询EXISTS 操作符 简介 子查询&#xff08;Subquery&#xff09;是指嵌套在其他 SELECT、INSERT、UPDATE 以及 DELETE 语句中的 查询语句。 子查询的作用与多表连接查询有点类似&#xff0c;也是为…

学习JAVA打卡第四十五天

StringBuffer类 StringBuffer对象 String对象的字符序列是不可修改的&#xff0c;也就是说&#xff0c;String对象的字符序列的字符不能被修改、删除&#xff0c;即String对象的实体是不可以再发生变化&#xff0c;例如&#xff1a;对于 StringBuffer有三个构造方法&#xff…

红黑树及其应用介绍(万字长文)

红黑树 定义与性质 红黑树是一种特殊的二叉查找树&#xff0c;它遵循了特定的规则使得其具有了平衡性。红黑树的定义包括以下几个方面&#xff1a; 每个节点要么是红色&#xff0c;要么是黑色。根节点是黑色的。每个叶子节点&#xff08;NIL节点&#xff0c;空节点&#xff…

【LeetCode题目详解】第八章 贪心算法 part06 738.单调递增的数字 968.监控二叉树 (day37补)

本文章代码以c为例&#xff01; 一、力扣第738题&#xff1a;单调递增的数字 题目&#xff1a; 当且仅当每个相邻位数上的数字 x 和 y 满足 x < y 时&#xff0c;我们称这个整数是单调递增的。 给定一个整数 n &#xff0c;返回 小于或等于 n 的最大数字&#xff0c;且数…

zookeeper介绍、zookeeper的安装与配置

1、zookeeper介绍 1.1 官网说明 官方地址&#xff1a;http://zookeeper.apache.org/ 它是拿来管理 Hadoop、Hive、Pig的管理员&#xff0c; Apache Hbase和Apache Solr以及阿里的Dubbo等项目中都采用到了Zookeeper。 一句话&#xff1a;ZooKeeper是一个分布式协调技术、高性…

MySQL基础入门

推荐查看 数据库相关概念 MySQL百度百科 MySQL是一个关系型数据库管理系统&#xff0c;由瑞典MySQL AB 公司开发&#xff0c;属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一&#xff0c;在 WEB 应用方面&#xff0c;MySQL是最好的 RDBMS (Relational Databa…

学习pytorch7 神经网络的基本骨架--nn,module的使用

神经网络的基本骨架--nn,module的使用 官网Module介绍Python父类子类继承关系前向神经网络pycharm快捷键重写类方法codedebug B站小土堆视频学习笔记 官网Module介绍 https://pytorch.org/docs/stable/generated/torch.nn.Module.html#torch.nn.Module Python父类子类继承关系…

自动化运维:Ansible脚本之playbook剧本

目录 一、理论 1.playbooks 2.YAML 3.使用ansible批量安装apache服务 4.定义、引用变量 5.指定远程主机sudo切换用户 6.when条件判断 7.迭代 8.Templates 模块 9.tags 模块 10.Roles 模块 二、实验 1.使用ansible批量安装apache服务 2.定义、引用变量…

对称加密 非对称加密 AC认证 https原理

文章目录 对称加密及漏洞非对称加密及漏洞什么是数据摘要&#xff08;也称数据指纹&#xff09;什么是CA认证CA证书签发过程https通信方案 对称加密及漏洞 对称加密是一种加密算法&#xff0c;使用相同的密钥&#xff08;也称为对称密钥&#xff09;用于加密和解密数据。在对称…

c++ lambda

Lambda Lambda 表达式一般用于定义匿名函数&#xff0c;使得代码更加灵活简洁&#xff0c;优点&#xff1a; 声明式编程风格&#xff1a;就地匿名定义目标函数或函数对象&#xff0c;不需要额外写一个命名函数或者函数对象。以更直接的方式去写程序&#xff0c;好的可读性和可…

Spring与Mybatis集成且Aop整合

目录 一、集成 1.1 集成的概述 1.2 集成的优点 1.3 代码示例 二、整合 2.1 整合概述 2.2 整合进行分页 一、集成 1.1 集成的概述 集成是指将不同的组件、部分或系统组合在一起&#xff0c;以形成一个整体功能完整的解决方案。它是通过连接、交互和协调组件之间的关系来实…

python的异步编程async

一、介绍 在Python 3.5中引入了async和await关键字&#xff0c;用于异步编程。async关键字用于定义一个协程&#xff08;coroutine&#xff09;&#xff0c;而await关键字则用于等待一个协程完成。 注&#xff1a;协程&#xff08;coroutine&#xff09;是是一种轻量级的线程…

LeetCode——回溯篇(三)

刷题顺序及思路来源于代码随想录&#xff0c;网站地址&#xff1a;https://programmercarl.com 目录 46. 全排列 47. 全排列 II 332. 重新安排行程 51. N 皇后 37. 解数独 46. 全排列 给定一个不含重复数字的数组 nums &#xff0c;返回其 所有可能的全排列 。你可以 按任…

Linux命令查看CPU、内存、IO使用情况简单介绍

文章目录 1. CPU相关介绍1.1 物理CPU1.2 物理CPU内核1.3 逻辑CPU1.4 几核几线程1.5 CPU设计图 2. top 查看系统负载、CPU使用情况2.1 系统整体的统计信息2.2 进程信息2.3 top命令使用 3. lscpu 显示有关 CPU 架构的信息4. free 查看内存信息5. iostat 查看io信息 1. CPU相关介绍…

雅思写作 三小时浓缩学习顾家北 笔记总结(一)

目录 饥饿网翻译100个句子记录 There are some economically deprived communities in large cities. there is no clear link between grouping student by ability and their levels of attainment. young people without tertiary education qualification normally hav…

python-数据可视化-下载数据-CSV文件格式

数据以两种常见格式存储&#xff1a;CSV和JSON CSV文件格式 comma-separated values import csv filename sitka_weather_07-2018_simple.csv with open(filename) as f:reader csv.reader(f)header_row next(reader)print(header_row) # [USW00025333, SITKA AIRPORT, A…

高等职业学校物联网实训室建设方案

一、概述 1.1专业背景 物联网&#xff08;Internet of Things&#xff09;被称为继计算机、互联网之后世界信息产业第三次浪潮&#xff0c;它并非一个全新的技术领域&#xff0c;而是现代信息技术发展到一定阶段后出现的一种聚合性应用与技术提升&#xff0c;是随着传感网、通…

解决博客不能解析PHP直接下载源码问题

背景&#xff1a; 在网站设置反向代理后&#xff0c;网站突然不能正常访问&#xff0c;而是会直接下载访问文件的PHP源码 解决办法&#xff1a; 由于在搞完反向代理之后&#xff0c;PHP版本变成了纯静态&#xff0c;所以网站不能正常解析&#xff1b;只需要把PHP版本恢复正常…

SQL注入漏洞复现(CVE-2017-8917)

文章目录 搭建环境启动环境漏洞复现报错注入使用sqlmap 前提条件&#xff1a; 1.安装docker docker pull medicean/vulapps:j_joomla_22.安装docker-compose docker run -d -p 8000:80 medicean/vulapps:j_joomla_23.下载vulhub Docker Compose是 docker 提供的一个命令行工具&…

springboot docker

在Spring Boot中使用Docker可以帮助你将应用程序与其依赖的容器化&#xff0c;并简化部署和管理过程。 当你在Spring Boot中使用Docker时&#xff0c;你的代码不需要特殊的更改。你可以按照通常的方式编写Spring Boot应用程序。 java示例代码&#xff0c;展示了如何编写一个基…