mybatis solves the problem that < foreach > tags cannot exceed 1000 _java_ Script Home
Script house Common server software
Quick navigation
Your location: Home page → Software programming → java→ mybatis foreach cannot exceed 1000

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

Updated: May 01, 2024 08:10:21 Author: Xiaohaihai is not afraid of difficulties
MyBatis is an open source persistent layer framework, which can help developers simplify the writing of database operations, and foreach is an important tag in MyBatis, used for loop operations in SQL statements, this article mainly introduces mybatis to solve the < foreach > tag can not exceed 1000 problem Need friends can refer to

Wrong way to write:

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

Error cause:

When the number of <foreach> tags exceeds 1000, an error message is displayed:

java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

Correct writing:

Option 1(nesting the parameter into an SQL statement in SQL) :

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

Option 2 (Add one or per 1000 with or)

	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>

This SQL we may feel a little confused, now write you a section of this sql will eventually become what, so that you instantly understand.

What the final execution of SQL looks like:

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

Option 3 (splice OR ID IN ()) :

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

Analysis:

<trim> Tag suffixOverrides: in this example, if the suffix OR t.tid in() matches the attribute value of suffixOverrides, then the iterated position of the OR t.tid in() index set starts from 0. Why do I need <if test="index! = 0">? If not, sql is t.cust in (,1,2...) The concatenation SQL without 999 data is: t.ust in (1,2... 998) Data concatenation SQL for more than 999 items is: t.ust in (1,2... 998) or T.tid in (999,1000... 1998)...

Expanded:

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

Each element in the item collection of the collection foreach loop, or the object of the collection, supports the subscript of the attribute #{obj.filed} or #{value} index loop as an object point attribute, starting with 0 separator What is used as the separator between each iteration? close indicates what ends

Extension 2: MyBatis trim four attributes of the tag and their effects

prefix Add prefix prefixOverrides Delete prefix suffix Add suffix suffixOverrides Delete suffix

This article about mybatis to solve the < foreach > tag can not exceed 1000 is introduced to this article, more related mybatis foreach can not exceed 1000 content, please search the previous articles of script home or continue to browse the following related articles hope that you will support script home in the future!

Articles you may be interested in:

Related article

Latest comments