三张表DDL如下：
CREATE TABLE tb01 ( "ID" ,) not null primary key, "NAME" NVARCHAR2() not null, "AGE" ,) not null , "CREATEDTIME" ) not null ) CREATE TABLE tb02 ( "ID" ,) not null primary key, "SN" NVARCHAR2() not null, "NAME" NVARCHAR2() not null, "AGE" ,) not null , "CREATEDTIME" ) not null ) CREATE TABLE tb03 ( "ID" ,) not null primary key, "SN" NVARCHAR2() not null, "NAME" NVARCHAR2() not null, "AGE" ,) not null , "ADDRESS" NVARCHAR2() not null, "CREATEDTIME" ) not null )
插值程序：
package com.hy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
/**
* 数据库连接参数
* @author horn1
*
*/
class DbParam{
final String Driver = "oracle.jdbc.driver.OracleDriver";
final String DbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
final String User = "ufo";
final String Pswd = "1234";
}
class TypeField{
String type;
String field;
}
/**
* 多表批量插入器
* @author horn1
*
*/
public class MultiTbBatchInserter {
private final int BatchSize=100;
// 有三个表需要插入,如果是多个表，扩充数组即可
// PK:主键 CH：文字 DT：Datetime，还可以根据需要扩充代号，在getInsertSql函数中则根据代号来设置值
private final String[][] tableArray= {
{"tb01:1000000","PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"},
{"tb02:1000000","PK:ID","CH:SN","CH:NAME","CH:AGE","DT:CREATEDTIME"},
{"tb03:1000000","PK:ID","CH:SN","CH:NAME","CH:AGE","CH:Address","DT:CREATEDTIME"},
};
/**
* 批量插值
*/
public void batchInsert() {
DbParam dbParam=new DbParam();
Connection conn = null;
Statement stmt = null;
try{
Class.forName(dbParam.Driver).newInstance();
conn = DriverManager.getConnection(dbParam.DbUrl, dbParam.User, dbParam.Pswd);
stmt = conn.createStatement();
System.out.println("Begin to access "+dbParam.DbUrl+" as "+dbParam.User+"...");
for(String[] innerArr:tableArray) {
String tableName=innerArr[0].split(":")[0];
int count=Integer.parseInt(innerArr[0].split(":")[1]);
System.out.println("准备向表"+tableName+"插入"+count+"条记录.");
// 插值前先清空
truncateTable(tableName,conn,stmt);
// 真正插入数据
insertTestDataTo(tableName,count,innerArr,conn,stmt);
}
} 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());
}
}
}
/**
* 以当前时间为基准减去数十秒
* @param n
* @return
*/
private static String getDatetimeBefore(int n) {
try {
Calendar now = Calendar.getInstance();
now.add(Calendar.SECOND,-n*10);//日期减去n*10秒
Date newDate=now.getTime();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String retval = sdf.format(newDate);
return retval;
}
catch(Exception ex) {
ex.printStackTrace();
return null;
}
}
/**
* 清空一个表的数据，注意此功能有破坏性，不可恢复，注意备份好数据
* @param tableName
* @param conn
* @param stmt
* @throws SQLException
*/
private void truncateTable(String tableName,Connection conn,Statement stmt) throws SQLException{
String sql="truncate table "+tableName;
stmt.execute(sql);
System.out.println("truncated table:"+tableName);
}
/**
* 向一个表插入数据
* @param tableName
* @param count
* @param innerArr
* @param conn
* @param stmt
* @throws SQLException
*/
private void insertTestDataTo(String tableName,int count,String[] innerArr,Connection conn,Statement stmt) throws SQLException{
// 得到字段名和字段类型
List<TypeField> typefields=new ArrayList<TypeField>();
for(int i=1;i<innerArr.length;i++) {
String temp=innerArr[i];
String[] arrTmp=temp.split(":");
TypeField tf=new TypeField();
tf.type=arrTmp[0];
tf.field=arrTmp[1];
typefields.add(tf);
}
List<String> fields=new ArrayList<String>();
List<String> values=new ArrayList<String>();
int index=0;
for(TypeField tf:typefields) {
fields.add(tf.field);
values.add("''{"+index+"}''");
index++;
}
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(getInsertSql(tableName,typefields,index));
}
sb.append(" select * from dual");
String sql = sb.toString();
//System.out.println("sql="+sql);
stmt.executeUpdate(sql);
System.out.println("#"+i+" "+BatchSize+" records inserted");
}
}
/**
* 得到批量插入语句
* @param tableName
* @param typefields
* @param index
* @return
*/
private String getInsertSql(String tableName,List<TypeField> typefields,int index) {
String currTime=getDatetimeBefore(index);
StringBuilder sb=new StringBuilder();
sb.append(" INTO "+tableName+"(");
List<String> fields=new ArrayList<String>();
for(TypeField tf:typefields) {
fields.add(tf.field);
}
sb.append(String.join(",",fields));
sb.append(") values(");
List<String> values=new ArrayList<String>();
for(TypeField tf:typefields) {
if(tf.type.equals("PK")) {
values.add("'"+String.valueOf(index)+"'");
}else if(tf.type.equals("CH")) {
values.add("'0'");
}else if(tf.type.equals("DT")) {
values.add("to_date('"+currTime+"','yyyy-MM-dd HH24:mi:ss')");
}
}
sb.append(String.join(",",values));
sb.append(")");
String insertSql=sb.toString();
return insertSql;
}
/**
* 将秒转化为日时分秒
* @param secondCount
* @return
*/
private static String sec2DHMS(long secondCount) {
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;
}
return retval;
}
public static void main(String[] args) {
MultiTbBatchInserter mi=new MultiTbBatchInserter();
long startTime = System.currentTimeMillis();
mi.batchInsert();
long endTime = System.currentTimeMillis();
System.out.println("Time elapsed:" + sec2DHMS((endTime - startTime)/1000) );
}
}
输出：
#9990 100 records inserted #9991 100 records inserted #9992 100 records inserted #9993 100 records inserted #9994 100 records inserted #9995 100 records inserted #9996 100 records inserted #9997 100 records inserted #9998 100 records inserted #9999 100 records inserted Time elapsed:18m3s
数据库的情况：
主程序稍微扩充一下就是一款性能测试的利器。
--END-- 2019年11月9日15:38:35