silence1005 opened a new issue, #28230:
URL: https://github.com/apache/shardingsphere/issues/28230
```java
@Component
public class ShardingSS {
@Value("${batch.datasource.url}")
private String jdbcUrl;
@Value("${batch.datasource.username}")
private String username;
@Value("${batch.datasource.password}")
private String password;
@Bean
public DataSource dataSource() throws SQLException {
HikariDataSource dataSource1 = new HikariDataSource();
dataSource1.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource1.setJdbcUrl(jdbcUrl);
dataSource1.setUsername(username);
dataSource1.setPassword(password);
//数据源配置
dataSource1.setConnectionTimeout(10000);
dataSource1.setIdleTimeout(1000000);
dataSource1.setMaxLifetime(1800000);
dataSource1.setMaximumPoolSize(50);
dataSource1.setMinimumIdle(50);
Map<String, DataSource> map = new HashMap<>();
StandardShardingStrategyConfiguration strategyConfiguration = new
StandardShardingStrategyConfiguration("BUSINESS_TIME", "alg-datetime-interval");
map.put("ds",dataSource1);
ShardingRuleConfiguration ruleConfiguration = new
ShardingRuleConfiguration();
ShardingTableRuleConfiguration erp_data = new
ShardingTableRuleConfiguration("erp_data","ds.erp_data_$->{202301..202312}");
erp_data.setTableShardingStrategy(strategyConfiguration);
ShardingTableRuleConfiguration yb_trans_detail = new
ShardingTableRuleConfiguration("yb_trans_detail","ds.yb_trans_detail_$->{202301..202312}");
ShardingTableRuleConfiguration yb_trans_record = new
ShardingTableRuleConfiguration("yb_trans_record","ds.yb_trans_record_$->{202301..202312}");
ShardingTableRuleConfiguration erp_detail_raw_data = new
ShardingTableRuleConfiguration("erp_detail_raw_data","ds.erp_detail_raw_data_$->{202301..202312}");
ShardingTableRuleConfiguration erp_raw_data = new
ShardingTableRuleConfiguration("erp_raw_data","ds.erp_raw_data_$->{202301..202312}");
ruleConfiguration.getTables().add(erp_data);
ruleConfiguration.getTables().add(yb_trans_detail);
ruleConfiguration.getTables().add(yb_trans_record);
ruleConfiguration.getTables().add(erp_detail_raw_data);
ruleConfiguration.getTables().add(erp_raw_data);
erp_data.setTableShardingStrategy(strategyConfiguration);
ruleConfiguration.setDefaultDatabaseShardingStrategy(strategyConfiguration);
Properties algorithmProperties = new Properties();
algorithmProperties.put("datetime-lower", "2023-01");
algorithmProperties.put("datetime-upper", "2222-12");
algorithmProperties.put("datetime-pattern", "yyyy-MM");
algorithmProperties.put("sharding-suffix-pattern", "yyyyMM");
algorithmProperties.put("datetime-interval-unit", "MONTHS");
IntervalShardingAlgorithm intervalShardingAlgorithm = new
IntervalShardingAlgorithm();
intervalShardingAlgorithm.init(algorithmProperties);
AlgorithmConfiguration interval = new
AlgorithmConfiguration("INTERVAL", algorithmProperties);
ruleConfiguration.getShardingAlgorithms().put("alg-datetime-interval",interval);
Properties shardingProperties = new Properties();
shardingProperties.put("sql-show","true");
return ShardingSphereDataSourceFactory.createDataSource(dataSource1,
Collections.singleton(ruleConfiguration), shardingProperties);
}
}
when i execute
(select * WHERE 1 = 1 and e.BUSINESS_TIME >= ? and e.BUSINESS_TIME <= ?
ORDER BY e.business_Time desc LIMIT ?)
An error will be reported: ### Cause: java.sql.SQLException: No sharding
database route info.
SQL queries using sub-tables will report errors, but it is normal to perform
SQL queries without using sub-tables
Is there a problem with the above configuration? I just want to implement
sharding through java code, not yaml
My version is 5.3.2, here is the complete error message:
```java
==> Preparing: select e.ID, e.CREATE_BY, e.CREATE_DATE, e.LAST_MODIFIED_BY,
e.LAST_MODIFIED_DATE, e.JGID, e.YWLX, e.DJH, e.SPID, e.SL, e.KC, e.DJ, e.YBKH,
e.SCPH, e.PHYXQ, e.YWSJ, e.CZY, e.YS, e.PSDH, e.FPH, e.BZ, e.CJSJ, e.tx_hash,
e.data_hash, e.hh, e.ybje, e.zfje, e.spzj, e.spdj, e.zglsj, e.qx_code,
e.qx_name, e.data_Year_Month, e.ERP_RAW_DATA_ID, e.BUSINESS_TIME , goods.SPMC,
goods.SPBM, goods.SPGG, goods.DW, goods.DZJGM FROM erp_data e left join goods
ON e.SPID = goods.ID WHERE 1 = 1 and e.BUSINESS_TIME >= ? and e.BUSINESS_TIME
<= ? ORDER BY e.business_Time desc LIMIT ?
==> Parameters: 2023-08-01 00:00:00.0(Timestamp), 2023-08-31
00:00:00.0(Timestamp), 50(Integer)
Releasing transactional SqlSession
[org.apache.ibatis.session.defaults.DefaultSqlSession@5e31ee54]
Transaction synchronization deregistering SqlSession
[org.apache.ibatis.session.defaults.DefaultSqlSession@5e31ee54]
Transaction synchronization closing SqlSession
[org.apache.ibatis.session.defaults.DefaultSqlSession@5e31ee54]
2023-08-23 14:25:04.107 [http-nio-8091-exec-1] ERROR
o.a.c.c.C.[.[.[/mcs-supn].[dispatcherServlet] - Servlet.service() for servlet
[dispatcherServlet] in context with path [/mcs-supn] threw exception [Request
processing failed; nested exception is
org.springframework.dao.DataIntegrityViolationException:
### Error querying database. Cause: java.sql.SQLException: No sharding
database route info.
### The error may exist in file
[C:\yongyou\mcs-supn-sh\mcs-supn-db\target\classes\mappings\mysql\biz\ErpDataMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select e.ID, e.CREATE_BY, e.CREATE_DATE,
e.LAST_MODIFIED_BY, e.LAST_MODIFIED_DATE, e.JGID,
e.YWLX, e.DJH, e.SPID, e.SL, e.KC,
e.DJ, e.YBKH, e.SCPH, e.PHYXQ, e.YWSJ,
e.CZY, e.YS, e.PSDH, e.FPH, e.BZ,
e.CJSJ, e.tx_hash, e.data_hash, e.hh, e.ybje,
e.zfje, e.spzj, e.spdj, e.zglsj,
e.qx_code, e.qx_name, e.data_Year_Month,
e.ERP_RAW_DATA_ID, e.BUSINESS_TIME , goods.SPMC,
goods.SPBM, goods.SPGG, goods.DW, goods.DZJGM
FROM erp_data e left join goods ON e.SPID = goods.ID
WHERE 1 = 1 and e.BUSINESS_TIME >= ? and
e.BUSINESS_TIME <= ? ORDER BY e.business_Time desc LIMIT ?
### Cause: java.sql.SQLException: No sharding database route info.
; No sharding database route info.; nested exception is
java.sql.SQLException: No sharding database route info.] with root cause
java.sql.SQLException: No sharding database route info.
at
org.apache.shardingsphere.infra.util.exception.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:62)
at
org.apache.shardingsphere.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:51)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:448)
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.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy244.execute(Unknown Source)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at
org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at
org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
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.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy243.query(Unknown Source)
at
com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:136)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy243.query(Unknown Source)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
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:433)
at com.sun.proxy.$Proxy179.selectList(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at
org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
at com.sun.proxy.$Proxy202.queryList(Unknown Source)
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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205)
at com.sun.proxy.$Proxy203.queryList(Unknown Source)
at
com.ufgov.yonyou.service.impl.ErpDataServiceImpl.queryList(ErpDataServiceImpl.java:150)
at
com.ufgov.yonyou.service.impl.ErpDataServiceImpl$$FastClassBySpringCGLIB$$d86bd414.invoke(<generated>)
at
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:769)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
at
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
at
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
at
com.ufgov.yonyou.service.impl.ErpDataServiceImpl$$EnhancerBySpringCGLIB$$94e66095.queryList(<generated>)
at
com.ufgov.yonyou.controller.ErpDataController.queryList(ErpDataController.java:48)
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.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
at
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793)
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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:772)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
at
org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
at
io.github.resilience4j.decorators.Decorators$DecorateCheckedSupplier.get(Decorators.java:426)
at
com.huaweicloud.governance.GovernanceRequestMappingHandlerAdapter.aroundInvoke(GovernanceRequestMappingHandlerAdapter.java:87)
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.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
at
org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
at
org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
at
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
at
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter$$EnhancerBySpringCGLIB$$516f429b.handleInternal(<generated>)
at
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
at
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
com.ufgov.yonyou.common.filter.UserTokenFilter.doFilter(UserTokenFilter.java:77)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:109)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at
org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:367)
at
org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at
org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1639)
at
org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
--
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]
