ShardingSphere5.2.0按月分表，自动建表

StruggleRookie

于 2024-06-02 21:48:50 发布

阅读量238

点赞数 2

文章标签： shardingSphere 单数据源分表

本文链接：https://blog.csdn.net/StruggleRookie/article/details/139397813

版权

1、依赖

<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
   <version>5.2.0</version>
</dependency>

2、配置文件

# 端口配置
server:
  port: 9020
  servlet:
    #    # 项目的前缀名
    context-path: /
# 开发环境配置
spring:
  servlet:
    multipart:
      max-file-size: 500MB
      max-request-size: 500MB
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    username: root
    password: root
    #    password: root
    #swell*2021
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/swell?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2b8
    #url: jdbc:mysql://localhost:3306/swell?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2b8
    druid:
      # 初始连接数
      initialSize: 5
      # 最小连接池数量
      minIdle: 5
      # 最大连接池数量
      maxActive: 20
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置检测连接是否有效
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
  shardingsphere:
    datasource:
      names: db0
      db0:
        data-source-name: com.alibaba.druid.pool.DruidDataSource
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/swell?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: root
        password: root
    rules:
      sharding:
        # 分片算法配置
        shardingAlgorithms:
          # 分片算法名称，不支持大写字母和下划线，否则启动就会报错
          time-sharding-algorithm:
            props:
              # 分片策略
              strategy: standard
              # 自定义分配算法
              algorithmClassName: com.rookie.shardingsphere.CustomTimeShardingAlgorithm
            # 类型：自定义策略
            type: CLASS_BASED
        tables:
          mq_message:#要分表的表名
            logicTable: mq_message
            #自定义节点表
            actualDataNodes: db0.$->{com.rookie.shardingsphere.ShardingAlgorithmTool.cacheTableNames()}
            tableStrategy:
              # 使用标准分片策略
              standard:
                # 配置分片字段
                shardingColumn: create_time
                # 分片算法名称，不支持大写字母和下划线，否则启动就会报错
                shardingAlgorithmName: time-sharding-algorithm

    props:
      sql-show: true
  redis:
    host: 127.0.0.1 # Redis服务器地址
    database: 0 # Redis数据库索引（默认为0）
    port: 6379 # Redis服务器连接端口
    password:  # Redis服务器连接密码（默认为空）
    jedis:
      pool:
        max-active: 8 # 连接池最大连接数（使用负值表示没有限制）
        max-wait: -1ms # 连接池最大阻塞等待时间（使用负值表示没有限制）
        max-idle: 8 # 连接池中的最大空闲连接
        min-idle: 0 # 连接池中的最小空闲连接
    timeout: 3000ms # 连接超时时间（毫秒

mybatis-plus:
  mapper-locations: classpath*:/com/rookie/dao/mapper/*.xml
  configuration:
    #    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    cache-enabled: true
#业务数据配置
biz:
  company:
    #默认统计供热开始时间，换热站累积能耗统计用
    default-hot-startDate: 10-31
    #默认统计供热结束时间，换热站累积能耗统计用
    default-hot-endDate: 05-01
  site:
    #热站未收到MQ消息时长限制，单位：分钟，超过则认为离线，同时产生告警
    mqdata-alert-minutes: 5

3、自定义分片算法CustomTimeShardingAlgorithm，根据自己实际情况可以修改。

import com.google.common.collect.Range;
import lombok.extern.slf4j.Slf4j;
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.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * @author: 晨光1
 * @description: 自定义时间分片算法
 * @date 2024/5/31 15:33
 * @Version 1.0
 */
@Slf4j
public class CustomTimeShardingAlgorithm implements StandardShardingAlgorithm<Date> {

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


    /**
     * 精准分片
     * @param collection 对应分片库中所有分片表的集合
     * @param preciseShardingValue
     * @return 表名
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        log.info("进入doSharding返回字符串");
        Object value = preciseShardingValue.getValue();
        String tableSuffix = null;
        if(value instanceof Date){
            LocalDate localDate = ((Date) value).toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            tableSuffix = localDate.format(DateTimeFormatter.ofPattern("yyyyMM"));
        }else{
            String column = (String)value;
            tableSuffix = LocalDateTime.parse(column, formatter).format(DateTimeFormatter.ofPattern("yyyyMM"));
        }
        String logicTableName = preciseShardingValue.getLogicTableName();
        String actualTableName = logicTableName.concat("_").concat(tableSuffix);
        if(!collection.contains(actualTableName)){
            collection.add(actualTableName);
        }
        return actualTableName;
    }

    /**
     * 范围分片
     * @param collection 对应分片库中所有分片表的集合
     * @param rangeShardingValue 分片范围
     * @return 表名集合
     */
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Date> rangeShardingValue) {
        log.info("进入doSharding返回列表");
        // 逻辑表名
        String logicTableName = rangeShardingValue.getLogicTableName();
        // 范围参数
        Range<Date> valueRange = rangeShardingValue.getValueRange();
        //起始时间  结束时间
        LocalDateTime start = null;
        LocalDateTime end = null;
        Object lowerEndpoint = (Object)valueRange.lowerEndpoint();
        Object upperEndpoint = (Object)valueRange.upperEndpoint();
        log.info("lowerEndpoint ======"+lowerEndpoint.toString());
        log.info("upperEndpoint ======"+upperEndpoint.toString());
        if(lowerEndpoint instanceof  String){
            String lower = (String) lowerEndpoint;
            String upper = (String) upperEndpoint;
            start = LocalDateTime.parse(lower,formatter);
            end = LocalDateTime.parse(upper,formatter);
        }else{
            start = valueRange.lowerEndpoint().toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
            end = valueRange.upperEndpoint().toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
            log.info("start ======",start.format(DateTimeFormatter.ofPattern("yyyyMM")));
            log.info("end ======"+end.format(DateTimeFormatter.ofPattern("yyyyMM")));
        }
        if(end.isAfter(LocalDateTime.now())){
            end = LocalDateTime.now();
        }
        // 查询范围的表
        Set<String> queryRangeTables = extracted(logicTableName, start, end);
        return queryRangeTables;
    }

    @Override
    public String getType() {
        return null;
    }

    @Override
    public Properties getProps() {
        return null;
    }


    /**
     * 根据范围计算表名
     * @param logicTableName 表名前缀
     * @param lowerEndpoint 起点
     * @param upperEndpoint 终点
     * @return 最终的表名集合
     */
    private Set<String> extracted(String logicTableName, LocalDateTime lowerEndpoint, LocalDateTime upperEndpoint) {
        Set<String> rangeTable = new HashSet<>();
        while (lowerEndpoint.isBefore(upperEndpoint)) {
            String str = getTableNameByDate(lowerEndpoint, logicTableName);
            rangeTable.add(str);
            lowerEndpoint = lowerEndpoint.plusMonths(1);
        }
        // 获取物理表名
        String tableName = getTableNameByDate(upperEndpoint, logicTableName);
        rangeTable.add(tableName);
        return rangeTable;
    }
    /**
     * 根据日期获取表名
     * @param dateTime 日期
     * @param logicTableName 逻辑表名
     * @return 物理表名
     */
    private String getTableNameByDate(LocalDateTime dateTime, String logicTableName) {
        String tableSuffix = dateTime.format(DateTimeFormatter.ofPattern("yyyyMM"));
        return logicTableName.concat("_").concat(tableSuffix);
    }

    @Override
    public void init(Properties properties) {

    }
}

4、分片算法工具类

import com.alibaba.fastjson.JSONObject;
import com.rookie.common.util.spring.SpringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource;

import javax.annotation.PostConstruct;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.CopyOnWriteArraySet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.springframework.core.env.Environment;

@Slf4j
public class ShardingAlgorithmTool {

    private static final Set<String> tableNameCache = new CopyOnWriteArraySet<>();

    // 启动时，实际表中要有值，启动后，在ShardingTablesLoadRunner中先清空在缓存
    static  {
        //默认是当前月份的，最少有一个否则报错。
        String tableSuffix = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMM"));
        String actualTableName = "mq_message".concat("_").concat(tableSuffix);
        tableNameCache.add(actualTableName);
    }

    @PostConstruct
    public void init() {

    }

    /**
     * 缓存重载方法
     */
    public static void tableNameCacheReload() {
        //读取数据库中所有表名
        List<String> tableList = getAllTableName();
        //清除缓存
        ShardingAlgorithmTool.tableNameCache.clear();
        //更新缓存
        ShardingAlgorithmTool.tableNameCache.addAll(tableList);
    }

    /**
     * 获取数据库中的表名
     */
    public static List<String> getAllTableName() {
        List<String> res = new ArrayList<>();
        // 获取数据中的表名
        Environment env = SpringUtil.getApplicationContext().getEnvironment();
        try (Connection connection = DriverManager.getConnection(env.getProperty("spring.datasource.url"), env.getProperty("spring.datasource.username"), env.getProperty("spring.datasource.password"));
             Statement st = connection.createStatement()) {
            try (ResultSet rs = st.executeQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA='swell' AND TABLE_NAME like 'mq_message_2%'")) {
                while (rs.next()) {
                    res.add(rs.getString(1));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    /**
     * 获取缓存中的表名
     * @return
     */
    public static Set<String> cacheTableNames() {
        return ShardingAlgorithmTool.tableNameCache;
    }
}

5、启动时设置自动建表任务

import com.rookie.dao.MqMessageMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.config.YamlPropertiesFactoryBean;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.core.env.Environment;
import org.springframework.core.io.ClassPathResource;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.text.SimpleDateFormat;
import java.util.*;

@Order(value = 1) // 数字越小，越先执行
@Component
public class ShardingTablesLoadRunner implements CommandLineRunner {

    @Autowired
    private MqMessageMapper mqMessageMapper;

    @Override
    public void run(String... args) {
        ShardingAlgorithmTool.tableNameCacheReload();
    }

    @Scheduled(cron = "0 0 23 * * ?")
    public void Scheduled(){
        final Calendar c = Calendar.getInstance();
        //c.get(Calendar.DATE) 当前时间
        //c.getActualMaximum(Calendar.DATE) 本月最后一日
        if (c.get(Calendar.DATE) == c.getActualMaximum(Calendar.DATE)) {
            c.add(Calendar.DATE, 1);
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMM");
            String monthStr = dateFormat.format(c.getTime());
            mqMessageMapper.createTable("mq_message_" + monthStr);
        }
    }
}

6、测试示例

下面的代码是查询条件

QueryWrapper<MqMessage> queryWrapper = new QueryWrapper<>();
queryWrapper.le("create_time",DatetimeUtils.stringToDate("2024-07-04 12:12:12","yyyy-MM-dd HH:mm:ss"));
queryWrapper.ge("create_time",DatetimeUtils.stringToDate("2024-04-04 12:12:12","yyyy-MM-dd HH:mm:ss"));
List<MqMessage> list = mqMessageService.list(queryWrapper);
return ApiResult.success(JSONObject.toJSONString(list));

结果

7、现在的缺点：如果查询时不指定时间段，默认是查询当前月份对应的表。

继续研究，争取能找出不设置时间段时默认全部表联合查询。有知道的给指点一下。