Lists in MyBatis 'IN' clause

Question

How can I pass an Integer List to MyBatis XML, to be used in an in clause in my MySQL query?

I am using Java 7, MySQL 5.6 DB and MyBatis 3.0.4 with queries in a mapper-xml file.

Presently, I am converting this list of integers to a string, and using string substitution (${} operator) to put the values in the 'IN' clause - while it works as expected, this approach leaves the parameter vulnerable to Injection.

I have tried using a <foreach> element, but I am not able to figure out what attributes to specify.

Below is a sample Java code :

public List<Stripper> getStripperDetails(String club, List<Integer> stripperIds) {
        Map<String, Object> input = new HashMap<>();
        input.put("club", club);
        input.put("stripperIds", stripperIds);
        return stripClubMapper.getStripperDetails(input);
}

Mapper xml :

<select id="getStripperDetails" parameterType="java.util.HashMap" resultMap="StripperMap">
    SELECT STRIPPER_ID, STAGE_NAME, REAL_NAME, CLUB FROM EXOTIC_DANCERS WHERE CLUB = #{club} AND STRIPPER_ID IN     
    <foreach item="item" index="index" collection="stripperIds" open="(" separator="," close=")">
        #{index}
    </foreach>
</select>

I am not able to figure out what attributes to specify for the <foreach> element - I keep running into a NullPointerException for the value at #{index}.

Can you please help me understand the correct usage of the <foreach> element?

Edit :

@10086 ,

Below is the stack trace :

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.NullPointerException
### The error may involve com.stripclub.mapper.stripClubMapper.getStripperDetails-Inline
### The error occurred while setting parameters
### Cause: java.lang.NullPointerException
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:67) ~[mybatis-spring-1.0.0-RC3.jar:1.0.0-RC3]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:345) ~[mybatis-spring-1.0.0-RC3.jar:1.0.0-RC3]
    at com.sun.proxy.$Proxy208.selectList(Unknown Source) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:193) ~[mybatis-spring-1.0.0-RC3.jar:1.0.0-RC3]
    at org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:85) ~[mybatis-3.0.4.jar:3.0.4]
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:65) ~[mybatis-3.0.4.jar:3.0.4]
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38) ~[mybatis-3.0.4.jar:3.0.4]
    at com.sun.proxy.$Proxy209.getTransactionIds(Unknown Source) ~[na:na]

I have checked a few related questions such as [**this one**] (http://stackoverflow.com/questions/18388936/how-to-iterate-through-hashmap-in-mybatis-foreach?lq=1) , but I am not able to figure out what to specify for the foreach attributes. Below is a sample from the [**dtd**](http://mybatis.org/dtd/mybatis-3-mapper.dtd). `<!ELEMENT foreach (#PCDATA | include | trim | where | set | foreach | choose | if | bind)*> <!ATTLIST foreach collection CDATA #REQUIRED item CDATA #IMPLIED index CDATA #IMPLIED open CDATA #IMPLIED close CDATA #IMPLIED separator CDATA #IMPLIED >` — bub, Jun 17 '16 at 15:26
Hi @10086 , attached the stack trace info to the question. I know I am messing up the attribute values for the foreach tag, can you pls help me figure out where I am going wrong? — bub, Jun 29 '16 at 12:30
Obviously, this is a NPE error, as stack exception printed, error occurred when setting parameter, so check if two values in the map is `null` or not, and is there null element in `stripperIds`, and why do you set `#{index}` in `foreach`? — Blank, Jun 30 '16 at 01:22
Nope, none of the elements in the map are null. I am presently using the ${} expression to create dynamic queries (Converting the stripperIds list to a list of string and substituting with ${stripperIds}), but this leaves the query open to injection. I want to use the #{} so that mybatis will execute this as a prepared statement, and I want to avoid the explicit conversion from list of Integer to string in my code. The issue is that I am not able to figure out what values to provide for the foreach tag attributes so that I can directly pass my list in a param map, to be used in the query. — bub, Jun 30 '16 at 03:02
#{index} - No reason in particular, because I tried putting other place holders related to the list and it wouldn't work — bub, Jun 30 '16 at 03:04

score 21 · Accepted Answer · answered May 17 '17 at 10:16

The value specified by the item attribute should be used inside the foreach tag, when used with Lists. Use as below :

    <foreach item="sId" collection="stripperIds" separator="," open="(" close=")">
        #{sId}
    </foreach>

The index attibute is not mandatory, when using a List. Refer the MyBatis docs section for more info, or check out the DTD - http://mybatis.org/dtd/mybatis-3-mapper.dtd for more info about the parameters :

    <!ELEMENT foreach (#PCDATA | include | trim | where | set | foreach | choose | if | bind)*>
    <!ATTLIST foreach
    collection CDATA #REQUIRED
    item CDATA #IMPLIED
    index CDATA #IMPLIED
    open CDATA #IMPLIED
    close CDATA #IMPLIED
    separator CDATA #IMPLIED
    >

Also, lists of objects can be accessed in foreach as below. You would typically use this for INSERT/UPDATE statements :

Sample bean :

public class StripperBean {

    public StripperBean(int stripperID, String stripperName, String realName) {
        this.stripperID = stripperID;
        this.stripperName = stripperName;
        this.realName = realName;
    }

    private int stripperID; 
    private String stripperName;
    private String realName;        

    public int getStripperID() {
        return stripperID;
    }
    public void setStripperID(int stripperID) {
        this.stripperID = stripperID;
    }
    public String getStripperName() {
        return stripperName;
    }
    public void setStripperName(String stripperName) {
        this.stripperName = stripperName;
    }
    public String getRealName() {
        return realName;
    }
    public void setRealName(String realName) {
        this.realName = realName;
    }       
}

In your implementation :

    Map<String, Object> input = new HashMap<>();
    input.put("club", club);
    List<StripperBean> strippers = new ArrayList<>();
    strippers.add(new StripperBean(1,"Ashley", "Jean Grey"));
    strippers.add(new StripperBean(2,"Candice","Diana Prince"));
    strippers.add(new StripperBean(3,"Cristal","Lara Croft"));        
    input.put("strippers", strippers);
    return stripClubMapper.saveStripperDetails(input);

In the mapper xml :

    <insert id="saveStripperDetails">
        INSERT INTO EXOTIC_DANCERS (STRIPPER_ID, STAGE_NAME, REAL_NAME)
        VALUES
        <foreach item="stripper" collection="input" separator=",">
            (#{stripper.stripperID},
            #{stripper.stripperName},
            #{stripper.realName})
        </foreach>
    </select>

Nice question BTW :)

score 12 · Answer 2 · answered May 26 '17 at 16:33

Using annotation should be easier

@Select({
        "<script>", "select", " * ", "FROM TABLE",
        "WHERE  CONDITION IN  " +
        "<foreach item='item' index='index' collection='list' open='(' separator=',' close=')'> #{item} </foreach>" +
        "</script>"  })
         @Results({ })
        List<POJO> selectByKeys(@Param("list") List<String> ids);

Sky · Answer 3 · 2016-07-29T15:38:00.223

4

Your xml should be like this:

<foreach item="item" index="index" collection="stripperIds" open="(" separator="," close=")">
    #{item}
</foreach>

When using a Map (or Collection of Map.Entry objects), index will be the key object and item will be the value object.

You can reference here for the details. You will have a solid understanding about the attributes.

edited Jul 29 '16 at 15:38

answered Jul 29 '16 at 15:31

Sky

7,343
8
31
42

score 0 · Answer 4 · answered Apr 14 '17 at 20:33

0

your input is a map. so you need to resolve stripperIds from input before directly calling stripperIds.

answered Apr 14 '17 at 20:33

starzmasta

31
3

Lists in MyBatis 'IN' clause

4 Answers4

Linked