Mybatis之foreach标签的使用

本帖主要分享mybatis的批量操作,由于工作繁忙暂不对标签进行讲解

本人不推荐在for循环中进行数据库操作,因为会进行大量的io操作,导致查询性能极差,并对数据库造成压力,

1.根据id进行批量查询

实现类层

    @Override
    public List<User> selectUserByIds() {
        Integer[] ids = {1, 4};
        return userMapper.selectUserByIds(ids);
    }

mapper接口

    /**
     * 批量查询
     * @param ids 用户信息
     * @return 用户信息列表
     */
    List<User> selectUserByIds(Integer[] ids);

xml文件

    <select id="selectUserByIds" resultType="com.jt.pojo.User">
        SELECT id, username, password, phone, email, status, created, updated
        FROM
        user
        where
        id
        in
        <foreach  collection="array" item="ids" open="(" close=")" separator=",">
            (#{ids})
        </foreach>
    </select>

简化写法

    <select id="selectUserByIds1" resultType="com.jt.pojo.User">
        SELECT id, username, password, phone, email, status, created, updated
        FROM
        user
        where
        id
        in
        (
        <foreach collection="array" item="ids" separator=",">
            #{ids}
        </foreach>
        )
    </select>

2.根据多个条件进行查询

实现类层

    @Override
    public List<User> selectUserByList() {
        List<User> users = new ArrayList<>();
        User user = new User();
        user.setUsername("admin").setStatus(true);
        User user1 = new User();
        user1.setUsername("admin666").setStatus(true);
        users.add(user);
        users.add(user1);
        return userMapper.selectByUserList(users);
    }

mapper接口

    /**
     * 批量查询
     * @param users 用户信息
     * @return 用户信息列表
     */
    List<User> selectByUserList(List<User> users);

xml文件

    <select id="selectByUserList" resultType="com.jt.pojo.User">
        SELECT id, username, password, phone, email, status, created, updated
        FROM
        user
        where
        <foreach  collection="list" item="users" open="(" close=")" separator=" or ">
            (
            username = #{users.username}
            and
            status = #{users.status}
            )
        </foreach>
    </select>

3.批量新增

实现类层 

    @Override
    public List<User> insertByUserList() {
        List<User> users = new ArrayList<>();
        User user = new User();                                      
        user.setId(7).setUsername("admin852").setStatus(true).setPassword("conversation")
        .setPhone("10010").setEmail("1600973491@qq.com").
        setCreated(new Date()).setUpdated(new Date());
        User user1 = new User();      
        user1.setId(6).setUsername("admin5476").setStatus(true).
        setPassword("admin5476").setPhone("10010").setEmail("1205498999@qq.com")
        .setCreated(new Date()).setUpdated(new Date());
        users.add(user);
        users.add(user1);
        //以下代码为查看插入后结果
        int i = userMapper.insertByUserList(users);
        Integer[] ids = {6,7};
        return userMapper.selectUserByIds(ids);
    }

mapper接口

/**
     * 批量新增
     * @param users 用户信息
     * @return 用户信息列表
     */
    int insertByUserList(List<User> users);

xml文件

    <insert id="insertByUserList">
        INSERT INTO
        user
        (id,password,username,phone,email,status,created,updated)
        VALUES
        <foreach collection="list" item="users" index="index" separator=",">
            (
            #{users.id},
            #{users.password},
            #{users.username},
            #{users.phone},
            #{users.email},
            #{users.status},
            #{users.created},
            #{users.updated}
            )
        </foreach>
    </insert>

4.根据多个条件进行批量修改

实现类层

@Override
    public List<User> updateByUserList() {
        List<User> users = new ArrayList<>();
        User user = new User();
        user.setId(7).setUsername("admin852").setStatus(false).setPassword("conv")
        .setPhone("112").setEmail("1600973491@qq.com").setCreated(new Date())
        .setUpdated(new Date());
        User user1 = new User();
        user1.setId(6).setUsername("admin5476").setStatus(false).setPassword("ad76")
        .setPhone("156").setEmail("1205498999@qq.com").setCreated(new Date())
        .setUpdated(new Date());
        users.add(user);
        users.add(user1);
        //以下代码为查看修改后结果
        int i = userMapper.updateByUserList(users);
        Integer[] ids = {6,7};
        return userMapper.selectUserByIds(ids);
    }

mapper接口

    /**
     * 批量修改
     * @param users 用户信息
     * @return 用户信息列表
     */
    int updateByUserList(List<User> users);

xml文件

    <update id="updateByUserList">
        <foreach collection="list" item="users" index="index" separator=";">
            UPDATE user
            set
            id=#{users.id},
            password=#{users.password},
            username=#{users.username},
            phone=#{users.phone},
            email=#{users.email},
            status=#{users.status},
            created=#{users.created},
            updated=#{users.updated}
            where
            id=#{users.id}
            and
            username=#{users.username}
        </foreach>
    </update>

如需要根据id进行修改 则参考根据id批量查询 二者如出一辙