mybatis的1.2级缓存：shardingjdbc5.1 mybatis-plus 3.5按年月单库分表
mybatis的1.2级缓存：shardingjdbc5.1 mybatis-plus 3.5按年月单库分表保存策略分表配置
分表最小日期
- 指定自定义分表策略实现类、分表字段
分表策略实现类主要包含2个操作：
1）保存策略：数据进行保存操作时会根据分表字段的查出对应的真实数据表名称。
2）查询策略：返回时间范围内的所有真实表名，例:dome_202201 dome_202202 dome_202203。注：必须使用between进行查询才会触发分表的查询策略。
分表配置
保存策略
查询策略
完整配置文件server:
port: 8082
spring:
profiles:
active: dev
main:
allow-bean-definition-overriding: true
application:
name: shardingjdbc-dome
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT 8
shardingsphere:
enabled: true
props:
sql-show: true
mode:
type: Memory
schema:
name: multit-tenant
datasource:
# 配置真实数据源
names: ds0 ds1
ds0: # 配置第 1 个数据源
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/shardingjdbc-dome?useUnicode=true&characterEncoding=UTF-8&useSSL=true&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
username: root
password: 123456
ds1: # 配置第 2 个数据源
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/shardingjdbc-dome?useUnicode=true&characterEncoding=UTF-8&useSSL=true&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
username: root
password: 123456
rules:
sharding:
# 配置 分片算法
sharding-algorithms:
my:
# 分片类型
type: CLASS_BASED
props:
strategy: STANDARD
algorithmClassName: com.example.dome.common.YearMonthShardingAlgorithm #引用分表类
tables:
dome: # 分表，表名
actual-data-nodes: ds.dome_${2022..2099}0${1..9} ds.test_${2022..2099}1${0..2} #数据分表
table-strategy: # 配置分表策略
standard:
sharding-column: create_date
sharding-algorithm-name: my
readwriteSplitting:
data-sources:
ds:
type: Static
loadBalancerName: my
props:
write-data-source-name: ds0
read-data-source-names: ds1
loadBalancers:
my: # 负载均衡算法名称
type: ROUND_ROBIN # 负载均衡算法类型
props:
default: 0
mybatis-plus:
#指定mapper.xml的位置
mapper-locations: classpath*:mapper/**/*.xml
configuration:
#开启驼峰命名法转换字段支持 用于对象关系绑定
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# 开启 SQL 解析缓存注解生效
global-config:
sql-parser-cache: true
swagger:
enable: true
logging:
config: classpath:logback-spring.xml
file.path: logs
#项目配置
pro:
#程序分表指定初始时间
sysDate: '2022-01-01'
分表策略实现类
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.example.dome.config.ProConfig;
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 org.springframework.stereotype.Component;
import java.util.*;
import java.util.stream.Collectors;
/**
* 根据年月分表规则
*/
@Component
@Slf4j
public class YearMonthShardingAlgorithm implements StandardShardingAlgorithm {
//获取系统初始时间，确定安时间分表后的最小表名
public Date getSysDate() {
ProConfig proConfig = SpringContextUtil.getBean(ProConfig.class);
if (StrUtil.isEmpty(proConfig.getSysDate())) {
throw new RuntimeException("未设置系统初始时间");
}
return DateUtil.parse(proConfig.getSysDate() "yyyy-MM-dd");
}
@Override
public void init() {
}
@Override
public String getType() {
return null;
}
//插入策略
@Override
public String doSharding(Collection collection PreciseShardingValue preciseShardingValue) {
//获取字段值
Date time = null;
if (preciseShardingValue.getValue() instanceof String) {
time = DateUtil.parse(preciseShardingValue.getValue().toString());
} else {
time = (Date) preciseShardingValue.getValue();
}
String year = DateUtil.date(time).toString("yyyyMM");
String logicTableName = preciseShardingValue.getLogicTableName();
String tableName = logicTableName "_" year;
return tableName;
}
//处理范围查询
@Override
public Collection<String> doSharding(Collection collection RangeShardingValue rangeShardingValue) {
//返回数据库节点名称list
Collection<String> collect = new ArrayList<>();
//获取查询条件中范围值
Range<String> valueRange = rangeShardingValue.getValueRange();
String lowerDate = valueRange.lowerEndpoint();
//系统时间
Date sysStartTime = getSysDate();
DateTime sst = DateUtil.parseDateTime(lowerDate);
if (sysStartTime.compareTo(sst) > 0) {
lowerDate = DateUtil.format(sysStartTime "yyyy-MM-dd 00:00:00");
}
String upperDate = valueRange.upperEndpoint();
//获取时间段内的所有年月，不会超过当前时间
List<String> suffixList = getSuffixListForRangeByMonth(lowerDate upperDate);
suffixList.forEach(yyyyMM -> {
collect.add(rangeShardingValue.getLogicTableName() "_" yyyyMM);
});
if (collect.size() == 0) {
throw new RuntimeException("没有对应的数据表");
}
return collect;
}
/**
* 两个日期之间所有月份
*/
public List<String> getSuffixListForRangeByMonth(String lowerDate String upperDate) {
DateTime st = DateUtil.parseDateTime(lowerDate);
DateTime et = DateUtil.parseDateTime(upperDate);
DateTime ct = DateUtil.parseDateTime(DateUtil.format(new Date() "yyyy-MM-dd 23:59:59"));
//判断结束时间不能大于当前时间
if (et.compareTo(ct) > 0) {
et = ct;
}
List<DateTime> dateTimes = DateUtil.rangeToList(st et DateField.DAY_OF_MONTH);
List<String> collect = dateTimes.stream().map(x -> x.toString("yyyyMM")).collect(Collectors.toList());
HashSet h = new HashSet(collect);
List<String> suffixList = ListUtil.toList(h);
return suffixList;
}
}结语
希望需要的程序员可以直接使用CTRL V、CTRL C就能实现你的需求那是我写这篇文章最大的成就。
为划水助力！
