参考链接:ShardingSphere 5.0 分库分表java写法
官方文档
学习笔记:
测试代码:
@Bean(name = "shardingDataSource")
public DataSource shardingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource ) throws SQLException
{
// 构建真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("base", masterDataSource);
// 构建属性配置
Properties props = getProperties();
// 构建具体规则
Collection<RuleConfiguration> ruleConfigs = new LinkedList<>();
ruleConfigs.add(getShardingRC());
// 获取数据源对象
DataSource dataSource = ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, ruleConfigs, props);
return dataSource;
}
private ShardingRuleConfiguration getShardingRC(){
ShardingRuleConfiguration shardingRC = new ShardingRuleConfiguration();
// 设置表(逻辑表&物理表),设置分库|表算法名
shardingRC.getTables().add(getShardingTRC());
// 算法配置参数
Properties properties = new Properties();
// !important : 作为需要做mod的字段,改参数类型必为数字类型
properties.setProperty("algorithm-expression","mybatisplus_test_$->{older_id % 2}");
// 设置算法类型+配置参数
shardingRC.getShardingAlgorithms().put("couponShardingAlgorithms",new ShardingSphereAlgorithmConfiguration("INLINE",properties));
return shardingRC;
}
private ShardingTableRuleConfiguration getShardingTRC(){
// 设置节点:逻辑表 + 物理表
ShardingTableRuleConfiguration shardingTRC = new ShardingTableRuleConfiguration("mybatisplus_test","base.mybatisplus_test_$->{0..1}");
// 设置分表策略 ShardingStrategyConfiguration => StandardShardingStrategyConfiguration|ComplexShardingStrategyConfiguration|HintShardingStrategyConfiguration
shardingTRC.setTableShardingStrategy(new StandardShardingStrategyConfiguration("older_id","couponShardingAlgorithms"));
return shardingTRC;
}
/**
* 系统参数配置
*/
private Properties getProperties()
{
Properties shardingProperties = new Properties();
shardingProperties.put("sql.show", true);
return shardingProperties;
}
按上述进行配置后,插入成功,但查询时出现:
### Cause: org.apache.shardingsphere.infra.exception.ShardingSphereException: Inline sharding algorithms expression `mybatisplus_test_${older_id % 2}` and sharding column `older_id` not match.
排查后发现因查询条件设置问题导致:
QueryWrapper<CouponPo> queryWrapper = new QueryWrapper<CouponPo>();
queryWrapper.eq("older_id","1"); // 👈 older_id作为需要进行mod计算的字段,不能为string
queryWrapper.eq("coupon_status","0");
queryWrapper.like("coupon_date","2024%");
List<CouponPo> couponPos = couponPlusMapper.selectList(queryWrapper);
ending:记录以备忘
后续记录:
一:数据源切换失败
复杂业务逻辑下,在代码中出现
@Override
public void a(){
// 业务代码....
b(); // 👈这里作为function使用时,切换数据源失败,要调用service.b() 的时候才能成功切换
}
@Override
@DataSource(DataSourceType.SHARDING)
public void b(){
// 业务代码....
xxxMapper.insertBatchSomeColumn();
}
二:数据源切换失败 xxx表 doesn’t exist
参考链接:多数据源@DS和@Transactional
前提:确认配置信息,表映射都没写错,也没出现上述的调用问题
业务基本逻辑:外层执行存储后,获取自增主键,创建内层业务的数据并存储,需切换数据源,不存在异常回滚问题
外层主业务逻辑执行完后,执行内层业务
在外层:添加回滚机制@Transactional(rollbackFor = Exception.class)
在内层:
切换数据源@DataSource(DataSourceType.SHARDING)
新建回滚机制@Transactional(propagation = Propagation.REQUIRES_NEW)