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

最新推荐文章于 2024-02-04 14:41:01 发布

一朵风中摇曳的水仙花

最新推荐文章于 2024-02-04 14:41:01 发布

阅读量321

点赞数

分类专栏：数据库文章标签： mysql 数据库批量处理优化

本文链接：https://blog.csdn.net/u012045045/article/details/80104613

版权

数据库专栏收录该内容

69 篇文章 0 订阅

订阅专栏

①批量插入

<!--批量插入并返回插入成功的条数-->
  <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>