springboot,jpa, druid, sharding5.1.2 按日分表配置

飞鹰_天涯

已于 2022-10-26 23:26:31 修改

阅读量1.1k

点赞数

文章标签： spring boot spring

于 2022-10-26 22:18:44 首次发布

本文链接：https://blog.csdn.net/kairen6645/article/details/127542034

版权

springbootApplication要排除druid的datasource，否则会与shardingsphere的配置冲突，加上如下注解：

@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class})
@EnableTransactionManagement
@EnableJpaAuditing(auditorAwareRef = "auditorAware")

application-dev.yml配置文件如下：

#配置数据源
spring:
  shardingsphere:
    datasource:
      names: ds
      ds:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sx_radar?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false
        password: root
        username: root
        # 初始连接数
        initial-size: 5
        # 最小连接数
        min-idle: 15
        # 最大连接数
        max-active: 30
        # 超时时间(以秒数为单位)
        remove-abandoned-timeout: 180
        # 获取连接超时时间
        max-wait: 3000
        # 连接有效性检测时间
        time-between-eviction-runs-millis: 60000
        # 连接在池中最小生存的时间
        min-evictable-idle-time-millis: 300000
        # 连接在池中最大生存的时间
        max-evictable-idle-time-millis: 900000
        # 指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除
        test-while-idle: true
        # 指明是否在从池中取出连接前进行检验,如果检验失败, 则从池中去除连接并尝试取出另一个
        test-on-borrow: true
        # 是否在归还到池中前进行检验
        test-on-return: false
        # 检测连接是否有效
        validation-query: select 1
        # 配置监控统计
        webStatFilter:
          enabled: true
        stat-view-servlet:
          enabled: true
          url-pattern: /druid/*
          reset-enable: false
        filter:
          stat:
            enabled: true
            # 记录慢SQL
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
          wall:
            config:
              multi-statement-allow: true
    rules:
      sharding:
        tables:
          #逻辑表  下面是节点表,分表后还有数据在原来的表，所有查询节点需要加上原来的表
          sx_radar_fog:
            actual-data-nodes: ds.sx_radar_$->{20221001..20301220}_fog
            table-strategy:
              standard:
                sharding-column: data_time
                shardingAlgorithmName: createtime-day
          sx_radar_visibility:
            actual-data-nodes: ds.sx_radar_$->{20221001..20301220}_visibility
            table-strategy:
              standard:
                sharding-column: data_time
                shardingAlgorithmName: createtime-day
        shardingAlgorithms:
          createtime-day:  #分片算法名称
            type: CLASS_BASED  # 分片算法配置:自定义类
            props:
              strategy: standard
              algorithmClassName: com.test.config.CreateTimeDayTableShardingAlgorithm  #自定义分片类
    props:
      sql-show: true

按天分表算法转自sharding-demo-5.1.2: sharding-demo-5.1.2 (gitee.com)

package com.test.config;

import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;

import java.sql.Timestamp;
import java.util.Collection;
import cn.hutool.core.date.DateUtil;
import com.google.common.collect.Range;
import org.apache.commons.collections4.CollectionUtils;
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.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * 按天分表实现
 * @author DS
 */
public class CreateTimeDayTableShardingAlgorithm
        implements StandardShardingAlgorithm<Timestamp>, CreateTimeShardingAlgorithm {

    private static final String FORMAT_LINK_DAY = "yyyyMMdd";

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames,
                                         RangeShardingValue<Timestamp> shardingValue) {
        Range<Timestamp> valueRange;
        valueRange = shardingValue.getValueRange();
        LocalDateTime start = null;
        try {
            start = valueRange.lowerEndpoint().toLocalDateTime();
        }
        catch (Exception e) {
            start = LocalDateTime.now().minusDays(15L);
        }
        LocalDateTime end = null;
        try {
            end = valueRange.upperEndpoint().toLocalDateTime();
        }
        catch (Exception e) {
            end = LocalDateTime.now();
        }
        Set<String> suffixList = new HashSet<>();
        Iterator<String> iterator = availableTargetNames.iterator();
        String tableName = iterator.next();
        String name = tableName.substring(0, tableName.lastIndexOf("_"));
        if (start != null && end != null) {
            String startName = DateUtil.format(start, FORMAT_LINK_DAY);
            String endName = DateUtil.format(end, FORMAT_LINK_DAY);
            while (!startName.equals(endName)) {
                if (availableTargetNames.contains(name + "_" + startName)) {
                    suffixList.add(name + "_" + startName);
                }
                start = start.plusDays(1L);
                startName = DateUtil.format(start, FORMAT_LINK_DAY);

            }
            if (availableTargetNames.contains(name + "_" + endName)) {
                suffixList.add(name + "_" + endName);
            }
        }
        if (CollectionUtils.isNotEmpty(suffixList)) {
            return suffixList;
        }

        return availableTargetNames;
    }

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

    @Override
    public void init(Properties properties) {
        // 空
    }

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Timestamp> shardingValue) {
        LocalDateTime time = shardingValue.getValue().toLocalDateTime();
        DateTimeFormatter dtf2 = DateTimeFormatter.ofPattern(FORMAT_LINK_DAY);
        String format = dtf2.format(time);
        for (String str : availableTargetNames) {
            if (str.contains(format)) {
                return str;
            }
        }
        return null;
    }

    @Override
    public String buildNodesSuffix(LocalDate date) {
        DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern(FORMAT_LINK_DAY);
        return date.format(dateFormatter);
    }

    @Override
    public LocalDate buildNodesBeforeDate(LocalDate date) {
        return date.minusDays(1);
    }

    @Override
    public LocalDate buildNodesAfterDate(LocalDate date) {
        return date.plusDays(1);
    }

}

package com.test.config;

import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

/**
 * @author GDS
 *
 * 跟据创建时间分表的接口
 */

public interface CreateTimeShardingAlgorithm {

    /**
     * 将日期转换为分表的后缀格式
     * @param date
     * @return
     */
    String buildNodesSuffix(LocalDate date);

    /**
     * 分表的阶梯方式，物理表每次递增规则,返回date的后一天或一个月或一年
     * @param date
     * @return
     */
    LocalDate buildNodesAfterDate(LocalDate date);

    /**
     * 分表的阶梯方式，物理表每次递减规则,返回date的前一天或一个月或一年
     * @param date
     * @return
     */
    LocalDate buildNodesBeforeDate(LocalDate date);

    /**
     * 构建可用表的nodes
     * @param tableName 表名
     * @param count 需要表的数量
     * @return 物理表的集合，用，号拼接
     */
    default String buildNodes(String tableName, Integer count) {
        List<String> tableNameList = new ArrayList<>();

        LocalDate today = LocalDate.now();

        for (int i = 0; i < count; i++) {
            tableNameList.add("db0." + tableName + "_${'" + buildNodesSuffix(today) + "'}");
            today = buildNodesBeforeDate(today);
        }
        return StringUtils.join(tableNameList, ",");
    }

    default void createTables(ShardingSphereDataSource dataSource, String tableName, Integer count) {

        try (Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement()) {

            LocalDate today = LocalDate.now();
            String oldTableName = "";
            String newTableName = "";
            for (int i = 0; i < count; i++) {
                oldTableName = tableName + "_" + buildNodesSuffix(today);
                today = buildNodesAfterDate(today);
                newTableName = tableName + "_" + buildNodesSuffix(today);
                statement.execute("create table IF NOT EXISTS `" + newTableName + "` like  `" + oldTableName + "`");
            }
        }
        catch (SQLException throwables) {
            throw new RuntimeException("建表失败");
        }

    }

}