批量操作sql语句_deletebatch的sql语句

批量删除

	<delete id="deleteUserPostById" parameterType="Long">
 		delete from sys_user_post where user_id in
 		<foreach collection="array" item="userId" open="(" separator="," close=")">
 			#{userId}
        </foreach> 
 	</delete>
	
1
2
3
4
5
6
7

批量逻辑删除

	//mapper.xml文件
	<delete id="deleteFileObjectByIds" parameterType="Long">
        update file_object set del_flag = '1',update_time =CURRENT_TIMESTAMP
        where id in
        <foreach collection="array" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    
	//Ctroller
	@GetMapping("/batchDelete")
    public AjaxResult projectFilesBatchDelete(Long[] ids)
    {
        try {
            //遍历前端传入的ids数组
            for(Long id : ids){
                FileObject fileObject = fileObjectMapper.selectFileObjectById(id);
                //删除minio中的文件
                minioClient.removeObject(MINIO_BUCKET_PF,fileObject.getName());
            }
            fileObjectMapper.deleteFileObjectByIds(ids);
            return AjaxResult.success("删除成功！");
        } catch (MinioException e) {
            e.printStackTrace();
        } catch (NoSuchAlgorithmException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidKeyException e) {
            e.printStackTrace();
        }
        return AjaxResult.error("删除失败！");
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

批量添加（也可用于关联表的数据添加）

	<insert id="batchUserPostById">
		insert into sys_user_post(user_id, post_id) values
		<foreach item="item" index="index" collection="list" separator=",">
			(#{item.userId},#{item.postId})
		</foreach>
	</insert>
1
2
3
4
5
6

批量更新

	<update id="batchUsersById" parameterType="Long">
 		update sys_user set del_flag = '2' where user_id in
 		<foreach collection="array" item="userId" open="(" separator="," close=")">
 			#{userId}
        </foreach> 
 	</update>
1
2
3
4
5
6