Mybatis 批量操作(新增和修改)

最新推荐文章于 2024-05-21 18:07:54 发布
最新推荐文章于 2024-05-21 18:07:54 发布
阅读量4k 收藏 25
点赞数 4
文章标签： MyBatis 批量操作 性能测试 SQL ExecutorType
原文链接：https://blog.csdn.net/qq_38011415/article/details/84928791
版权

批量新增

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>

1234567891011121314151617181920212223242526

测试结果

数量耗时
10001469ms
20002534ms
30002613ms
40003549ms
50004733ms
80005761ms
100006055ms

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>

12345678910111213141516171819202122232425262728293031

测试结果

数量耗时
10001692ms
20002346ms
30003249ms
40003443ms
50003999ms
80006460ms
100007053ms

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>
12345678910111213141516171819202122
@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();
    }
}
1234567891011121314151617181920212223242526272829

测试结果

数量耗时
10002174ms
20003104ms
30003801ms
40004991ms
50005930ms
80008151ms
100008252ms

批量修改

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>

12345678910111213141516171819202122232425262728293031

测试结果
注：当前表内数据行数 10000

数量耗时
10001505ms
20002617ms
30002922ms
40003292ms
50003443ms
80004832ms
100004886ms

优点：速度快
缺点：使用特殊语法 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>

12345678910
@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();
    }
}

1234567891011121314151617181920212223242526272829303132

测试结果
注：当前表内数据行数 10000

数量耗时
10003158ms
20004324ms
30006466ms
40007572ms
50009812ms
800012846ms
1000016088ms

优点：通过日志观察，生成一条执行语句sql ,多行参数，统一commit
缺点：比方式一速度略慢

3.方式三

java程序循环调用单条修改语句
执行方式：一条sql ,程序循环执行

for (UserTestBatchDO userTestBatch : testBatchDAOList) {
    userTestBatchDOMapper.updateByUserId(userTestBatch);
}
123

测试结果
注：当前表内数据行数 10000

数量耗时
100033907ms
200042866ms
300089675ms
5000104833ms

优点：方便单条控制提交事物
缺点：耗时，耗性能、每一次循环都需要与数据库交互一次

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>

1234567891011121314

测试结果
注：当前表内数据行数 10000

数量耗时
10002671ms
20004170ms
30004514ms
40005152ms
50006572ms
800010209ms
1000012158ms

优点：生成多条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>

12345678910111213141516171819202122232425262728293031323334353637383940414243444546

测试结果
注：当前表内数据行数 10000

数量耗时
10003201ms
20004804ms
30006833ms
40008554ms
500011688ms
800026501ms
1000034724ms

缺点:
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
注：环境不同可能会引起耗时存在差异。

总结

单次批量操作不要过大，批量新增使用方式一，批量更新方式一与方式二经过测试是最优的选择
也可以根据安全方面综合考虑，选择适合的方式。

关注程序员小强公众号更多编程趣事，知识心得与您分享
在这里插入图片描述

yanl_differ_jackyun
关注
  • 4
    点赞
  • 踩
  • 25
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MyBatis 系列2 -- 增加、删除、修改操作
小白的博客
07-17 1037
mybatis操作数据库
MyBatis实现批量修改的三种办法
lili的点点滴滴
11-23 3万+
MyBatis实现批量修改
mybatis实现批量插入或更新
二掌柜的博客
05-08 6001
主要实现mybatis高效批处理的思路和方法
mybatis批量更新与插入
最新发布
liqinglonguo的博客
05-21 1261
foreach模式批量插入模式与MyBatis中Batch模式对比差异：1.二者速度差异不大，for模式使用简单，Batch模式使用复杂2.如果mysql自身对接收数据量有大小限制，建议使用Batch模式。
mybatis 时间_44天 MyBatis 学习.
weixin_39531780的博客
11-21 329
这两天开学了,事情有点多 所以鸽了两天.第59次(mybatis)学习主题：mybatis学习目标：1 掌握typeAliases的作用2 掌握mybatis如何传递参数3 掌握mybatis对事务的管理4 掌握mybatis的crud5 掌握接口绑定对应作业typeAliases标签配置别名typeAliases标签有什么作用? 如何使用?功能; 用于java 类型定义别名,方便配置文件中使用....
mybatis 新增语句_mybatis的select、insert、update、delete语句
weixin_39828193的博客
12-22 857
一、selectSELECT ST.STUDENT_ID,ST.STUDENT_NAME,ST.STUDENT_SEX,ST.STUDENT_BIRTHDAY,ST.CLASS_IDFROM STUDENT_TBL STWHERE ST.STUDENT_ID = #{studentID}这条语句就叫做‘getStudent，有一个String参数，并返回一个StudentEntity类型的对象。注...
Mybatis批量插入的四种方式
热门推荐
一零贰肆的博客
06-13 3万+
测试结果还是显而易见的，循环插入>批量插入>BatchType插入>JDBC批量插入
Mybatis之@ResultMap，@Results，@Result注解
q蓝色Tu耳其
10-08 2036
文章目录Mybatis注解之@Results、@Result、@ResultMap问题方法一方法二 Mybatis注解之@Results、@Result、@ResultMap 问题 在使用mybatis时发现，mybatis能自动匹配实体名和数据库字段名相同的字段。当有实体名与数据库的字段名不同时该如何解决？？ 数据库的表对应的列名： springboot项目中建的实体类为： public class MapModel { private Long key; private String
Mybatis批量更新三种方式的实现
08-26
Mybatis批量更新三种方式的实现 Mybatis是一款流行的持久层框架，它提供了强大的数据库交互能力。其中，批量更新是一种常见...Mybatis批量更新三种方式的实现都有其优缺点，选择哪种方式取决于具体的业务场景和需求。
Mybatis Plus 自定义批量插入或批量更新（根据唯一索引）
01-08
Mybatis Plus 是 Mybatis 的一个扩展工具，它在 Mybatis 的基础上提供了许多便捷的功能，包括自动 CRUD 操作、条件构造器、批量操作等。在实际的项目开发中，批量插入和批量更新是常见的数据处理场景，尤其是在处理...
SpringBoot整合Mybatis连接SQL Server 跨库批量插入
01-27
本篇将深入探讨如何在Spring Boot项目中整合Mybatis，实现对SQL Server数据库的跨库批量插入操作。 首先，我们需要在Spring Boot项目中引入相关的依赖。对于Mybatis的支持，我们需要添加Mybatis和其Spring Boot ...
ssm项目实现批量新增（批量 修改，删除都可以）.docx
01-31
本篇文章将详细介绍如何在SSM项目中实现批量新增，同时也适用于批量修改和删除操作。 首先，我们需要在前端JavaScript部分声明一个数组，用于存储待批量处理的数据。可以通过push方法动态添加元素，无论是预先定义...
Mybatis3.42
02-15
Mybatis 是一个流行的开源持久层框架，用于简化Java应用程序中的数据库操作。Mybatis3.4.2 是这个框架的一个更新版本，它包含了之前版本的改进、修复和新特性。在这个版本中，开发者可以期待更高效、更稳定以及更...
Mybatis批量修改
陌上胥黎
09-28 700
1.修改的字段值都是一样的，id不同<update id="batchUpdate" parameterType="String"> update cbp_order set status=1 where id in <foreach item="id" collection="array" open="(" separator="," close=")">
MySQL根据某一集合批量修改集合对应的不同值
weixin_44609018的博客
03-02 521
1、foreach标签 <foreach collection="list" item="item" index="index" open="" close="" separator=";"> update table_test <set> name = #{item.name}, date_updated =now() </set> where id= #{
mysql批量修改语句
weixin_30488313的博客
05-15 1309
1.第一种情况根据自己查询出来的语句来批量修改,注意在最外面要在包一层查询UPDATE '表名'SET '你要修改的字段'WHEREid in (SELECT tab.'第一层查询的字段'FROM (SELECT'查询出来的字段'FROM'表名'WHERE'根据实际情况加入条件') tab) 2.第二种情况传入一个集合来批量修改,这里展示在idea里面的写法(projectIds集合里面只装了一...
mybatis添加新用户
qq_40285302的博客
04-21 1731
1、配置数据库映射文件（User.xml） 在其中加入如下代码： [html] view plain copy &lt;!--   添加用户   parameterType:指定输入的参数类型是pojo   #{}中指定pojo的属性名，接收到pojo对象的属性值，mybatis也是通过OGNL来获取对象的属性值    --&gt;   &lt;insert id="insertU...
MyBatis批量update更新
cctvyuzhou的博客
11-12 841
最近业务需要同步远程数据，而远程数据只开放HTTP接口，返回JSON，用ETL工具比较难做，还是决定写个简单程序。看到好多文章写到批量更新时提到如下格式会报错 <update id="updateBatch" parameterType="java.util.List"> <foreach item="item" collection="list" separator="...
mybatis拦截器，动态修改sql语句
zsj777的专栏
08-23 2万+
1、拦截器代码 package com.ps.uzkefu.common; /** * Author:ZhuShangJin * Date:2018/8/23 */ import java.sql.SQLException; import java.util.Properties; import org.apache.commons.lang3.StringUtils; import...
达梦数据库在mybatis中批量进行新增或修改
05-26
在MyBatis中批量进行新增或修改，可以通过使用foreach标签和批量操作语句来实现。以下是一个基本的示例： ```xml <insert id="batchInsert" parameterType="java.util.List"> insert into user (id, name, age) values <foreach collection="list" item="item" separator=","> (#{item.id}, #{item.name}, #{item.age}) </foreach> </insert> <update id="batchUpdate" parameterType="java.util.List"> <foreach collection="list" item="item" separator=";"> update user set name=#{item.name}, age=#{item.age} where id=#{item.id} </foreach> </update> ``` 这里的`batchInsert`和`batchUpdate`分别是插入和更新操作的ID。`parameterType`指定了传递到操作中的参数类型，这里是一个`java.util.List`对象。在`foreach`标签中，`collection`属性指定要迭代的列表，`item`属性指定迭代时使用的变量名，`separator`属性指定每个元素之间的分隔符。 对于插入操作，可以使用`values`子句将每个对象的字段值作为参数传递给操作。对于更新操作，可以在`foreach`标签中构建一个批量的更新语句，将每个对象的字段值作为参数传递给操作。 需要注意的是，这里的示例仅适用于插入和更新单个表中的数据。如果需要批量操作多个表，需要在操作中使用多个`foreach`标签来处理。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值