Nested foreach in MyBatis 3 for a HashMap parameter

Question

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.

Answer 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.

Answer 2

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>