[Java知识库]SharDingJDBC-5.1.0按月水平分表+读写分离，自动创表、自动刷新节点表

4升到5过后还是解决了许多问题，4版本的跨库和子查询问题都可以了，性能也提高了
实现自动创建表，动态获取节点表，配置如下
建表语句可以使用下面这句，解决很多麻烦问题
CREATE TABLE newName LIKE oldName

        <!-- ShardingSphere -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.0</version>
        </dependency>
         <!-- 使用druid连接池需要加dbcp依赖 -->
        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-dbcp</artifactId>
            <version>10.0.16</version>
        </dependency>
        <!-- 使用druid需要注意artifactId， -->
      <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.13</version>
        </dependency>
          <!-- 使用druid就不导这个依赖了，都是连接池 -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>

读写分离需要配置数据库有主从同步

spring:
  #shardingjdbc主要配置
  shardingsphere:
    # 是否启用sharding
    enabled: true
    props:
      #　是否显示sql
      sql-show: true
    datasource:
      names: master,slave #数据源名称，多个以逗号隔开
      master:
        type: ${spring.datasource.type}
        driver-class-name: ${spring.datasource.hikari.driver-class-name}
        url: ${spring.datasource.hikari.jdbc-url}
        username: ${spring.datasource.hikari.username}
        password: ${spring.datasource.hikari.password}
      slave:
        type: ${spring.datasource.type}
        driver-class-name: ${spring.datasource.hikari.driver-class-name}
        jdbc-url: jdbc:mysql://127.0.0.1:3308/tecloman_dev?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
        username: ${spring.datasource.hikari.username}
        password: 123456
    rules:
      sharding:
        tables:
          hss_history: # 分表，逻辑表名 数据库未分表的表名
            # 节点数据
            actual-data-nodes: master.hss_history,master.hss_history_202$->{201..212} #数据节点,均匀分布
            table-strategy: # 配置分表策略
              standard: # 用于单分片键的标准分片场景
                sharding-column: create_time # 分片列名称
                sharding-algorithm-name: hss-history-inline # 分片算法名称 不能使用下划线
        # 分片算法配置
        sharding-algorithms:
          hss-history-inline: # 分片算法名称 不能使用下划线
            type: CLASS_BASED #自定义策略
            props:
              strategy: standard
              algorithmClassName: mqtt.server.sharding.DateShardingAlgorithm
              #type: INTERVAL  # 分片算法类型 时间范围分片算法
              #props:
              #datetime-pattern: yyyy-MM-dd HH:mm:ss  #分片键的时间戳格式
              #datetime-lower: 2022-01-01 00:00:00 #时间分片下界值，
              #datetime-upper: 2024-01-01 00:00:00 #时间分片上界值
              #sharding-suffix-pattern: yyyyMM #分片数据源或真实表的后缀格式
              #datetime-interval-amount: 1 #分片键时间间隔，超过该时间间隔将进入下一分片
              #datetime-interval-unit: MONTHS #分片键时间间隔单位
        binding-tables: hss_history
        # 主键生成策略 也可以使用MP的主键生成
        default-key-generate-strategy:
          column: id # 自增列名称
          key-generator-name: id-key  # 分布式序列算法名称
        key-generators:
          id-key:
            type: SNOWFLAKE # 分布式序列算法类型
      #读写分离配置
      readwrite-splitting:
        data-sources:
          master: # 逻辑数据源名字 不要乱写名字，否则读写分离不生效
            type: STATIC #静态类型，（动态Dynamic）
            props:
              # 主库
              write-data-source-name: master
              # 从库
              read-data-source-names: slave
              # 负载均衡算法名称
              load-balancer-name: round
            # 负载均衡算法
        load-balancers:
          round: # 负载均衡算法名称
            type: ROUND_ROBIN  #负载均衡算法类型轮询算法
  jackson:
    #time-zone: UTC
    # UTC会晚8小时 所以要改GMT+8
    time-zone: UTC
    #date-format: yyyy-MM-dd HH:mm:ss
    #default-property-inclusion: non_null
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    #type: com.alibaba.druid.pool.DruidDataSource
    hikari:
      # 个人测试数据库
      jdbc-url: jdbc:mysql://127.0.0.1:3307/tecloman_dev?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: 123456

分片策略实现类 和4版本不一样，5版本更新和查询合并在一起了 实现 StandardShardingAlgorithm

package mqtt.server.sharding;

import hss.server.utils.DateUtils;
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.stereotype.Component;

import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.List;


//sharding分表规则
//按单月分表
@Component
public class DateShardingAlgorithm implements StandardShardingAlgorithm<Long> {
    // 查询使用
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
        for (String s : collection) {
            System.out.println("节点配置表名为: "+s);
        }
        // 查询数据库中的表 hss_history
        List<String> tableNames = ShardingAlgorithmTool.getAllTableNameBySchema();
        for (String s : tableNames) {
            System.out.println("数据库实时表名: "+s);
        }
        HashSet<String> tableNameCache = ShardingAlgorithmTool.cacheTableNames();
        for (String s : tableNameCache) {
            System.out.println("缓存中的表名: "+s);
        }
        // 返回数据库实时存在的表 如果返回collection会提示表不存在
        return tableNameCache;
    }
    // 添加使用
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        StringBuilder resultTableName = new StringBuilder();
        String logicTableName = preciseShardingValue.getLogicTableName();
        //表名精确匹配，表名加上截取的时间
        resultTableName.append(logicTableName)
                //时间戳秒级转毫秒级转成date类型
                .append("_").append(DateUtils.format(new Date(preciseShardingValue.getValue() * 1000), DateUtils.YEAR_MONTH_NUMBER));
        System.out.println("插入表名为:" + resultTableName);
        return ShardingAlgorithmTool.shardingTablesCheckAndCreatAndReturn(logicTableName, resultTableName.toString());
    }

    @Override
    public void init() {

    }

    @Override
    public String getType() {
        //　自定义 这里需要spi支持
        return null;
    }
}

缓存工具类，自动创建表，放入缓存中，

package mqtt.server.sharding;

import hss.server.utils.SpringUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.env.Environment;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Objects;

@Slf4j
public class ShardingAlgorithmTool {
    private static final HashSet<String> tableNameCache = new HashSet<>();
    /**
     * 判断 分表获取的表名是否存在 不存在则自动建表
     *
     * @param logicTableName  逻辑表名(表头)
     * @param resultTableName 真实表名
     * @return 确认存在于数据库中的真实表名
     */
    public static String shardingTablesCheckAndCreatAndReturn(String logicTableName, String resultTableName) {
        synchronized (logicTableName.intern()) {
            // 缓存中有此表 返回
            if (tableNameCache.contains(resultTableName)) {
                return resultTableName;
            }
            // 缓存中无此表 建表 并添加缓存
            List<String> sqlList = selectTableCreateSql(logicTableName);
            for (int i = 0; i < sqlList.size(); i++) {
                sqlList.set(i, sqlList.get(i).replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS").replace(logicTableName, resultTableName));
            }
            executeSql(sqlList);
            tableNameCache.add(resultTableName);
        }
        return resultTableName;
    }

    /**
     * 缓存重载方法
     */
    public static void tableNameCacheReload() {
        // 读取数据库中所有表名
        List<String> tableNameList = getAllTableNameBySchema();
        // 删除旧的缓存(如果存在)
        ShardingAlgorithmTool.tableNameCache.clear();
        // 写入新的缓存
        ShardingAlgorithmTool.tableNameCache.addAll(tableNameList);
    }


    private static void executeSql(List<String> sqlList) {
        Environment env = SpringUtil.getApplicationContext().getEnvironment();
        try (Connection conn = DriverManager.getConnection(Objects.requireNonNull(env.getProperty("spring.datasource.hikari.jdbc-url")), env.getProperty("spring.datasource.hikari.username"), env.getProperty("spring.datasource.hikari.password"))) {
            try (Statement st = conn.createStatement()) {
                conn.setAutoCommit(false);
                for (String sql : sqlList) {
                    st.execute(sql);
                }
                conn.commit();
            } catch (Exception ex) {
                conn.rollback();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
    private static List<String> selectTableCreateSql(String tableName) {
        List<String> res = new ArrayList<>();
        if (tableName.equals("hss_history")) {
            res.add("CREATE TABLE `hss_history` (\n" +
                    "  `id` bigint unsigned NOT NULL,\n" +
                    "  `type_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '设备类型id',\n" +
                    "  `equipment_id` bigint unsigned NOT NULL COMMENT '设备id',\n" +
                    "  `data` json DEFAULT NULL COMMENT '原始数据',\n" +
                    "  `parse_data` json DEFAULT NULL COMMENT '解析数据',\n" +
                    "  `parse_time` bigint NOT NULL DEFAULT '0' COMMENT '解析时间',\n" +
                    "  `create_time` bigint NOT NULL DEFAULT '0',\n" +
                    "  PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='设备历史数据'");
            res.add("ALTER TABLE `hss_history` ADD INDEX n1 ( `create_time`, `equipment_id` ) USING BTREE");
            res.add("ALTER TABLE `hss_history` ADD INDEX n2 ( `equipment_id` ) USING BTREE");
            res.add("ALTER TABLE `hss_history` ADD INDEX n3 ( `parse_time` ) USING BTREE");
        }
        return res;
    }

    public static List<String> getAllTableNameBySchema() {
        List<String> res = new ArrayList<>();
        Environment env = SpringUtil.getApplicationContext().getEnvironment();
        try (Connection connection = DriverManager.getConnection(env.getProperty("spring.datasource.hikari.jdbc-url"), env.getProperty("spring.datasource.hikari.username"), env.getProperty("spring.datasource.hikari.password"));
             Statement st = connection.createStatement()) {
            try (ResultSet rs = st.executeQuery("show TABLES like 'hss_history%'")) {
                while (rs.next()) {
                    res.add(rs.getString(1));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public static HashSet<String> cacheTableNames() {
        return tableNameCache;
    }
}

项目启动就加载缓存 更新和查询数据直接从缓存中读取表名

import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

/**
 * 项目启动后 读取已有分表 进行缓存
 */
@Slf4j
@Order(value = 1) // 数字越小 越先执行
@Component
public class ShardingTablesLoadRunner implements CommandLineRunner {
    @Override
    public void run(String... args) {
        ShardingAlgorithmTool.tableNameCacheReload();
    }
}

时间工具类

import org.apache.commons.lang.StringUtils;
import org.joda.time.DateTime;
import org.joda.time.LocalDate;

import java.text.ParseException;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

/**
 * 日期处理
 */
public class DateUtils {
    /**
     * 时间格式(yyyy-MM-dd)
     */
    public final static String DATE_PATTERN = "yyyy-MM-dd";
    /**
     * shardingJDBC分表使用
     */
    public static final String YEAR_MONTH_NUMBER = "yyyyMM";
    /**
     * 时间格式(yyyy-MM-dd HH:mm:ss)
     */
    public final static String DATE_TIME_PATTERN = "yyyy-MM-dd HH:mm:ss";

    /**
     * 日期格式化 日期格式为：yyyy-MM-dd
     *
     * @param date 日期
     * @return 返回yyyy-MM-dd格式日期
     */
    public static String format(Date date) {
        return format(date, DATE_PATTERN);
    }


    /**
     * 日期格式化 日期格式为：yyyy-MM-dd
     *
     * @param date    日期
     * @param pattern 格式，如：DateUtils.DATE_TIME_PATTERN
     * @return 返回yyyy-MM-dd格式日期
     */
    public static String format(Date date, String pattern) {
        if (date != null) {
            SimpleDateFormat df = new SimpleDateFormat(pattern);
            return df.format(date);
        }
        return null;
    }

    /**
     * 字符串转换成日期
     *
     * @param strDate 日期字符串
     * @param pattern 日期的格式，如：DateUtils.DATE_TIME_PATTERN
     */
    public static Date stringToDate(String strDate, String pattern) {
        if (StringUtils.isBlank(strDate)) {
            return null;
        }
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        return sdf.parse(strDate, new ParsePosition(0));
    }

    /**
     * 时间戳转成日期
     *
     * @param time    日期
     * @param pattern 格式，如：DateUtils.DATE_TIME_PATTERN
     * @return 返回yyyy-MM-dd格式日期
     */
    public static String format(Integer time, String pattern) {
        Date date = new Date((long) time * 1000);
        SimpleDateFormat df = new SimpleDateFormat(pattern);
        return df.format(date);
    }

    /**
     * 根据周数，获取开始日期、结束日期
     *
     * @param week 周期  0本周，-1上周，-2上上周，1下周，2下下周
     * @return 返回date[0]开始日期、date[1]结束日期
     */
    public static Date[] getWeekStartAndEnd(int week) {
        DateTime dateTime = new DateTime();
        LocalDate date = new LocalDate(dateTime.plusWeeks(week));

        date = date.dayOfWeek().withMinimumValue();
        Date beginDate = date.toDate();
        Date endDate = date.plusDays(6).toDate();
        return new Date[]{beginDate, endDate};
    }

    /**
     * 对日期的【秒】进行加/减
     *
     * @param date    日期
     * @param seconds 秒数，负数为减
     * @return 加/减几秒后的日期
     */
    public static Date addDateSeconds(Date date, int seconds) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusSeconds(seconds).toDate();
    }

    /**
     * 对日期的【分钟】进行加/减
     *
     * @param date    日期
     * @param minutes 分钟数，负数为减
     * @return 加/减几分钟后的日期
     */
    public static Date addDateMinutes(Date date, int minutes) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusMinutes(minutes).toDate();
    }

    /**
     * 对日期的【小时】进行加/减
     *
     * @param date  日期
     * @param hours 小时数，负数为减
     * @return 加/减几小时后的日期
     */
    public static Date addDateHours(Date date, int hours) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusHours(hours).toDate();
    }

    /**
     * 对日期的【天】进行加/减
     *
     * @param date 日期
     * @param days 天数，负数为减
     * @return 加/减几天后的日期
     */
    public static Date addDateDays(Date date, int days) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusDays(days).toDate();
    }

    /**
     * 对日期的【周】进行加/减
     *
     * @param date  日期
     * @param weeks 周数，负数为减
     * @return 加/减几周后的日期
     */
    public static Date addDateWeeks(Date date, int weeks) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusWeeks(weeks).toDate();
    }

    /**
     * 对日期的【月】进行加/减
     *
     * @param date   日期
     * @param months 月数，负数为减
     * @return 加/减几月后的日期
     */
    public static Date addDateMonths(Date date, int months) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusMonths(months).toDate();
    }

    /**
     * 对日期的【年】进行加/减
     *
     * @param date  日期
     * @param years 年数，负数为减
     * @return 加/减几年后的日期
     */
    public static Date addDateYears(Date date, int years) {
        DateTime dateTime = new DateTime(date);
        return dateTime.plusYears(years).toDate();
    }

    /**
     * 判断字符串是否为日期
     */
    public static boolean isDate(String date, String pattern) {
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        try {
            sdf.parse(date);
            return true;
        } catch (ParseException e) {
            return false;
        }
    }

    /**
     * 今天开始
     */
    public static Date todayStart() {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(new Date());
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        return calendar.getTime();
    }

    /**
     * 今天结束
     */
    public static Date todayEnd() {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(new Date());
        calendar.set(Calendar.HOUR_OF_DAY, 23);
        calendar.set(Calendar.MINUTE, 59);
        calendar.set(Calendar.SECOND, 59);
        return calendar.getTime();
    }
}

自动刷新节点表，也可以采用别的方式进行，
sharding5.1还是有许多坑，许多依赖冲突。
顺便讲一下，我分片键是Long，但实际上是Date 转为Long的，前端传入后端会自动转换成Long时间戳存入数据库，查询将时间戳转成date返回前端

    // 实体类
    @TableField(typeHandler = DateLongTypeHandler.class)
    private Date createTime;

Date和Long互转 handler

import org.apache.ibatis.type.*;
import org.springframework.stereotype.Component;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

@Component
@MappedTypes({Date.class})
@MappedJdbcTypes({JdbcType.BIGINT})
public class DateLongTypeHandler extends BaseTypeHandler<Date> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
        if (parameter == null) {
            if (jdbcType == null) {
                throw new TypeException(
                        "JDBC requires that the JdbcType must be specified for all nullable parameters.");
            }
            try {
                ps.setNull(i, jdbcType.TYPE_CODE);
            } catch (SQLException e) {
                throw new TypeException(
                        "Error setting null for parameter #"
                                + i
                                + " with JdbcType "
                                + jdbcType
                                + " . "
                                + "Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. "
                                + "Cause: " + e, e);
            }
        } else {
            ps.setLong(i, parameter.getTime() / 1000);
        }
    }

    @Override
    public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
        long res = rs.getLong(columnName);
        if (res == 0) {
            return null;
        }
        long time = res * 1000;
        return new Date(time);
    }

    @Override
    public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        long res = rs.getLong(columnIndex);
        if (res == 0) {
            return null;
        }
        long time = res * 1000;
        return new Date(time);
    }

    @Override
    public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        long res = cs.getLong(columnIndex);
        if (res == 0) {
            return null;
        }
        long time = res * 1000;
        return new Date(time);
    }
}