springboot实现对数据库批量增删（List里的内容）

适用场景：需要将一个List的内容添加到数据库或从数据库删除
0、.xml文件

代码

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="top.ptcc9.mapper.UserMapper">
    <insert id="insertFunctions">
        <if test="list != null and list.size() > 0">
            insert into user_ref_functions
            values
            <foreach collection="list" item="userRefFunctions" index="index" separator=",">
                (#{userRefFunctions.id},#{userRefFunctions.userId},#{userRefFunctions.functionsId},#{userRefFunctions.createTime})
            </foreach>
        </if>
    </insert>

    <delete id="deleteFunctions">
        delete from user_ref_functions
        where functions_id in
        (
        <foreach collection="cancelAuth" item="item" index="index" separator=",">
            #{item}
        </foreach>
        ) and user_id = #{userId}
    </delete>
</mapper>

测试结果：

以下内容供测试使用
1、Controller 这个方法是给数据库授权或者取消授权

@RestController
@RequestMapping(value = "/user")
public class UserController {
    @Resource
    private UserService userService;
@RequestMapping(value = "/doAuth",method = RequestMethod.POST)
    public CommonResult<String> doAuth(@RequestBody DoAuthDto doAuthDto){
        String message = userService.doAuth(doAuthDto);
        //返回值类型大家可以根据自己需求设置
        return CommonResult.build(SUCCESS_DO_AUTH,message);
    }
   }  

2、Dto 接收参数类型

```java
package top.ptcc9.entity.Dto;
import java.util.List;
public class DoAuthDto {
    private String managerId;
    private String userId;
    private List<String> newAuth;
    private List<String> cancelAuth;

    public String getManagerId() {
        return managerId;
    }

    public void setManagerId(String managerId) {
        this.managerId = managerId;
    }

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public List<String> getNewAuth() {
        return newAuth;
    }

    public void setNewAuth(List<String> newAuth) {
        this.newAuth = newAuth;
    }

    public List<String> getCancelAuth() {
        return cancelAuth;
    }

    public void setCancelAuth(List<String> cancelAuth) {
        this.cancelAuth = cancelAuth;
    }
}

3、service层及service实现层

package top.ptcc9.service;
import top.ptcc9.entity.Dto.DoAuthDto;
public interface UserService {
    String doAuth(DoAuthDto doAuthDto);
}

package top.ptcc9.service.impl;
import org.springframework.stereotype.Service;
import top.ptcc9.entity.Do.UserRefFunctions;
import top.ptcc9.entity.Dto.DoAuthDto;
import top.ptcc9.mapper.UserMapper;
import top.ptcc9.service.UserService;
import top.ptcc9.utils.CommonUtil;
import javax.annotation.Resource;
import java.util.LinkedList;
import java.util.List;


@Service
public class UserServiceImpl implements UserService {
    @Resource
    private UserMapper userMapper;
    @Resource
    private CommonUtil commonUtil;

    @Override
    public String doAuth(DoAuthDto doAuthDto) {
        int newAuthCount;
        int cancelAuthCount;
        List<String> newAuth = doAuthDto.getNewAuth();
        List<String> cancelAuth = doAuthDto.getCancelAuth();
        if (commonUtil.isEmpty(newAuth)){
            newAuthCount = 0;
        }else {
            LinkedList<UserRefFunctions> list = new LinkedList<>();
            for (String item : newAuth) {
                UserRefFunctions userRefFunctions = new UserRefFunctions();
                userRefFunctions.setId(commonUtil.getSimpleUUID());
                userRefFunctions.setUserId(doAuthDto.getUserId());
                userRefFunctions.setFunctionsId(item);
                userRefFunctions.setCreateTime(commonUtil.getSimpleDateTime());
                list.add(userRefFunctions);
            }
            newAuthCount = userMapper.insertFunctions(list);
        }
        if (commonUtil.isEmpty(cancelAuth)){
            cancelAuthCount = 0;
        }else {
            cancelAuthCount = userMapper.deleteFunctions(cancelAuth,doAuthDto.getUserId());
        }

        if (newAuthCount + cancelAuthCount != newAuth.size() + cancelAuth.size()) {
            // TODO: 2022/5/8 回滚
        }

        return "授权成功，新增授权"+ newAuthCount +"条，取消授权"+ cancelAuthCount +"条";
    }
}

4、mapper层

package top.ptcc9.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import top.ptcc9.entity.Do.UserRefFunctions;

import java.util.List;

@Mapper
@Repository
public interface UserMapper {
    int insertFunctions (@Param("list")List<UserRefFunctions> list);

    int deleteFunctions (@Param("cancelAuth")List<String> cancelAuth,@Param("userId")String userId);

}