java insert方法_【Oracle/Java】以Insert ALL方式向表中插入百万条记录，耗时9分17秒...

packagecom.hy;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importjava.text.MessageFormat;/*** 百万数据插入Oracle表中

*@authorhorn1

**/

public classMillionInserter {//连接到数据库的四大属性

private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";private static final String DBURL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";private static final String USER = "system";private static final String PSWD = "XXXX";public void batchInsert(intcount) {

Connection conn= null;

Statement stmt= null;try{long startTime =System.currentTimeMillis();

Class.forName(DRIVER).newInstance();

conn=DriverManager.getConnection(DBURL, USER, PSWD);

stmt=conn.createStatement();int BatchSize=100;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;

String name="M"+index;int age=j % 100;

String raw=" INTO firsttb(NAME, age,createdtime) values(''{0}'',''{1}'',sysdate) ";

Object[] arr={name,age};

String particialSql=MessageFormat.format(raw, arr);

sb.append(particialSql);

}

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

String sql=sb.toString();

stmt.executeUpdate(sql);

System.out.println("#"+i+" "+BatchSize+" records inserted");

}long endTime =System.currentTimeMillis();

System.out.println("Time elapsed:" + sec2DHMS((endTime - startTime)/1000) );

String sql= "select count(*) as cnt from firsttb";

ResultSet rs=stmt.executeQuery(sql);while(rs.next()) {

String cnt= rs.getString("cnt");

System.out.println("当前记录数："+cnt);

}

}catch(Exception e) {

System.out.print(e.getMessage());

}finally{try{

stmt.close();

conn.close();

}catch(SQLException e) {

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

}

}

}/*** 将秒转化为日时分秒

*@paramsecondCount

*@return

*/

private static String sec2DHMS(longsecondCount) {

String retval= null;long days = secondCount / (60 * 60 * 24);long hours = (secondCount % (60 * 60 * 24)) / (60 * 60);long minutes = (secondCount % (60 * 60)) / 60;long seconds = secondCount % 60;

String strSeconds="";if(seconds!=0) {

strSeconds=seconds + "s";

}if (days > 0) {

retval= days + "d" + hours + "h" + minutes + "m" +strSeconds;

}else if (hours > 0) {

retval= hours + "h" + minutes + "m" +strSeconds;

}else if (minutes > 0) {

retval= minutes + "m" +strSeconds;

}else{

retval=strSeconds;

}returnretval;

}/*** 执行点

*@paramargs*/

public static voidmain(String[] args) {

MillionInserter mi=newMillionInserter();

mi.batchInsert(1000000);

}

}