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

Ortrud

Answer 1

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)

Ortrud

BEWARE: This ways is broken since version 3.2.x: - version 3.2.x has direct support for a map Issue #709 with <foreach item="value" index="key" collection="map"> and it must be rewritten to this new form - see Issue #208 or @Prorate solution – Working

@MichalBernhard Can you please post your own answer? That will help further visitors a lot. – Ackley

I posted one valid for my batis 3.5 – Unconcerned

@MichalBernhard I am using 3.2.x so how exactly i should use map values. It's still not clear from Issue #208 .Should I write #{value()} – Pulchia

@RujutaS solution from user "Arcones" below does not work ? Arcones answer link: https://mcmap.net/q/659838/-how-to-iterate-through-hashmap-in-mybatis-foreach – Working

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>

Prorate

this works only since version 3.2.x (see Issue #709 – Working

important distinction is that for List or Array the index indeed works as index of the element but for a Map index becomes the key instead see: developpaper.com/mybatis-loop-foreach – Planimeter

Answer 3

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.

Unconcerned

It worked for me as well. I am using spring-boot with mybatis. – Alegre

Answer 4

In your first example mybatis is looking for an entry in the parameterMap with the key "map". I suspect that you are actually trying to iterate the key set of the parameterMap. If you nested the map within the parameter map using the key "map" it should work.

In the second example you should just be able to pass the HashMap.entrySet() which provides a getKey and getValue.

Sag

Answer 5

Here is a simple example.

<foreach index="key" item="value" collection="your_map_collection">
    #{key} = #{value}
</foreach>

When using a Map (or Collection of Map.Entry objects), index will be the key object and item will be the value object. https://mybatis.org/mybatis-3/dynamic-sql.html#foreach

Barony