批量新增
1.方式一(常用)
<!-- 批量新增-->
<insert id="batchSave" parameterType="java.util.List">
INSERT INTO lp_user_test_batch
(
id,
user_id,
user_name,
user_age,
type,
create_time,
update_time
)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id,jdbcType=BIGINT},
#{item.userId,jdbcType=VARCHAR},
#{item.userName,jdbcType=VARCHAR},
#{item.userAge,jdbcType=INTEGER},
#{item.type,jdbcType=INTEGER},
#{item.createTime,jdbcType=TIMESTAMP},
#{item.updateTime,jdbcType=TIMESTAMP}
)
</foreach>
</insert>
测试结果
数量 耗时
1000 1469ms
2000 2534ms
3000 2613ms
4000 3549ms
5000 4733ms
8000 5761ms
10000 6055ms
2.方式二
批量新增或更新方式
注:需要给唯一主键添加唯一索引,update才会生效
<!-- 批量新增或更新-->
<insert id="batchSaveOrUpdate" parameterType="java.util.List">
INSERT INTO lp_user_test_batch
(
id,
user_id,
user_name,
user_age,
type,
create_time,
update_time
)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id,jdbcType=BIGINT},
#{item.userId,jdbcType=VARCHAR},
#{item.userName,jdbcType=VARCHAR},
#{item.userAge,jdbcType=INTEGER},
#{item.type,jdbcType=INTEGER},
#{item.createTime,jdbcType=TIMESTAMP},
#{item.updateTime,jdbcType=TIMESTAMP}
)
</foreach>
ON DUPLICATE KEY UPDATE
user_name = VALUES(user_name),
user_age = VALUES(user_age),
type = VALUES(type),
update_time = VALUES(update_time)
</insert>
测试结果
数量 耗时
1000 1692ms
2000 2346ms
3000 3249ms
4000 3443ms
5000 3999ms
8000 6460ms
10000 7053ms
3.方式三
单条sql+批量方式的SqlSession
<insert id="insert" >
INSERT INTO lp_user_test_batch
(
id,
user_id,
user_name,
user_age,
type,
create_time,
update_time
)
values
(
#{id,jdbcType=BIGINT},
#{userId,jdbcType=VARCHAR},
#{userName,jdbcType=VARCHAR},
#{userAge,jdbcType=INTEGER},
#{type,jdbcType=INTEGER},
#{createTime,jdbcType=TIMESTAMP},
#{updateTime,jdbcType=TIMESTAMP}
)
</insert>
@Resource(name = "sqlSessionFactory")
private SqlSessionFactory sqlSessionFactory;
/**
* 利用 MyBatis 批处理特性,批量提交
*/
public void batchInsert(List<UserTestBatchDO> testBatchDAOList) {
//集合非空
if (CollectionUtils.isEmpty(testBatchDAOList)) {
return;
}
//批处理方式 SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
//获得对应的Mapper
UserTestBatchDOMapper userTestBatchDOMapper = sqlSession.getMapper(UserTestBatchDOMapper.class);
try {
for (UserTestBatchDO testBatchDO : testBatchDAOList) {
userTestBatchDOMapper.insert(testBatchDO);
}
//统一提交
sqlSession.commit();
} catch (Exception e) {
//没有提交的数据可以回滚
sqlSession.rollback();
} finally {
//关闭 sqlSession
sqlSession.close();
}
}
测试结果
数量 耗时
1000 2174ms
2000 3104ms
3000 3801ms
4000 4991ms
5000 5930ms
8000 8151ms
10000 8252ms
批量修改
1.方式一
批量新增或更新方式
注:需要给唯一主键添加唯一索引,update才会生效
<!-- 批量新增或更新-->
<insert id="batchSaveOrUpdate" parameterType="java.util.List">
INSERT INTO lp_user_test_batch
(
id,
user_id,
user_name,
user_age,
type,
create_time,
update_time
)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id,jdbcType=BIGINT},
#{item.userId,jdbcType=VARCHAR},
#{item.userName,jdbcType=VARCHAR},
#{item.userAge,jdbcType=INTEGER},
#{item.type,jdbcType=INTEGER},
#{item.createTime,jdbcType=TIMESTAMP},
#{item.updateTime,jdbcType=TIMESTAMP}
)
</foreach>
ON DUPLICATE KEY UPDATE
user_name = VALUES(user_name),
user_age = VALUES(user_age),
type = VALUES(type),
update_time = VALUES(update_time)
</insert>
测试结果
注:当前表内数据行数 10000
数量 耗时
1000 1505ms
2000 2617ms
3000 2922ms
4000 3292ms
5000 3443ms
8000 4832ms
10000 4886ms
优点:速度快
缺点:使用特殊语法 on duplicate key update 语法 增加sql难度性
2.方式二
单条sql+批量方式的SqlSession
<update id="updateByUserId" >
UPDATE lp_user_test_batch
SET
user_name = #{userName,jdbcType=VARCHAR},
user_age = #{userAge,jdbcType=INTEGER},
type = #{type,jdbcType=INTEGER},
update_time = #{updateTime,jdbcType=TIMESTAMP}
WHERE user_id = #{userId,jdbcType=VARCHAR}
</update>
@Resource(name = "sqlSessionFactory")
private SqlSessionFactory sqlSessionFactory;
/**
* 利用 MyBatis 批处理特性,批量更新
*/
public void batchUpdate(List<UserTestBatchDO> testBatchDAOList) {
//集合非空
if (CollectionUtils.isEmpty(testBatchDAOList)) {
return;
}
//批处理方式 SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
//获得对应的Mapper
UserTestBatchDOMapper userTestBatchDOMapper = sqlSession.getMapper(UserTestBatchDOMapper.class);
try {
for (UserTestBatchDO testBatchDO : testBatchDAOList) {
userTestBatchDOMapper.updateByUserId(testBatchDO);
}
//统一提交
sqlSession.commit();
//清理缓存,防止溢出
sqlSession.clearCache();
} catch (Exception e) {
//没有提交的数据可以回滚
sqlSession.rollback();
} finally {
//关闭 sqlSession
sqlSession.close();
}
}
测试结果
注:当前表内数据行数 10000
数量 耗时
1000 3158ms
2000 4324ms
3000 6466ms
4000 7572ms
5000 9812ms
8000 12846ms
10000 16088ms
优点:通过日志观察,生成一条执行语句sql ,多行参数,统一commit
缺点:比方式一速度略慢
3.方式三
java程序循环调用单条修改语句
执行方式:一条sql ,程序循环执行
for (UserTestBatchDO userTestBatch : testBatchDAOList) {
userTestBatchDOMapper.updateByUserId(userTestBatch);
}
1
2
3
测试结果
注:当前表内数据行数 10000
数量 耗时
1000 33907ms
2000 42866ms
3000 89675ms
5000 104833ms
优点:方便单条控制提交事物
缺点:耗时,耗性能、每一次循环都需要与数据库交互一次
4.方式四
Mybatis foreach 循环
执行方式:拼接好一条sql,后执行
<!-- 接收list参数,循环着组装sql语句,注意for循环的写法
separator=";" 代表着每次循环完,在sql后面放一个分号 -->
<update id="updateForeachByUserId" parameterType="java.util.List">
<foreach collection="list" item="item" separator=";">
UPDATE lp_user_test_batch
SET
user_name = #{item.userName,jdbcType=VARCHAR},
user_age = #{item.userAge,jdbcType=INTEGER},
type = #{item.type,jdbcType=INTEGER},
update_time = #{item.updateTime,jdbcType=TIMESTAMP}
WHERE user_id = #{item.userId,jdbcType=VARCHAR}
</foreach>
</update>
测试结果
注:当前表内数据行数 10000
数量 耗时
1000 2671ms
2000 4170ms
3000 4514ms
4000 5152ms
5000 6572ms
8000 10209ms
10000 12158ms
优点:生成多条sql,统一执行,与数据库交互次数少
缺点 : 生成多条拼接的update语句,update语句比较多,量大了就有可能造成sql阻塞。
5.方式五
mybatis sql 使用 case when
<!-- 批量更新第二种方法,通过 case when语句变相的进行批量更新 -->
<update id="updateCaseByUserId" parameterType="java.util.List">
update lp_user_test_batch
<trim prefix="set" suffixOverrides=",">
<!-- 拼接case when 这是另一种写法 -->
<trim prefix="user_name =case" suffix="end,">
<foreach collection="list" item="item">
<if test="item.userName!=null">
when user_id = #{item.userId,jdbcType=VARCHAR} then #{item.userName,jdbcType=VARCHAR}
</if>
</foreach>
</trim>
<trim prefix="user_age =case" suffix="end,">
<foreach collection="list" item="item">
<if test="item.userAge!=null">
when user_id = #{item.userId,jdbcType=VARCHAR} then #{item.userAge,jdbcType=INTEGER}
</if>
</foreach>
</trim>
<trim prefix="type =case" suffix="end,">
<foreach collection="list" item="item">
<if test="item.type!=null">
when user_id = #{item.userId,jdbcType=VARCHAR} then #{item.type,jdbcType=INTEGER}
</if>
</foreach>
</trim>
<trim prefix="update_time =case" suffix="end,">
<foreach collection="list" item="item">
<if test="item.type!=null">
when user_id = #{item.userId,jdbcType=VARCHAR} then #{item.updateTime,jdbcType=TIMESTAMP}
</if>
</foreach>
</trim>
</trim>
<where>
user_id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.userId,jdbcType=VARCHAR}
</foreach>
</where>
</update>
测试结果
注:当前表内数据行数 10000
数量 耗时
1000 3201ms
2000 4804ms
3000 6833ms
4000 8554ms
5000 11688ms
8000 26501ms
10000 34724ms
缺点:
xml中的循环体有点多,每一个case when 都要循环一遍list集合,所以大批量拼sql的时候会比较慢。
生成多条拼接sql,sql长度过长,容易sql超长引起报错 Packet for query is too large。
MySQL 最大允许的 packet
Mybatis批处理介绍
Mybatis内置执行器类型ExecutorType有3种
分别是
ExecutorType.SIMPLE: 不做特殊处理,为每个语句的执行创建一个新的预处理语句。
ExecutorType.REUSE: 可以复用预处理语句。
ExecutorType.BATCH:可以批量执行所有更新语句
SIMPLE与BATCH(批量)对比
默认的是simple,该模式下它为每个语句的执行创建一个新的预处理语句,单条提交sql;
而batch模式重复使用已经预处理的语句,并且批量执行所有更新语句,显然batch性能将更优;但是批量模式无法返回自增主键
测试环境配置
系统:win 8.1
Mysql : 5.7
java环境:junit