你不会玩儿的mybatis根据list泛型批量查询

水深无声 2021-09-24 01:46 381阅读 0赞

有User实体类,表字段与其对应

package test.send;
    
    public class User {
        private String id;
        private String name;
        private Integer age;
    
        public String getId() {
            return id;
        }
    
        public void setId(String id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Integer getAge() {
            return age;
        }
    
        public void setAge(Integer age) {
            this.age = age;
        }
    }

网上的mybatis批量查询资料,一把都是如下这种

根据id,进行in关键字进行条件赛选

<select id="selectUser" resultType="User" parameterType="java.util.List">
        select 
            id,name,age
        from
            user
        where id in
    <foreach collection="list" item="item" open="(" close=")" separator=",">
        #{item.id}
    </foreach>
    </select>

但是有个需求,是需要根据List<User>泛型的name和age进行条件查询

准备样例数据

List<User> list = new ArrayList<>();
    
    User u1 = new User();
    
    u1.setName("u1");
    
    u1.setAge(12)
    
    
    User u2 = new User();
    
    u2.setName("u2");
    
    u2.setAge(13)
    list.add(u1);
    list.add(u2);
    
    List<User> reslist mapper.selectUser(list);

错误示范(这里不能用in,自己细品)

<select id="selectUser" resultType="User" parameterType="java.util.List">
        select 
            id,name,age
        from
            user
        where name in
    <foreach collection="list" item="item" open="(" close=")" separator=",">
        #{item.name}
    </foreach>
    and age in
    <foreach collection="list" item="item" open="(" close=")" separator=",">
        #{item.age}
    </foreach>
    </select>

正确操作(使用foreach循环查询之后,在用union all并集)

<select id="selectUser" resultType="User" parameterType="java.util.List">
         <foreach collection="list" item="item" index="index" separator="union all">
                SELECT
                    id,name,age
                FROM
                  user
                where name = #{item.name} and age= #{item.age}
         </foreach>
    </select>