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

package com.hy;/** * 数据库连接参数，按你的实际情况修改 * @author 逆火 * * 2019年11月16日 上午8:09:24 */public final class DBParam { public final static String Driver = "oracle.jdbc.driver.OracleDriver"; public final static String DbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; public final static String User = "ufo"; public final static String Pswd = "1234";}

package com.hy;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.text.MessageFormat;import java.util.ArrayList;import java.util.List;import java.util.Random;import org.apache.log4j.Logger;// Used to insert ten thousands of records to tablepublic class TableRecordInserter { private static Logger log = Logger.getLogger(TableCreater.class); private final String Table="delivery_history07";// 表名在这里改 private final int Total=500000;// 插入数据总量在这里改 public boolean fillTable() { 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<String> 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) throws SQLException { stmt.executeUpdate("truncate table "+Table); conn.commit(); log.info("Cleared table:'"+Table+"'."); } private int betachInsert(List<String> insertSqls,Statement stmt,Connection conn) throws SQLException { int inserted=0; final int BatchSize=250; int count=insertSqls.size(); int index=0; int times=count/BatchSize; for(int i=0;i<times;i++) { StringBuilder sb=new StringBuilder(); sb.append("INSERT ALL "); for(int j=0;j<BatchSize;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."); } return inserted; } // 具体如何插值需要根据你的业务修改，产生本地化SQL private List<String> generateInsertSqlList() { List<String> sqlList=new ArrayList<String>(); int index=0; do { int orderNoRange=getRandom(1,100);// 调整order_no，L89 int orderNo=index*1000+orderNoRange; for(int i=0;i<orderNoRange;i++) { int shipper_code=getShipperCode(); String insertSql=getInsertSql(index,orderNo,shipper_code); sqlList.add(insertSql); index++; } }while(index<Total); log.info("generated "+sqlList.size()+" insert sqls."); return sqlList; } // get partial insert sql，这里字段名需要修改 private String getInsertSql(int id,int orderNo,int shipperCode) { 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)}; return MessageFormat.format(raw, arr); } // get a random shipper-code private int getShipperCode() { 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); return arr[seed]; } // get a random integer between min and max public static int getRandom(int min, int max){ Random random = new Random(); int rnd = random.nextInt(max) % (max - min + 1) + min; return rnd; } // change seconds to DayHourMinuteSecond format private static String ms2DHMS(long startMs, long endMs) { 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 void main(String[] args) { TableRecordInserter tri=new TableRecordInserter(); tri.fillTable(); }}