With JDBC, you can prepare several database update statements and execute them at once. This is called batch updates. The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add individual statements to the batch. executeBatch() is used to start the execution of all the statements grouped together.
executeBatch() returns an array of integers, and each element of the array represents the update count for the respective update statement. The possible values for update counts are as follows:
EXECUTE_FAILED: The associated statement in the batch failed and a BatchUpdateException has been thrown.
SUCCESS_NO_INFO: The associated statement didn’t return an update count value.
0 or greater: The update count value.
The example provided in Listing 7-11 illustrates how batch updates are done. Note that in this example, if an update fails within the batch, all the other updates will be rolled back. I recommend letting the application developer decide whether to commit or roll back the whole set of updates when a failure occurs.
Listing 7-11: Performing Batch Updates
// batch updates import java.sql.*; import java.io.*; class SimpleExample { static DataInputStream stdin = new DataInputStream(System.in); public static void main(String args[]) { String url = "jdbc:odbc:mysource"; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection myConnection = DriverManager.getConnection(url, "javauser", "hotjava"); myConnection.setAutoCommit(false); Statement stmt = myConnection.createStatement(); String record = null; try { while ((record = stdin.readLine()) != null) { String currency = record.substring(0, record.indexOf(",")); String rate = record.substring(record.indexOf(",") + 1); String sql = "UPDATE currencies SET rate = " + rate + " WHERE currency = ‘" + currency + "‘"; stmt.addBatch(sql); } stmt.executeBatch(); myConnection.commit(); } catch (BatchUpdateException e) { System.out.println("An exception occurred after " + e.getUpdateCounts() + " updates."); myConnection.rollback(); } myConnection.close(); } catch(java.lang.Exception ex) { ex.printStackTrace(); } } }
In this example, I assume that a file containing comma-delimited values for currencies is redirected to the standard input of the Java Virtual Machine. This file should look as follows:
BEF, 0.025 FFR, 0.150 GBP, 1.625 USD, 1.125 ...