MyBatis批量更新报错org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.bindin

短命女 2022-07-13 10:20 169阅读 0赞

需求描述：今天在项目中写了一个批量更新的操作，dao层使用的是MyBatis框架。

错误信息：`org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter '__frch_l_0' not found. Available parameters are [list, param1] at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371) at com.sun.proxy.$Proxy16.update(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:254) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:54) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52) at com.sun.proxy.$Proxy19.repayment(Unknown Source) at com.shoufubang.service.impl.RepaymentServiceImpl.repayment(RepaymentServiceImpl.java:99) 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:317) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at com.sun.proxy.$Proxy32.repayment(Unknown Source) at com.shoufubang.web.controller.RepaymentController.repayment(RepaymentController.java:96) 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.invoke(InvocableHandlerMethod.java:215) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:685) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:919) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:851) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:855) at javax.servlet.http.HttpServlet.service(HttpServlet.java:641) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:307) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: org.apache.ibatis.binding.BindingException: Parameter '__frch_l_0' not found. Available parameters are [list, param1] at org.apache.ibatis.binding.MapperMethod$ParamMap.get(MapperMethod.java:165) at org.apache.ibatis.reflection.wrapper.MapWrapper.get(MapWrapper.java:44) at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:116) at org.apache.ibatis.reflection.MetaObject.metaObjectForProperty(MetaObject.java:138) at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:109) at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:76) at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:80) at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:61) at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:74) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:47) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152) 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:358) ... 57 more`

错误分析：

1.  Dao层代码：`void repayment(@Param(value = "list") List<Map<String, String>> list);`
2.  Mapper.xml代码（修改前）：`<update id="repayment" parameterType="Object"> <if test="list != null and list.size() > 0"> <foreach collection="list" item="l" open="" close="" separator=";"> update sfb_project project set project.capital_balance = project.capital_balance = #{l.repay_ttlamt}, project.repay_ttlamt = project.repay_ttlamt + #{l.repay_ttlamt}, project.repay_duedate = #{l.repay_duedate}, project.eventtype = #{l.eventtype}, project.status = #{l.status}, project.received_interest = project.received_interest = #{l.received_interest}, project.received_charge = IFNULL(project.received_charge,0) + #{l.received_charge} where project.project_id = #{l.project_id} </foreach> </if> </update>`
3.  经检查SQL语句，发现SQL语句中有两处错误。两处错误均是为表中某一列做赋值运算是写的不是运算符号，而是写的【=】号。如下图：  
    ![错误图片][SouthEast]  
    好低级的错误啊！！！  
    后来，改正后测试，依然报错。尴尬，无奈。  
    再后来，一个字段一个字段开始排查，最终发现是因为参数里少传了一个值导致。可是，MyBatis这个缺少参数值为什么在批量里面无法暴露出来呢？无解！！！

错误总结：

1.  检查配置文件是否正确
2.  检查SQL语句拼写是否正确
3.  检查参数是否完整

[SouthEast]: /images/20220713/baf28acf4ef644a3ac22d21bb9b65b19.png