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

在项目的开发中，我们经常需要对数据进行批量的操作，如：批量新增、批量删除等。下面将介绍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.youkuaiyun.com/pan_junbiao"));
    userInfoList.add(new UserInfo("pan_junbiao的博客_03","您好，欢迎访问 pan_junbiao的博客"));
    userInfoList.add(new UserInfo("pan_junbiao的博客_04","https://blog.youkuaiyun.com/pan_junbiao"));
    userInfoList.add(new UserInfo("pan_junbiao的博客_05","您好，欢迎访问 pan_junbiao的博客"));

    //执行批量新增操作
    int count = userMapper.addUserBatch(userInfoList);

    //打印结果
    System.out.println("执行结果：成功新增" + count + "条数据！");
}

执行结果：

 

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);
}

执行结果：

 

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 + "条数据！");
}

执行结果：