4升到5过后还是解决了许多问题，4版本的跨库和子查询问题都可以了，性能也提高了
实现自动创建表，动态获取节点表，配置如下
建表语句可以使用下面这句，解决很多麻烦问题
CREATE TABLE newName LIKE oldName
org.apache.shardingsphere
shardingsphere-jdbc-core-spring-boot-starter
5.1.0
org.apache.tomcat
tomcat-dbcp
10.0.16
com.alibaba
druid-spring-boot-starter
1.1.13
com.zaxxer
HikariCP
【面试|SharDingJDBC-5.1.0按月水平分表+读写分离，自动创表、自动刷新节点表】读写分离需要配置数据库有主从同步
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 {
// 查询使用
@Override
public Collection doSharding(Collection collection, RangeShardingValue rangeShardingValue) {
for (String s : collection) {
System.out.println("节点配置表名为: "+s);
}
// 查询数据库中的表 hss_history
List tableNames = ShardingAlgorithmTool.getAllTableNameBySchema();
for (String s : tableNames) {
System.out.println("数据库实时表名: "+s);
}
HashSet tableNameCache = ShardingAlgorithmTool.cacheTableNames();
for (String s : tableNameCache) {
System.out.println("缓存中的表名: "+s);
}
// 返回数据库实时存在的表 如果返回collection会提示表不存在
return tableNameCache;
}
// 添加使用
@Override
public String doSharding(Collection collection, PreciseShardingValue 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 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 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 tableNameList = getAllTableNameBySchema();
// 删除旧的缓存(如果存在)
ShardingAlgorithmTool.tableNameCache.clear();
// 写入新的缓存
ShardingAlgorithmTool.tableNameCache.addAll(tableNameList);
}private static void executeSql(List 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 selectTableCreateSql(String tableName) {
List res = new ArrayList<>();
if (tableName.equals("hss_history")) {
res.add("CREATE TABLE `hss_history` (
" +
"`id` bigint unsigned NOT NULL,
" +
"`type_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '设备类型id',
" +
"`equipment_id` bigint unsigned NOT NULL COMMENT '设备id',
" +
"`data` json DEFAULT NULL COMMENT '原始数据',
" +
"`parse_data` json DEFAULT NULL COMMENT '解析数据',
" +
"`parse_time` bigint NOT NULL DEFAULT '0' COMMENT '解析时间',
" +
"`create_time` bigint NOT NULL DEFAULT '0',
" +
"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 getAllTableNameBySchema() {
List 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 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 = https://www.it610.com/article/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 {
@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);
}
}
推荐阅读
- spring|Spring Boot 面试杀手锏（自动配置原理）
- spring|Spring Boot常见面试题及答案
- vue|手写一个webpack，看看AST怎么用
- 课设|数据库课设之学生成绩管理系统
- @RequestParam Map paramMap
- 计算机毕业设计|Java项目毕业设计（基于springboot+vue的旅游信息网站）
- 面试文|分布式id生成策略,我和面试官扯了一个半小时
- 面试|SSM框架讲解（史上最详细的文章）
- 面试|SpringBoot启动流程