需求: 执行in操作查询,且in()中的参数超过1000条,如果不做处理,当in中的参数超过1000条会发生错误。
解决方案: IN(1,2,3,…,1000) OR IN(1001,1002,1003,2000)
FROM
HPET32TR ET32
WHERE
ET32.JUCHU_NO IN
<foreach collection="list" item="item" index="index" open="(" close=")">
<if test="index != 0">
<choose>
<when test="index % 1000 == 0">) OR ET32.JUCHU_NO IN (</when>
<otherwise>,</otherwise>
</choose>
</if>
#{item}
</foreach>
测试结果如下:(我将1000改成3测试的。排除入力参数为0个的情况)
①参数个数为6:
FROM
DEV_F.HPET32TR ET32
WHERE
ET32.JUCHU_NO IN ('0000000001 ', '0000000002 ', '0000000003 ')
OR ET32.JUCHU_NO IN ('0000000004 ', '0000000005 ', '0000000006 ')
②参数个数为7:
FROM
DEV_F.HPET32TR ET32
WHERE
ET32.JUCHU_NO IN ('0000000001 ', '0000000002 ', '0000000003 ')
OR ET32.JUCHU_NO IN ('0000000004 ', '0000000005 ', '0000000006 ')
OR ET32.JUCHU_NO IN ('0000000007 ')
③参数个数为8:
FROM
DEV_F.HPET32TR ET32
WHERE
ET32.JUCHU_NO IN ('0000000001 ', '0000000002 ', '0000000003 ')
OR ET32.JUCHU_NO IN ('0000000004 ', '0000000005 ', '0000000006 ')
OR ET32.JUCHU_NO IN ('0000000007 ', '0000000008 ')