SpringBoot入门-Mybatis通过注解批量插入/删除 SpringBoot入门-Mybatis通过注解批

声明：原创文章，转载请注明出处。https://www.jianshu.com/p/7904dd854141
本文代码地址：https://github.com/hawkingfoo/java-web
一、概述
我们在写Mapper的时候，经常会通过注解的方式来写SQL语句，像下面这样。这要求我们传递的参数为一个具体的对象。

@Insert("INSERT INTO student (name, sex, addr) VALUES (#{name}, #{sex}, #{addr})") int insert(Student stu);

但是，如果需要批量插入List studentList；不可能遍历studentList并依次执行插入语句，这样效率太差。
还有一种是配置Mybatis的xml文件，反正题主觉得那个配置好复杂。有没有还是通过注解的方式，执行批量操作呢？
二、批量执行
【SpringBoot入门-Mybatis通过注解批量插入/删除】我们回想下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(); } } }