4.Benchmark SQL 数据库测试工具代码——关于导入类
欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50357472
导入类LoadData 继续与配置类jTPCCConfig。
开始先设置私有静态变量，包括JDBC 协议变量，通用变量。
1. 主函数MAIN
设置warehouse变量，默认继承于配置类中的静态值。
如果参数有有numwarehouses，那么紧接其后的参数就是新的warehouse值。
此外还可以设置参数filelocation，如果存在该参数，紧接其后的参数就是新的filelocation值。
如果没有设置filelocation，则调用initJDBC函数。
完事后，初始化随机种子。
接着输出开始加载数据的时间。
然后调用loadWhse 函数加载warehouse
然后调用loadItem
然后调用loadStock
然后调用loadDist
然后调用loadCust
然后调用loadOrder
然后输出结束时间。
最后根据需要（因为可能没有进行连接）关闭JDBC连接。
2. initJDBC
initJDBC函数，先加载属性文件
获取属性文件中的配置，driver,conn,user,password
加载driver变量设置的数据库驱动。
然后获得连接 conn,关闭自动提交。
然后在数据库中创建语法对象（stmt =conn.createStatement() ），接着创建9个表的预准备语句对象。
如果出错就回滚。
3. loadWhse
WAREHOUSE表不会随着测试而变化。
先输出导入多少WAREHOUSE。
如果设置了filelocation，则导出到warehouse.csv文件。
先实例化warehouse类。
然后根据warehouse数量，进行循环导入数据。
其中调用jTPCCUtil中的函数randomNumber和randomStr.
然后调用executeUpdate来执行SQL语句。
4. loadItem
导入物品ITEM。共10 0000个物品。这个表空间测试时候不会增加。
Item名字随机产生
i_price 物品价格随机1~100的浮点。
其中 i_data 列：90%的概率 随机26~50长度的字符串
10%的概率 中间带字符串 ORIGINAL
I_im_id是1~10000 随机值。
执行SQL的时候，为了提高性能，也是每次执行10000个。
5. loadStock
stock的记录数量是warehouse乘以10 0000. 测试过程不会发生变化。
然后循环插入，每个物品的物品需要设置warehouse的ID和item的ID。
数量是随机10~100.
表中s_data列，90%概率是随机长度26~50的字符串
10%概率是字符串中间包含ORIGINAL字符
此外列s_dist_01是随机长度为24的字符串。
为了提高效率，进行批量插入，每次处理10000行。
6. loadDist
district表在测试过程中刚也不会变化。
数量为WAREHOUSE数量乘以10
根据WAREHOUSE_ID和 D_ID 来插入行，
其中 列 d_tax 是0~0.2
列 d_name 是 长度为6~10的字符串
列 d_street_1是长度为10~20的字符串
列 d_state 是长度为3的字符串
列 d_zip 都是 123456789
然后执行语句。
7. loadCust
加载CUSTOMER表和HISTORY表。
其中表HISTORY随着测试会发生变化，CUSTOMER表不会变化。
每插入一行到CUSTOMER，需要插入一行到HISTORY表中。
数量时WAREHOUSE乘以10 乘以3000.（每个WAREHOUSE有10个分区，每个分区服务3000个客户）
主要有列c_id,c_d_id,c_w_id，
此外c_discount 是0.01%到50%。
C_credit列，10% 概率是BC（ Bad Credit），90%概率是GC（Good Credit）
表history中的列 h_c_id,h_c_d_id,h_c_w_id,h_d_id,h_w_id和 customer基本一致。
提高效率也是10000个10000个进行插入执行。
8. loadOrder
加载ORDERLINE,OORDER,NEW_ORDER表。这三个表在测试过程中发生变化。
该函数调用jdbcIO类。
jdbcIO类主要实现插入ORDER、NEW_ORDER、ORDERLINE表中。
列 o_ol_cnt 是随机5~15，表示订单中物品数量。
① 都插入到oorder表中。
② 如果用户ID 大于2100时候，插入到new_order表中。（30%）
③ 此外根据订单中数量（1条插入到order表中平均对应10条），插入到order_line 表，其中ol_i_id 是1~10 0000中的随机数，ol_o_id是客户id.如果 ol_o_id小于2101（70%）,则设置变量 ol_amount=0,大于则设置为随机0.01~9999.99（30%）
Ol_supply_w_id设置为随机1~仓库数量，提供物品的WAREHOUSE_ID
Ol_qunantity是订单中该物品的数量为5。
为了提高插入效率，也是10000个为单位进行插入。
9. 源码如下：
import java.sql.*;
import java.util.*;
import java.io.*;
import java.lang.Integer;
publicclassLoadData implementsjTPCCConfig {
// *********** JDBC specific variables***********************
privatestaticConnection conn = null;
privatestaticStatement stmt = null;
privatestaticjava.sql.Timestampsysdate = null;
privatestaticPreparedStatement custPrepStmt;
privatestaticPreparedStatement distPrepStmt;
privatestaticPreparedStatement histPrepStmt;
privatestaticPreparedStatement itemPrepStmt;
privatestaticPreparedStatement nworPrepStmt;
privatestaticPreparedStatement ordrPrepStmt;
privatestaticPreparedStatement orlnPrepStmt;
privatestaticPreparedStatement stckPrepStmt;
privatestaticPreparedStatement whsePrepStmt;
// ********** generalvars**********************************
privatestaticjava.util.Date now = null;
privatestaticjava.util.Date startDate = null;
privatestaticjava.util.Date endDate = null;
privatestaticRandom gen;
privatestaticString dbType;
privatestaticint numWarehouses = 0;
privatestaticString fileLocation = "";
privatestaticboolean outputFiles = false;
privatestaticPrintWriter out = null;
privatestaticlong lastTimeMS = 0;
publicstaticvoidmain(String[]args){
System.out.println("Starting BenchmarkSQL LoadData");
System.out.println("----------------- Initialization-------------------");
numWarehouses=configWhseCount;
for(inti = 0;i < args.length;i++)
{
System.out.println(args[i]);
String str = args[i];
if (str.toLowerCase().startsWith("numwarehouses"))
{
String val = args[i + 1];
numWarehouses = Integer.parseInt(val);
}
if (str.toLowerCase().startsWith("filelocation"))
{
fileLocation =args[i + 1];
outputFiles =true;
}
}
if(outputFiles==false){
initJDBC();
}
// seed the random number generator
gen=newRandom(System.currentTimeMillis());
//######################### MAINLINE ######################################
startDate=newjava.util.Date();
System.out.println("");
System.out.println("------------- LoadData StartTime =" +startDate +
"-------------");
longstartTimeMS=newjava.util.Date().getTime();
lastTimeMS=startTimeMS;
System.out.println("");
longtotalRows=loadWhse(numWarehouses);
System.out.println("");
totalRows+=loadItem(configItemCount);
System.out.println("");
totalRows+=loadStock(numWarehouses, configItemCount);
System.out.println("");
totalRows+=loadDist(numWarehouses, configDistPerWhse);
System.out.println("");
totalRows+=loadCust(numWarehouses, configDistPerWhse, configCustPerDist);
System.out.println("");
totalRows+=loadOrder(numWarehouses, configDistPerWhse, configCustPerDist);
longrunTimeMS= (newjava.util.Date().getTime()) + 1 -startTimeMS;
endDate=newjava.util.Date();
System.out.println("");
System.out.println("------------- LoadJDBC Statistics--------------------");
System.out.println(" Start Time = " +startDate);
System.out.println(" End Time = " +endDate);
System.out.println(" Run Time = " + (int)runTimeMS/1000 + " Seconds");
System.out.println(" Rows Loaded = " +totalRows + " Rows");
System.out.println("Rows Per Second = " + (totalRows/(runTimeMS/1000)) + " Rows/Sec");
System.out.println("------------------------------------------------------");
//exit Cleanly
try{
if (outputFiles ==false)
{
if (conn !=null)
conn.close();
}
} catch(SQLExceptionse) {
se.printStackTrace();
} // end try
} // end main
staticvoidtransRollback () {
if(outputFiles==false)
{
try {
conn.rollback();
} catch(SQLExceptionse) {
System.out.println(se.getMessage());
}
} else {
out.close();
}
}
staticvoidtransCommit() {
if (outputFiles==false)
{
try {
conn.commit();
} catch(SQLExceptionse) {
System.out.println(se.getMessage());
transRollback();
}
} else {
out.close();
}
}
staticvoidinitJDBC() {
try{
// load theini file
Properties ini= newProperties();
ini.load(newFileInputStream(System.getProperty("prop")));
// display the values we need
System.out.println("driver="+ini.getProperty("driver"));
System.out.println("conn="+ini.getProperty("conn"));
System.out.println("user="+ini.getProperty("user"));
System.out.println("password=******");
// Register jdbcDriver
Class.forName(ini.getProperty("driver" ));
// make connection
conn= DriverManager.getConnection(ini.getProperty("conn"),
ini.getProperty("user"),ini.getProperty("password"));
conn.setAutoCommit(false);
// Create Statement
stmt=conn.createStatement();
distPrepStmt=conn.prepareStatement
("INSERT INTO benchmarksql.district" +
" (d_id, d_w_id, d_ytd, d_tax, d_next_o_id, d_name, d_street_1,d_street_2, d_city, d_state, d_zip) "+
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
itemPrepStmt=conn.prepareStatement
("INSERT INTO benchmarksql.item " +
" (i_id, i_name, i_price, i_data, i_im_id) " +
"VALUES (?, ?, ?, ?, ?)");
custPrepStmt=conn.prepareStatement
("INSERT INTO benchmarksql.customer" +
" (c_id, c_d_id, c_w_id, "+
"c_discount, c_credit, c_last, c_first, c_credit_lim, " +
"c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, " +
"c_street_1, c_street_2, c_city, c_state, c_zip, " +
"c_phone, c_since, c_middle, c_data) "+
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)");
histPrepStmt=conn.prepareStatement
("INSERT INTO benchmarksql.history" +
" (hist_id, h_c_id, h_c_d_id, h_c_w_id, " +
"h_d_id, h_w_id, " +
"h_date, h_amount, h_data) "+
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
ordrPrepStmt=conn.prepareStatement
("INSERT INTO benchmarksql.oorder" +
" (o_id, o_w_id, o_d_id,o_c_id, " +
"o_carrier_id, o_ol_cnt, o_all_local, o_entry_d) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
orlnPrepStmt=conn.prepareStatement
("INSERT INTO benchmarksql.order_line" +
" (ol_w_id, ol_d_id, ol_o_id, "+
"ol_number, ol_i_id, ol_delivery_d, "+
"ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
nworPrepStmt=conn.prepareStatement
("INSERT INTO benchmarksql.new_order" +
" (no_w_id, no_d_id, no_o_id) "+
"VALUES (?, ?, ?)");
stckPrepStmt=conn.prepareStatement
("INSERT INTO benchmarksql.stock " +
" (s_i_id, s_w_id, s_quantity, s_ytd, s_order_cnt, s_remote_cnt,s_data, " +
"s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, " +
"s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
whsePrepStmt=conn.prepareStatement
("INSERT INTO benchmarksql.warehouse "+
" (w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city,w_state, w_zip) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
} catch(SQLExceptionse) {
System.out.println(se.getMessage());
transRollback();
} catch(Exceptione) {
e.printStackTrace();
transRollback();
} // end try
} // end initJDBC()
staticintloadItem(intitemKount){
intk = 0;
intt = 0;
intrandPct= 0;
intlen= 0;
intstartORIGINAL= 0;
try{
now =new java.util.Date();
t = itemKount;
System.out.println("Start Item Load for " +t + " Items @ "+now+ " ...");
if (outputFiles ==true)
{
out =new PrintWriter(new FileOutputStream(fileLocation +"item.csv"));
System.out.println("Writing Item file to: " +fileLocation+ "item.csv");
}
Item item = new Item();
for (inti=1; i <= itemKount; i++) {
item.i_id = i;
item.i_name = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(14,24,gen));
item.i_price = (float)(jTPCCUtil.randomNumber(100,10000,gen)/100.0);
// i_data
randPct = jTPCCUtil.randomNumber(1, 100, gen);
len = jTPCCUtil.randomNumber(26, 50, gen);
if (randPct > 10 ) {
// 90% of time i_data isa random string of length [26 .. 50]
item.i_data = jTPCCUtil.randomStr(len);
} else {
// 10% of time i_data has "ORIGINAL" crammed somewhere inmiddle
startORIGINAL = jTPCCUtil.randomNumber(2,(len- 8),gen);
item.i_data =
jTPCCUtil.randomStr(startORIGINAL - 1) +
"ORIGINAL" +
jTPCCUtil.randomStr(len -startORIGINAL - 9);
}
item.i_im_id = jTPCCUtil.randomNumber(1, 10000,gen);
k++;
if (outputFiles ==false)
{
itemPrepStmt.setLong(1,item.i_id);
itemPrepStmt.setString(2,item.i_name);
itemPrepStmt.setDouble(3,item.i_price);
itemPrepStmt.setString(4,item.i_data);
itemPrepStmt.setLong(5,item.i_im_id);
itemPrepStmt.addBatch();
if ((k % configCommitCount) == 0) {
longtmpTime = newjava.util.Date().getTime();
String etStr = " Elasped Time(ms): " + ((tmpTime-lastTimeMS)/1000.000)+ " ";
System.out.println(etStr.substring(0, 30) +" Writingrecord " + k + " of "+ t);
lastTimeMS =tmpTime;
itemPrepStmt.executeBatch();
itemPrepStmt.clearBatch();
transCommit();
}
} else {
Stringstr="";
str = str + item.i_id + ",";
str = str + item.i_name + ",";
str = str + item.i_price + ",";
str = str + item.i_data + ",";
str = str + item.i_im_id;
out.println(str);
if ((k % configCommitCount) == 0) {
longtmpTime = newjava.util.Date().getTime();
String etStr = " Elasped Time(ms): " + ((tmpTime-lastTimeMS)/1000.000)+ " ";
System.out.println(etStr.substring(0, 30) +" Writingrecord " + k + " of "+ t);
lastTimeMS =tmpTime;
}
}
} // end for
longtmpTime = newjava.util.Date().getTime();
String etStr = " Elasped Time(ms): " + ((tmpTime-lastTimeMS)/1000.000)+ " ";
System.out.println(etStr.substring(0, 30) +" Writing final records " + k + " of " + t);
lastTimeMS =tmpTime;
if (outputFiles ==false)
{
itemPrepStmt.executeBatch();
}
transCommit();
now =new java.util.Date();
System.out.println("End Item Load @ " +now);
} catch(SQLExceptionse) {
System.out.println(se.getMessage());
transRollback();
} catch(Exceptione) {
e.printStackTrace();
transRollback();
}
return(k);
} // end loadItem()
staticintloadWhse(intwhseKount){
try{
now =new java.util.Date();
System.out.println("Start Whse Load for " +whseKount + " Whses @ "+now+ " ...");
if (outputFiles ==true)
{
out =new PrintWriter(new FileOutputStream(fileLocation +"warehouse.csv"));
System.out.println("Writing Warehouse file to: " +fileLocation+ "warehouse.csv");
}
Warehouse warehouse = new Warehouse();
for (inti=1; i <= whseKount; i++) {
warehouse.w_id = i;
warehouse.w_ytd = 300000;
// random within [0.0000 .. 0.2000]
warehouse.w_tax = (float)((jTPCCUtil.randomNumber(0,2000,gen))/10000.0);
warehouse.w_name = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(6,10,gen));
warehouse.w_street_1 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
warehouse.w_street_2 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
warehouse.w_city = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
warehouse.w_state = jTPCCUtil.randomStr(3).toUpperCase();
warehouse.w_zip = "123456789";
if (outputFiles ==false)
{
whsePrepStmt.setLong(1,warehouse.w_id);
whsePrepStmt.setDouble(2,warehouse.w_ytd);
whsePrepStmt.setDouble(3,warehouse.w_tax);
whsePrepStmt.setString(4,warehouse.w_name);
whsePrepStmt.setString(5,warehouse.w_street_1);
whsePrepStmt.setString(6,warehouse.w_street_2);
whsePrepStmt.setString(7,warehouse.w_city);
whsePrepStmt.setString(8,warehouse.w_state);
whsePrepStmt.setString(9,warehouse.w_zip);
whsePrepStmt.executeUpdate();
} else {
Stringstr="";
str = str + warehouse.w_id + ",";
str = str + warehouse.w_ytd + ",";
str = str + warehouse.w_tax + ",";
str = str + warehouse.w_name + ",";
str = str + warehouse.w_street_1 + ",";
str = str + warehouse.w_street_2 + ",";
str = str + warehouse.w_city + ",";
str = str + warehouse.w_state + ",";
str = str + warehouse.w_zip;
out.println(str);
}
} // end for
transCommit();
now =new java.util.Date();
longtmpTime = newjava.util.Date().getTime();
System.out.println("Elasped Time(ms): " + ((tmpTime -lastTimeMS)/1000.000));
lastTimeMS =tmpTime;
System.out.println("End Whse Load @ " +now);
} catch(SQLExceptionse) {
System.out.println(se.getMessage());
transRollback();
} catch(Exceptione) {
e.printStackTrace();
transRollback();
}
return(whseKount);
} // end loadWhse()
staticintloadStock(intwhseKount,intitemKount){
intk = 0;
intt = 0;
intrandPct= 0;
intlen= 0;
intstartORIGINAL= 0;
try{
now =new java.util.Date();
t = (whseKount * itemKount);
System.out.println("Start Stock Load for " +t + " units @ "+now+ " ...");
if (outputFiles ==true)
{
out =new PrintWriter(new FileOutputStream(fileLocation +"stock.csv"));
System.out.println("Writing Stock file to: " +fileLocation+ "stock.csv");
}
Stock stock = new Stock();
for (inti=1; i <= itemKount; i++) {
for (intw=1; w <= whseKount; w++) {
stock.s_i_id = i;
stock.s_w_id = w;
stock.s_quantity = jTPCCUtil.randomNumber(10, 100,gen);
stock.s_ytd = 0;
stock.s_order_cnt = 0;
stock.s_remote_cnt = 0;
// s_data
randPct = jTPCCUtil.randomNumber(1, 100, gen);
len = jTPCCUtil.randomNumber(26, 50, gen);
if (randPct > 10 ) {
// 90% of time i_data isa random string of length [26 .. 50]
stock.s_data = jTPCCUtil.randomStr(len);
} else {
// 10% of time i_data has "ORIGINAL" crammed somewhere inmiddle
startORIGINAL = jTPCCUtil.randomNumber(2,(len- 8),gen);
stock.s_data =
jTPCCUtil.randomStr(startORIGINAL - 1) +
"ORIGINAL" +
jTPCCUtil.randomStr(len -startORIGINAL - 9);
}
stock.s_dist_01 = jTPCCUtil.randomStr(24);
stock.s_dist_02 = jTPCCUtil.randomStr(24);
stock.s_dist_03 = jTPCCUtil.randomStr(24);
stock.s_dist_04 = jTPCCUtil.randomStr(24);
stock.s_dist_05 = jTPCCUtil.randomStr(24);
stock.s_dist_06 = jTPCCUtil.randomStr(24);
stock.s_dist_07 = jTPCCUtil.randomStr(24);
stock.s_dist_08 = jTPCCUtil.randomStr(24);
stock.s_dist_09 = jTPCCUtil.randomStr(24);
stock.s_dist_10 = jTPCCUtil.randomStr(24);
k++;
if (outputFiles ==false)
{
stckPrepStmt.setLong(1,stock.s_i_id);
stckPrepStmt.setLong(2,stock.s_w_id);
stckPrepStmt.setDouble(3,stock.s_quantity);
stckPrepStmt.setDouble(4,stock.s_ytd);
stckPrepStmt.setLong(5,stock.s_order_cnt);
stckPrepStmt.setLong(6,stock.s_remote_cnt);
stckPrepStmt.setString(7,stock.s_data);
stckPrepStmt.setString(8,stock.s_dist_01);
stckPrepStmt.setString(9,stock.s_dist_02);
stckPrepStmt.setString(10,stock.s_dist_03);
stckPrepStmt.setString(11,stock.s_dist_04);
stckPrepStmt.setString(12,stock.s_dist_05);
stckPrepStmt.setString(13,stock.s_dist_06);
stckPrepStmt.setString(14,stock.s_dist_07);
stckPrepStmt.setString(15,stock.s_dist_08);
stckPrepStmt.setString(16,stock.s_dist_09);
stckPrepStmt.setString(17,stock.s_dist_10);
stckPrepStmt.addBatch();
if ((k % configCommitCount) == 0) {
longtmpTime = newjava.util.Date().getTime();
String etStr = " Elasped Time(ms): " + ((tmpTime-lastTimeMS)/1000.000)+ " ";
System.out.println(etStr.substring(0, 30) +" Writingrecord " + k + " of "+ t);
lastTimeMS =tmpTime;
stckPrepStmt.executeBatch();
stckPrepStmt.clearBatch();
transCommit();
}
} else {
Stringstr="";
str = str + stock.s_i_id + ",";
str = str + stock.s_w_id + ",";
str = str + stock.s_quantity + ",";
str = str + stock.s_ytd + ",";
str = str + stock.s_order_cnt + ",";
str = str + stock.s_remote_cnt + ",";
str = str + stock.s_data + ",";
str = str + stock.s_dist_01 + ",";
str = str + stock.s_dist_02 + ",";
str = str + stock.s_dist_03 + ",";
str = str + stock.s_dist_04 + ",";
str = str + stock.s_dist_05 + ",";
str = str + stock.s_dist_06 + ",";
str = str + stock.s_dist_07 + ",";
str = str + stock.s_dist_08 + ",";
str = str + stock.s_dist_09 + ",";
str = str + stock.s_dist_10;
out.println(str);
if ((k % configCommitCount) == 0) {
longtmpTime = newjava.util.Date().getTime();
String etStr = " Elasped Time(ms): " + ((tmpTime-lastTimeMS)/1000.000)+ " ";
System.out.println(etStr.substring(0, 30) +" Writingrecord " + k + " of "+ t);
lastTimeMS =tmpTime;
}
}
} // end for [w]
} // end for [i]
longtmpTime = newjava.util.Date().getTime();
String etStr = " Elasped Time(ms): " + ((tmpTime-lastTimeMS)/1000.000)+ " ";
System.out.println(etStr.substring(0, 30) +" Writing final records " + k + " of " + t);
lastTimeMS =tmpTime;
if (outputFiles ==false)
{
stckPrepStmt.executeBatch();
}
transCommit();
now =new java.util.Date();
System.out.println("End Stock Load @ " +now);
} catch(SQLExceptionse) {
System.out.println(se.getMessage());
transRollback();
} catch(Exceptione) {
e.printStackTrace();
transRollback();
}
return(k);
} // end loadStock()
staticintloadDist(intwhseKount,intdistWhseKount){
intk = 0;
intt = 0;
try{
now =new java.util.Date();
if (outputFiles ==true)
{
out =new PrintWriter(new FileOutputStream(fileLocation +"district.csv"));
System.out.println("Writing District file to: " +fileLocation+ "district.csv");
}
District district = new District();
t = (whseKount * distWhseKount);
System.out.println("Start District Data for "+t + " Dists @ "+now+ " ...");
for (intw=1; w <= whseKount; w++) {
for (intd=1; d <= distWhseKount; d++) {
district.d_id = d;
district.d_w_id = w;
district.d_ytd = 30000;
// random within [0.0000 .. 0.2000]
district.d_tax = (float)((jTPCCUtil.randomNumber(0,2000,gen))/10000.0);
district.d_next_o_id = 3001;
district.d_name = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(6,10,gen));
district.d_street_1 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
district.d_street_2 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
district.d_city = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
district.d_state = jTPCCUtil.randomStr(3).toUpperCase();
district.d_zip = "123456789";
k++;
if (outputFiles ==false)
{
distPrepStmt.setLong(1,district.d_id);
distPrepStmt.setLong(2,district.d_w_id);
distPrepStmt.setDouble(3,district.d_ytd);
distPrepStmt.setDouble(4,district.d_tax);
distPrepStmt.setLong(5,district.d_next_o_id);
distPrepStmt.setString(6,district.d_name);
distPrepStmt.setString(7,district.d_street_1);
distPrepStmt.setString(8,district.d_street_2);
distPrepStmt.setString(9,district.d_city);
distPrepStmt.setString(10,district.d_state);
distPrepStmt.setString(11,district.d_zip);
distPrepStmt.executeUpdate();
} else {
String str = "";
str = str + district.d_id + ",";
str = str + district.d_w_id + ",";
str = str + district.d_ytd + ",";
str = str + district.d_tax + ",";
str = str + district.d_next_o_id + ",";
str = str + district.d_name + ",";
str = str + district.d_street_1 + ",";
str = str + district.d_street_2 + ",";
str = str + district.d_city + ",";
str = str + district.d_state + ",";
str = str + district.d_zip;
out.println(str);
}
} // end for [d]
} // end for [w]
longtmpTime = newjava.util.Date().getTime();
String etStr = " Elasped Time(ms): " + ((tmpTime-lastTimeMS)/1000.000)+ " ";
System.out.println(etStr.substring(0, 30) +" Writing record " + k + " of " + t);
lastTimeMS =tmpTime;
transCommit();
now =new java.util.Date();
System.out.println("End District Load @ " +now);
}
catch(SQLExceptionse) {
System.out.println(se.getMessage());
transRollback();
}
catch(Exceptione) {
e.printStackTrace();
transRollback();
}
return(k);
} // end loadDist()
staticintloadCust(intwhseKount,intdistWhseKount,intcustDistKount){
intk = 0;
intt = 0;
inti = 1;
doublecCreditLim= 0;
Customer customer = new Customer();
History history= newHistory();
PrintWriter outHist= null;
try{
now =new java.util.Date();
if (outputFiles ==true)
{
out =new PrintWriter(new FileOutputStream(fileLocation +"customer.csv"));
System.out.println("Writing Customer file to: " +fileLocation+ "customer.csv");
outHist = new PrintWriter(new FileOutputStream(fileLocation +"cust-hist.csv"));
System.out.println("Writing Customer History file to: " +fileLocation+ "cust-hist.csv");
}
t = (whseKount * distWhseKount* custDistKount* 2);
System.out.println("Start Cust-Hist Load for "+t + " Cust-Hists @ "+now+ " ...");
for (intw=1; w <= whseKount; w++) {
for (intd=1; d <= distWhseKount; d++) {
for (intc=1; c <= custDistKount; c++) {
sysdate =new java.sql.Timestamp(System.currentTimeMillis());
customer.c_id = c;
customer.c_d_id = d;
customer.c_w_id = w;
// discount is random between [0.0000 ... 0.5000]
customer.c_discount =
(float)(jTPCCUtil.randomNumber(1,5000,gen) / 10000.0);
if (jTPCCUtil.randomNumber(1,100,gen) <= 90) {
customer.c_credit = "BC"; // 10% Bad Credit
} else {
customer.c_credit = "GC"; // 90% Good Credit
}
//customer.c_credit = "GC";
customer.c_last = jTPCCUtil.getLastName(gen);
customer.c_first = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(8,16,gen));
customer.c_credit_lim = 50000;
customer.c_balance = -10;
customer.c_ytd_payment = 10;
customer.c_payment_cnt = 1;
customer.c_delivery_cnt = 0;
customer.c_street_1 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
customer.c_street_2 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
customer.c_city = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
customer.c_state = jTPCCUtil.randomStr(3).toUpperCase();
customer.c_zip = "123456789";
customer.c_phone = "(732)744-1700";
customer.c_since = sysdate.getTime();
customer.c_middle = "OE";
customer.c_data = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(300,500,gen));
history.hist_id = i;
i++;
history.h_c_id = c;
history.h_c_d_id = d;
history.h_c_w_id = w;
history.h_d_id = d;
history.h_w_id = w;
history.h_date = sysdate.getTime();
history.h_amount = 10;
history.h_data = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,24,gen));
k = k + 2;
if (outputFiles ==false)
{
custPrepStmt.setLong(1,customer.c_id);
custPrepStmt.setLong(2,customer.c_d_id);
custPrepStmt.setLong(3,customer.c_w_id);
custPrepStmt.setDouble(4,customer.c_discount);
custPrepStmt.setString(5,customer.c_credit);
custPrepStmt.setString(6,customer.c_last);
custPrepStmt.setString(7,customer.c_first);
custPrepStmt.setDouble(8,customer.c_credit_lim);
custPrepStmt.setDouble(9,customer.c_balance);
custPrepStmt.setDouble(10,customer.c_ytd_payment);
custPrepStmt.setDouble(11,customer.c_payment_cnt);
custPrepStmt.setDouble(12,customer.c_delivery_cnt);
custPrepStmt.setString(13,customer.c_street_1);
custPrepStmt.setString(14,customer.c_street_2);
custPrepStmt.setString(15,customer.c_city);
custPrepStmt.setString(16,customer.c_state);
custPrepStmt.setString(17,customer.c_zip);
custPrepStmt.setString(18,customer.c_phone);
Timestamp since = new Timestamp(customer.c_since);
custPrepStmt.setTimestamp(19,since);
custPrepStmt.setString(20,customer.c_middle);
custPrepStmt.setString(21,customer.c_data);
custPrepStmt.addBatch();
histPrepStmt.setInt(1,history.hist_id);
histPrepStmt.setInt(2,history.h_c_id);
histPrepStmt.setInt(3,history.h_c_d_id);
histPrepStmt.setInt(4,history.h_c_w_id);
histPrepStmt.setInt(5,history.h_d_id);
histPrepStmt.setInt(6,history.h_w_id);
Timestamp hdate = new Timestamp(history.h_date);
histPrepStmt.setTimestamp(7,hdate);
histPrepStmt.setDouble(8,history.h_amount);
histPrepStmt.setString(9,history.h_data);
histPrepStmt.addBatch();
if ((k % configCommitCount) == 0) {
longtmpTime = newjava.util.Date().getTime();
String etStr = " Elasped Time(ms): " + ((tmpTime-lastTimeMS)/1000.000)+ " ";
System.out.println(etStr.substring(0, 30) +" Writingrecord " + k + " of "+ t);
lastTimeMS =tmpTime;
custPrepStmt.executeBatch();
histPrepStmt.executeBatch();
custPrepStmt.clearBatch();
custPrepStmt.clearBatch();
transCommit();
}
} else {
String str = "";
str = str + customer.c_id + ",";
str = str + customer.c_d_id + ",";
str = str + customer.c_w_id + ",";
str = str + customer.c_discount + ",";
str = str + customer.c_credit + ",";
str = str + customer.c_last + ",";
str = str + customer.c_first + ",";
str = str + customer.c_credit_lim + ",";
str = str + customer.c_balance + ",";
str = str + customer.c_ytd_payment + ",";
str = str + customer.c_payment_cnt + ",";
str = str + customer.c_delivery_cnt + ",";
str = str + customer.c_street_1 + ",";
str = str + customer.c_street_2 + ",";
str = str + customer.c_city + ",";
str = str + customer.c_state + ",";
str = str + customer.c_zip + ",";
str = str + customer.c_phone +",";
Timestamp since = new Timestamp(customer.c_since);
str = str + since+ ",";
str = str + customer.c_middle + ",";
str = str + customer.c_data;
out.println(str);
str = "";
str = str + history.hist_id + ",";
str = str + history.h_c_id + ",";
str = str + history.h_c_d_id + ",";
str = str + history.h_c_w_id + ",";
str = str + history.h_d_id + ",";
str = str + history.h_w_id + ",";
Timestamp hdate = new Timestamp(history.h_date);
str = str + hdate+ ",";
str = str + history.h_amount + ",";
str = str + history.h_data;
outHist.println(str);
if ((k % configCommitCount) == 0) {
longtmpTime = newjava.util.Date().getTime();
String etStr = " Elasped Time(ms): " + ((tmpTime-lastTimeMS)/1000.000)+ " ";
System.out.println(etStr.substring(0, 30) +" Writingrecord " + k + " of "+ t);
lastTimeMS =tmpTime;
}
}
} // end for [c]
} // end for [d]
} // end for [w]
longtmpTime = newjava.util.Date().getTime();
String etStr = " Elasped Time(ms): " + ((tmpTime-lastTimeMS)/1000.000)+ " ";
System.out.println(etStr.substring(0, 30) +" Writing record " + k + " of " + t);
lastTimeMS =tmpTime;
if (outputFiles ==true) {
out.close();
outHist.close();
}else{
custPrepStmt.executeBatch();
histPrepStmt.executeBatch();
transCommit();
}
now =new java.util.Date();
System.out.println("End Cust-Hist Data Load @ " +now);
} catch(SQLExceptionse) {
System.out.println(se.getMessage());
transRollback();
if (outputFiles ==true) {
out.close();
outHist.close();
}
} catch(Exceptione) {
e.printStackTrace();
transRollback();
if (outputFiles ==true) {
out.close();
outHist.close();
}
}
return(k);
} // end loadCust()
staticintloadOrder(intwhseKount,intdistWhseKount,intcustDistKount){
intk = 0;
intt = 0;
PrintWriter outO= null;
PrintWriter outLine = null;
PrintWriter outNewOrder = null;
try{
if (outputFiles ==true)
{
outO = new PrintWriter(new FileOutputStream(fileLocation +"order.csv"));
System.out.println("Writing Order file to: " +fileLocation+ "order.csv");
outLine = new PrintWriter(new FileOutputStream(fileLocation +"order-line.csv"));
System.out.println("Writing OrderLine file to: " +fileLocation+ "order-line.csv");
outNewOrder = new PrintWriter(new FileOutputStream(fileLocation +"new-order.csv"));
System.out.println("Writing NewOrder file to: " +fileLocation+ "new-order.csv");
}
now =new java.util.Date();
Oorder oorder = new Oorder();
NewOrder new_order = new NewOrder();
OrderLine order_line = newOrderLine();
jdbcIO myJdbcIO = new jdbcIO();
t = (whseKount * distWhseKount* custDistKount);
t = (t * 11) + (t/ 3);
System.out.println("whse=" +whseKount +", dist="+distWhseKount+
", cust=" + custDistKount);
System.out.println("Start Order-Line-New Load for approx "+
t + " rows @ "+ now+" ...");
for (intw=1; w <= whseKount; w++) {
for (intd=1; d <= distWhseKount; d++) {
for (intc=1; c <= custDistKount; c++) {
oorder.o_id = c;
oorder.o_w_id = w;
oorder.o_d_id = d;
oorder.o_c_id = jTPCCUtil.randomNumber(1,custDistKount, gen);
oorder.o_carrier_id = jTPCCUtil.randomNumber(1, 10,gen);
oorder.o_ol_cnt = jTPCCUtil.randomNumber(5, 15,gen);
oorder.o_all_local = 1;
oorder.o_entry_d = System.currentTimeMillis();
k++;
if (outputFiles ==false)
{
myJdbcIO.insertOrder(ordrPrepStmt,oorder);
} else {
String str = "";
str = str + oorder.o_id + ",";
str = str + oorder.o_w_id + ",";
str = str + oorder.o_d_id + ",";
str = str + oorder.o_c_id + ",";
str = str + oorder.o_carrier_id + ",";
str = str + oorder.o_ol_cnt + ",";
str = str + oorder.o_all_local + ",";
Timestamp entry_d = new java.sql.Timestamp(oorder.o_entry_d);
str = str + entry_d;
outO.println(str);
}
// 900 rows in the NEW-ORDER table corresponding to the last
// 900 rows in the ORDER table for that district (i.e., with
// NO_O_ID between 2,101 and 3,000)
if (c > 2100 ) {
new_order.no_w_id = w;
new_order.no_d_id = d;
new_order.no_o_id = c;
k++;
if (outputFiles ==false)
{
myJdbcIO.insertNewOrder(nworPrepStmt,new_order);
} else {
String str = "";
str = str + new_order.no_w_id+ ",";
str = str + new_order.no_d_id+ ",";
str = str + new_order.no_o_id;
outNewOrder.println(str);
}
} // end new order
for (intl=1; l <= oorder.o_ol_cnt; l++) {
order_line.ol_w_id = w;
order_line.ol_d_id = d;
order_line.ol_o_id = c;
order_line.ol_number = l; // ol_number
order_line.ol_i_id = jTPCCUtil.randomNumber(1, 100000,gen);
order_line.ol_delivery_d = oorder.o_entry_d;
if (order_line.ol_o_id< 2101) {
order_line.ol_amount = 0;
} else {
// random within [0.01 .. 9,999.99]
order_line.ol_amount =
(float)(jTPCCUtil.randomNumber(1,999999,gen)/ 100.0);
}
order_line.ol_supply_w_id = jTPCCUtil.randomNumber(1,numWarehouses, gen);
order_line.ol_quantity = 5;
order_line.ol_dist_info = jTPCCUtil.randomStr(24);
k++;
if (outputFiles ==false)
{
myJdbcIO.insertOrderLine(orlnPrepStmt,order_line);
} else {
String str = "";
str = str + order_line.ol_w_id + ",";
str =str + order_line.ol_d_id +",";
str = str + order_line.ol_o_id + ",";
str = str + order_line.ol_number + ",";
str = str + order_line.ol_i_id + ",";
Timestamp delivery_d = new Timestamp(order_line.ol_delivery_d);
str = str + delivery_d+ ",";
str = str + order_line.ol_amount + ",";
str = str + order_line.ol_supply_w_id + ",";
str = str + order_line.ol_quantity + ",";
str = str + order_line.ol_dist_info;
outLine.println(str);
}
if ((k % configCommitCount) == 0) {
longtmpTime = newjava.util.Date().getTime();
String etStr = " ElaspedTime(ms): " + ((tmpTime -lastTimeMS)/1000.000) + " ";
System.out.println(etStr.substring(0, 30) +" Writingrecord " + k + " of "+ t);
lastTimeMS =tmpTime;
if (outputFiles ==false)
{
ordrPrepStmt.executeBatch();
nworPrepStmt.executeBatch();
orlnPrepStmt.executeBatch();
ordrPrepStmt.clearBatch();
nworPrepStmt.clearBatch();
orlnPrepStmt.clearBatch();
transCommit();
}
}
} // end for [l]
} // end for [c]
} // end for [d]
} // end for [w]
System.out.println(" Writing final records " +k + " of " +t);
if (outputFiles ==true)
{
outO.close();
outLine.close();
outNewOrder.close();
} else {
ordrPrepStmt.executeBatch();
nworPrepStmt.executeBatch();
orlnPrepStmt.executeBatch();
transCommit();
}
now =new java.util.Date();
System.out.println("End Orders Load @ " +now);
}
catch(SQLExceptionse) {
System.out.println(se.getMessage());
transRollback();
if(outputFiles==true){
outO.close();
outLine.close();
outNewOrder.close();
}
}
catch(Exceptione) {
e.printStackTrace();
transRollback();
if (outputFiles ==true)
{
outO.close();
outLine.close();
outNewOrder.close();
}
}
return(k);
} // end loadOrder()
} // end LoadDataClass
扫一扫
961
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
