boot编写批量新增 spring_SpringBoot入门-Mybatis通过注解批量插入/删除

一、概述

我们在写Mapper的时候，经常会通过注解的方式来写SQL语句，像下面这样。这要求我们传递的参数为一个具体的对象。

@Insert("INSERT INTO student (name, sex, addr) VALUES (#{name}, #{sex}, #{addr})")

int insert(Student stu);

但是，如果需要批量插入List studentList；不可能遍历studentList并依次执行插入语句，这样效率太差。

还有一种是配置Mybatis的xml文件，反正题主觉得那个配置好复杂。有没有还是通过注解的方式，执行批量操作呢？

二、批量执行

我们回想下SQL语句，插入一条记录：

INSERT INTO student (name, sex, addr) VALUES ("LiMing", 0, "Beijing");

批量插入：

INSERT INTO student (name, sex, addr) VALUES ("LiMing", 0, "Beijing"), ("LiNing", 0, "Shanghai");

根据 id 删除一条记录：

DELETE FROM student WHERE id = 1;

根据ids批量删除：

DELETE FROM student WHERE id IN (1, 2, 3);

三、方法

既然可以通过SQL语句批量执行，那我们可以修改注解。这里需要通过自定义Provider来实现。

@Component

@Mapper

public interface StudentMapper {

@Insert("INSERT INTO student (name, sex, addr) VALUES (#{name}, #{sex}, #{addr})")

int insert(Student stu);

@InsertProvider(type = Provider.class, method = "batchInsert")

int batchInsert(List students);

@Select("SELECT * FROM student WHERE id = #{id}")

Student selectById(@Param("id") int id);

@DeleteProvider(type = Provider.class, method = "batchDelete")

int batchDelete(List students);

@Select("SELECT * FROM student")

List selectAll();

class Provider {

/* 批量插入 */

public String batchInsert(Map map) {

List students = (List) map.get("list");

StringBuilder sb = new StringBuilder();

sb.append("INSERT INTO student (name,sex,addr) VALUES ");

MessageFormat mf = new MessageFormat(

"(#'{'list[{0}].name}, #'{'list[{0}].sex}, #'{'list[{0}].addr})"

);

for (int i = 0; i < students.size(); i++) {

sb.append(mf.format(new Object[] {i}));

if (i < students.size() - 1)

sb.append(",");

}

return sb.toString();

}

/* 批量删除 */

public String batchDelete(Map map) {

List students = (List) map.get("list");

StringBuilder sb = new StringBuilder();

sb.append("DELETE FROM student WHERE id IN (");

for (int i = 0; i < students.size(); i++) {

sb.append("'").append(students.get(i).getId()).append("'");

if (i < students.size() - 1)

sb.append(",");

}

sb.append(")");

return sb.toString();

}

}

}