YangGuanQun opened a new issue, #37997:
URL: https://github.com/apache/shardingsphere/issues/37997
version: ShardingSphere-JDBC 5.4.1
In PostgreSQL databases sharding1 and sharding2, there is a schema named
base in both, and the t_order table exists under the base schema in both
databases.
I want to route requests to the two databases based on the value of the
data_time field through a custom rule.
```
public static DataSource getShardingDataSource() throws SQLException {
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("sharding1", createDruidDataSource("sharding1"));
dataSourceMap.put("sharding2", createDruidDataSource("sharding2"));
ShardingRuleConfiguration shardingRuleConfiguration = new
ShardingRuleConfiguration();
ShardingTableRuleConfiguration tableRuleConfiguration = new
ShardingTableRuleConfiguration("base.t_order",
"sharding1.base.t_order,sharding2.base.t_order");
shardingRuleConfiguration.getTables().add(tableRuleConfiguration);
shardingRuleConfiguration.setDefaultDatabaseShardingStrategy(new
StandardShardingStrategyConfiguration("data_time", "CUSTOM"));
Properties props = new Properties();
shardingRuleConfiguration.getShardingAlgorithms().put("CUSTOM", new
AlgorithmConfiguration("CUSTOM", props));
return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap,
Arrays.asList(shardingRuleConfiguration), new Properties());
}
private static DataSource createDruidDataSource(final String dbName) {
DruidDataSource druidDs = new DruidDataSource();
druidDs.setDriverClassName("org.postgresql.Driver");
druidDs.setUrl(String.format("jdbc:postgresql://localhost:15432/%s?characterEncoding=utf8",
dbName));
druidDs.setUsername("postgres");
druidDs.setPassword("xxxxx");
return druidDs;
}
```
When executing the SQL statement:
`INSERT INTO base.t_order (id, data_time) VALUES ("sharding1", "2025-01-01
00:00:00")`
error is:
`Exception in thread "main"
org.apache.shardingsphere.infra.metadata.database.schema.exception.UnsupportedActualDataNodeStructureException:
Can not support 3-tier structure for actual data node
sharding1.base.t_orderwith JDBC[jdbc:postgresql:].`
After modifying the code to:
`ShardingTableRuleConfiguration tableRuleConfiguration = new
ShardingTableRuleConfiguration("base.t_order",
"sharding1.t_order,sharding2.t_order");`
new error is:
`Exception in thread "main"
org.apache.shardingsphere.infra.exception.TableNotExistsException: Table or
view t_order does not exist.`
I tried to add currentSchema=base to the JDBC URL, but still get the same
error.
How should ShardingTableRuleConfiguration be configured to support SQL
statements with schema in PostgreSQL?
--
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]
