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 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]);
}
/** 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();
}
/** 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()
/**
* 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();
}
}
}
}