MyBatis实现数据的批量新增和删除

阳光穿透心脏的1/2处 2021-08-31 03:38 451阅读 0赞

在项目的开发中，我们经常需要对数据进行批量的操作，如：批量新增、批量删除等。下面将介绍MyBatis如何实现数据的批量新增和删除操作。

创建UserMapper接口（用户信息Mapper动态代理接口），实现用户信息的批量新增、批量删除、批量查询。

package com.pjb.mapper;
    
    import com.pjb.entity.UserInfo;
    import org.apache.ibatis.annotations.Delete;
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    
    /**
     * 用户信息Mapper动态代理接口
     * @author pan_junbiao
     **/
    @Repository
    public interface UserMapper
    {
        /**
         * 批量新增用户
         */
        @Insert({"<script>",
                "INSERT INTO tb_user(user_name,remark) VALUES",
                    "<foreach collection='userInfoList' item='item' index='index' separator=','>",
                        "(#{item.userName},#{item.remark})",
                    "</foreach>",
                "</script>"})
        public int addUserBatch(@Param("userInfoList") List<UserInfo> userInfoList);
    
        /**
         * 批量删除用户
         */
        @Delete({"<script>",
                "DELETE FROM tb_user WHERE user_id IN",
                    "<foreach item='id' index='index' collection='array' open='(' separator=',' close=')'>",
                        "#{id}",
                    "</foreach>",
                "</script>"})
        public int deleteUserBatch(int[] userIds);
    
        /**
         * 批量获取用户
         */
        @Select({"<script>",
                    "SELECT * FROM tb_user WHERE user_id IN",
                    "<foreach item='id' index='index' collection='array' open='(' separator=',' close=')'>",
                        "#{id}",
                    "</foreach>",
                "</script>"})
        public List<UserInfo> getUserBatch(int[] userIds);
    }

# 1、批量新增 #

@Autowired
    private UserMapper userMapper;
    
    /**
     * 批量新增用户
     * @author pan_junbiao
     */
    @Test
    public void addUserBatch()
    {
        //创建新用户列表
        List<UserInfo> userInfoList = new ArrayList<UserInfo>();
        userInfoList.add(new UserInfo("pan_junbiao的博客_01","您好，欢迎访问 pan_junbiao的博客"));
        userInfoList.add(new UserInfo("pan_junbiao的博客_02","https://blog.csdn.net/pan_junbiao"));
        userInfoList.add(new UserInfo("pan_junbiao的博客_03","您好，欢迎访问 pan_junbiao的博客"));
        userInfoList.add(new UserInfo("pan_junbiao的博客_04","https://blog.csdn.net/pan_junbiao"));
        userInfoList.add(new UserInfo("pan_junbiao的博客_05","您好，欢迎访问 pan_junbiao的博客"));
    
        //执行批量新增操作
        int count = userMapper.addUserBatch(userInfoList);
    
        //打印结果
        System.out.println("执行结果：成功新增" + count + "条数据！");
    }

**执行结果：**

![20200728170401638.jpg][]

# 2、批量查询 #

@Autowired
    private UserMapper userMapper;
    
    /**
     * 批量获取用户
     * @author pan_junbiao
     */
    @Test
    public void getUserBatch()
    {
        //用户编号数组
        int[] userIds = new int[]{1,2,3,4,5};
    
        //执行批量获取操作
        List<UserInfo> userInfoList = userMapper.getUserBatch(userIds);
    
        //打印结果
        userInfoList.stream().forEach(System.out::println);
    }

**执行结果：**

![20200728171531107.png][]

# 3、批量删除 #

@Autowired
    private UserMapper userMapper;
    
    /**
     * 批量删除用户
     * @author pan_junbiao
     */
    @Test
    public void deleteUserBatch()
    {
        //用户编号数组
        int[] userIds = new int[]{1,2,3,4,5};
    
        //执行批量删除操作
        int count = userMapper.deleteUserBatch(userIds);
    
        //打印结果
        System.out.println("执行结果：成功删除" + count + "条数据！");
    }

**执行结果：**

![20200728172021305.jpg][]

[20200728170401638.jpg]: /images/20210724/6fb91f464fab4e3db9b7b5a5fa640649.png
[20200728171531107.png]: /images/20210724/210d6e1bc18a429198a1934fe6a30ad0.png
[20200728172021305.jpg]: /images/20210724/beb9a8b6cbce4a73846c3614cec2d8bb.png