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

原创 晨光 程序员老刘 2024年06月02日 21:56 河北

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 */@Slf4jpublic 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;
@Slf4jpublic 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) // 数字越小，越先执行@Componentpublic 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));

结果

现在的缺点：如果查询时不指定时间段，默认是查询当前月份对应的表。继续研究，争取能找出不设置时间段时默认全部表联合查询。有知道的给指点一下。