int rows = 0;
int last = new Integer(iterations).intValue();
long start = 0;
long end = 0;
Statement stmt = null;
PreparedStatement pstmt = null;
String text =
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890" +
"12345678901234567890123456789012345678901234567890";
// Set autocommit off
try {
conn.setAutoCommit(false);
conn.commit();
}
catch (SQLException e) {
System.err.println(e.getMessage());
}
// One statement at a time, awfully slow!
try {
start = System.currentTimeMillis();
stmt = conn.createStatement();
for (int i=0;i < last;i++) {
rows = stmt.executeUpdate(
"insert into test_batch " +
"( test_batch_id, text ) " +
"values " +
"( test_batch_id.nextval, '" + text + "' )");
}
end = System.currentTimeMillis();
stmt.close();
stmt = null;
conn.commit();
System.out.println(
last + " inserts using statement: " +
(end - start) + " milliseconds");
}
catch (SQLException e) {
System.err.println(e.getMessage());
}
finally {
if (stmt != null)
try { stmt.close(); } catch (SQLException ignore) { }
}
// One prepared statement at a time, better!
try {
pstmt = conn.prepareStatement(
"insert into test_batch " +
"( test_batch_id, text ) " +
"values " +
"( test_batch_id.nextval, ? )");
start = System.currentTimeMillis();
for (int i=0;i < last;i++) {
pstmt.setString( 1, text );
rows = pstmt.executeUpdate();
}
end = System.currentTimeMillis();
pstmt.close();
pstmt = null;
conn.commit();
System.out.println(
last + " inserts using prepared statement: " +
(end - start) + " milliseconds");
}
catch (SQLException e) {
System.err.println(e.getMessage());
}
finally {
if (pstmt != null)
try { pstmt.close(); } catch (SQLException ignore) { }
}
// But now using standard batching: wow!!!
try {
pstmt = conn.prepareStatement(
"insert into test_batch " +
"( test_batch_id, text ) " +
"values " +
"( test_batch_id.nextval, ? )");
start = System.currentTimeMillis();
for (int i=0;i < last;i++) {
pstmt.setString( 1, text );
pstmt.addBatch();
}
int [] rowArray = pstmt.executeBatch();
end = System.currentTimeMillis();
pstmt.close();
pstmt = null;
conn.commit();
System.out.println(
last + " inserts using prepared statement batching: " +
(end - start) + " milliseconds");
}
catch (SQLException e) {
System.err.println(e.getMessage());
}
finally {
if (pstmt != null)
try { pstmt.close(); } catch (SQLException ignore) { }
}
// Clean up
try {
stmt = conn.createStatement();
rows = stmt.executeUpdate("delete test_batch");
conn.commit();
stmt.close();
stmt = null;
System.out.println(rows + " rows deleted");
System.out.println("");
}
catch (SQLException e) {
System.err.println(e.getMessage());
}
finally {
if (stmt != null)
try { stmt.close(); } catch (SQLException ignore) { }
}
