乐趣区

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
退出移动版