Lists in MyBatis 'IN' clause [duplicate]

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]

Theodoretheodoric

I have checked a few related questions such as [this one] (#18389436) , but I am not able to figure out what to specify for the foreach attributes. Below is a sample from the 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 > – Theodoretheodoric

Is your stripperIds in map null or empty? – Choe

Nope, stripperIds is not null/empty, it has values. – Theodoretheodoric

OK, could you please post full stack exception information? – Choe

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? – Theodoretheodoric

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? – Choe

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. – Theodoretheodoric

#{index} - No reason in particular, because I tried putting other place holders related to the list and it wouldn't work – Theodoretheodoric

Answer 1

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 :)

Garv

Answer 2

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);

Benil

Answer 3

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.

Renick

Answer 4

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

Diarmuid