How to pass an Integer Array to IN clause in MyBatis

Question

There is a query in my Mybatis containing an IN clause which is basically a set of Id's ( Integers)

I am now stuck on how can I pass an Integer array to this IN clause so that it pulls up the proper records.Tried by passing a String containing the ID's to the IN clause , but this did not work as expected.

Code Sample below

Mybatis Method using Annotations

@Select(SEL_QUERY)
    @Results(value = {@Result(property="id",column="ID")})
    List<Integer> getIds(@Param("usrIds") Integer[] usrIds);

Query

select distinct ID from table a where a.id in ( #{usrIds} )

Method Call

Integer[] arr = new Integer[2];
arr[0] = 1;
arr[1] = 2;

mapper.getIds(arr)

This is not working , Mybatis throws an error when I call the mapper method

Any suggestions please

score 46 · Accepted Answer · edited May 23 '17 at 11:47

46

The myBatis User Guide on Dynamic SQL has an example on how to use a foreach loop to build the query string, which works for lists and arrays.

Prior to release 3.2 you had to use xml configuration to use dynamic sql, with newer versions it should also be possible to use dynamic sql in annotations.

<select id="selectPostIn" resultType="domain.blog.Post">
    SELECT *
    FROM POST P
    WHERE ID in
    <foreach item="item" index="index" collection="list"
             open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

edited May 23 '17 at 11:47

Community

1
1

answered Jan 06 '12 at 12:29

Jörn Horstmann

33,639
11
75
118

Thanks for the tip, I'll check it out and update the space here – Vivek Jan 06 '12 at 13:12
1

Actually, it also works with annotations as long as you make your query string start with ` – LordOfThePigs Mar 25 '14 at 21:30
@LordOfThePigs: I could not find that in the official docs, do you have a link for that? – Jörn Horstmann Apr 11 '14 at 14:34
@JörnHorstmann To be honnest, I couldn't find the reference to that feature in the documentation, but it definitely works (and I use it frequently). Just check the two other questions I linked. – LordOfThePigs Apr 11 '14 at 14:46
1

@JörnHorstmann There is a [extract from the mybatis-user mailing list](http://mybatis-user.963551.n3.nabble.com/Dynamic-queries-using-annotations-td4026489.html) that has some MyBatis devs telling someone how to use dynamic SQL with annotations, saying that it is supported starting from version 3.2.0, with version 3.2.1 introductiong the ` – LordOfThePigs Apr 11 '14 at 14:51
That dzone article looks almost identical to the MyBatis official documentation. http://mybatis.github.io/mybatis-3/dynamic-sql.html – Bill Rosmus Dec 05 '14 at 08:02
Hi Jörn Horstmann, thank you for the answer, but the link [**myBatis User Guide on Dynamic SQL**](http://www.mybatis.org/core/dynamic-sql.html) seems to be broken, not able to find a alternative for the same. – bub Jun 15 '16 at 12:42
Er is there any **shorter** method...? Or can I make some shortcuts? Thanks! – ch271828n Apr 23 '20 at 14:21

score 6 · Answer 2 · edited May 23 '17 at 12:10

6

YES, you can do that using annotations.

If you're using postgresql, you can do like in this post.

If you're using MySQL try this changes in your code sample:

Mybatis Method using Annotations

@Select(SEL_QUERY)
    @Results(value = {@Result(property="id",column="ID")})
    List<Integer> getIds(@Param("usrIds") String usrIds);

Query (using MySQL)

select distinct ID from table a where FIND_IN_SET( a.id, #{usrIds}) <> 0

Method call

Integer[] arr = new Integer[2];
arr[0] = 1;
arr[1] = 2;

String usrIds= "";
for (int id : ids) {
    usrIds += id + ",";
}

mapper.getIds(usrIds)

edited May 23 '17 at 12:10

Community

1
1

answered Feb 09 '12 at 04:40

Italo Borssatto

15,044
7
62
88

exactly what i needed – deeshank Aug 09 '16 at 15:59
4

A valuable note here is that FIND_IN_SET will not use indexes so this version is likely to perform worse than an IN statement – Zack Apr 25 '17 at 22:44
FIND_IN_SET is the most easy way to do this (for those who don't care about performance) – Zhang Buzz Nov 26 '18 at 09:17

Italo Borssatto · Answer 3 · 2019-03-08T20:39:30.247

You can create a new type handler and use it only for your parameter. The query would change to:

SELECT ... WHERE FIND_IN_SET(id, #{usrIds, typeHandler=my.pkg.ListTypeHandler}) <> 0

And the type handler:

import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.ObjectTypeHandler;

import com.google.common.base.Joiner;

public class ListTypeHandler extends ObjectTypeHandler {
    @Override
    public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        ps.setObject(i, Joiner.on(",").join((Iterable<?>) parameter), JdbcType.OTHER.TYPE_CODE);
    }
}

score 0 · Answer 4 · answered Aug 18 '20 at 11:33

List distinctID = (List) getSqlSession().selectOne("dataMapper.getUniqueData", uniqueIDList);

Send the list of unique id

<select id="getUniqueData" resultType="List">
     select distinct ID from table a where a.id in 
      <foreach item="item" index="index" collection="list"
          open="(" separator="," close=")">
            #{item}
      </foreach>
</select>

When using an List, index will be the number of current iteration and value item will be the element retrieved in this iteration

How to pass an Integer Array to IN clause in MyBatis

4 Answers4

Linked

Related