title: Nested foreach in MyBatis 3 for a HashMap parameter
body: <p>I am trying to figure out a solution to the following problem using MyBatis 3.0.6:</p>
  
  <p>I need to build a dynamic select statement based on a series of parameters, one of which is of type <code>HashMap&lt;String, List&lt;String&gt;&gt;</code>. The challenge is to figure out how to make MyBatis iterate over all the keys in an outer <em>foreach</em> loop as well as iterate over the elements of the value list in the inner loop.</p>
  
  <p>To illustrate, suppose my hash map parameter called <strong><em>filter</em></strong> contains states (lists of state codes, each list being the value) per country (country code as the key) like so:</p>
  
  <pre><code>'US' -&gt; {'CO','NY','MI','AZ'};
  'CA' -&gt; {'ON','BC','QC'}
  </code></pre>
  
  <p>I need my dynamic SQL to look like this (in a grossly simplified form):</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>I would imagine my mapper XML should look something like this:</p>
  
  <pre><code>&lt;select id="getData" resultType="QueryResult"&gt;
  SELECT *
  FROM  Table1
  WHERE ... some static criteria goes here...
       &lt;if test="filter != null"&gt;
           &lt;foreach item="country" index="i" collection="filter" separator="AND"&gt;
               RowId IN (SELECT RowId 
                         FROM Table2 
                         WHERE Country = #{country} AND State IN
               &lt;foreach item="state" index="j" collection="country.states" separator="," open="(" close=")"&gt;
                   #{state}
               &lt;/foreach&gt;
           &lt;/foreach&gt;
       &lt;/if&gt;
  &lt;/select&gt;
  </code></pre>
  
  <p>So the question is, what's the proper syntax to get the <strong>country.states</strong> to iterate over in the nested <em>foreach</em> loop?</p>
  
  <hr>
  
  <p><strong>UPDATE</strong></p>
  
  <p>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:</p>
  
  <pre><code>public class Filter {
     private String country;
     private ArrayList&lt;String&gt; states;
  
     // ... public get accessors here ...
  }
  </code></pre>
  
  <p>The DAO method:</p>
  
  <pre><code>public void QueryResult[] getResults( @Param("criteria") List&lt;Filter&gt; criteria) ...
  </code></pre>
  
  <p>And the MyBatis mapping:</p>
  
  <pre><code>&lt;select id="getData" resultType="QueryResult"&gt;
  SELECT *
  FROM  Table1
  WHERE ... some static criteria goes here...
       &lt;if test="criteria!= null"&gt;
           &lt;foreach item="filter" index="i" collection="criteria" separator="AND" open="AND"&gt;
               RowId IN (SELECT RowId 
                         FROM Table2 
                         WHERE Country = #{filter.country} AND State IN
               &lt;foreach item="state" index="j" collection="filter.states" separator="," open="(" close=")"&gt;
                   #{state}
               &lt;/foreach&gt;
           &lt;/foreach&gt;
       &lt;/if&gt;
  &lt;/select&gt;
  </code></pre>
  
  <p>Works like a charm.</p>