Mybatis 批量操作多种实现总结

最新推荐文章于 2024-07-24 17:29:42 发布
置顶 最新推荐文章于 2024-07-24 17:29:42 发布
阅读量2.6k 收藏 6
点赞数
分类专栏： java 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36961226/article/details/112275797
版权
本文探讨了五种在MyBatis中批量操作用户数据的方法，包括方式一的简单插入、方式二的更新策略、方式三的SqlSession批处理、方式四的foreach循环和方式五的casewhen批量更新。对比了它们的性能和适用场景，突出了速度快但SQL复杂度高的方式一和通过一条SQL批量提交的高效方式三。
摘要由CSDN通过智能技术生成

批量操作新增

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>

测试结果

数量耗时
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>
  •  

测试结果

数量耗时
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>

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

测试结果

数量耗时
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>

测试结果
注：当前表内数据行数 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>


@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

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

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

3.方式三

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

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

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

数量耗时
100033907ms
200042866ms
300089675ms
5000​104833ms

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

4.方式四

Mybatis foreach 循环
执行方式：拼接好一条sql,后执行

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

数量耗时
10002671ms
2000​4170ms
30004514ms
40005152ms
5000​6572ms
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>

测试结果
注：当前表内数据行数 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性能将更优；但是批量模式无法返回自增主键

my_styles
关注
专栏目录
【深度总结】MyBatis 批量插入的多种方式及对比
甘蓝的专栏
01-02 903
深度总结批量插入的多种方式，并对比不同的解决方案！
【SpringBoot】26、SpringBoot中整合MyBatis-Plus实现数据库操作
热门推荐
Asurplus
07-23 21万+
MyBatis 框架相信大家都用过，虽然 MyBatis 可以直接在 xml 中通过 SQL 语句操作数据库，很是灵活。但正其操作都要通过 SQL 语句进行，就必须写大量的 xml 文件，很是麻烦。于是 MyBatis-Plus 应运而生，作为 MyBatis 的增强工具，更是为我们开发效率得到了质的飞跃。 一、简介 1、MyBatis MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBa
mybatis批量操作
Kiss键
04-20 562
foreach 用于迭代传入过来的参数，在sql中通常放在in关键字后面。 它的属性介绍分别是collection：表示传入过来的参数的数据类型。该参数为必选。要做 foreach 的对象，作为入参时，List 对象默认用 list 代替作为键，数组对象有 array 代替作为键，Map 对象没有默认的键。当然在作为入参时可以使用 @Param(“keyName”) 来设置键，设置 keyName...
Mybatis 批量操作8种实现总结
程序员小强的博客
12-09 2万+
Mybatis 批量操作8种实现总结超全带实例
基于Springboot+mybatis批量查询，修改，删除；@RequestParam、@RequestBody、@PathVariable、@RequestHeader的适用和区别
最新发布
f552126506的博客
07-24 1087
基于Springboot+mybatis批量查询，修改，删除；@RequestParam、@RequestBody、@PathVariable、@RequestHeader的适用和区别
MyBatis执行批量操作
奔跑的小豪吖的博客
06-21 6239
Mybatis常会出现批量操作，如批量查询，批量插入，批量修改(replace into)。批量操作要比循环执行效率提升很多，这里对mybatis的批量操作做一个总结讲解。 foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。foreach标签的属性主要有item，index，collection，open，separator，close。在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要
Mybatis批量操作
咸鱼老罗的博客
09-08 1万+
Mybatis常会出现批量操作，如批量查询，批量插入，批量修改(replace into)。批量操作要比循环执行效率提升很多，这里对mybatis的批量操作做一个总结讲解。 Foreach foreach：foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有 item，index，collection，open，separator，clo
MyBatis实现Mysql数据库分库分表操作和总结（推荐）
08-29
MyBatis实现Mysql数据库分库分表操作和总结 MyBatis是一款流行的持久层框架，能够与Mysql数据库进行交互。在大规模的应用系统中，数据库的性能和可扩展性变得非常重要。为了解决这个问题，需要实现数据库的分库分表...
mybaits 多线程 实现数据批量插入 （运用CountDownLatch实现闭锁）
10-12
总结来说，MyBatis结合多线程和CountDownLatch闭锁实现数据批量插入是一种高效且安全的方法。它不仅能显著提升数据处理速度，还能有效防止并发问题，是处理大数据量场景下的明智选择。在实际开发中，可以根据具体...
mybatis批量 insert
08-29
MyBatis提供了多种方法来实现批量插入操作。根据引用中的描述，可以使用MyBatis的拼接原生SQL一次性插入的方法或者使用MyBatis Plus框架的批量插入功能。其中，使用MyBatis Plus框架的批量插入功能相对更为推荐。...
mybatis的批量操作
gys9895的博客
04-16 442
系列文章目录 文章目录系列文章目录一、模糊查询1.concat2.INSTR 一、模糊查询 1.concat <if test="queryDTO.regid != null and queryDTO.regid !=''"> AND lca.RegId like concat("%",#{queryDTO.regid},"%") </if> 2.INSTR <if test="queryDTO.tagid != null and queryDTO.tagid !='
# mybatis批量操作
weixin_34004576的博客
11-01 124
mybatis批量操作   应用场景：今天做了一个插入数据的小功能，将含有50个对象的集合(List)插入到数据库。自然而然的就要到了mybatis批量插入，记得刚参加工作时，类似场景我会写一个for循环，逐条插入数据。   操作数据库的时候会经过这样一个流程：创建、打开、执行SQL、关闭连接，创建和打开数据库连接对性能是有损失的,下面简单地说一下批量插入和逐条插入的区别。 逐条插入会...
Mybatis（三）：批量操作
Saytime
01-08 2万+
批量操作分为批量插入和批量更新 主要使用 foreach collection="list" item="item" open="(" close=")" separator="," index="index"> #{item.xx}, #{item.xx} /foreach> collection="list" 其中list是固定的，如果是数组就是array item
mybatis 批量操作大全
demo_yo的博客
08-04 330
代码】mybatis 批量操作大全。
MyBatis的几种批量操作
topMan'blog
03-20 2498
MyBatis中批量插入   方法一： &lt;insert id="insertbatch" parameterType="java.util.List"&gt;   &lt;selectKey keyProperty="fetchTime" order="BEFORE"   resultType="java.lang.String"&gt;   SELECT
MyBatis最佳实践之——批量操作
yuiezt的专栏
02-05 2645
我们在项目中会有一些的场景，比如从导入文件中解析数据进行批量新增入库，需要做批处理操作。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值