mybatis批量插入oracle大量資料記錄效能問題解決，mybatisoracle

mybatis批量插入oracle大量資料記錄效能問題解決，mybatisoracle

 環境: mybatis  + oracle11g r2

1.使用"直接路徑插入"(下面sql語句中的"/*+append_values */"),並且使用關鍵字"union all":

<insert id="addUidCodeBatch"  parameterType="java.util.List">      insert into /*+append_values */        T_UID_CODE(C_UID_CODE,         C_SERAIL_LEN,         C_BATCH_CODE,         C_TYPE,         C_CREATE_TIME,         C_SUPER_CODE,         c_security_code,         C_SERIAL_CODE         )       <foreach collection="list" item="item" index="index" separator="union all" >    select #{item.uidCode},         #{item.kCode},               #{item.batchCode},         #{item.type},         sysdate,         #{item.superCode},         #{item.securityCode},         #{item.serialCode}  from dual       </foreach>      </insert>  

2.dao層實現:   之前是一次性commit,這樣做會隨著插入數目的增大,執行速度陡然變慢,所以應該分批次進行插入:

public void save(List<UidCodeBean> uidCodeList) throws Exception {SqlSession batchSqlSession = null;try {batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);//擷取批量方式的sqlsessionint batchCount = 1000;//每批commit的個數int batchLastIndex = batchCount - 1;//每批最後一個的下標for(int index = 0; index < uidCodeList.size()-1;){if(batchLastIndex > uidCodeList.size()-1){batchLastIndex = uidCodeList.size() - 1;batchSqlSession.insert(NAMESPACE+".addUidCodeBatch", uidCodeList.subList(index, batchLastIndex));batchSqlSession.commit();System.out.println("index:"+index+"     batchLastIndex:"+batchLastIndex);break;//資料插入完畢,退出迴圈}else{batchSqlSession.insert(NAMESPACE+".addUidCodeBatch", uidCodeList.subList(index, batchLastIndex));                batchSqlSession.commit();System.out.println("index:"+index+"     batchLastIndex:"+batchLastIndex);index = batchLastIndex + 1;//設定下一批下標batchLastIndex = index + (batchCount - 1);}}}finally{batchSqlSession.close();}}

mybatis使用oracle插入資料返回主鍵問題

把下面哪幾行放到sql後面試試
<selectKey resultType="java.lang.Integer" keyProperty="ID" order="BEFORE">
SELECT IBOKEE_COMM_TAG_LIBRARY_SEQ.nextval AS Id FROM DUAL
</selectKey>
 
mybatis實現oracle 批量插入java方法

java代碼裡插入的時候迴圈將其一個個的放入批處理batch裡面。然後只要處理一次batch就可以了。。