mybatis solves the problem that the <foreach> tag cannot exceed 1000

Wrong writing method:

    <select  resultType="vo">
        select  
        from A a
        where 
        <foreach collection="ids" item="item" index="index" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </select>

Cause of error:

When the number of <foreach> tags exceeds 1,000, an error will be prompted:

: ORA-01795: maximum number of expressions in a list is 1000

Correct writing:

Scheme 1 (Nest the pass parameter into SQL statement in SQL):

    <select  resultType="vo">
        select  
        from A a
        where  in
         (select  from B b where  = #{billNo}  and DELETED = 0)
    </select>

Scheme 2 (use or add one or for every 1000 items)

	SELECT
		*
	FROM
		${tabNameMx} M
		WHERE
		M.CODE_ID IN
		<foreach collection="idList" index="index" open="(" close=")" item="id" separator=",">
			<if test="(index % 999) == 998"> NULL) OR M.CODE_ID IN(</if>#{id}
		</foreach>

Everyone may feel a little confused when they see this SQL. Now I will write you what this SQL will eventually become, so that you will understand it instantly.

What SQL will do in the end:

CODE_ID IN('......','998',NULL ) OR M.CODE_ID IN('999',.....  NULL) OR M.CODE_ID IN('.....')

Solution 3 (Split OR ID IN ()):

<select >
  select ,to_char() as eno from T_E_ACCOUNT t where  !='2' and    in
 <trim suffixOverrides=" OR  in()">
 <foreach collection="capita" item="custId" index="index" open="(" close=")">   
            <if test="index != 0">
                <choose>
                    <when test="index % 1000 == 999">) OR  in (</when>
                    <otherwise>,</otherwise>
                </choose>
            </if>
     #{custId,jdbcType=VARCHAR} 
 </foreach>
 </trim>
</select>

analyze:

&lt;trim&gt;LabelsuffixOverrides：Remove the suffix matching，If the suffix in this example is OR  in()andsuffixOverridesThe attribute values just match，Remove OR  in()
 
index The location of the collection iteration from0start，Why do you need it&lt;if test="index != 0"&gt;？If not，butsqlyes  in (,1,2..)There will be an extra comma
 
No999Splicing of dataSQLfor： in (1,2..998) 
Exceed999Data stitching of stripsSQLfor： in (1,2..998) or  in（999,1000...1998） ...

expand:

Extension 1: The main attributes of the foreach element are item, index, collection, open, separator, and close

collection  foreachLooping object
item Each element in the collection or an object of the collection,Supports the method of obtaining attributes of object point properties#{} or #{value}index The subscript of the loop,from0start 
open 表示以什么start 
separator What symbols are used as separators between each iteration 
closeIndicates what ends

Extension 2: The four properties and their functions of the MyBatis trim tag

prefix  Add a prefix
prefixOverrides  Remove the prefix
suffix  Add a suffix
suffixOverrides  Delete the suffix

This is the article about mybatis solving the problem of <foreach> tags that cannot exceed 1,000. For more related content that mybatis foreach cannot exceed 1,000, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!