mybatis+foreach+其它查询条件

最新推荐文章于 2025-06-12 17:32:02 发布

zhangustb

最新推荐文章于 2025-06-12 17:32:02 发布

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： Mysql 后端

本文链接：https://blog.youkuaiyun.com/xing930408/article/details/90415086

后端同时被 2 个专栏收录

35 篇文章

订阅专栏

Mysql

10 篇文章

订阅专栏

在MyBatis中遇到使用foreach处理查询条件时遇到了问题，通过在参数前添加@Param注解并修正collection属性解决了启动报错。当第二个参数是对象时，需要正确设置对象中的集合属性作为collection。参考相关博客文章了解更多关于@Param的作用和如何处理对象中包含集合属性的查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一开始是这样写的

int updateMsgRecordReadStatus(List<Long> idList, String custNo, String userId);

对应的mapper.xml

    <update id="updateMsgRecordReadStatus">
        update msg_record
        set read_flag = 1,
        modified_date = NOW()
        where id IN
        <foreach collection="list" index="index" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        AND cust_no = #{custNo, jdbcType = VARCHAR} AND user_id = #{userId, jdbcType = VARCHAR}
    </update>

后来发现启动报错，

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'list' not found. Available parameters are [custNo, idList, param3, userId, param1, param2]
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy74.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy75.updateMsgRecordReadStatus(Unknown Source)
	at com.jdd.mcser.facade.impl.MsgCenterFacadeImpl.updateMsgRecordReadStatus(MsgCenterFacadeImpl.java:137)
	at com.jdd.mcser.facade.impl.MsgCenterFacadeImplTest.updateMsgRecordReadStatus(MsgCenterFacadeImplTest.java:64)
	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.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

于是在idList参数前面加了@Param注解，同时foreach里面的collection改成对应的Param的值

int updateMsgRecordReadStatus(@Param("idList") List<Long> idList, String custNo, String userId);

对应的mapper.xml

    <update id="updateMsgRecordReadStatus">
        update msg_record
        set read_flag = 1,
        modified_date = NOW()
        where id IN
        <foreach collection="idList" index="index" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        AND cust_no = #{custNo, jdbcType = VARCHAR} AND user_id = #{userId, jdbcType = VARCHAR}
    </update>

如果第二个参数是对象参数，那么应该按照如下方式写

int countAll(@Param("person") Person person, @Param("codeList") List<String> codeList);

  <select id="countAll" resultType="java.lang.Integer" >
    select count(*) from app
    <include refid="Example_Where_Clause" />
  </select>

<sql id="Example_Where_Clause" >
    where delete_status = 0
    and code in
    <foreach collection="codeList" index="index" item="item" open="(" separator="," close=")">
      #{item}
    </foreach>
    <if test="person.memo != null and person.memo !=''">
      and memo like CONCAT('%', #{person.memo}, '%')
    </if>
    <if test="person.age!= null and person.age!=''">
      and age= #{person.age}
    </if>
  </sql>

参考：https://www.cnblogs.com/huanggy/p/9326669.html

@Param的作用可参考

https://blog.youkuaiyun.com/qq_36134369/article/details/84349575

如果参数为对象在对象里面有集合属性查询，注意collection为参数名

<if test="merchantIds != null and  merchantIds.size() > 0"> 
    and MERCHANT_ID in 
    <foreach item="merId" index="index" collection="merchantIds" open="(" separator="," close=")">   
      #{merId}        
    </foreach>
</if>

https://blog.youkuaiyun.com/zq405677456/article/details/90695532