Mybatis 批量操作多种实现总结

喜欢ヅ旅行 2021-09-07 06:00 414阅读 0赞

# 批量操作新增 #

## 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>

测试结果

## 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>

测试结果

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

测试结果

# 批量修改 #

## 1.方式一 ##

批量新增或更新方式  
注：需要给唯一主键添加唯一索引，update才会生效

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

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

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

## 3.方式三 ##

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

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

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

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

## 4.方式四 ##

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

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

优点：生成多条sql,统一执行，与数据库交互次数少  
缺点 : 生成多条拼接的update语句,update语句比较多，量大了就有可能造成sql阻塞。

## 5.方式五 ##

mybatis sql 使用 case when

<update id="updateCaseByUserId" parameterType="java.util.List">
            update lp_user_test_batch
            <trim prefix="set" suffixOverrides=",">
    
                
                <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

缺点:  
xml中的循环体有点多，每一个case when 都要循环一遍list集合，所以大批量拼sql的时候会比较慢。  
生成多条拼接sql,sql长度过长，容易sql超长引起报错 Packet for query is too large。

MySQL 最大允许的 packet  
![在这里插入图片描述][20181209155634326.png]

## Mybatis批处理介绍 ##

Mybatis内置执行器类型ExecutorType有3种  
分别是  
ExecutorType.SIMPLE: 不做特殊处理，为每个语句的执行创建一个新的预处理语句。  
ExecutorType.REUSE: 可以复用预处理语句。  
ExecutorType.BATCH:可以批量执行所有更新语句

**SIMPLE与BATCH（批量）对比**  
默认的是simple，该模式下它为每个语句的执行创建一个新的预处理语句，单条提交sql；  
而batch模式重复使用已经预处理的语句，并且批量执行所有更新语句，显然batch性能将更优；但是批量模式无法返回自增主键

[20181209155634326.png]: /images/20210827/9602ab7811e240cf84f5e02e2c10f9f0.png