使用环境:
ShardingSphere5.1.0+MyBatisPlus
场景、问题:
我在application.properties中配置了自定义分片算法类,但是在实际执行中并没有生效,而是直接进行对应真实表的全子表查找
已进行操作:
application.properties分表配置项
spring.shardingsphere.rules.sharding.tables.signetrecord20220357.actual-data-nodes=current.signetrecord20220357$->{[2].collect{t → t.toString().padLeft(2,‘0’)}}0$->{0…1}
#spring.sharding.sharding.tables.signetrecord20220357.database-strategy.none.any=
spring.shardingsphere.rules.sharding.tables.signetrecord20220357.table-strategy.hint.sharding-algorithm-name=hint-sharding-algorithm
spring.shardingsphere.rules.sharding.tables.signetrecord20220357.key-generate-strategy.column=recordid
spring.shardingsphere.rules.sharding.tables.signetrecord20220357.key-generate-strategy.key-generator-name=SNOWFLAKE
spring.shardingsphere.rules.sharding.sharding-algorithms.hint-sharding-algorithm.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.hint-sharding-algorithm.props.strategy=HINT
spring.shardingsphere.rules.sharding.sharding-algorithms.hint-sharding-algorithm.props.algorithmClassName=com.xuec.demo.hintstrategy.algorithm.MyHintShardingAlgorithm
自定义算法类:
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Integer> shardingValue) {
List<String> result = new ArrayList<>();
Collection<Integer> values = shardingValue.getValues();
String tableName = "signetrecord";
for (String table : availableTargetNames) {
for (Integer value : values) {
tableName += value.toString();
}
if (table.equals(tableName)) {
result.add(tableName);
}
}
return result;
}
测试方法:
/*查询所有*/
public List<SignetRecord> findAll(){
String tableName = "signetrecord";
String year = "2021";
String orgIndex = "0006";
String month = "2";
String resHash = "01";
String logicTableNameSuffix = year + orgIndex;
String logicTableName = tableName + year + orgIndex;
String realTableName = logicTableName + month + resHash;
RequestDataHelper.setRequestData(new HashMap<String, Object>() {{
put(tableName, logicTableNameSuffix);
}});
HintManager.clear();
HintManager hintManager = HintManager.getInstance();
hintManager.addTableShardingValue(logicTableName, Integer.parseInt(year+orgIndex));
hintManager.addTableShardingValue(logicTableName, Integer.parseInt(month+resHash));
List<SignetRecord> resFindAll = signetRecordMapper.selectList(new LambdaQueryWrapper<>());
hintManager.close();
return resFindAll;
}
代码说明:
我利用mybatisplus拼接逻辑表名,然后再将相关的强制路由字段传入到算法类中
执行结果:
所有测试结果都是“
2022-06-10 15:05:38.926 INFO 62449 — [nio-8080-exec-1] ShardingSphere-SQL : Logic SQL: SELECT recordId,appId,requestBusinessId,encryKeyId,serialNo,signetId,signetModelId,useOrgId,userAccount,useDate,requestDate,globalSeqNo,ipAddress FROM signetRecord20210006
2022-06-10 15:05:38.926 INFO 62449 — [nio-8080-exec-1] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-06-10 15:05:38.926 INFO 62449 — [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: current ::: SELECT recordId,appId,requestBusinessId,encryKeyId,serialNo,signetId,signetModelId,useOrgId,userAccount,useDate,requestDate,globalSeqNo,ipAddress FROM signetrecord202100060200 UNION ALL SELECT recordId,appId,requestBusinessId,encryKeyId,serialNo,signetId,signetModelId,useOrgId,userAccount,useDate,requestDate,globalSeqNo,ipAddress FROM signetrecord202100060201
<== Columns: recordId, appId, requestBusinessId, encryKeyId, serialNo, signetId, signetModelId, useOrgId, userAccount, useDate, requestDate, globalSeqNo, ipAddress
<== Row: 1, 0044, 990000000001, S001, ABCD123457002, YZ001, YM001, 9900, TZTest, null, null, 20220317000000009000000000, 10.112.6.100
<== Row: 1, 0044, 990000000001, S001, ABCD123457001, YZ001, YM001, 9900, TZTest, null, null, 20220317000000009000000000, 10.112.6.100