本文代码地址:https://github.com/hawkingfoo/java-web
一、概述
我们在写Mapper的时候,经常会通过注解的方式来写SQL语句,像下面这样。这要求我们传递的参数为一个具体的对象。
@Insert("INSERT INTO student (name, sex, addr) VALUES (#{name}, #{sex}, #{addr})")
int insert(Student stu);
但是,如果需要批量插入List<Student> 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<Student> students);
@Select("SELECT * FROM student WHERE id = #{id}")
Student selectById(@Param("id") int id);
@DeleteProvider(type = Provider.class, method = "batchDelete")
int batchDelete(List<Student> students);
@Select("SELECT * FROM student")
List<Student> selectAll();
class Provider {
/* 批量插入 */
public String batchInsert(Map map) {
List<Student> students = (List<Student>) 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<Student> students = (List<Student>) 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();
}
}
}
也可以用如下定义(stackoverflow 中的回答我做了扩展):
批量插入:
// 批量插入
@Insert({
"<script>",
"insert into author(id, name)",
"values ",
"<foreach collection='authorList' item='item' separator=','>",
"( #{item.id,jdbcType=INTEGER},#{item.name,jdbcType=VARCHAR} )",
"</foreach>",
"</script>"
})
int insertBatch(@Param("authorList") List<Author> dmoList);
调用使用:
// 批量插入
List<Author> insertList = new ArrayList<>();
Author a = new Author();
a.setId( 4 );
a.setName( "aa" );
insertList.add( a );
Author b = new Author();
b.setId( 5 );
b.setName( "bb" );
insertList.add( b );
authorMapper.insertBatch( insertList );
这种方法有个好处,就是如果数据很大,因为mysql sqlserver等数据库sql 语句 1M 4M 的大小限制,mybatis本身没有这个限制,所以只要控制调用的地方就可以了, 分割 dmoList 为比如大于1000 大小 批量插入就可以了。
批量删除:
// 批量删除
@Delete({
"<script>" +
"delete from author where id in " +
"<foreach item='eid' collection='idList' open='(' separator=',' close=')'>\n" +
"#{eid}\n" +
"</foreach>" +
"</script>"
})
int deleteBatch(@Param("idList") List< Integer > idList );
调用:
List<Integer> list = new ArrayList<>();
Integer i = new Integer(3);
Integer i2 = new Integer( 4 );
list.add( i );
list.add( i2 );
authorMapper.deleteBatch( list );
批量修改:
spring.datasource.url=jdbc:mysql://localhost/yangzm?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true&allowMultiQueries=true
要增加这个项,否则批量更新不能成功: &allowMultiQueries=true
@Update({
"<script>" +
"<foreach collection='authorList' item='item' separator=';' open='' close='\n'>\n" +
" UPDATE author <set> name = #{item.name} </set>" +
" WHERE id = #{item.id} \n" +
"</foreach></script>"
})
int updateBatch(@Param("authorList") List<Author> aList);
调用:
List<Author> aList = new ArrayList<>();
Author a = new Author();
a.setId(4);
a.setName("change");
aList.add(a);
Author b = new Author();
b.setId(5);
b.setName("change2");
aList.add(b);
authorMapper.updateBatch(aList);
现在这几种情况都可以运行了:(^ ^)