在stock_common工程下定义公共分库算法类:
package com.itheima.stock.sharding;
import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.joda.time.DateTime;
import java.util.Collection;
import java.util.Date;
import java.util.Optional;
import java.util.stream.Collectors;
/**
* @author by itheima
* @Date 2022/6/13
* @Description 定义公共的数据库分片算法类:包含精准匹配数据库和范围匹配数据库
* 因为分库是根据日期分库的,一年一个库,所以片键的类型是Date
*/
public class CommonShardingAlgorithm4Db implements PreciseShardingAlgorithm<Date>, RangeShardingAlgorithm<Date> {
/**
* 精准匹配数据库的方法 cur_time 条件必须是 = 或者in
* @param dsNames 所有可匹配数据源的集合 ds-2021 ds-2022
* @param shardingValue
* @return
*/
@Override
public String doSharding(Collection<String> dsNames, PreciseShardingValue<Date> shardingValue) {
//1.思路:根据传入的日期值,获取年份字符串
//获取分片字段的名称colume
// String columnName = shardingValue.getColumnName();
//获取逻辑表名称
// String logicTableName = shardingValue.getLogicTableName();
//获取分片值
Date value = shardingValue.getValue();
//获取年份字符串
String year = new DateTime(value).getYear()+"";
//2.获取数据源中以
Optional<String> optional = dsNames.stream().filter(ds -> ds.endsWith(year)).findFirst();
String actual=null;
//判断是否有符合指定年份的数据源
if (optional.isPresent()) {
actual=optional.get();
}
return actual;
}
/**
* 范围查询匹配数据源 关键字:between and
* @param dsNames ds-2021 ds-2022
* @param shardingValue
* @return
*/
@Override
public Collection<String> doSharding(Collection<String> dsNames, RangeShardingValue<Date> shardingValue) {
//获取分片字段名称
// String columnName = shardingValue.getColumnName();
// //获取逻辑表名称
// String logicTableName = shardingValue.getLogicTableName();
//1.获取范围封装对象
Range<Date> valueRange = shardingValue.getValueRange();
//2.1 判断是否有下限值
if (valueRange.hasLowerBound()) {
//获取下限日期
Date lowerDate = valueRange.lowerEndpoint();
//获取年份 dsNames--> ds_2021 ds_2022 ds_2023
int year = new DateTime(lowerDate).getYear();//2022
dsNames= dsNames.stream().filter(dsName->Integer.valueOf(dsName.substring(dsName.lastIndexOf("-")+1))>=year)
.collect(Collectors.toList());
}
//2.2 判断是否有上限值
if (valueRange.hasUpperBound()) {
Date upperDate = valueRange.upperEndpoint();
int year = new DateTime(upperDate).getYear();
dsNames= dsNames.stream().filter(dsName->Integer.valueOf(dsName.substring(dsName.lastIndexOf("-")+1))<=year)
.collect(Collectors.toList());
}
return dsNames;
}
}package com.itheima.stock.sharding;
import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.joda.time.DateTime;
import java.util.Collection;
import java.util.Date;
import java.util.Optional;
import java.util.stream.Collectors;
/**
* @author by itheima
* @Date 2022/6/13
* @Description 定义公共的数据库分片算法类:包含精准匹配数据库和范围匹配数据库
* 因为分库是根据日期分库的,一年一个库,所以片键的类型是Date
*/
public class CommonShardingAlgorithm4Db implements PreciseShardingAlgorithm<Date>, RangeShardingAlgorithm<Date> {
/**
* 精准匹配数据库的方法 cur_time 条件必须是 = 或者in
* @param dsNames 所有可匹配数据源的集合 ds-2021 ds-2022
* @param shardingValue
* @return
*/
@Override
public String doSharding(Collection<String> dsNames, PreciseShardingValue<Date> shardingValue) {
//1.思路:根据传入的日期值,获取年份字符串
//获取分片字段的名称colume
// String columnName = shardingValue.getColumnName();
//获取逻辑表名称
// String logicTableName = shardingValue.getLogicTableName();
//获取分片值
Date value = shardingValue.getValue();
//获取年份字符串
String year = new DateTime(value).getYear()+"";
//2.获取数据源中以
Optional<String> optional = dsNames.stream().filter(ds -> ds.endsWith(year)).findFirst();
String actual=null;
//判断是否有符合指定年份的数据源
if (optional.isPresent()) {
actual=optional.get();
}
return actual;
}
/**
* 范围查询匹配数据源 关键字:between and
* @param dsNames ds-2021 ds-2022
* @param shardingValue
* @return
*/
@Override
public Collection<String> doSharding(Collection<String> dsNames, RangeShardingValue<Date> shardingValue) {
//获取分片字段名称
// String columnName = shardingValue.getColumnName();
// //获取逻辑表名称
// String logicTableName = shardingValue.getLogicTableName();
//1.获取范围封装对象
Range<Date> valueRange = shardingValue.getValueRange();
//2.1 判断是否有下限值
if (valueRange.hasLowerBound()) {
//获取下限日期
Date lowerDate = valueRange.lowerEndpoint();
//获取年份 dsNames--> ds_2021 ds_2022 ds_2023
int year = new DateTime(lowerDate).getYear();//2022
dsNames= dsNames.stream().filter(dsName->Integer.valueOf(dsName.substring(dsName.lastIndexOf("-")+1))>=year)
.collect(Collectors.toList());
}
//2.2 判断是否有上限值
if (valueRange.hasUpperBound()) {
Date upperDate = valueRange.upperEndpoint();
int year = new DateTime(upperDate).getYear();
dsNames= dsNames.stream().filter(dsName->Integer.valueOf(dsName.substring(dsName.lastIndexOf("-")+1))<=year)
.collect(Collectors.toList());
}
return dsNames;
}
}
5.2 配置properties
在stock_backend工程下配置application-sharding.properties:
# 第二步:配置板块表的数据节点信息
spring.shardingsphere.sharding.tables.stock_block_rt_info.actual-data-nodes=ds-${2021..2022}.stock_block_rt_info
spring.shardingsphere.sharding.tables.stock_market_index_info.actual-data-nodes=ds-${2021..2022}.stock_market_index_info
spring.shardingsphere.sharding.tables.stock_outer_market_index_info.actual-data-nodes=ds-${2021..2022}.stock_outer_market_index_info
# 提取公共数据库分片算法配置类
common.algorithm4db=com.itheima.stock.sharding.CommonShardingAlgorithm4Db
common.algorithm4StockRtInfoTable=com.itheima.stock.sharding.ShardingAlgorithm4StockRtInfoTable
# 第三步:配置数据库的分片算法
# 分片列名称
spring.shardingsphere.sharding.tables.stock_block_rt_info.database-strategy.standard.sharding-column=cur_time
# 精确分片算法类名称,用于 = 和 IN。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_block_rt_info.database-strategy.standard.precise-algorithm-class-name=${common.algorithm4db}
# 范围分片算法类名称,用于 BETWEEN,可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_block_rt_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm4db}
spring.shardingsphere.sharding.tables.stock_market_index_info.database-strategy.standard.sharding-column=cur_time
# 精确分片算法类名称,用于 = 和 IN。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_market_index_info.database-strategy.standard.precise-algorithm-class-name=${common.algorithm4db}
# 范围分片算法类名称,用于 BETWEEN,可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_market_index_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm4db}
spring.shardingsphere.sharding.tables.stock_outer_market_index_info.database-strategy.standard.sharding-column=cur_time
# 精确分片算法类名称,用于 = 和 IN。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_outer_market_index_info.database-strategy.standard.precise-algorithm-class-name=${common.algorithm4db}
# 范围分片算法类名称,用于 BETWEEN,可选。该类需实现 RangeShardingAlgorithm 接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.stock_outer_market_index_info.database-strategy.standard.range-algorithm-class-name=${common.algorithm4db}
5.3 功能测试
@Autowired
private StockBlockRtInfoMapper stockBlockRtInfoMapper;
@Test
public void testCommonSharding(){
Date curDate= DateTime.parse("2022-01-03 09:30:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")).toDate();
List<StockBlockDomain> info = stockBlockRtInfoMapper.findBlockInfoByTimeLimit(curDate);
System.out.println(info);
}