4BenchmarkSQL数据库测试工具代码关于导入类概论.docx

1、4BenchmarkSQL数据库测试工具代码关于导入类概论4.Benchmark SQL 数据库测试工具代码关于导入类导入类LoadData 继续与配置类jTPCCConfig。开始先设置私有静态变量，包括JDBC 协议变量，通用变量。1.主函数MAIN设置warehouse变量，默认继承于配置类中的静态值。如果参数有有numwarehouses，那么紧接其后的参数就是新的warehouse值。此外还可以设置参数 filelocation，如果存在该参数，紧接其后的参数就是新的filelocation值。 如果没有设置filelocation，则调用initJDBC函数。 完事后，初始化随机种

2、子。 接着输出开始加载数据的时间。 然后调用loadWhse 函数加载warehouse 然后调用loadItem然后调用loadStock然后调用loadDist然后调用loadCust然后调用loadOrder 然后输出结束时间。 最后根据需要（因为可能没有进行连接）关闭JDBC连接。2.initJDBCinitJDBC函数，先加载属性文件获取属性文件中的配置，driver,conn ,user,password加载driver变量设置的数据库驱动。然后获得连接 conn,关闭自动提交。然后在数据库中创建语法对象（stmt = conn.createStatement() ），接着创建9个

3、表的预准备语句对象。如果出错就回滚。3.loadWhseWAREHOUSE表不会随着测试而变化。先输出导入多少WAREHOUSE。如果设置了filelocation，则导出到warehouse.csv文件。先实例化warehouse类。然后根据warehouse数量，进行循环导入数据。其中调用jTPCCUtil中的函数randomNumber和randomStr.然后调用executeUpdate来执行SQL语句。4.loadItem导入物品ITEM。共10 0000个物品。这个表空间测试时候不会增加。Item名字随机产生i_price 物品价格随机1100的浮点。其中 i_data 列：90

4、%的概率 随机2650长度的字符串 10%的概率 中间带字符串 ORIGINALI_im_id是110000 随机值。执行SQL的时候，为了提高性能，也是每次执行10000个。5.loadStockstock的记录数量是 warehouse乘以10 0000. 测试过程不会发生变化。然后循环插入，每个物品的物品需要设置warehouse 的ID和item的ID。数量是随机10100.表中s_data列，90%概率是随机长度2650的字符串 10%概率是字符串中间包含ORIGINAL字符此外列s_dist_01是随机长度为24的字符串。为了提高效率，进行批量插入，每次处理10000行。6.loa

5、dDistdistrict表在测试过程中刚也不会变化。数量为WAREHOUSE数量乘以10根据WAREHOUSE_ID和 D_ID 来插入行，其中 列 d_tax 是00.2 列 d_name 是 长度为610的字符串 列 d_street_1是长度为1020的字符串 列 d_state 是长度为3的字符串 列 d_zip 都是 123456789然后执行语句。7.loadCust加载CUSTOMER表和HISTORY表。 其中表HISTORY随着测试会发生变化，CUSTOMER表不会变化。每插入一行到CUSTOMER，需要插入一行到HISTORY表中。数量时WAREHOUSE乘以10 乘以3

6、000.（每个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类。j

7、dbcIO类主要实现插入ORDER、NEW_ORDER、ORDERLINE表中。列 o_ol_cnt 是随机515，表示订单中物品数量。1都插入到oorder表中。2如果用户ID 大于2100时候，插入到new_order表中。（30%）3此外根据订单中数量（1条插入到order表中平均对应10条），插入到order_line 表，其中ol_i_id 是110 0000中的随机数，ol_o_id是客户id.如果 ol_o_id小于2101（70%）,则设置变量 ol_amount=0,大于则设置为随机0.019999.99（30%）Ol_supply_w_id设置为随机1仓库数量，提供物品的W

8、AREHOUSE_IDOl_qunantity是订单中该物品的数量为5。为了提高插入效率，也是10000个为单位进行插入。9.源码如下：import java.sql.*;import java.util.*;import java.io.*;import java.lang.Integer;public class LoadData implements jTPCCConfig / * JDBC specific variables * private static Connection conn = null; private static Statement stmt = null; p

9、rivate static java.sql.Timestamp sysdate = null; private static PreparedStatement custPrepStmt; private static PreparedStatement distPrepStmt; private static PreparedStatement histPrepStmt; private static PreparedStatement itemPrepStmt; private static PreparedStatement nworPrepStmt; private static P

10、reparedStatement ordrPrepStmt; private static PreparedStatement orlnPrepStmt; private static PreparedStatement stckPrepStmt; private static PreparedStatement whsePrepStmt; / * general vars * private static java.util.Date now = null; private static java.util.Date startDate = null; private static java

11、.util.Date endDate = null; private static Random gen; private static String dbType; private static int numWarehouses = 0; private static String fileLocation = ; private static boolean outputFiles = false; private static PrintWriter out = null; private static long lastTimeMS = 0; public static void m

12、ain(String args) System.out.println(Starting BenchmarkSQL LoadData); System.out.println(- Initialization -); numWarehouses = configWhseCount; for (int i = 0; i args.length; i+) System.out.println(argsi); String str = argsi; if (str.toLowerCase().startsWith(numwarehouses) String val = argsi + 1; numW

13、arehouses = Integer.parseInt(val); if (str.toLowerCase().startsWith(filelocation) fileLocation = argsi + 1; outputFiles = true; if (outputFiles = false) initJDBC(); / seed the random number generator gen = new Random(System.currentTimeMillis(); /# MAINLINE # startDate = new java.util.Date(); System.

14、out.println(); System.out.println(- LoadData StartTime = + startDate + -); long startTimeMS = new java.util.Date().getTime(); lastTimeMS = startTimeMS; System.out.println(); long totalRows = loadWhse(numWarehouses); System.out.println(); totalRows += loadItem(configItemCount); System.out.println();

15、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, configD

16、istPerWhse, configCustPerDist); long runTimeMS = (new java.util.Date().getTime() + 1 - startTimeMS; endDate = new java.util.Date(); System.out.println(); System.out.println(- LoadJDBC Statistics -); System.out.println( Start Time = + startDate); System.out.println( End Time = + endDate); System.out.

17、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(SQLExcep

18、tion se) se.printStackTrace(); / end try / end main static void transRollback () if (outputFiles = false) try conn.rollback(); catch(SQLException se) System.out.println(se.getMessage(); else out.close(); static void transCommit() if (outputFiles = false) try mit(); catch(SQLException se) System.out.

19、println(se.getMessage(); transRollback(); else out.close(); static void initJDBC() try / load the ini file Properties ini = new Properties(); ini.load( new FileInputStream(System.getProperty(prop); / display the values we need System.out.println(driver= + ini.getProperty(driver); System.out.println(

20、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);

21、 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); itemPr

22、epStmt = 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_yt

23、d_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

24、_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

25、= 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 +

26、(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_

27、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(SQLException se) System.out.println(se

28、.getMessage(); transRollback(); catch(Exception e) e.printStackTrace(); transRollback(); / end try / end initJDBC() static int loadItem(int itemKount) int k = 0; int t = 0; int randPct = 0; int len = 0; int startORIGINAL = 0; try now = new java.util.Date(); t = itemKount; System.out.println(Start It

29、em 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 (int i=1; i = itemKount; i+) item.i_id = i; item.i_name = jTPCCUtil.randomStr(jT