Java异常 #BindingException: Parameter ‘xxList‘ not found. Available parameters are [collection, list]
1.问题现象
通过 mybatis 框架，实现 SQL 批量插入并返回每条数据的主键id时，出现异常：Caused by: org.apache.ibatis.binding.BindingException: Parameter ‘couponCodeList’ not found. Available parameters are [collection, list]
1 org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'couponCodeList' not found. Available parameters are [collection, list] 2 at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) 3 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364) 4 at com.sun.proxy.$Proxy34.insert(Unknown Source) 5 at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:236) 6 at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:52) 7 at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) 8 at com.sun.proxy.$Proxy77.batchSaveCouponCodeList(Unknown Source) 9 at com.yyw.hs.eshop.promotion.manage.EshopCouponManageService.batchSaveCouponCode(EshopCouponManageService.java:200) 10 at com.yyw.hs.eshop.promotion.manage.EshopCouponManageService.createCouponActivity(EshopCouponManageService.java:144) 11 at com.yyw.hs.eshop.promotion.EshopCouponServiceTest.testCreateCouponActivity4(EshopCouponServiceTest.java:125) 12 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 13 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 14 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 15 at java.lang.reflect.Method.invoke(Method.java:498) 16 at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) 17 at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) 18 at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) 19 at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) 20 at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:73) 21 at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82) 22 at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73) 23 at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) 24 at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:217) 25 at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83) 26 at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) 27 at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) 28 at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) 29 at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) 30 at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) 31 at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) 32 at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68) 33 at org.junit.runners.ParentRunner.run(ParentRunner.java:363) 34 at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163) 35 at org.junit.runner.JUnitCore.run(JUnitCore.java:137) 36 at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68) 37 at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47) 38 at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242) 39 at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) 40 Caused by: org.apache.ibatis.binding.BindingException: Parameter 'couponCodeList' not found. Available parameters are [collection, list] 41 at org.apache.ibatis.session.defaults.DefaultSqlSession$StrictMap.get(DefaultSqlSession.java:294) 42 at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextAccessor.getProperty(DynamicContext.java:115) 43 at org.apache.ibatis.ognl.OgnlRuntime.getProperty(OgnlRuntime.java:2420) 44 at org.apache.ibatis.ognl.ASTProperty.getValueBody(ASTProperty.java:114) 45 at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212) 46 at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:258) 47 at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:494) 48 at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:458) 49 at org.apache.ibatis.scripting.xmltags.OgnlCache.getValue(OgnlCache.java:44) 50 at org.apache.ibatis.scripting.xmltags.ExpressionEvaluator.evaluateIterable(ExpressionEvaluator.java:43) 51 at org.apache.ibatis.scripting.xmltags.ForEachSqlNode.apply(ForEachSqlNode.java:55) 52 at org.apache.ibatis.scripting.xmltags.MixedSqlNode.apply(MixedSqlNode.java:33) 53 at org.apache.ibatis.scripting.xmltags.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:41) 54 at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:279) 55 at org.apache.ibatis.executor.statement.BaseStatementHandler.<init>(BaseStatementHandler.java:64) 56 at org.apache.ibatis.executor.statement.PreparedStatementHandler.<init>(PreparedStatementHandler.java:39) 57 at org.apache.ibatis.executor.statement.RoutingStatementHandler.<init>(RoutingStatementHandler.java:45) 58 at org.apache.ibatis.session.Configuration.newStatementHandler(Configuration.java:502) 59 at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:47) 60 at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115) 61 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 62 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 63 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 64 at java.lang.reflect.Method.invoke(Method.java:498) 65 at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) 66 at com.sun.proxy.$Proxy258.update(Unknown Source) 67 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 68 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 69 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 70 at java.lang.reflect.Method.invoke(Method.java:498) 71 at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) 72 at com.sun.proxy.$Proxy258.update(Unknown Source) 73 at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170) 74 at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:157) 75 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 76 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 77 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 78 at java.lang.reflect.Method.invoke(Method.java:498) 79 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354) 80 ... 36 common frames omitted
2.排查分析
DAO 接口及出入参定义：
1 List<TCoupon> batchSave(List<TCoupon> couponCodeList);
SQL 脚本中， <foreach> 标签 collection 属性给的变量名是与 DAO 接口入参一致的 “couponCodeList” 变量名，而不是固定变量 "list" 。问题就在这里。如下：
1 <insert id="batchSaveCouponCodeList" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="couponId"> 2 INSERT INTO 3 t_coupon (activity_id, coupon_code, use_status, create_user, create_time) 4 VALUES 5 <foreach collection="couponCodeList" item="item" index="index" separator=","> 6 (#{item.activityId}, #{item.couponCode}, 0, 'admin', NOW()) 7 </foreach> 8 </insert>
3.解决方案
方案一）
批量插入 SQL 的 collection 属性给的入参对象变量名改成使用 "list" 变量名，<insert> 标签参数类型用 parameterType="java.util.List" 接收 DAO 接口中的参数集合。即可。
<insert id="batchSaveCouponCodeList" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="couponId"> INSERT INTO t_coupon (activity_id, coupon_code, use_status, create_user, create_time) VALUES <foreach collection="list" item="item" index="index" separator=","> (#{item.activityId}, #{item.couponCode}, 0, 'admin', NOW()) </foreach> </insert>
此方案的 DAO 接口无需使用 @Param 注解。
方案二）
批量插入 SQL 的 collection 属性给的入参对象变量名改成使用 "mylist" 变量名，<insert> 标签参数类型用 parameterType="java.util.List" 接收 DAO 接口中的参数集合。
<insert id="batchSaveCouponCodeList" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="couponId"> INSERT INTO t_coupon (activity_id, coupon_code, use_status, create_user, create_time) VALUES <foreach collection="mylist" item="item" index="index" separator=","> (#{item.activityId}, #{item.couponCode}, 0, 'admin', NOW()) </foreach> </insert>
DAO 接口入参使用 @Param 注解场景，入参别名使用 "mylist" 。即 DAO 接口入参与 SQL 入参名称一致且为 "mylist"。亦可。
List<TCoupon> batchSave(@Param("mylist") List<TCoupon> couponCodeList);
