java oracle 10万数据_[oracle/java/sql]用于上十万批量数据插入Oracle表的Java程序

packagecom.hy;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.SQLException;importjava.sql.Statement;importjava.text.MessageFormat;importjava.util.ArrayList;importjava.util.List;importjava.util.Random;importorg.apache.log4j.Logger;//Used to insert ten thousands of records to table

public classTableRecordInserter {private static Logger log = Logger.getLogger(TableCreater.class);private final String Table="delivery_history07";// 表名在这里改private final int Total=500000;// 插入数据总量在这里改public booleanfillTable() {

Connection conn= null;

Statement stmt= null;try{

Class.forName(DBParam.Driver).newInstance();

conn=DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);

conn.setAutoCommit(false);

stmt=conn.createStatement();long startMs =System.currentTimeMillis();

clearTable(stmt,conn);

List insertSqls=generateInsertSqlList();

betachInsert(insertSqls,stmt,conn);long endMs =System.currentTimeMillis();

log.info("It takes "+ms2DHMS(startMs,endMs)+" to fill "+Total+" records.");

}catch(Exception e) {

e.printStackTrace();

}finally{try{

stmt.close();

conn.close();

}catch(SQLException e) {

System.out.print("Can't close stmt/conn because of " +e.getMessage());

}

}return false;

}private void clearTable(Statement stmt,Connection conn) throwsSQLException {

stmt.executeUpdate("truncate table "+Table);

conn.commit();

log.info("Cleared table:'"+Table+"'.");

}private int betachInsert(List insertSqls,Statement stmt,Connection conn) throwsSQLException {int inserted=0;final int BatchSize=250;int count=insertSqls.size();int index=0;int times=count/BatchSize;for(int i=0;i

StringBuilder sb=newStringBuilder();

sb.append("INSERT ALL ");for(int j=0;j

index=i*BatchSize+j;

sb.append(insertSqls.get(index));

}

sb.append(" select * from dual");

String sql=sb.toString();int n=stmt.executeUpdate(sql);

inserted+=n;

conn.commit();

log.info("#"+i+" inserted " +n+" records.");

}returninserted;

}

// 具体如何插值需要根据你的业务修改，产生本地化SQLprivate ListgenerateInsertSqlList() {

List sqlList=new ArrayList();int index=0;do{int orderNoRange=getRandom(1,100);//调整order_no，L89

int orderNo=index*1000+orderNoRange;for(int i=0;i

String insertSql=getInsertSql(index,orderNo,shipper_code);

sqlList.add(insertSql);

index++;

}

}while(index

log.info("generated "+sqlList.size()+" insert sqls.");returnsqlList;

}//get partial insert sql，这里字段名需要修改

private String getInsertSql(int id,int orderNo,intshipperCode) {

String raw=" INTO {0}(id,name, order_no,shipper_code,createtime) values(''{1}'',''{2}'',''{3}'',''{4}'',sysdate) ";

String ids=String.valueOf(id);

String name="N_"+ids;

Object[] arr={Table,ids,name,String.valueOf(orderNo),String.valueOf(shipperCode)};returnMessageFormat.format(raw, arr);

}//get a random shipper-code

private intgetShipperCode() {int[] arr= {1111,2222,3333,4444,5555,6666,7777,8888,9999,1010,2020,3030,4040,5050,6060,7070,8080,9090,1011,2022,3033,4044,5055,6066,7077,8088,9099,1811,2822,3833,4844,5855,6866,7877,8888,9899};//调整shipper_code，L120

int seed=getRandom(0,arr.length-1);returnarr[seed];

}//get a random integer between min and max

public static int getRandom(int min, intmax){

Random random= newRandom();int rnd = random.nextInt(max) % (max - min + 1) +min;returnrnd;

}//change seconds to DayHourMinuteSecond format

private static String ms2DHMS(long startMs, longendMs) {

String retval= null;long secondCount = (endMs - startMs) / 1000;

String ms= (endMs - startMs) % 1000 + "ms";long days = secondCount / (60 * 60 * 24);long hours = (secondCount % (60 * 60 * 24)) / (60 * 60);long minutes = (secondCount % (60 * 60)) / 60;long seconds = secondCount % 60;if (days > 0) {

retval= days + "d" + hours + "h" + minutes + "m" + seconds + "s";

}else if (hours > 0) {

retval= hours + "h" + minutes + "m" + seconds + "s";

}else if (minutes > 0) {

retval= minutes + "m" + seconds + "s";

}else{

retval= seconds + "s";

}return retval +ms;

}//Entry point

public static voidmain(String[] args) {

TableRecordInserter tri=newTableRecordInserter();

tri.fillTable();

}

}