The foreach file attachment in MyBatis 3 for the HashMap parameter

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 = #{country} AND State IN
             <foreach item="state" index="j" collection="country.states" separator="," open="(" close=")">
                 #{state}
             </foreach>
         </foreach>
     </if>
</select>

, , country.states foreach?


UPDATE

MyBatis , HashMap, , , MyBatis. /, :

public class Filter {
   private String country;
   private ArrayList<String> states;

   // ... public get accessors here ...
}

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 = #{filter.country} AND State IN
             <foreach item="state" index="j" collection="filter.states" separator="," open="(" close=")">
                 #{state}
             </foreach>
         </foreach>
     </if>
</select>

.

+5
3

, , . filter.get(country), . , , , .

+3

-, , . .

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>
+1

I can do this for now.

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

All Articles