How to use more than 1000 args in IN clause in mybatis?

Question

I am using Oracle database and myBatis as ORM framework, all queries are in XML mapper files. I need to put couple thousand arguments in IN caluse and I know there is a constraint that only 1000 arguments could be put there. I have decided for solution:

WHERE something IN (a1, a2, ..., a1000)
OR something IN (a1001, a1002, ..., a2000)
OR ...

How could I make that in XML mapper file?

There are <foreach>, <if> tags, etc. but I don't find any suitable that can insert SQL code from above into template.

It would be great that something like this can make it work:

<some_tag collection="huge_collection" item="huge_collection[1:1000]>

</some_tag>

Duplicate of the Oracle/Java questions https://stackoverflow.com/q/34699223/1509264 or https://stackoverflow.com/q/37160300/1509264 but with a MyBatis tag added. Just pass one collection in with all the values. — MT0, Jun 11 '18 at 19:33

Karol Dowbecki · Answer 1 · 2018-06-11T16:15:04.883

Although you can work around number of IN parameters:

Thousands of bound values will results in potentially megabytes of SQL. It will take long time to send this SQL to the database.
It will be inefficient due to SQL parsing. Not only it takes long time to parse this long SQL but each invocation with different number of bound parameters will be parsed and planned separately (see this article which explains it).
There is a hard limit of bound parameters in a SQL statement. You can repeat the OR few times to work around IN limit but you are going to hit the SQL statement limit at some point.

For that type of queries it's usually better to create a temporary tables. Create it before the query, insert all the identifiers into it and join it with this temporary table in the actual query to simulate the WHERE statement.

Ideally you can replace the query with a stored procedure. Pulling out tens of thousands identifiers from the database just to pass it back to the database in the next query is just inefficient.

The Impaler · Answer 2 · 2018-06-11T15:51:24.313

You can use a < foreach > tag. This tag iterates over a collection (set, list, etc.) and can assemble a big SQL statement.

Your example will look like:

WHERE something IN (
  <foreach item="i1" collection="list1" separator=",">
    #{i1}
  </foreach>
  )
OR something IN (
  <foreach item="i2" collection="list2" separator=",">
    #{i2}
  </foreach>
  )
OR ...

The following example taken from MyBatis Manual tells you more about it:

<insert id="insertAuthor" useGeneratedKeys="true"
    keyProperty="id">
  insert into Author (username, password, email, bio) values
  <foreach item="item" collection="list" separator=",">
    (#{item.username}, #{item.password}, #{item.email}, #{item.bio})
  </foreach>
</insert>

How to use more than 1000 args in IN clause in mybatis?

2 Answers2