How to Iterate through HashMap in MyBatis foreach?

Question

I'm trying to produce a sql which is as below in mybatis.

SELECT COL_C
FROM TBLE_1
WHERE (COL_A, COL_B) in ( ('kp','kar'),('srt','sach'));

And my input parameter type is HashMap. Now How do I generate SQL from mapper xml file. The below code throws exception saying map evaluated to null.

<select id="selectCOLC" parameterType="java.util.HashMap" resultType="String">
    SELECT COL_C
    FROM TBLE_1
    WHERE (COL_A, COL_B) in 
    <foreach item="item" collection="#{map.keySet()}" open="((" separator="),(" close="))">
        #{item},#{item.get(item)}
    </foreach>
</select>

One of the other approach is to create a class with key value fields, create a list of object and then pass the parameterType as list which would look like following.

<select id="selectCOLC" parameterType="list" resultType="String">
        SELECT COL_C
        FROM TBLE_1
        WHERE (COL_A, COL_B) in 
        <foreach item="item" collection="list" open="((" separator="),(" close="))">
            #{item.getKey()},#{item.getVal()}
        </foreach>
    </select>

But is there any way to my mapper work for the first approach? other than changing the query to union

Answer 1

As a user of mybatis 3.5, I came through this.

Unfortunately, none of the solutions posted here worked for me but this does:

<foreach collection="_parameter.entrySet()" index="key" item="element" separator=",">
    MY_COLUMN = #{key} AND MY_OTHER_COLUMN = #{element}
</foreach>

So, in my case collection="_parameter.entrySet()" did the trick!

Moreover, none specification regarding the parameterType was needed.

Answer 2

this is an example in my project and it works fine

<select id="getObject" parameterType="Map" resultType="hashmap">    
    select * from TABL where 
    <foreach  collection="dataMap"  index="key" item="value"  open=""  separator=" and "  close="">
        #{key}=#{value}
    </foreach>
</select>

Answer 3

This solution doesn't work since version 3.2 - see more in Issue #208 !

Finally I've the solution for HashMap

I Should use entrySet() in order to make it iteratable

<select id="selectCOLC" parameterType="map" resultType="kpMap">
    SELECT COL_C
    FROM TBLE_1
    WHERE (COL_A, COL_B) in 
    <foreach item="item" collection="entries.entrySet()" open="((" separator="),(" close="))">
        #{item.key},#{item.value}
    </foreach>
</select>

One more Isue I was facing parameter name was not getting injected, Hence added @Param annotation

Hence mapper interface looks like below.

List<TblData> selectCOLC(@Param("entries")
            HashMap<String, String> entries)