Programmatic 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

Programmatic updates provide an ability to make database updates using the JDBC 2.0 APIs instead of using SQL statements. This application illustrates how to insert, update and delete records programmatically from a database table using JDBC 2.0 APIs.

Working of the Sample

The sample application uses a database table:OTN_HOTELS to store hotel related information.

The sample application is a SWING based client displaying all the existing hotels in a JTable. Hotels can be inserted, updated and deleted using this client. ResultSet Updatable APIs are used for insert, update and delete operations.

Here is the sample code using ResultSet Updatabe APIs. You can find more details of the code in ProgUpdateSample.java file under src/oracle/otnsamples/jdbc/progupdate folder. Look into Description of Sample Files section for folder and file details.

public class ProgUpdateSample {

  ........
  ........


  // The updatable resultSet.
  private ResultSet updatableRst = null;
  
  /**
  * Method to select records from the otn_hotels table. Here, updatableRst is
  * assigned a set of rows that satisfies the select query. This ResultSet will
  * be used to handle insert, update and delete operations using programmatic 
  * update APIs.
  */
  private void selectRecords(String name,String address,String id,
                           String phone,String fax,String hotelUrl,
                           String airport) {


    // The SQL query for fetching rows from the otn_hotels table.
    String query =  "SELECT id,name,address,phone,fax,"
                     +"hotel_url,hotel_airport FROM otn_hotels "
                     +"WHERE UPPER(name) like ? AND UPPER(address) "
                     +"LIKE ? AND id LIKE ? "
                     +"AND phone LIKE ? AND UPPER(NVL(fax, '%')) LIKE ? "
                     +"AND UPPER(NVL(hotel_url, '%')) LIKE ? "
                     +"AND UPPER(NVL(hotel_airport, '%')) LIKE ?";

    try {
      // Create a PreparedStatement using the query.
      // To get an updatable and sensitive resultSet here,

      // appropriate arguments have been used.
      stmt = connection.prepareStatement( query,
                                          ResultSet.TYPE_SCROLL_SENSITIVE,
                                          ResultSet.CONCUR_UPDATABLE
                                         );

      // Bind the parameters
      ...........
      ...........
        
      // Bind the PreparedStatement with corresponding values.
      ...........
      ...........


      // Execute the PreparedStatement to get the updatable result set.
      updatableRst = stmt.executeQuery();

      .........

      // Populating the Result set, retrieve rows, and update gui to
      // reflect each selected record.
      while (updatableRst.next()) { // Point result set to next row.

        // Retrieve column values for this row.
        String localname     = updatableRst.getString("name");
        String localaddress  = updatableRst.getString("address");
        String localid       = String.valueOf(updatableRst.getInt("id")); 
        String localphone    = updatableRst.getString("phone");
        String localfax      = updatableRst.getString("fax");

        String localhotelUrl = updatableRst.getString("hotel_url");
        String localairport  = updatableRst.getString("hotel_airport");
        ...........
        ...........
      }
    } catch (SQLException ex) { // Trap SQL errors.
        .........
    }
  }

  /**
   * This method demonstrates how to insert a new row into the database using
   * programmatic updates. To make this possible, the JDBC 2.0 API defines the
   * concept of an insert row. This is a special row, associated with the result
   * set but not part of it. It serves as a staging area for building the row
   * that is to be inserted. To access the insert row, we have to call the

   * ResultSet method moveToInsertRow(), which positions the cursor on the insert
   * row. Then appropriate updateXXX()  methods are called to add column values
   * to the insert row. When all of the columns of the row to be inserted have
   * been set, finally a call to the method insertRow() will insert that row in
   * the updatableRst resultSet. 
   */
  private void insertRecord(String name,String address,String id,
                           String phone,String fax,String hotelUrl,
                           String airport ) {
    try {
      updatableRst.moveToInsertRow(); // Move the cursor to the insert row.

      // Update the different columns.
      try {
        updatableRst.updateInt("id",Integer.parseInt(id));  
        updatableRst.updateString("name",name);             
        updatableRst.updateString("address",address);       

        updatableRst.updateString("phone",phone);           
        updatableRst.updateString("fax",fax);               
        updatableRst.updateString("hotel_url",hotelUrl);    
        updatableRst.updateString("hotel_airport",airport); 
      
        // Insert the contents of the insert row into the result set and database.
        updatableRst.insertRow();
      
        ..........
        ..........
      } catch (NumberFormatException nfEx) {
          ........
      }
    } catch (Exception ex) {
      ..........
    }
  }

  /**
   * Method to update the selected row with new values using programmatic updates.

   * Here, we have to first position the cursor at the row to be updated, then
   * update the columns one by one using updatexxx(..) method. Finally a call to
   * updateRow() will make the updation reflected in the updatableRst.
   */
  private void updateRecord(String name,String address,String id,
                           String phone,String fax,String hotelUrl,
                           String airport ) {

    try {
      // Get the index of row to be updated from the JTable.
      int rowIndex = gui.hotelTable.getSelectedRow();

      // Position the cursor of Updatable ResultSet to the row to be updated.
      updatableRst.absolute(rowIndex+1);

      // Update the different columns.
      updatableRst.updateInt("id",Integer.parseInt(id)); // set Hotel ID
      updatableRst.updateString("name",name);            // Sets Hotel name

      updatableRst.updateString("address",address);      // Sets Hotel address
      updatableRst.updateString("phone",phone);          // Sets Phone Number
      updatableRst.updateString("fax",fax);              // Sets Fax Number
      updatableRst.updateString("hotel_url",hotelUrl);   // Sets Hotel URL
      updatableRst.updateString("hotel_airport",airport);// Sets Airport

      // Update the ResultSet and the underlying database.
      updatableRst.updateRow();

    } catch (Exception ex) {
        ..........
    }
  }

  /**
   * Method to delete the selected row using the programmatic updates
   * Here, first we have to point the cursor at the row to be deleted, and then
   * call the deleteRow method available in ResultSet.
   */

  private void deleteRecord() {
    try {
      // Get the index of row to be deleted from the JTable.
      int rowIndex = gui.hotelTable.getSelectedRow();

      // Set the cursor of the updatable ResultSet to the row to be deleted.
      updatableRst.absolute(rowIndex+1);
      
      // Deletes the current row from the result set and the underlying database.
      updatableRst.deleteRow();
   
      ............
      ............
      
    } catch(Exception ex){  // Trap the exceptions.
        ........... 
   }
 }
    
               

• 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 ProgUpdateSample.jar using the following command 

> jar xvf ProgUpdateSample.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 ProgUpdateSample with all the source files.

Edit ProgUpdateSample/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 ProgUpdate.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:\ProgUpdateSample\config\ProgUpdate.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 ProgUpdate.jws from the ProgUpdateSample directory.
• Next, select Project/Make ProgUpdate.jpr from main menu.
• Now, select Run/Run ProgUpdate.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 ProgUpdateSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

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

Running the application manually:

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

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 batch file: run.sh from the command prompt, from ProgUpdateSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to ProgUpdateSample 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: classes12.zip
• ProgUpdateSample 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/ProgUpdateSample:.
• Make sure that Java is in the PATH
  Example: $export PATH=/usr/java/jdk1.3.1_02/bin:$PATH
  
• From the directory ProgUpdateSample/src/oracle/otnsamples/jdbc/progupdate, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same ProgUpdateSample/src/oracle/otnsamples/jdbc/progupdate directory.
  Example:
  $java oracle.otnsamples.jdbc.progupdate.ProgUpdateSample