Nested foreach in MyBatis 3 for a HashMap parameter

14,566

Solution 1

Actually, you can use the country value to look it up in the filter map and make it work as you initially had it. In the second loop, your collection will be defined as filter.get(country) and that should be good. This is of course considering I'm interpreting your question correctly.

Solution 2

As of now, I can do this.

<foreach collection="myMap" index="key" item="value">
  <foreach collection="value" item="element">
    ... = #{key} ...
    ... = #{element})
  </foreach>
</foreach>

Solution 3

I had something to be inserted as a map where each map key maps to a list. I wrote the query this way.

INSERT INTO TB_TEST (group_id, student_id) VALUES
<foreach collection="idMap.entrySet()" item="element" index="index" separator=",">
         <foreach collection="element.value" item="item" separator="," > 
            ( #{element.key}  #{item} )     
         </foreach> 
    </foreach>
Share:
14,566
Caspian Canuck
Author by

Caspian Canuck

Updated on July 01, 2022

Comments

  • Caspian Canuck
    Caspian Canuck over 1 year

    I am trying to figure out a solution to the following problem using MyBatis 3.0.6:

    I need to build a dynamic select statement based on a series of parameters, one of which is of type HashMap<String, List<String>>. The challenge is to figure out how to make MyBatis iterate over all the keys in an outer foreach loop as well as iterate over the elements of the value list in the inner loop.

    To illustrate, suppose my hash map parameter called filter contains states (lists of state codes, each list being the value) per country (country code as the key) like so:

    'US' -> {'CO','NY','MI','AZ'};
    'CA' -> {'ON','BC','QC'}
    

    I need my dynamic SQL to look like this (in a grossly simplified form):

    SELECT *
    FROM  Table1
    WHERE ... some static criteria goes here...
          AND RowId IN (SELECT RowId FROM Table2 WHERE Country = 'US' AND State IN ('CO','NY','MI','AZ')
          AND RowId IN (SELECT RowId FROM Table2 WHERE Country = 'CA' AND State IN ('ON','BC,'QC')
    

    I would imagine my mapper XML should look something like this:

    <select id="getData" resultType="QueryResult">
    SELECT *
    FROM  Table1
    WHERE ... some static criteria goes here...
         <if test="filter != null">
             <foreach item="country" index="i" collection="filter" separator="AND">
                 RowId IN (SELECT RowId 
                           FROM Table2 
                           WHERE Country = #{country} AND State IN
                 <foreach item="state" index="j" collection="country.states" separator="," open="(" close=")">
                     #{state}
                 </foreach>
             </foreach>
         </if>
    </select>
    

    So the question is, what's the proper syntax to get the country.states to iterate over in the nested foreach loop?


    UPDATE

    After some tinkering I couldn't get MyBatis to play nicely with the HashMap-based approach, so I ended up adding a new class that maps multiple values to their parent value, then passing a list of such objects to MyBatis. Using the countries/states example above, the class looks like so:

    public class Filter {
       private String country;
       private ArrayList<String> states;
    
       // ... public get accessors here ...
    }
    

    The DAO method:

    public void QueryResult[] getResults( @Param("criteria") List<Filter> criteria) ...
    

    And the MyBatis mapping:

    <select id="getData" resultType="QueryResult">
    SELECT *
    FROM  Table1
    WHERE ... some static criteria goes here...
         <if test="criteria!= null">
             <foreach item="filter" index="i" collection="criteria" separator="AND" open="AND">
                 RowId IN (SELECT RowId 
                           FROM Table2 
                           WHERE Country = #{filter.country} AND State IN
                 <foreach item="state" index="j" collection="filter.states" separator="," open="(" close=")">
                     #{state}
                 </foreach>
             </foreach>
         </if>
    </select>
    

    Works like a charm.

  • 98percentmonkey
    98percentmonkey over 8 years
    THIS! fixed it for me. Thanks!