spring中使用mybatis处理select时传入List参数

1. 使用 List<Map.Entry<String, String>> params 作为传入参数

• service 层：

    public List<Student> getMultiStudentInMap(List<Map.Entry<String, String>> params, long percentage) {System.out.println(params);
        return studentMapper.selectForeachEntry(params, percentage);
    }

• mapper.java

    public List<Student> selectForeachEntry(@Param("params") List<Map.Entry<String, String>> params, @Param("percentage") long percentage);

• mapper.xml

    <!-- 在 foreach 中，传入参数为 Map 或 Map.Entry 对象时，index 是键，item 是值 -->
    <select id="selectForeachEntry" resultMap="studentResult">
        select * from test_student t where t.percentage = #{percentage}
        <if test="params.size() > 0">
            and (
            <foreach item="param" index="index" collection="params" separator="or">
                (t.name = #{index} and t.branch = #{param}) 
            </foreach>
            )
        </if>
    </select>

• 调用方法：

        List<Map.Entry<String, String>> idList = new ArrayList<>();
        Map<String, String> map = new TreeMap<>();
        map.put("name_1", "a");
        map.put("name_2", "b");
        map.put("name_3", "c");
        Iterator<Map.Entry<String, String>> it = map.entrySet().iterator();
        while (it.hasNext()) {idList.add(it.next());
        }
        
        List<Student> students = studentDao.getMultiStudentInMap(idList, 0);

• mybatis 生成的结果：

==> Preparing: select * from test_student t where t.percentage = ? and ((t.name = ? and t.branch = ?) or (t.name = ? and t.branch = ?) or (t.name = ? and t.branch = ?) ) 
==> Parameters: 0(Long), name_1(String), a(String), name_2(String), b(String), name_3(String), c(String)
<== Total: 0

• 缺点：

使用 Map 生成 Map.Entry 有局限性，key 值不能重复。不使用 Map，就需要自己实现 Map.Entry 接口：class MyEntry implements Map.Entry<String, String> {…}

2. 自定义键值对的类

• Pair 类：

public class Pair<K, V> {
    private K key;
    private V value;
}

• service 层：

    public List<Student> getMultiStudentByPairList(List<Pair<String, String>> myentryList, long percentage) {System.out.println(myentryList);
        return studentMapper.selectByPairList(myentryList, percentage);
    }

• mapper.java

    public List<Student> selectByPairList(@Param("pairs") List<Pair<String, String>> pairs, @Param("percentage") long percentage);

• mapper.xml

    <!-- 在 foreach 中，传入参数为 List/Set 对象时，index 是当前迭代的序号，item 是当前迭代的元素 -->
    <select id="selectByPairList" resultMap="studentResult">
        select * from test_student t where t.percentage = #{percentage}
        <if test="pairs.size() > 0">
            and (
            <foreach item="pair" index="index" collection="pairs" separator="or">
                (t.name = #{pair.key} and t.branch = #{pair.value}) 
            </foreach>
            )
        </if>
    </select>

• 调用方法：

        List<Pair<String, String>> myentryList = new ArrayList<>();
        myentryList.add(new Pair<String, String>("name_1", "a"));
        myentryList.add(new Pair<String, String>("name_2", "b"));
        myentryList.add(new Pair<String, String>("name_3", "c"));
        
        List<Student> students = studentDao.getMultiStudentByPairList(myentryList, 0);

• mybatis 生成的结果：

==> Preparing: select * from test_student t where t.percentage = ? and ((t.name = ? and t.branch = ?) or (t.name = ? and t.branch = ?) or (t.name = ? and t.branch = ?) ) 
==> Parameters: 0(Long), name_1(String), a(String), name_2(String), b(String), name_3(String), c(String)
<== Total: 0