mybatis-批量插入与更新

批量操作只需要连接一次数据库,在mybatis中执行一次sql即可.相比逐条操作,频繁打开断开数据库连接,效率会提高很多.虽然在逐条循环操作时,引入连接池会优化很大的效率,但相比批量操作效率也是比较低的.

批量操作的优点:

• 减少连接数据库的频率;
• 减少提交事务的频率;

批量操作的缺点:

• 一次失败,整个批次的操作都会回归,问题不易排查;故一次批量操作,不易将数据设置过大;

当数据量很大,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>