15178955723 opened a new issue, #24381:
URL: https://github.com/apache/shardingsphere/issues/24381
**依赖:**
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.16</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc8</artifactId>
<version>12.2.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.9</version>
</dependency>
</dependencies>
**采用java配置类的方式配置分表**
表名:document,分表字段:year
@Configuration
@Slf4j
@RequiredArgsConstructor
public class ShardingDataSourceConfig {
private final Database0Config database0Config;
@Bean(name = "shardingDataSource")
@Qualifier("shardingDataSource")
DataSource getShardingDataSource() throws SQLException {
//数据源配置
// ... 忽略其他数据库配置项
Map<String, DataSource> datasourceMaps = new HashMap<>(6);
datasourceMaps.put("ds0", database0Config.createDataSource());
//分片规则配置
// 表达式 ds_${0..1} 枚举值表示的是主从配置的逻辑数据源名称列表
ShardingTableRuleConfiguration tOrderRuleConfiguration = new
ShardingTableRuleConfiguration("document", "ds0.document_$->{2021..2023}");
tOrderRuleConfiguration.setKeyGenerateStrategy(new
KeyGenerateStrategyConfiguration("id", "snowflake"));
tOrderRuleConfiguration.setTableShardingStrategy(new
StandardShardingStrategyConfiguration("year", "tOrderInlineShardingAlgorithm"));
ShardingRuleConfiguration shardingRuleConfiguration = new
ShardingRuleConfiguration();
shardingRuleConfiguration.getTables().add(tOrderRuleConfiguration);
shardingRuleConfiguration.getBindingTableGroups().add("document");
Properties tOrderShardingInlineProps = new Properties();
tOrderShardingInlineProps.setProperty("algorithm-expression",
"document_$->{year}");
shardingRuleConfiguration.getShardingAlgorithms().putIfAbsent("tOrderInlineShardingAlgorithm",
new ShardingSphereAlgorithmConfiguration("INLINE", tOrderShardingInlineProps));
// 默认分库策略
// shardingRuleConfiguration.setDefaultDatabaseShardingStrategy(new
StandardShardingStrategyConfiguration("user_id", "default_db_strategy_inline"));
// Properties defaultDatabaseStrategyInlineProps = new Properties();
//
defaultDatabaseStrategyInlineProps.setProperty("algorithm-expression",
"ds_${user_id % 2}");
//
shardingRuleConfiguration.getShardingAlgorithms().put("default_db_strategy_inline",new
ShardingSphereAlgorithmConfiguration("INLINE",defaultDatabaseStrategyInlineProps));
//其他配置
Properties otherProperties = new Properties();
otherProperties.setProperty("sql-show", "true");
//shardingDataSource 就是最终被 ORM 框架或其他 jdbc 框架引用的数据源名称
DataSource shardingDataSource =
ShardingSphereDataSourceFactory.createDataSource(datasourceMaps,
Arrays.asList(shardingRuleConfiguration), otherProperties);
return shardingDataSource;
}
}
**插入数据时报错:**
用MySQL数据库就成功了,换成oracle数据库失败了,SQL拿到navicat执行没有错误,是我哪里没配置对吗
2023-02-28 16:45:23.061 INFO 19248 --- [nio-8088-exec-2]
o.s.web.servlet.DispatcherServlet : FrameworkServlet
'dispatcherServlet': initialization completed in 27 ms
Hibernate: select documenten0_.id as id1_0_0_, documenten0_.document_detail
as document2_0_0_, documenten0_.document_name as document3_0_0_,
documenten0_.year as year4_0_0_ from document documenten0_ where
documenten0_.id=?
2023-02-28 16:45:23.939 ERROR 19248 --- [nio-8088-exec-2]
o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet
[dispatcherServlet] in context with path [] threw exception [Request processing
failed; nested exception is
com.google.common.util.concurrent.UncheckedExecutionException:
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an
error in your SQL syntax] with root cause
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have
an error in your SQL syntax
at
org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.twoPhaseParse(SQLParserExecutor.java:68)
~[shardingsphere-sql-parser-engine-5.0.0.jar:5.0.0]
--
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]
