448700174 commented on issue #12315:
URL:
https://github.com/apache/shardingsphere/issues/12315#issuecomment-916602491
1.The version is shardingSphere-JDBC 5.0.0.beta
2.The config is:
shardingsphere:
props:
sql-show: true
rules:
sharding:
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
sharding-algorithms:
tenant-id-sharding:
type: CLASS_BASED
props:
strategy: STANDARD
algorithmClassName:
com.youban.common.dao.TenantIdShardingAlgorithm
binding-tables:
- care_task,care_task_detail
tables:
care_task:
actual-data-nodes: ds.care_task_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_task_detail:
actual-data-nodes: ds.care_task_detail_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_task_item:
actual-data-nodes: ds.care_task_item_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_patrol_task:
actual-data-nodes: ds.care_patrol_task_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_patrol_item:
actual-data-nodes: ds.care_patrol_item_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_patrol_subtask:
actual-data-nodes: ds.care_patrol_subtask_$->{1..99}
table-strategy:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_order_detail:
actual-data-nodes: ds.care_order_detail_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
3.The sharding algorithm class is:
public class TenantIdShardingAlgorithm implements
StandardShardingAlgorithm<Integer> {
private static final ConcurrentHashMap<String, List<String>>
ACTUAL_TABLES = new ConcurrentHashMap<>();
public String doSharding(Collection<String> availableTargetNames,
PreciseShardingValue<Integer> shardingValue) {
String logicTableName = shardingValue.getLogicTableName();
String tenantId = shardingValue.getValue() + "";
String actualTableName = logicTableName + "_" + tenantId;
List<String> actualTalbes =
ACTUAL_TABLES.computeIfAbsent(logicTableName, key -> new ArrayList<>());
// create table if not exists
if (!actualTalbes.contains(actualTableName)) {
String create = String.format("create table if not
exists `%s` like `%s` ", actualTableName,
logicTableName);
Connection connection = null;
try {
DataSource dataSource =
SpringContextUtils.getBean(DataSource.class);
connection = dataSource.getConnection();
Statement statement =
connection.createStatement();
if (0 == statement.executeUpdate(create)) {
log.info("create table success: " +
actualTableName);
actualTalbes.add(actualTableName);
} else {
log.error("create table fail: " +
actualTableName);
}
statement.close();
connection.close();
} catch (Exception e) {
log.error("createActualTable", e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// nothing todo
}
}
}
}
return actualTableName;
}
@Override
public String getType() {
return null;
}
@Override
public void init() {
}
@Override
public Collection<String> doSharding(Collection<String> paramCollection,
RangeShardingValue<Integer> paramRangeShardingValue) {
return paramCollection;
}
}
4.The Logic SQL is:
select count(*)
from care_task t,care_task_item i left join care_task_detail d
on d.task_item_id = i.id and d.tenant_id = #{tenantId}
where i.task_id = t.id and t.enabled = 1 and t.done_count > 0
and i.enabled = 1
and t.tenant_id = #{tenantId}
and i.tenant_id = #{tenantId}
5.The Actual SQL is:
### SQL: select count(*) from care_task t,care_task_item i left join
care_task_detail d on d.task_item_id = i.id and d.tenant_id = ? where
i.task_id = t.id and t.enabled = 1 and t.done_count > 0 and i.enabled = 1
and t.tenant_id = ? and i.tenant_id = ? and t.target_value=?
and t.target_type=? and t.service_id=? AND
i.operate_time >= ? and i.operate_time <= ?
### Cause:
org.apache.shardingsphere.infra.exception.ShardingSphereException: Route table
care_task_12 does not exist, available actual table: [care_task_detail_1,
care_task_detail_2, care_task_detail_3, care_task_detail_4, care_task_detail_5,
care_task_detail_6, care_task_detail_7, care_task_detail_8, care_task_detail_9,
care_task_detail_10, care_task_detail_11, care_task_detail_12,
care_task_detail_13, care_task_detail_14, ...]
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
... 81 common frames omitted
Caused by:
org.apache.shardingsphere.infra.exception.ShardingSphereException: Route table
care_task_12 does not exist, available actual table: [care_task_detail_1,
care_task_detail_2, care_task_detail_3, care_task_detail_4, care_task_detail_5,
care_task_detail_6, care_task_detail_7, care_task_detail_8, care_task_detail_9,
care_task_detail_10, care_task_detail_11, care_task_detail_12,
care_task_detail_13, ....]
at
org.apache.shardingsphere.sharding.route.strategy.type.standard.StandardShardingStrategy.doSharding(StandardShardingStrategy.java:73)
at
org.apache.shardingsphere.sharding.route.strategy.type.standard.StandardShardingStrategy.doSharding(StandardShardingStrategy.java:57)
at
org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeTables(ShardingStandardRoutingEngine.java:214)
at
org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route0(ShardingStandardRoutingEngine.java:194)
at
org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditionsWithCondition(ShardingStandardRoutingEngine.java:114)
at
org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditions(ShardingStandardRoutingEngine.java:107)
at
org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.getDataNodes(ShardingStandardRoutingEngine.java:84)
at
org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:69)
at
org.apache.shardingsphere.sharding.route.engine.type.complex.ShardingComplexRoutingEngine.route(ShardingComplexRoutingEngine.java:57)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
