批量操作只需要连接一次数据库,在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>
