mybatis批量插入oracle大量数据记录性能问题解决

1.使用"直接路径插入"(下面sql语句中的"/*+append_values */"),并且使用关键字"union all":

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

< 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,这样做会随着插入数目的增大,执行速度陡然变慢,所以应该分批次进行插入:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

public void save(List<UidCodeBean> uidCodeList) throws Exception {      SqlSession batchSqlSession = null ;      try {          batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false );//获取批量方式的sqlsession               int 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 ();      }       }