ywendy edited a comment on issue #5850:
URL: https://github.com/apache/shardingsphere/issues/5850#issuecomment-748741716
@strongduanmu
版本 4.1.1
你好我做了个测试，传递的参数如果为string类型就报下面的错误，如果把类型修正为Date类型则不会报如下错误
配置：
```yml
spring:
shardingsphere:
encrypt:
encryptors:
aesEncryptor:
type: aes
props:
aes.key.value: 123456
tables:
linkman:
columns:
mobile:
plainColumn: mobile
cipherColumn: mobile_encrypted
encryptor: aesEncryptor
datasource:
names: ds
ds:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url:
jdbc:mysql://localhost:3306/shardingsphere_test?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&autoReconnect=true
username: root
password: root
connection-timeout: 30000
maximum-pool-size: 20
minimum-idle: 5
auto-commit: true
idle-timeout: 600000
pool-name: crm-hikari
max-lifetime: 1800000
connection-test-query: SELECT 1
props:
query.with.cipher.column: false
```
代码：
```java
<select id="selectByTime" resultMap="BaseResultMap" parameterType="string">
SELECT <include refid="Base_Column_List"/>
FROM linkman
WHERE DATE_FORMAT(create_time, '%Y-%m-%d') =
DATE_FORMAT(#{createTime},'%Y-%m-%d')
</select>
List<Linkman> selectByTime(@Param("createTime") String createTime);
//如果把createTime的类型修改为Date类型则不会报错.
```
```code
[] 2020-12-21 11:44:28.030 DEBUG 15556 --- [ main]
o.e.m.LinkmanMapper.selectByTime_COUNT : ==> Preparing: SELECT count(0) FROM
linkman WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = DATE_FORMAT(?, '%Y-%m-%d')
[] 2020-12-21 11:44:28.041 DEBUG 15556 --- [ main]
o.e.m.LinkmanMapper.selectByTime_COUNT : ==> Parameters: 2020-12-21
11:44:27(String)
[] 2020-12-21 11:44:28.575 DEBUG 15556 --- [ main]
o.e.m.LinkmanMapper.selectByTime_COUNT : <== Total: 1
[] 2020-12-21 11:44:28.580 DEBUG 15556 --- [ main]
o.e.mapper.LinkmanMapper.selectByTime : ==> Preparing: SELECT id, mobile,
name, mobile_encrypted, create_time FROM linkman WHERE DATE_FORMAT(create_time,
'%Y-%m-%d') = DATE_FORMAT(?,'%Y-%m-%d') LIMIT ?
[] 2020-12-21 11:44:28.581 DEBUG 15556 --- [ main]
o.e.mapper.LinkmanMapper.selectByTime : ==> Parameters: 2020-12-21
11:44:27(String), 30(Integer)
org.mybatis.spring.MyBatisSystemException: nested exception is
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.lang.ClassCastException:
java.lang.String cannot be cast to java.lang.Integer
### The error may exist in file
[D:\workspace\gitsource\shardingsphere-test\target\classes\mapper\LinkmanMapper.xml]
### The error may involve
org.example.mapper.LinkmanMapper.selectByTime-Inline
### The error occurred while setting parameters
### SQL: SELECT id, mobile, name, mobile_encrypted, create_time
FROM linkman WHERE DATE_FORMAT(create_time, '%Y-%m-%d') =
DATE_FORMAT(?,'%Y-%m-%d') LIMIT ?
### Cause: java.lang.ClassCastException: java.lang.String cannot be cast to
java.lang.Integer
at
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
at
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at com.sun.proxy.$Proxy78.selectList(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
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$PlainMethodInvoker.invoke(MapperProxy.java:144)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
at com.sun.proxy.$Proxy80.selectByTime(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.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at
org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy81.selectByTime(Unknown Source)
at
org.example.mapper.LinkmanMapperTest.selectByTime(LinkmanMapperTest.java:59)
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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at
org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at
org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at
org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at
org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at
org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at
org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at
org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at
org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at
org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at
org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at
com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
at
com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:230)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:58)
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.lang.ClassCastException:
java.lang.String cannot be cast to java.lang.Integer
### The error may exist in file
[D:\workspace\gitsource\shardingsphere-test\target\classes\mapper\LinkmanMapper.xml]
### The error may involve
org.example.mapper.LinkmanMapper.selectByTime-Inline
### The error occurred while setting parameters
### SQL: SELECT id, mobile, name, mobile_encrypted, create_time
FROM linkman WHERE DATE_FORMAT(create_time, '%Y-%m-%d') =
DATE_FORMAT(?,'%Y-%m-%d') LIMIT ?
### Cause: java.lang.ClassCastException: java.lang.String cannot be cast to
java.lang.Integer
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 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:426)
... 49 more
Caused by: java.lang.ClassCastException: java.lang.String cannot be cast to
java.lang.Integer
at
org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext.getValue(PaginationContext.java:57)
at
org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext.<init>(PaginationContext.java:51)
at
org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.engine.LimitPaginationContextEngine.createPaginationContext(LimitPaginationContextEngine.java:38)
at
org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.engine.PaginationContextEngine.createPaginationContext(PaginationContextEngine.java:48)
at
org.apache.shardingsphere.sql.parser.binder.statement.dml.SelectStatementContext.<init>(SelectStatementContext.java:100)
at
org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory.getDMLStatementContext(SQLStatementContextFactory.java:103)
at
org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:87)
at
org.apache.shardingsphere.underlying.route.DataNodeRouter.createRouteContext(DataNodeRouter.java:99)
at
org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:89)
at
org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76)
at
org.apache.shardingsphere.underlying.pluggble.prepare.PreparedQueryPrepareEngine.route(PreparedQueryPrepareEngine.java:54)
at
org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96)
at
org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83)
at
org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement.getSQLUnit(EncryptPreparedStatement.java:161)
at
org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement.execute(EncryptPreparedStatement.java:135)
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.$Proxy95.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
com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:136)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy94.query(Unknown Source)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
... 55 more
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
[email protected]
