huyu-tom commented on issue #32734:
URL:
https://github.com/apache/shardingsphere/issues/32734#issuecomment-2322728974
### Main方法
`
public class Main {
public static void main(String[] args) throws SQLException {
//配置对应不同库的数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
HikariDataSource dataSource1 = new HikariDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setJdbcUrl("jdbc:mysql://192.168.0.199:13307/apl_sharding_test_db1");
dataSource1.setUsername("root");
dataSource1.setPassword("Apl9980");
dataSourceMap.put("apl_sharding_test_db1", dataSource1);
// HikariDataSource dataSource2 = new HikariDataSource();
// dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
//
dataSource2.setJdbcUrl("jdbc:mysql://192.168.0.199:13307/apl_sharding_test_db2");
// dataSource2.setUsername("root");
// dataSource2.setPassword("Apl9980");
// dataSourceMap.put("apl_sharding_test_db2", dataSource2);
//属性配置
Properties props = new Properties();
props.setProperty("sql-show", "true");
//分片规则配置
ShardingRuleConfiguration ruleConfiguration = new
ShardingRuleConfiguration();
//表配置
ruleConfiguration.getTables().add(chargeReceivableRuleConfig());
//分库策略(不分库，直接定位库)
Properties databaseProperties = new Properties();
databaseProperties.setProperty("databaseName", "apl_sharding_test_db1");
databaseProperties.setProperty("strategy", "STANDARD");
databaseProperties.setProperty("algorithmClassName",
"com.apl.shardingjdbc.sharding.AplDefaultDatabaseShardingAlgorithm");
ruleConfiguration.getShardingAlgorithms().put("aplDataBaseShardingStrategy",
new AlgorithmConfiguration("CLASS_BASED", databaseProperties));
Properties chargeReceivableOrgproperties = new Properties();
chargeReceivableOrgproperties.setProperty("shardingColumn",
"inner_org_id");
chargeReceivableOrgproperties.setProperty("strategy", "STANDARD");
chargeReceivableOrgproperties.setProperty("algorithmClassName",
"com.apl.shardingjdbc.sharding.OrgParamStandardShardingAlgorithm");
ruleConfiguration.getShardingAlgorithms().put("receivableTableOrgStandardShardingStrategy",
new AlgorithmConfiguration("CLASS_BASED",
chargeReceivableOrgproperties));
//创建sharding-dataSource
DataSource shardingDataSource =
ShardingSphereDataSourceFactory.createDataSource(dataSourceMap,
Arrays.asList(ruleConfiguration), props);
Connection connection = shardingDataSource.getConnection();
Statement statement = connection.createStatement();
statement.executeQuery("select * from charge_receivable where
inner_org_id = 100");
}
private static ShardingTableRuleConfiguration chargeReceivableRuleConfig()
{
ShardingTableRuleConfiguration tableRuleConfiguration = new
ShardingTableRuleConfiguration(
"charge_receivable", "apl_sharding_test_db1.charge_receivable");
StandardShardingStrategyConfiguration strategyConfig = new
StandardShardingStrategyConfiguration(
"inner_org_id", "receivableTableOrgStandardShardingStrategy");
tableRuleConfiguration.setTableShardingStrategy(strategyConfig);
//由于我们采用租户code,所以 actualDataNodes肯定无法满足,所以必须采用加上分库表
StandardShardingStrategyConfiguration databaseStrategyConfig = new
StandardShardingStrategyConfiguration(
"inner_org_id", "aplDataBaseShardingStrategy");
tableRuleConfiguration.setDatabaseShardingStrategy(databaseStrategyConfig);
return tableRuleConfiguration;
}
}
`
### 分库算法(其实只有库,直接外部传入，相当于直接写死)
`public class AplDefaultDatabaseShardingAlgorithm implements
StandardShardingAlgorithm<Comparable<?>>,
ComplexKeysShardingAlgorithm<Comparable<?>> {
private String databaseName;
private Collection<String> databaseNames;
public void init(Properties properties) {
final var databaseName = properties.getProperty("databaseName");
if (Strings.isNullOrEmpty(databaseName)) {
throw new IllegalArgumentException("databaseName 不能不指定");
}
this.databaseName = databaseName;
this.databaseNames = Arrays.asList(databaseName);
}
@Override
public String doSharding(Collection<String> collection,
PreciseShardingValue<Comparable<?>> preciseShardingValue) {
return databaseName;
}
@Override
public Collection<String> doSharding(Collection<String> collection,
RangeShardingValue<Comparable<?>> rangeShardingValue) {
return databaseNames;
}
@Override
public Collection<String> doSharding(Collection<String> collection,
ComplexKeysShardingValue<Comparable<?>> complexKeysShardingValue) {
return databaseNames;
}
}
`
### 分表算法 (基础表名+租户ID对应的租户Code)
`public class OrgParamStandardShardingAlgorithm implements
StandardShardingAlgorithm<Comparable<?>> {
public void init(Properties properties) {
}
@Override
public String doSharding(Collection<String> collection,
PreciseShardingValue<Comparable<?>> preciseShardingValue) {
final String logicTableName = preciseShardingValue.getLogicTableName();
final Comparable<?> value = preciseShardingValue.getValue();
if (Number.class.isAssignableFrom(value.getClass())) {
return
STR."\{logicTableName}_\{OrgUtils.getOrgCode(Long.valueOf(value.toString()))}";
} else {
throw new RuntimeException("租户不支持非数字类型");
}
}
@Override
public Collection<String> doSharding(Collection<String> collection,
RangeShardingValue<Comparable<?>> rangeShardingValue) {
throw new UnsupportedOperationException("租户分表操作不支持该操作");
}
}`
### OrgUtils
`public class OrgUtils {
public static String getOrgCode(Long orgId) {
return "sharding" + orgId;
}
}`
### 由于Main方法没有指定
actualDataNodes(租户ID无法精确指定，需要从sql分片键中动态获取，并且每个租户id都有一个相对应的表),出现如下错误
`Exception in thread "main" java.sql.SQLException: Routed target
'charge_receivable_sharding100' does not exist, available targets are
'[charge_receivable]'.
at
org.apache.shardingsphere.infra.exception.core.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:76)
at
org.apache.shardingsphere.infra.exception.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:54)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.executeQuery(ShardingSphereStatement.java:188)
at com.apl.shardingjdbc.sharding.Main.main(Main.java:72)`
### sharding-jdbc的版本
` <dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc</artifactId>
<version>5.5.0</version>
</dependency>
`
这种情况我该如何做到不指定actual-data-nodes，能够正常运行
--
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]
