Performing Batch Updates

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

start example
// 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();                 }         } }
end example

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 ... 



JDBC 3. 0. JAVA Database Connectivity
JDBC 3: Java Database Connectivity
ISBN: 0764548751
EAN: 2147483647
Year: 2002
Pages: 148

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net