开发者

Mybatis实现批量操作8种小结

目录
  • 批量新增
    • 1.方式一(常用)
    • 2.方式二
    • 3.方式三
  • 批量修改
    • 1.方式一
    • 2.方式二
    • 3.方式三
    • 4.方式四
    • 5.方式五
    • MyBATis批处理介绍
    • 测试环境配置
  • 总结

    批量新增

    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,后执行

    <!-- 接收list参数,循环着组装sql语句,注意for循环的写法
                 separator=";" 代表着每次循环完,在sql后面放一个分号 -->
    <update id="updateForeachByUserId" parameterType="java.util.List">
        <foreach collection="list" item="item" separator=";">
            UPDATE lp_user_test_batch
            www.devze.comSET
            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

    数量耗时
    10002671ms
    2000​4170ms
    30004514ms
    40005152ms
    5000​6572ms
    800010209ms
    1000012158ms

    优点:生成多条sql,统一执行,与数据python库交互次数少

    缺点 : 生成多条拼接的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,">
                    <fore编程客栈ach 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集合,所以大批量jTZMqpGPH拼sql的时候会比较慢。

    生成多条拼接sql,sql长度过长,容易sql超长引起报错 Packet for query is too large。

    mysql python最大允许的 packet

    Mybatis实现批量操作8种小结

    Mybatis批处理介绍

    Mybatis内置执行器类型ExecutorType有3种

    分别是

    ExecutorType.SIMPLE: 不做特殊处理,为每个语句的执行创建一个新的预处理语句。

    ExecutorType.REUSE: 可以复用预处理语句。

    ExecutorType.BATCH:可以批量执行所有更新语句

    SIMPLE与BATCH(批量)对比默认的是simple,该模式下它为每个语句的执行创建一个新的预处理语句,单条提交sql;

    而batch模式重复使用已经预处理的语句,并且批量执行所有更新语句,显然batch性能将更优;但是批量模式无法返回自增主键

    测试环境配置

    系统:win 8.1

    Mysql : 5.7

    java环境:junit

    注:环境不同可能会引起耗时存在差异。

    总结

    单次批量操作不要过大,批量新增使用方式一,批量更新方式一与方式二经过测试是最优的选择

    也可以根据安全方面综合考虑,选择适合的方式。

    到此这篇关于Mybatis实现批量操作8种小结的文章就介绍到这了,更多相关Mybatis 批量操作内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)!

    0

    上一篇:

    下一篇:

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    最新开发

    开发排行榜