Batch Update Sample Application

Table Of Contents 

• Overview of the Sample Application
• Required Software
• Notations Used
• Application Set-up and Configuration
• Database Set-up
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files

The Batch Update feature of JDBC 2.0 API provides the option to send multiple updates to the database to be executed as a batch rather than sending each update separately. This sample application uses Batch Update mechanism of JDBC2.0 API to do the reservations and cancellations in batches.

Working of the Sample

The sample application uses two database tables viz.. OTN_HOTELS and OTN_HOTEL_BOOKINGS.

Reservations and cancellation can be done on any hotel existing in the database. More than one reservation and cancellation records can be created in a reservation or cancellation cart and all of them can be committed in batch at one button press. Two separate batches are created for Reservation and Cancellation. All reservations go in one batch and all cancellations go in another batch.

When the application is invoked, it connects to the database and fetches all records available in the OTN_HOTELS and is displayed. The Reservation Cart Table is initially empty. The user can select any hotel from the 'Hotels' Table and opt for reservation or cancellation using Reserve or Cancel button. Whenever a user performs a reservation or cancellation it is added to the 'Reservation Cart'. Both the batches, Reservation Batch and Cancellation Batch get committed when the 'Submit Cart' button is clicked. In case of a BatchUpdateException or SQLException, the transaction of both the batches are rolled back to the previous state at which the cart was not submitted.

Here is the code usage of Batch Update APIs. You can find more details of the code in BatchUpdateSample.java file under src/oracle/otnsamples/jdbc/batchupdate folder. Look into Description of Sample Files section for folder and file details.

private void submitCart() { // PreparedStatement for cancellation. PreparedStatement cancelStmt = null; try { // Get the No. of Rows in the Reservation Cart to be Updated. int noOfRows = gui.hotelReservationModel.getRowCount(); ................. ................. // Create a PreparedStatement for reservation. PreparedStatement reStmt = connection.prepareStatement( "insert into otn_hotel_bookings (booking_id, hot_id, "+ "arrival_date,room_type, no_of_rooms, no_of_nights, "+ "reserved_by) VALUES(?,?,?,?,?,?,?)"); // Create a PreparedStatement for cancellation. cancelStmt = connection.prepareStatement( "delete from otn_hotel_bookings where BOOKING_ID=?"); for(int i=0; i<noOfRows; i++) { // Get a row from the JTable Vector newVect = gui.hotelReservationModel.getRow(i); // Get the Reservation/Cancellation ID String resOrCan = newVect.elementAt(7).toString(); // Booking ID int bookID = Integer.parseInt(newVect.elementAt(1).toString()); // Check Whether Reservation or Cancellation. if (resOrCan.equals("R")) { // if it is Reservation int hotID = Integer.parseInt(newVect.elementAt(0).toString()); String name = newVect.elementAt(2).toString(); // name String arrDate = newVect.elementAt(3).toString(); // Arrival Date String roomType = newVect.elementAt(4).toString();// Room Type int noRooms = Integer.parseInt(newVect.elementAt(5).toString()); int noNights = Integer.parseInt(newVect.elementAt(6).toString()); .............. .............. // Bind the column values reStmt.setInt(1,bookID); // Booking ID reStmt.setInt(2,hotID); // Hotel ID reStmt.setDate(3,tempDate); // Arrival Date reStmt.setString(4,roomType); // Room rate reStmt.setInt(5,noRooms); // No of Rooms reStmt.setInt(6,noNights); // No of Nights reStmt.setString(7,name); // Reserved by // Add the update to the reservation batch. reStmt.addBatch(); } else if (resOrCan.equals("C")) { // bind the column value to Booking ID cancelStmt.setInt(1,bookID); // Add the update to cancellation batch. cancelStmt.addBatch(); } } // Execute the reservation batch. int [] upDateCounts = reStmt.executeBatch(); // Close the Statement. reStmt.close(); } catch(BatchUpdateException b) { // Catch BatchUpdate Exception ......... ......... // Roll back to the previous state. try { connection.rollback(); return; } catch (SQLException e) { } // Catch SQL Errors ......... ......... // Update the Cancellation Batch. int [] upDateCounts2 = cancelStmt.executeBatch(); cancelStmt.close(); // Close the Statement connection.commit(); // Commit the changes. ......... .........

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here)
  or JDK1.2.x or above. This can be downloaded from here.
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here.
• Oracle9i JDBC Driver. This can be downloaded from here.

• Unjar the provided BatchUpdateSample.jar using the following command 

> jar xvf BatchUpdateSample.jar

Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin is present in your system path. (JDK_HOME is the root directory of the JDKx.x installation). This creates a folder BatchUpdateSample with all the source files.

• Edit BatchUpdateSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and Password to connect to your own database.

From a SQL*Plus Client, connect to the database using the credentials used above. Run the SQL Script file BatchUpdate.sql to create the database tables and records required by the application. Look into Description of Sample Files section for folder and file details of the SQL file.
For example: SQL>@D:\BatchUpdateSample\config\BatchUpdate.sql

This sample application can be run in 3 different ways listed below.

• From Oracle9i JDeveloper
• From JDK for Windows
• From JDK for Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the BatchUpdate.jws from the BatchUpdateSample directory.
• Next, select Project/Make BatchUpdate.jpr from main menu.
• Now, select Run/Run BatchUpdate.jpr from main menu to run the application.

From JDK for Windows

This section will describe steps to run the application from console using JDK on Windows. The sample can be run either manually or using a script file .

Run application using batch file: run.bat provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.bat from the command prompt, from BatchUpdateSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

D:\BatchUpdateSample> set JDBC_HOME=d:\oracle9i\jdbc\lib
D:\BatchUpdateSample> set JAVA_HOME=d:\jdk1.3.1
D:\BatchUpdateSample> run

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file. Example: classes12.zip
• BatchUpdateSample directory where Connection.properties exists and current directory are also added to the CLASSPATH
  Example: 
  D:\BatchUpdateSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\BatchUpdateSample;.
  
• Make sure that Java is in the PATH
  Example: D:\BatchUpdateSample>set PATH=.;d:\jdk1.3.1\bin;%PATH%
  
• From the directory BatchUpdateSample\src\oracle\otnsamples\jdbc\batchupdate, compile all the java files using javac:
  Example: 
  D:\BatchUpdateSample\src\oracle\otnsamples\jdbc\batchupdate>javac -d . *.java
  
• Run the class file using java from the same BatchUpdateSample\src\oracle\otnsamples\jdbc\batchupdate directory
  Example:
  D:\BatchUpdateSample\src\oracle\otnsamples\jdbc\batchupdate>
  java oracle.otnsamples.jdbc.batchupdate.BatchUpdateSample

From JDK for Linux

This section will describe steps to run the application from console using JDK on Red Hat Linux Advanced Server Release 2.1. The sample can be run either manually or using a script file.

Run application using script file: run.sh provided:

By setting few environment variables, the sample application could be directly run by just executing the script file: run.sh from the command prompt, from BatchUpdateSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to BatchUpdateSample directory and from the $ prompt, use the command below to run the script file:
  $sh run.sh

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file. Example: classes12.zip
• BatchUpdateSample directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/idcotn/download/jdbc/lib/classes12.zip:
  /home1/idcotn/download/basicjdbc/BatchUpdateSample:.
• Make sure that Java is in the PATH
  Example: $export PATH=/usr/java/jdk1.3.1_02/bin:$PATH
  
• From the directory BatchUpdateSample/src/oracle/otnsamples/jdbc/batchupdate, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same BatchUpdateSample/src/oracle/otnsamples/jdbc/batchupdate directory.
  Example:
  $java oracle.otnsamples.jdbc.batchupdate.BatchUpdateSample