/** Test batched prepared statements. */
public void testPrepStmtBatch() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255))");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
for (int i = 0; i < 5; i++) {
if (i == 2) {
pstmt.setString(1, "xxx");
} else {
pstmt.setInt(1, i);
}
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")) {
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
} else {
// Sybase - Entire batch fails due to data conversion error
// detected in statement 3
assertEquals(5, x.length);
assertEquals(EXECUTE_FAILED, x[0]);
assertEquals(EXECUTE_FAILED, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
}
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
x = pstmt.executeBatch();
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
public Connection executeBatch() throws Sql2oException {
long start = System.currentTimeMillis();
try {
logExecution();
PreparedStatement statement = buildPreparedStatement();
connection.setBatchResult(statement.executeBatch());
this.currentBatchRecords = 0;
try {
connection.setKeys(this.returnGeneratedKeys ? statement.getGeneratedKeys() : null);
connection.setCanGetKeys(this.returnGeneratedKeys);
} catch (SQLException sqlex) {
throw new Sql2oException(
"Error while trying to fetch generated keys from database. If you are not expecting any generated keys, fix this error by setting the fetchGeneratedKeys parameter in the createQuery() method to 'false'",
sqlex);
}
} catch (Throwable e) {
this.connection.onException();
throw new Sql2oException("Error while executing batch operation: " + e.getMessage(), e);
} finally {
closeConnectionIfNecessary();
}
long end = System.currentTimeMillis();
logger.debug(
"total: {} ms; executed batch [{}]",
new Object[] {end - start, this.getName() == null ? "No name" : this.getName()});
return this.connection;
}
/** Test large batch behavior. */
public void testLargeBatch() throws Exception {
final int n = 5000;
getConnection().close();
Statement stmt = con.createStatement();
stmt.executeUpdate("create table #testLargeBatch (val int)");
stmt.executeUpdate("insert into #testLargeBatch (val) values (0)");
PreparedStatement pstmt = con.prepareStatement("update #testLargeBatch set val=? where val=?");
for (int i = 0; i < n; i++) {
pstmt.setInt(1, i + 1);
pstmt.setInt(2, i);
pstmt.addBatch();
}
int counts[] = pstmt.executeBatch();
// System.out.println(pstmt.getWarnings());
assertEquals(n, counts.length);
for (int i = 0; i < n; i++) {
assertEquals(1, counts[i]);
}
pstmt.close();
ResultSet rs = stmt.executeQuery("select count(*) from #testLargeBatch");
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertFalse(rs.next());
rs.close();
stmt.close();
}
public void doSQL() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people;");
stat.executeUpdate("create table people (name, occupation);");
PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
prep.setString(1, "Wittgenstein");
prep.setString(2, "smartypants");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("job = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
/** Test for bug [1371295] SQL Server continues after duplicate key error. */
public void testPrepStmtBatchDupKey() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255), PRIMARY KEY (id))");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
for (int i = 0; i < 5; i++) {
if (i == 2) {
pstmt.setInt(1, 1); // Will cause duplicate key batch will continue
} else {
pstmt.setInt(1, i);
}
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
x = pstmt.executeBatch();
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
public void run() {
try {
PreparedStatement pstmt =
con.prepareStatement(
"insert into #testConcurrentBatch (v1, v2, v3, v4, v5, v6) values (?, ?, ?, ?, ?, ?)");
for (int i = 0; i < 64; ++i) {
// Make sure we end up with 64 different prepares, use the binary representation of i to
// set each
// of the 6 parameters to either an int or a string.
int mask = i;
for (int j = 1; j <= 6; ++j, mask >>= 1) {
if ((mask & 1) != 0) {
pstmt.setInt(j, i);
} else {
pstmt.setString(j, String.valueOf(i));
}
}
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
e.printStackTrace();
x = e.getUpdateCounts();
}
if (x.length != 64) {
throw new SQLException("Expected 64 update counts, got " + x.length);
}
for (int i = 0; i < x.length; ++i) {
if (x[i] != 1) {
throw new SQLException("Error at position " + i + ", got " + x[i] + " instead of 1");
}
}
// Rollback the transaction, exposing any race conditions.
con.rollback();
pstmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
exceptions.add(ex);
}
}
/** Test for PreparedStatement batch with no parameters. */
public void testPrepStmtNoParams() throws Exception {
Statement stmt = con.createStatement();
stmt.execute(
"create table #testbatch (id numeric(10) identity, data varchar(255), PRIMARY KEY (id))");
PreparedStatement pstmt =
con.prepareStatement("INSERT INTO #testbatch (data) VALUES ('Same each time')");
for (int i = 0; i < 5; i++) {
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
/** Test for PreparedStatement batch with variable parameter types. */
public void testPrepStmtVariableParams() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data int, PRIMARY KEY (id))");
PreparedStatement pstmt =
con.prepareStatement("INSERT INTO #testbatch VALUES (?, convert(int, ?))");
for (int i = 0; i < 5; i++) {
pstmt.setInt(1, i);
if (i == 2) {
// This statement will require a string param instead of an int
pstmt.setString(2, "123");
} else {
pstmt.setInt(2, 123);
}
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
ResultSet rs = stmt.executeQuery("SELECT * FROM #testbatch");
assertNotNull(rs);
int i = 0;
while (rs.next()) {
assertEquals(123, rs.getInt(2));
i++;
}
assertEquals(5, i);
}
/** this is a test for the data truncation problem described in bug [2731952] */
public void testDataTruncation() throws SQLException {
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #DATATRUNC (id int, data text)");
stmt.close();
// create 2 different strings
StringBuilder sb1 = new StringBuilder(10000);
StringBuilder sb2 = new StringBuilder(100);
for (int i = 1; i <= 1000; i++) {
sb1.append(" +++ ")
.append(" ".substring(String.valueOf(i).length()))
.append(i)
.append("\n");
}
for (int i = 1; i <= 10; i++) {
sb2.append(" --- ")
.append(" ".substring(String.valueOf(i).length()))
.append(i)
.append("\n");
}
String string1 = sb1.toString();
String string2 = sb2.toString();
PreparedStatement pstmt =
con.prepareStatement("insert into #DATATRUNC (id, data) values (?, ?)");
// insert both values into DB in batch mode
pstmt.setInt(1, 1);
pstmt.setString(2, string1);
pstmt.addBatch();
pstmt.setInt(1, 2);
pstmt.setString(2, string2);
pstmt.addBatch();
assertTrue(Arrays.equals(new int[] {1, 1}, pstmt.executeBatch()));
// insert first string again, no batch
pstmt.setInt(1, 3);
pstmt.setString(2, string1);
assertEquals(1, pstmt.executeUpdate());
pstmt.close();
// ensure all 3 entries are still intact
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select data from #DATATRUNC order by id asc");
// 1st value, should be string1
assertTrue(rs.next());
String value = rs.getString(1);
assertEquals(string1.length(), value.length());
assertEquals(string1, value);
// 2nd value, should be string2
assertTrue(rs.next());
value = rs.getString(1);
assertEquals(string2.length(), value.length());
assertEquals(string2, value);
// 3rd value, should be string1
assertTrue(rs.next());
value = rs.getString(1);
assertEquals(string1.length(), value.length());
assertEquals(string1, value);
rs.close();
stmt.close();
}
static int loadCust(int whseKount, int distWhseKount, int custDistKount) {
int k = 0;
int t = 0;
Customer customer = new Customer();
History history = new History();
PrintWriter outHist = null;
try {
now = new java.util.Date();
if (outputFiles == true) {
out = new PrintWriter(new FileOutputStream(fileLocation + "customer.csv"));
System.out.println("\nWriting Customer file to: " + fileLocation + "customer.csv");
outHist = new PrintWriter(new FileOutputStream(fileLocation + "cust-hist.csv"));
System.out.println("\nWriting Customer History file to: " + fileLocation + "cust-hist.csv");
}
t = (whseKount * distWhseKount * custDistKount * 2);
System.out.println("\nStart Cust-Hist Load for " + t + " Cust-Hists @ " + now + " ...");
for (int w = 1; w <= whseKount; w++) {
for (int d = 1; d <= distWhseKount; d++) {
for (int c = 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) <= 10) {
customer.c_credit = "BC"; // 10% Bad Credit
} else {
customer.c_credit = "GC"; // 90% Good Credit
}
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.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.h_c_id);
histPrepStmt.setInt(2, history.h_c_d_id);
histPrepStmt.setInt(3, history.h_c_w_id);
histPrepStmt.setInt(4, history.h_d_id);
histPrepStmt.setInt(5, history.h_w_id);
Timestamp hdate = new Timestamp(history.h_date);
histPrepStmt.setTimestamp(6, hdate);
histPrepStmt.setDouble(7, history.h_amount);
histPrepStmt.setString(8, history.h_data);
histPrepStmt.addBatch();
if ((k % configCommitCount) == 0) {
long tmpTime = new java.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;
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;
out.println(str);
str = "";
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) {
long tmpTime = new java.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;
}
}
} // end for [c]
} // end for [d]
} // end for [w]
long tmpTime = new java.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;
custPrepStmt.executeBatch();
histPrepStmt.executeBatch();
transCommit();
now = new java.util.Date();
if (outputFiles == true) {
outHist.close();
}
System.out.println("End Cust-Hist Data Load @ " + now);
} catch (SQLException se) {
System.out.println(se.getMessage());
transRollback();
if (outputFiles == true) {
outHist.close();
}
} catch (Exception e) {
e.printStackTrace();
transRollback();
if (outputFiles == true) {
outHist.close();
}
}
return (k);
} // end loadCust()
static int loadStock(int whseKount, int itemKount) {
int k = 0;
int t = 0;
int randPct = 0;
int len = 0;
int startORIGINAL = 0;
try {
now = new java.util.Date();
t = (whseKount * itemKount);
System.out.println("\nStart Stock Load for " + t + " units @ " + now + " ...");
if (outputFiles == true) {
out = new PrintWriter(new FileOutputStream(fileLocation + "stock.csv"));
System.out.println("\nWriting Stock file to: " + fileLocation + "stock.csv");
}
Stock stock = new Stock();
for (int i = 1; i <= itemKount; i++) {
for (int w = 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 in middle
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) {
long tmpTime = new java.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;
stckPrepStmt.executeBatch();
stckPrepStmt.clearBatch();
transCommit();
}
} else {
String str = "";
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) {
long tmpTime = new java.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;
}
}
} // end for [w]
} // end for [i]
long tmpTime = new java.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 (SQLException se) {
System.out.println(se.getMessage());
transRollback();
} catch (Exception e) {
e.printStackTrace();
transRollback();
}
return (k);
} // end loadStock()
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("\nStart Item Load for " + t + " Items @ " + now + " ...");
if (outputFiles == true) {
out = new PrintWriter(new FileOutputStream(fileLocation + "item.csv"));
System.out.println("\nWriting 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(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 in middle
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) {
long tmpTime = new java.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;
itemPrepStmt.executeBatch();
itemPrepStmt.clearBatch();
transCommit();
}
} else {
String str = "";
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) {
long tmpTime = new java.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;
}
}
} // end for
long tmpTime = new java.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 == false) {
itemPrepStmt.executeBatch();
}
transCommit();
now = new java.util.Date();
System.out.println("End Item Load @ " + now);
} catch (SQLException se) {
System.out.println(se.getMessage());
transRollback();
} catch (Exception e) {
e.printStackTrace();
transRollback();
}
return (k);
} // end loadItem()
static int loadOrder(int whseKount, int distWhseKount, int custDistKount) {
int k = 0;
int t = 0;
PrintWriter outLine = null;
PrintWriter outNewOrder = null;
try {
if (outputFiles == true) {
out = new PrintWriter(new FileOutputStream(fileLocation + "order.csv"));
System.out.println("\nWriting Order file to: " + fileLocation + "order.csv");
outLine = new PrintWriter(new FileOutputStream(fileLocation + "order-line.csv"));
System.out.println("\nWriting Order Line file to: " + fileLocation + "order-line.csv");
outNewOrder = new PrintWriter(new FileOutputStream(fileLocation + "new-order.csv"));
System.out.println("\nWriting New Order file to: " + fileLocation + "new-order.csv");
}
now = new java.util.Date();
Oorder oorder = new Oorder();
NewOrder new_order = new NewOrder();
OrderLine order_line = new OrderLine();
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("\nStart Order-Line-New Load for approx " + t + " rows @ " + now + " ...");
for (int w = 1; w <= whseKount; w++) {
for (int d = 1; d <= distWhseKount; d++) {
for (int c = 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;
out.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 (int l = 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]
float f = (float) (jTPCCUtil.randomNumber(1, 999999, gen) / 100.0);
order_line.ol_amount = f; // XXX float error line 1.
}
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) {
long tmpTime = new java.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 == 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 == false) {
ordrPrepStmt.executeBatch();
nworPrepStmt.executeBatch();
orlnPrepStmt.executeBatch();
} else {
outLine.close();
outNewOrder.close();
}
transCommit();
now = new java.util.Date();
System.out.println("End Orders Load @ " + now);
} catch (Exception e) {
e.printStackTrace();
transRollback();
if (outputFiles == true) {
outLine.close();
outNewOrder.close();
}
}
return (k);
} // end loadOrder()
/**
* Insert all service in services table
*
* @param noms
* @throws SQLException
*/
public static void insertStations(List<String[]> pdvs) throws SQLException {
int idPdv;
String nom = "";
String adresse = "";
String ville = "";
String cp = "";
String type_route;
String[] ouvert = new String[3];
String[] fermer = new String[3];
Time hor_Ouv = null;
hor_Ouv.valueOf("00:00:00");
Time hor_Ferm = null;
hor_Ferm.valueOf("00:00:00");
double latitudePdv;
double longitudePdv;
String path = "src\\DAL\\StationsNOM.csv";
List<String> nomStations = LectureFichier.readTXT(path);
Random rand = new Random();
int nbrand = 2;
String requete =
"INSERT INTO stations "
+ "(id_Station,Nom,Adresse,Ville,CP,Type_route,Horaire_ouverture,Horaire_fermeture,latitude,longitude) "
+ "VALUES(?,?,?,?,?,?,?,?,?,?)";
// int[] count= new int[0];
Connection connection = ConnexionManager.GetInstance().GetConnection();
for (String[] pdv : pdvs) {
if (!pdv[0].equals("id")) {
idPdv = Integer.parseInt(pdv[0]);
// integration des noms g�n�r� al�atoirement
nbrand = rand.nextInt(nomStations.size() - 2) + 1;
nom = nomStations.get(nbrand);
adresse = pdv[1];
ville = pdv[2];
cp = pdv[3];
type_route = pdv[4];
ouvert = pdv[5].split(":");
fermer = pdv[6].split(":");
// fermer = pdv[6].replace(':', '-');
hor_Ouv =
new Time(
Integer.parseInt(ouvert[0]),
Integer.parseInt(ouvert[1]),
Integer.parseInt(ouvert[2]));
hor_Ferm =
new Time(
Integer.parseInt(fermer[0]),
Integer.parseInt(fermer[1]),
Integer.parseInt(fermer[2]));
latitudePdv = Double.parseDouble(pdv[7]);
longitudePdv = Double.parseDouble(pdv[8]);
try {
PreparedStatement requeteSql = connection.prepareStatement(requete);
requeteSql.setInt(1, idPdv);
requeteSql.setString(2, nom);
requeteSql.setString(3, adresse);
requeteSql.setString(4, ville);
requeteSql.setString(5, cp);
requeteSql.setString(6, type_route);
requeteSql.setTime(7, hor_Ouv);
requeteSql.setTime(8, hor_Ferm);
requeteSql.setDouble(9, latitudePdv);
requeteSql.setDouble(10, longitudePdv);
requeteSql.addBatch();
int[] count = requeteSql.executeBatch();
// requeteSql.executeBatch();
// System.out.println(count.length);
} catch (SQLException sqle) {
// TODO Auto-generated catch block
ConnexionManager.GetInstance().GetConnection().rollback();
sqle.printStackTrace();
}
}
}
}