ShardingSphere查询报错-org.apache.ibatis.type.TypeException: Could not set parameters for mapping

淡淡的烟草味﹌ 2023-10-10 18:08 75阅读 0赞

#### 目录 ####

*   *  一、场景
     *  二、报错信息
     *  三、报错方法的定义
     *  四、排查
     *  五、原因
     *  六、解决

--------------------

### 一、场景 ###

1、使用ShardingJDBC操作数据库

2、查询SQL执行报错，但将sql复制到navicat中执行，是正常的

--------------------

### 二、报错信息 ###

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{
        property='originId', mode=IN, javaType=class java.lang.Integer, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer
    	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    	at com.sun.proxy.$Proxy105.selectList(Unknown Source)
    	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
    	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
    	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
    	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    	at com.sun.proxy.$Proxy138.getDeployMsgByOriginId(Unknown Source)
    	at com.xxx.service.service.impl.WechatMenuServiceImpl.getDeployMsg(WechatMenuServiceImpl.java:252)
    	at com.xxx.service.service.impl.WechatMenuServiceImpl$$FastClassBySpringCGLIB$$be80a3a8.invoke(<generated>)
    	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
    	at com.xxx.datasource.DataSourceAspect.around(DataSourceAspect.java:47)
    	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:175)
    	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
    	at com.xxx.service.service.impl.WechatMenuServiceImpl$$EnhancerBySpringCGLIB$$cb508aee.getDeployMsg(<generated>)
    	at com.xxx.utils.ServiceUtil.getDeployMsg(ServiceUtil.java:97)
    	at com.xxx.listener.StartListener.initCcsConfig(StartListener.java:58)
    	at com.xxx.listener.StartListener.onApplicationEvent(StartListener.java:46)
    	at com.xxx.listener.StartListener.onApplicationEvent(StartListener.java:22)
    	at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172)
    	at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165)
    	at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139)
    	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:402)
    	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:359)
    	at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:896)
    	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.finishRefresh(ServletWebServerApplicationContext.java:163)
    	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:552)
    	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:142)
    	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:775)
    	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
    	at org.springframework.boot.SpringApplication.run(SpringApplication.java:316)
    	at org.springframework.boot.web.servlet.support.SpringBootServletInitializer.run(SpringBootServletInitializer.java:157)
    	at org.springframework.boot.web.servlet.support.SpringBootServletInitializer.createRootApplicationContext(SpringBootServletInitializer.java:137)
    	at org.springframework.boot.web.servlet.support.SpringBootServletInitializer.onStartup(SpringBootServletInitializer.java:91)
    	at org.springframework.web.SpringServletContainerInitializer.onStartup(SpringServletContainerInitializer.java:171)
    	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5311)
    	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:145)
    	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:753)
    	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:729)
    	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
    	at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1126)
    	at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1868)
    	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    	at java.lang.Thread.run(Thread.java:748)
    Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{
        property='originId', mode=IN, javaType=class java.lang.Integer, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer
    	at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:89)
    	at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:93)
    	at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64)
    	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86)
    	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
    	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:108)
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
    	at com.sun.proxy.$Proxy241.query(Unknown Source)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
    	at sun.reflect.GeneratedMethodAccessor173.invoke(Unknown Source)
    	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)
    	... 57 common frames omitted
    Caused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer
    	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:55)
    	at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:87)
    	... 73 common frames omitted
    Caused by: java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer
    	at org.apache.ibatis.type.IntegerTypeHandler.setNonNullParameter(IntegerTypeHandler.java:26)
    	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:53)
    	... 74 common frames omitted

--------------------

### 三、报错方法的定义 ###

![在这里插入图片描述][e2c040c1c94440af947cc96415756592.png]

<select id="getDeployMsgByOriginId" parameterType="java.lang.Integer" resultType="com.focustar.entity.po.WxMenuPO">
    	select id,content,newsId,msgType
    	From T_WX_Menu
    	Where originId = #{originId}  AND eable=1
    </select>

--------------------

### 四、排查 ###

1、数据库表originId字段的类型与结果类originId属性类型一致

2、方法传递的参数类型也是Long

--------------------

### 五、原因 ###

是XML中`parameterType="java.lang.Integer"`导致，方法参数是Long类型，这里却指定为Integer

--------------------

### 六、解决 ###

方法1、删除XML中`parameterType="java.lang.Integer"`

方法2、将XML中`parameterType="java.lang.Integer"`改为`parameterType="java.lang.Long"`

[e2c040c1c94440af947cc96415756592.png]: https://img-blog.csdnimg.cn/e2c040c1c94440af947cc96415756592.png