mybatis+foreach+其它查询条件

╰半橙微兮° 2022-01-29 11:07 335阅读 0赞

一开始是这样写的

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][https_www.cnblogs.com_huanggy_p_9326669.html]

@Param的作用可参考

[https://blog.csdn.net/qq\_36134369/article/details/84349575][https_blog.csdn.net_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.csdn.net/zq405677456/article/details/90695532][https_blog.csdn.net_zq405677456_article_details_90695532]

[https_www.cnblogs.com_huanggy_p_9326669.html]: https://www.cnblogs.com/huanggy/p/9326669.html
[https_blog.csdn.net_qq_36134369_article_details_84349575]: https://blog.csdn.net/qq_36134369/article/details/84349575
[https_blog.csdn.net_zq405677456_article_details_90695532]: https://blog.csdn.net/zq405677456/article/details/90695532