批量操作只需要連接一次數(shù)據(jù)庫,在mybatis中執(zhí)行一次sql即可.相比逐條操作,頻繁打開斷開數(shù)據(jù)庫連接,效率會提高很多.雖然在逐條循環(huán)操作時,引入連接池會優(yōu)化很大的效率,但相比批量操作效率也是比較低的.

批量操作的優(yōu)點:

• 減少連接數(shù)據(jù)庫的頻率;
• 減少提交事務(wù)的頻率;

批量操作的缺點:

• 一次失敗,整個批次的操作都會回歸,問題不易排查;故一次批量操作,不易將數(shù)據(jù)設(shè)置過大;

當(dāng)數(shù)據(jù)量很大,mybatis級別的批量操作也是扛不住的,到時需要考慮其他手段了.

1. 批量插入

    <insert id="insertBatch" parameterType="java.util.List">
        insert into MBR_POINTS_BATCH_NEW(
            ID, MEMBER_ID, POINTS_TYPE, TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_CREATE_TIME, 
            MERCHANT_CODE, STORE_CODE, POINTS_PRICING_ID, CONTRACT_NO, AGREEMENT_NO, POINTS_PRICE, 
            ISSUE_POINTS_COST, POINTS, POINTS_BALANCE, HOLD_POINTS, VALUE_DATE, MATURITY_DATE, 
            REFER_NO, CREATE_TIME, LAST_UPDATE_TIME
        )
       <foreach collection="list" item="item" index="index" open="(" close=")"
           separator="UNION ALL">
           SELECT
                    #{item.id, jdbcType=DECIMAL},
                    #{item.memberId, jdbcType=DECIMAL},
                    #{item.pointsType, jdbcType=VARCHAR},
                    #{item.transactionId, jdbcType=DECIMAL},
                    #{item.transactionType, jdbcType=VARCHAR},
                    #{item.transactionCreateTime, jdbcType=TIMESTAMP},
                    #{item.merchantCode, jdbcType=VARCHAR},
                    #{item.storeCode, jdbcType=VARCHAR},
                    #{item.pointsPricingId, jdbcType=DECIMAL},
                    #{item.contractNo, jdbcType=VARCHAR},
                    #{item.agreementNo, jdbcType=VARCHAR},
                    #{item.pointsPrice, jdbcType=DECIMAL},
                    #{item.issuePointsCost, jdbcType=DECIMAL},
                    #{item.points, jdbcType=DECIMAL},
                    #{item.pointsBalance, jdbcType=DECIMAL},
                    #{item.holdPoints, jdbcType=DECIMAL},
                    #{item.valueDate, jdbcType=TIMESTAMP},
                    #{item.maturityDate, jdbcType=TIMESTAMP},
                    #{item.referNo, jdbcType=VARCHAR},
                    #{item.createTime, jdbcType=TIMESTAMP},
                    #{item.lastUpdateTime, jdbcType=TIMESTAMP}       
           from dual
       </foreach>
    </insert>

2. 批量更新

    <update id="updateBatch" parameterType="java.util.List">
        update POINTS_TRANSACTION_DETAIL 
        set  CLEAN_STATUS=
        <foreach collection="list" item="item" index="index" 
            separator=" " open="case ID" close="end">
            when #{item.id} then #{item.cleanStatus}
        </foreach>
        where ID in
        <foreach collection="list" index="index" item="item" 
            separator="," open="(" close=")">
            #{item.id,jdbcType=DECIMAL}
        </foreach>