Databases
StackOverflow2013
Postsdbo
PONested foreach in MyBatis 3 for a HashMap parameter
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONested foreach in MyBatis 3 for a HashMap parameter
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload