mybaits sql批量处理的几种方法总结

 ①批量插入
 

<!--批量插入并返回插入成功的条数-->
  <insert id="insertBranchReviseData" parameterType="java.util.List">
     insert into SAS_BRANCH_REVISE_BATCH
     (
   ID,
   ORG_ID,
   FIRST_LEVEL_BRANCH_NAME,
   SECOND_LEVEL_BRANCH_NAME,
   SUB_BRANCH_NAME,
   ORG_CODE,
   ONLINE_RECOMMED_CODE,
   OFFLINE_RECOMMED_CODE,
   BATCH_NO,
   CREATE_DATE
  )
  select 
      SAS_BRANCH_REVISE_BATCH_SEQ.nextval,
   A.*
  from
  (
      <foreach collection="branchs" item="item" index="index" separator="union all">    
      select
    #{item.orgId,jdbcType=CHAR},
    #{item.firstLevelBranchName,jdbcType=CHAR},
    #{item.secondLevelBranchName,jdbcType=CHAR},
    #{item.subBranchName,jdbcType=CHAR},
    #{item.orgCode,jdbcType=CHAR},
    #{item.onlineRecommedCode,jdbcType=CHAR},
    #{item.offlineRecommedCode,jdbcType=CHAR},
    #{batchNo,jdbcType=VARCHAR},
    sysdate
       from dual
      </foreach>  
  )A
 </insert> 
 

 ②批量更新

  <update id="batchUpdateApplyState" parameterType="java.util.List">
        <foreach collection="applyStateList" item="item" index="index" open="BEGIN" close=";END;" separator=";">
            UPDATE ORG_RECOMM_RELATIONSHIP
            <set>
                <if test="item.applyState != null and item.applyState != ''">
                    APPLY_STATE=#{item.applyState,jdbcType=VARCHAR},
                </if>
                MODIFY_DATE=sysdate
            </set>
            WHERE 
                SNO=#{item.sno,jdbcType=BIGINT} 
            AND 
                APPLY_STATE != #{item.applyState,jdbcType=VARCHAR}
        </foreach>
    </update>