Mybatis批量插入或更新数据
对于大量的数据，使用批量插入或修改可以提高效率。原因是批量添加或修改是执行一条sql语句，传入多个值，可以减少与数据库的访问次数，从而会提高效率。下面分别介绍Oracle和MySQL的用法：
1.Oracle批量插入数据
对于集合类型的数据，在插入时会使用mybatis的<foreach>标签，那么正确的用法如下：
<insert id="insertUserBatch">
insert into user(id,name,password,addr)
select user_seq.nextval,a.* from(
<foreach collection="list" item="item" open="(" close=")" separator="union all">
select
#{item.name},
#{item.password},
#{item.addr}
from dual
</foreach>
) a
</insert>
以上语句是向user表循环查询数据，传递的参数是List<User>类型的集合。
需要注意是的，在使用时，分隔符separator必须是union all。
2.MySQL批量插入数据
2.1 使用foreach
对于集合类型的数据，在插入时会使用mybatis的<foreach>标签，那么mysql的批量插入数据sql如下：
<insert id="insertBatch">
insert into user(name,password,addr)
values
<foreach collection="list" item="item" separator=",">
(
#{item.name},
#{item.password},
#{item.addr}
)
</foreach>
</insert>
和oracle的语句相比，没有指定主键，原因是主键id使用了自增。在foreach中里面的两个括号不能省略，要使用逗号作为分隔符。
批量处理的java代码如下：
public void test(List<User> list) { Integer maxSize = 2000;//每次处理的数据条数，也是分批处理 mysql支持单个sql插入中最大values是2000 Integer queryCnt = list.size() % maxSize == 0 ? list.size() / maxSize : (list.size() / maxSize) + 1; for (int i = 0; i < queryCnt; i++) { Integer endIndex = (i + 1) * maxSize; if (endIndex > list.size()) { endIndex = list.size(); } //集合截取 List<User> tempList = new ArrayList<>(list.subList(i * maxSize, endIndex)); userDao.insertBatch(tempList); } }
对于foreach而言，当表的列数在20列以上，一次性插入的行数在5000条以上时，整个插入的过程比较耗时，预计在10分钟以上。因此，foreach可使用在一次插入20~50条数据的场景中。
2.2使用批处理（推荐）
foreach对大批量多列的数据插入效率不高，这是可采用mybatis自带的批量插入方式。
1）在配置文件中开始批量插入
将rewriteBatchedStatements设置为true，这步必不可少，否则批量插入不生效
spring.datasource.url=jdbc:mysql://localhost:3306/db2020?useUnicode=true&rewriteBatchedStatements=true
2）生成数据，进行批量插入
@Test public void test3() { //数据生成 List<User> list = createData(30000); StopWatch stopWatch = new StopWatch(); //使用批处理 stopWatch.start(); SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false); try { UserDao userDao = sqlSession.getMapper(UserDao.class); list.stream().forEach(user-> userDao.insert(user)); sqlSession.commit();//批量提交 } catch (Exception e) { log.error("发生异常:{}", e); } finally { sqlSession.clearCache();//清除缓存 sqlSession.close();//关闭连接 } stopWatch.stop(); log.info("批处理耗时：{} ms", stopWatch.getTotalTimeMillis()); } private List<User> createData(int size) { List<User> list = new ArrayList<>(); User user; for (int i = 0; i < size; i++) { user = new User(); user.setName("小王" + i); user.setPassword("983" + i); user.setUsername("zhangs" + i); user.setAge(20 + i % 5); user.setImgurl("www.baidu.com"); list.add(user); } return list; }
本人亲测，3000条数据使用批量插入不到1秒即可完成。其中userDao中的insert方法就是单个数据的插入方法
3.Mybatis的trim标签
3.1功能属性表
trim标签的功能属性如下表：
| 属性名 | 说明 |
| prefix | 在sql语句前面拼接的字符串 |
| suffix | 在sql语句后面拼接的字符串 |
| prefixOverrides | 去除sql语句前面指定的字符或者关键字 |
| suffixOverrides | 去除sql语句后面指定的字符或者关键字 |
3.2去除多余的and关键字
1）先看下面最原始的sql：
<select id="findActiveBlogLike"> SELECT * FROM BLOG WHERE <if test="state != null"> state = #{state} </if> <if test="title != null"> AND title like #{title} </if> <if test="author != null and author.name != null"> AND author_name like #{author.name} </if> </select>
若这些条件没有一个能匹配上，那么这条 SQL 会变成：
SELECT * FROM BLOG
WHERE
2）这种sql肯定会导致异常的出现。如果仅仅第二个条件匹配，那么这条 SQL 最终的样子:
SELECT * FROM BLOG WHERE AND title like '%aaa%'
这种sql就多了一个and关键字，也会导致异常的发生。
3）对于mybatis而言，可以使用where标签来解决上述的问题，其sql如下：
<select id="findActiveBlogLike"> SELECT * FROM BLOG <where> <if test="state != null"> state = #{state} </if> <if test="title != null"> AND title like #{title} </if> <if test="author != null"> AND author like #{author} </if> </where> </select>
4）除此之外，mybatis还提供了另一个标签trim，可以用其来去除多余的关键字，上一步的代码使用trim如下：
<select id="findActiveBlogLike"> SELECT * FROM BLOG <trim prefix="WHERE" prefixOverrides="AND"> <if test="state != null"> state = #{state} </if> <if test="title != null"> AND title like #{title} </if> <if test="author != null"> AND author like #{author} </if> </trim> </select>
上述的sql中，当条件中没有匹配的数据时，不会添加where关键字；当匹配到条件时，会在前面拼接where关键字，若匹配的条件前面有and，那么会自动去掉and关键字。举例说明，条件的内容只作为说明：
A：当state、title、author都为NULL时，查询语句是
SELECT * FROM BLOG
B：当title、author都为NULL而state不为NULL时，查询语句是：
SELECT * FROM BLOG where state = 1
C：当state、author都为NULL而title不为NULL时，去除了多余的and关键字，查询语句是：
SELECT * FROM BLOG where title like '%aaa%'
D：当state、title都不为NULL而author为NULL时，查询语句是：
SELECT * FROM BLOG where state = 1 and title like '%aaa%'
3.3去除多余的逗号
1）先看下面的sql：
<insert id="insertBlogSelective"> insert into BLOG ( <if test="state != null"> state</if> <if test="title != null"> title, </if> <if test="author != null"> author </if> ) values( <if test="state != null"> #{state}, </if> <if test="title != null"> #{title}, </if> <if test="author != null"> #{author} </if> ) </insert >
若这些条件中author没有匹配上，那么这条 SQL 会变成：
insert into BLOG(state,title,) values(1,'mysql入门',);
明显看出多了两个逗号，会导致异常。使用trim标签解决如下：
<insert id="insertBlogSelective"> insert into BLOG <trim prefix="(" suffix=")" suffixOverrides=","> <if test="state != null"> state </if> <if test="title != null"> title, </if> <if test="author != null"> author </if> </trim> <trim prefix="values(" suffix=")" suffixOverrides=","> <if test="state != null"> #{state}, </if> <if test="title != null"> #{title}, </if> <if test="author != null"> #{author} </if> </trim> </insert >
上述的sql中，当条件中没有匹配的数据时，不会插入对应的列数据。主要是使用suffixOverrides属性，当条件不满足时会删除结尾多余的逗号。
4.Oracle批量修改数据
当数据量很大时，使用批量修改可以提高效率。看下面对用户信息进行批量修改：
<update id="updateBatch"> update user t <trim prefix="set" suffixOverrides=","> <trim prefix="name = case" suffix="end ,"> <foreach collection="list" item="item"> <if test="item.name != null "> when t.id = #{item.id} then #{item.name} </if> </foreach> </trim> <trim prefix="password = case" suffix="end ,"> <foreach collection="list" item="item"> <if test="item.password != null "> when t.id = #{item.id} then #{item.password} </if> </foreach> </trim> <trim prefix="addr = case" suffix="end ,"> <foreach collection="list" item="item"> <if test="item.addr != null "> when t.id = #{item.id} then #{item.addr} </if> </foreach> </trim> </trim> where id in <foreach collection="list" item="item" open="(" close=")" separator=","> #{item.id} </foreach> </update>
使用了trim标签，生成的完整的sql如下，为了说明方便仅对三条数据进行修改，批量的数据是类似的：
update user t set name = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end , password = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end , addr = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end where id in ( ? , ? , ? )
在上述的sql中，使用了两层的trim标签，外层的标签添加了前缀"set"关键字，另外如果有多余的逗号则会删除。内层的trim对应一个字段，当此字段不为null时才会包含内部的代码；也使用了case when关键字。下面对几种情况进行分析：
A：name为NULL，其他属性不为NULL时，实际的语句如下：
update user t set password = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end , addr = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end where id in ( ? , ? , ? )
当属性为NULL时，此字段便不会被添加到sql语句当中，上述这种情况就是很好的说明。
B：password为NULL，其他属性不为NULL时，实际的语句
update user t set name = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end , addr = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end where id in ( ? , ? , ? )
C：addr为NULL，其他属性不为NULL时，实际的语句
update user t set name = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end , password = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end where id in ( ? , ? , ? )
由于addr的前面有其他的trim，那么当addr为NULL时，就会自动删除关键字'end"后面的逗号。
5.MySQL批量修改数据
MySQL的修改语法和oracle的语法基本类似，批量修改的语句可直接参考oracle的批量修改。
