pom:
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.1</version>
</dependency>
yml:
spring:
# 分表配置
shardingsphere:
datasource:
names: ds0,ds1
ds0:
url: jdbc:dm://localhost:5236?schema=xxx0&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
username: SYSDBA
password: SYSDBA
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: dm.jdbc.driver.DmDriver
ds1:
url: jdbc:dm://localhost:5236?schema=xxx1&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
username: SYSDBA
password: SYSDBA
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: dm.jdbc.driver.DmDriver
props:
sql-show: true
rules:
sharding:
sharding-algorithms:
table-inline:
type: TableShardingAlgorithm5 #自定义分表算法
props:
strategy: STANDARD
algorithm-class-name: com.cru.shardingalgorithm.TableShardingAlgorithm5
tables:
POWER_STATION_DATA:
actualDataNodes: ds0.xxx_$->{2022..2023}
table-strategy:
standard:
sharding-column: data_time
sharding-algorithm-name: table-inline
java:
package com.cru.shardingalgorithm;
import cn.hutool.core.collection.CollectionUtil;
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.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Properties;
@Slf4j
public class TableShardingAlgorithm5 implements StandardShardingAlgorithm<String>{
DateTimeFormatter pattern = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
private Properties props = new Properties();
@Override
public String getType() {
return "TableShardingAlgorithm5";
}
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
log.info("[分表算法]");
// 真实节点
availableTargetNames.stream().forEach((item) -> {
log.info("[存在的表<{}>]", item);
});
log.info("[表名<{}> 列名<{}>]", shardingValue.getLogicTableName(), shardingValue.getColumnName());
//精确分片
String time = shardingValue.getValue();
log.info("[分表列的值<{}>]", time);
// 根据当前日期来分库分表
String tbName = shardingValue.getLogicTableName() + "_";
String year = time.split(" ")[0];
String date = "";
try {
LocalDate d = LocalDate.parse(time);
date = d.getYear()+"";
} catch (Exception e) {
LocalDateTime d = LocalDateTime.parse(time, pattern);
date = d.getYear()+"";
}
log.info("[year<{}>]", year);
tbName = tbName + date;
log.info("[分片确定的表名<{}>]", tbName);
for (String each : availableTargetNames) {
if (each.equals(tbName)) {
return each;
}
}
throw new IllegalArgumentException();
}
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<String> shardingValue) {
log.info("[分表算法]");
// 真实节点
availableTargetNames.stream().forEach((item) -> {
log.info("[存在的表<{}>]", item);
});
log.info("[表名<{}> 列名<{}>]", shardingValue.getLogicTableName(), shardingValue.getColumnName());
//精确分片
Range<String> valueRange = shardingValue.getValueRange();
String startTime = valueRange.lowerEndpoint();
String endTime = valueRange.upperEndpoint();
String startYear = "";
String endYear = "";
String tbName = shardingValue.getLogicTableName() + "_";
try {
LocalDate startDate = LocalDate.parse(startTime);
LocalDate endDate = LocalDate.parse(endTime);
startYear = startDate.getYear()+"";
endYear = endDate.getYear()+"";
} catch (Exception e) {
LocalDateTime startDate = LocalDateTime.parse(startTime, pattern);
LocalDateTime endDate = LocalDateTime.parse(endTime, pattern);
startYear = startDate.getYear()+"";
endYear = endDate.getYear()+"";
}
List<String> results = new ArrayList<>();
for (String availableTargetName : availableTargetNames) {
if(availableTargetName.equals(tbName+startYear)){
results.add(tbName+startYear);
}
if(availableTargetName.equals(tbName+endYear)){
results.add(tbName+endYear);
}
}
log.info("[分片确定的表名<{}>]", CollectionUtil.join(results,","));
return results;
}
@Override
public Properties getProps() {
return props;
}
@Override
public void init(Properties properties) {
}
}
需要注意的是,shardingSphere全版本不支持substring语法,请使用substr替换