I am trying to find a solution to the following problem using MyBatis 3.0.6:
I need to build a dynamic selection operator based on a number of parameters, one of which is of type HashMap<String, List<String>>. The task is to figure out how to get MyBatis to iterate over all the keys in the foreach outer loop, and also to iterate over the elements of the list of values in the inner loop.
To illustrate, suppose my hash map parameter, called a filter , contains states (lists of state codes, each of which is a value) for each country (country code as a key), for example:
'US' -> {'CO','NY','MI','AZ'};
'CA' -> {'ON','BC','QC'}
I need my dynamic SQL to look like this (in a very 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')
, XML- :
<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 =
<foreach item="state" index="j" collection="country.states" separator="," open="(" close=")">
</foreach>
</foreach>
</if>
</select>
, , country.states foreach?
UPDATE
MyBatis , HashMap, , , MyBatis. /, :
public class Filter {
private String country;
private ArrayList<String> states;
}
DAO:
public void QueryResult[] getResults( @Param("criteria") List<Filter> criteria) ...
MyBatis:
<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 =
<foreach item="state" index="j" collection="filter.states" separator="," open="(" close=")">
</foreach>
</foreach>
</if>
</select>
.