Mybatis的批处理操作详解

Mybatis批处理操作

---

一、Foreach方式

批量插入

插入方式一：
注：（oracle不支持，mysql支持，有返回插入行数）

<insert id="insertByBatch" parameterType="java.util.List">
    INSERT INTO WBM_ERT_DED_RST_T
    (
     <include refid="AllColumns" />
    )
    VALUES
    <foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index" separator=",">
        (
        #{ertDedRstDbo.sysDate},
        #{ertDedRstDbo.chgCard},
        #{ertDedRstDbo.dedBatchId},
        #{ertDedRstDbo.dedGroupId},
        #{ertDedRstDbo.dedTaskId},
        #{ertDedRstDbo.prcResult},
        #{ertDedRstDbo.prcErrmsg},
        #{ertDedRstDbo.specCode},
        #{ertDedRstDbo.techBusinessId},
        #{ertDedRstDbo.techTraceId},
        #{ertDedRstDbo.techDbName},
        #{ertDedRstDbo.techMntTime},
        #{ertDedRstDbo.techVersion},
        #{ertDedRstDbo.techResv}
        )
    </foreach>
</insert>

插入方式二：
注：（有返回插入行数）

<insert id="insertByBatch" parameterType="java.util.List">    
    INSERT ALL    
    <foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index">        
        INTO WBM_ERT_DED_RST_T       
        (       
         <include refid="AllColumns" />      
        )       
        VALUES     
        (       
        #{ertDedRstDbo.sysDate},   
        #{ertDedRstDbo.chgCard},
        #{ertDedRstDbo.dedBatchId},    
        #{ertDedRstDbo.dedGroupId},      
        #{ertDedRstDbo.dedTaskId},      
        #{ertDedRstDbo.prcResult},    
        #{ertDedRstDbo.prcErrmsg},      
        #{ertDedRstDbo.specCode},       
        #{ertDedRstDbo.techBusinessId},
        #{ertDedRstDbo.techTraceId},     
        #{ertDedRstDbo.techDbName},   
        #{ertDedRstDbo.techMntTime},    
        #{ertDedRstDbo.techVersion},     
        #{ertDedRstDbo.techResv}    
        )   
    </foreach>  
    SELECT 1 FROM DUAL
</insert>

插入方式三：

<insert id="insertList" parameterType="java.util.List">
        INSERT INTO WFE_RULE_DIM_T (
        <include refid="AllColumn"/>
        )
        <foreach collection="ruleDimList" item="ruleDim" open="(" close=")" separator="union">SELECT
            #{ruleDim.itemCode}, 
            #{ruleDim.ruleNumber},
            #{ruleDim.seqGroup}, 
            #{ruleDim.seqInner},
            #{ruleDim.dimCode},
            #{ruleDim.opCode},
            #{ruleDim.dimValue},
            ' ',
            #{ruleDim.techBusinessId},
            #{ruleDim.techTraceId},
            #{ruleDim.techDbName}, 
            #{ruleDim.techMntTime}, 
            #{ruleDim.techMntUser}, 
            #{ruleDim.techVersion},
            #{ruleDim.techResv} FROM DUAL
        </foreach>
</insert>

插入方式四：

insert into t1 (p1,p2,p3,p4) select id,'2','3',name from t2 where .... 

该方式，是通过select构造得到的结果集，批量插入另一个表中。

批量更新

更新方式一：
注：（oracle和mysql数据库都支持，且有返回更新行数）

<update id="updateByBatch" parameterType="java.util.List">  
    UPDATE WBM_ERT_DED_RST_T    
    <trim prefix="SET" suffixOverrides=",">    
        <trim prefix="PRC_RESULT = CASE" suffix="END,">    
            <foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index">   
                WHEN CHG_CARD = #{ertDedRstDbo.chgCard} THEN #{ertDedRstDbo.prcResult}     
            </foreach>       
        </trim>        
        <trim prefix="TECH_MNT_TIME = CASE" suffix="END,">    
            <foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index">   
                WHEN CHG_CARD = #{ertDedRstDbo.chgCard} THEN #{ertDedRstDbo.techMntTime}    
            </foreach>   
        </trim>       
        <trim prefix="TECH_VERSION = CASE" suffix="END,"> 
            <foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index">    
                WHEN CHG_CARD = #{ertDedRstDbo.chgCard} THEN MOD(TECH_VERSION, 9999) + 1     
            </foreach>   
        </trim> 
    </trim>  
    WHERE SYS_DATE = #{sysDate}    
    AND CHG_CARD IN  
    <foreach collection="ertDedRstDboList" index="index" item="ertDedRstDbo" separator="," open="(" close=")"> 
        #{ertDedRstDbo.chgCard} 
    </foreach>
</update>

更新方式二：
注：（orcale需要加上begin end，mysql未测试）

<update id="updateByBatch" parameterType="java.util.List">   
    <foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index" open="begin" close=";end;" separator=";">    
        UPDATE WBM_ERT_DED_RST_T      
        <set>        
            PRC_RESULT = #{ertDedRstDbo.prcResult},    
            PRC_ERRMSG = #{ertDedRstDbo.prcErrmsg},
            TECH_BUSINESS_ID = #{ertDedRstDbo.techBusinessId},   
            TECH_TRACE_ID = #{ertDedRstDbo.techTraceId},        
            TECH_DB_NAME = #{ertDedRstDbo.techDbName},          
            TECH_MNT_TIME = #{ertDedRstDbo.techMntTime},        
            TECH_VERSION = MOD(TECH_VERSION, 9999) + 1      
        </set>       
        WHERE SYS_DATE = #{sysDate}     
    </foreach>
</update>

批量删除

注：（oracle和mysql数据库都支持，且有返回更新行数）

<select id="deleteByBatch" resultMap="resultMap">
    DELETE FROM WBM_ERT_DED_RST_T
            WHERE SYS_DATE = #{sysDate}
            AND CHG_CARD IN
            <foreach item="chgCard" separator="," open="(" close=")" collection="chgCardSet">
                #{chgCard}
            </foreach>
</select>

二、Batch方式

（示例）：

@Transactional
publicvoid test() {
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
    ErtDedRstMapper ertDedRstMapper = sqlSession.getMapper(ErtDedRstMapper.class);
    for (Map.Entry<String, Integer> entry : chgCardCountMap.entrySet()) {    
        String chgCard = entry.getKey();    
        List<String> resultAndErrmsgList = multiValueMap.get(chgCard); 
        String prcResult = resultAndErrmsgList.get(0); 
        String prcErrmsg = resultAndErrmsgList.get(1);   
        ErtDedRstDbo ertPrcDtlDbo = ErtDedRstDbo.builder()
                .sysDate(x1.getSysDate())           
                .chgCard(chgCard)            
                .dedBatchId(x1.getDedBatchId())     
                .dedGroupId(x1.getDedGroupId())          
                .dedTaskId(x1.getDedTaskId())            
                .prcResult(prcResult)         
                .prcErrmsg(prcErrmsg)         
                .build();   
        if (entry.getValue() == 1) {   
            ertDedRstMapper.insert(ertPrcDtlDbo);  
        }else {     
            TechFieldDbo techFieldDbo = TechFieldDbo.builder().build();   
            ertDedRstMapper.update(x1.getSysDate(), chgCard, x1.getDedBatchId(), x1.getDedGroupId(), x1.getDedTaskId(),     
                    prcResult, prcErrmsg, techFieldDbo); 
        }
    }
}

注意：ertDedRstMapper不能直接使用@Autowired注入的，需要在开启批处理方式之后重新获取，且此时循环内的insert和update返回值不是影响行数。

在上面例子中，在 Service 中直接注入了 SqlSessionFactory，通过下面方法获取了一个可以批量提交的 SqlSession：

SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);

后续使用该SqlSession 。

注意事项（这里理解可能有误，仅供参考）

1. 事务
   由于在 Spring 集成的情况下，事务连接由 Spring 管理（SpringManagedTransaction），所以这里不需要手动关闭 sqlSession，在这里手动提交（commit）或者回滚（rollback）也是无效的。

2. 批量提交
   批量提交只能应用于 insert, update, delete。

并且在批量提交使用时，如果在操作同一SQL时中间插入了其他数据库操作，就会让批量提交方式变成普通的执行方式，所以在使用批量提交时，要控制好 SQL 执行顺序。

三、preparedStatement方式

以springboot项目为基础，不说原生jdbc（原生jdbc需要一步步去获取数据连接,较为繁琐）

 @Autowiredprivate 
 SqlSessionTemplate sqlSessionTemplate;
 Private String sqlText = “INSERT INTO WRU_RULE_GLOB_REMARK_T ( TECH_MNT_TIME , TECH_VERSION , TECH_RESV ) VALUES( ?,?,? )”;

 @Transactional
 public void test() { 
     try {      
         Connection connection = sqlSessionTemplate.getConnection();
         //connection.setAutoCommit(false);
         for (int i = 0; i < 100; i++) {        
             this.preparedStatement = connection.prepareStatement(sqlText);
             preparedStatement.setString(parameterIndex++, “1”);       
             preparedStatement.setString(parameterIndex++, “2”);   
             preparedStatement.setString(parameterIndex++, “3”);
             preparedStatement.addBatch();
             //connection.commit();
             //connection.setAutoCommit(true);
         }
         int[] updateCount =  preparedStatement.executeBatch();   
     } catch (SQLException exception) {  
         throw exception; 
     } finally {
         preparedStatement.close();
     }
 }

注意：使用JDBC方式获取的数据连接，需要自己手动设置事务提交事件。
使用了@Transactional事务注解后，同batch方式一样，不需要手动执行commit()方法来提交事务。

总结

Foreach方式：
当数据过多时，可能生成的动态sql过大，mysql默认仅1M的sql字符串，过长可能会执行失败，可以通过修改配置文件，batch方式无限制。在sql循环时设置batch与否其实执行时间差别不是很大，几乎可以忽略不计。所以其实如果不是特别要求性能。可以直接在sql中使用for循环即可。
Batch方式：
如果需要使用batch，请在需要的函数上面设置batch，不要全局使用。因为batch也是有副作用的。比如在Insert操作时，在事务没有提交之前，是没有办法获取到自增的id，；此外，对于update、delete无法返回更新、插入条数。这在某型情形下是不符合业务要求

批处理选择
已知sql执行效率：insert > delete > update，因此对应的批处理也是一样的顺序。
场景示例：批处理，若数据表未存在记录则作插入，若已存在则作更新。
方案一：使用select + update + insert做批处理，但此方案效率较低。
方案二：使用select + delete + insert 做批处理，推荐使用。