Sample Applications

JDBC 2.0 Result Set Enhancement Samples

This section provides samples that demonstrate the functionality of result set enhancements available with JDBC 2.0. This includes positioning in a scrollable result set, updating a result set, using a scroll-sensitive result set that can automatically see external updates, and explicitly refetching data into a result set:

The sample applications in this section are located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/jdbc20-samples

Positioning in a Result Set--ResultSet2.java

This section demonstrates scrollable result set functionality--moving to relative and absolute row positions and iterating backwards through the result set.

For discussion on these topics, see "Positioning and Processing in Scrollable Result Sets".

/**
 * A simple sample to demonstrate previous(), absolute() and relative().
 */

import java.sql.*;

public class ResultSet2
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a Statement
    Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);

    // Query the EMP table
    ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

    // iterate through the result using next()
    show_resultset_by_next(rset);

    // iterate through the result using previous()
    show_resultset_by_previous(rset);

    // iterate through the result using absolute()
    show_resultset_by_absolute(rset);

    // iterate through the result using relative()
    show_resultset_by_relative(rset);

    // Close the ResultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Close the connection
    conn.close();   
  }

  /**
   * Iterate through the result using next().
   *
   * @param rset a result set object
   */ 
  public static void show_resultset_by_next(ResultSet rset) 
    throws SQLException
  {
    System.out.println ("List the employee names using ResultSet.next():");

    // Make sure the cursor is placed right before the first row
    if (!rset.isBeforeFirst())
    {
      // Place the cursor right before the first row
      rset.beforeFirst ();
    }
 
    // Iterate through the rows using next()
    while (rset.next())
      System.out.println (rset.getString (1));

    System.out.println ();
  }

  /**
   * Iterate through the result using previous().
   *
   * @param rset a result set object
   */ 
  public static void show_resultset_by_previous(ResultSet rset) 
    throws SQLException
  {
    System.out.println ("List the employee names using ResultSet.previous():");

    // Make sure the cursor is placed after the last row
    if (!rset.isAfterLast())
    {
      // Place the cursor after the last row
      rset.afterLast ();
    }
 
    // Iterate through the rows using previous()
    while (rset.previous())
      System.out.println (rset.getString (1));

    System.out.println ();
  }

  /**
   * Iterate through the result using absolute().
   *
   * @param rset a result set object
   */ 
  public static void show_resultset_by_absolute (ResultSet rset) 
    throws SQLException
  {
    System.out.println ("List the employee names using ResultSet.absolute():");

    // The begin index for ResultSet.absolute (idx)
    int idx = 1;

    // Loop through the result set until absolute() returns false.
    while (rset.absolute(idx))
    {
      System.out.println (rset.getString (1));
      idx ++;
    }
    System.out.println (); 
  }
     
  /**
   * Iterate through the result using relative().
   *
   * @param rset a result set object
   */ 
  public static void show_resultset_by_relative (ResultSet rset) 
    throws SQLException
  {
    System.out.println ("List the employee names using ResultSet.relative():");

    // getRow() returns 0 if there is no current row
    if (rset.getRow () == 0 || !rset.isLast())
    {
      // place the cursor on the last row
      rset.last ();
    }

    // Calling relative(-1) is similar to previous(), but the cursor 
    // has to be on a valid row before calling relative().
    do
    {
      System.out.println (rset.getString (1));
    }
    while (rset.relative (-1));

    System.out.println (); 
  }
}

Inserting and Deleting Rows in a Result Set--ResultSet3.java

This sample shows some of the functionality of an updatable result set--inserting and deleting rows that will in turn be inserted into or deleted from the database.

For discussion on these topics, see "Performing an INSERT Operation in a Result Set" and "Performing a DELETE Operation in a Result Set".

/**
 * A simple sample to to demonstrate ResultSet.insertRow() and 
 * ResultSet.deleteRow().
 */

import java.sql.*;

public class ResultSet3
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Cleanup
    cleanup (conn);

    // Create a Statement
    Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);

    // Query the EMP table
    ResultSet rset = stmt.executeQuery ("select EMPNO, ENAME from EMP");

    // Add three new employees using ResultSet.insertRow()
    addEmployee (rset, 1001, "PETER");
    addEmployee (rset, 1002, "MARY");
    addEmployee (rset, 1003, "DAVID");

    // Close the result set
    rset.close ();

    // Verify the insertion
    System.out.println ("\nList EMPNO and ENAME in the EMP table: ");

    rset = stmt.executeQuery ("select EMPNO, ENAME from EMP");
    while (rset.next())
    {
      // We expect to see the three new employees
      System.out.println (rset.getInt(1)+" "+rset.getString(2));
    }
    System.out.println ();

    // Delete the new employee 'PETER' using ResultSet.deleteRow()
    removeEmployee (rset, 1001);
    rset.close ();

    // Verify the deletion
    System.out.println ("\nList EMPNO and ENAME in the EMP table: ");
    rset = stmt.executeQuery ("select EMPNO, ENAME from EMP");
    while (rset.next())
    {
      // We expect "PETER" is removed
      System.out.println (rset.getInt(1)+" "+rset.getString(2));
    }
    System.out.println ();

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Cleanup
    cleanup(conn);

    // Close the connection
    conn.close();   
  }

  /**
   * Add a new employee to EMP table.
   */
  public static void addEmployee (ResultSet rset, 
                                  int employeeId,
                                  String employeeName) 
    throws SQLException
  {
    System.out.println ("Adding new employee: "+employeeId+" "+employeeName);

    // Place the cursor on the insert row
    rset.moveToInsertRow();

    // Assign the new values
    rset.updateInt (1, employeeId);
    rset.updateString (2, employeeName);

    // Insert the new row to database
    rset.insertRow();
  }

  /**
   * Remove the employee from EMP table.
   */
  public static void removeEmployee (ResultSet rset, 
                                     int employeeId)
    throws SQLException
  {
    System.out.println ("Removing the employee: id="+employeeId);

    // Place the cursor right before the first row if it doesn't
    if (!rset.isBeforeFirst())
    {
      rset.beforeFirst();
    }

    // Iterate the result set
    while (rset.next())
    {
      // Place the cursor the row with matched employee id
      if (rset.getInt(1) == employeeId)
      {
        // Delete the current row
        rset.deleteRow();
        break;
      }
    }
  }

  /**
   * Generic cleanup.
   */
  public static void cleanup (Connection conn) 
    throws SQLException
  {
    Statement stmt = conn.createStatement ();
    stmt.execute 
         ("DELETE FROM EMP WHERE EMPNO=1001 OR EMPNO=1002 OR EMPNO=1003");
    stmt.execute ("COMMIT");
    stmt.close ();
  }
}

Updating Rows in a Result Set--ResultSet4.java

This sample shows some of the functionality of an updatable result set--updating rows that will in turn be updated in the database.

For a discussion on this topic, see "Performing an UPDATE Operation in a Result Set".

/**
 * A simple sample to demonstrate ResultSet.udpateRow().
 */

import java.sql.*;

public class ResultSet4
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a Statement
    Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);

    // Query the EMP table
    ResultSet rset = stmt.executeQuery ("select EMPNO, ENAME, SAL from EMP");

    // Give everybody a $500 raise
    adjustSalary (rset, 500);

    // Verify the sarlary changes
    System.out.println ("Verify the changes with a new query: ");
    rset = stmt.executeQuery ("select EMPNO, ENAME, SAL from EMP");
    while (rset.next())
    {
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          rset.getInt(3));
    }
    System.out.println ();

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Cleanup
    cleanup(conn);

    // Close the connection
    conn.close();   
  }

  /**
   * Update the ResultSet content using updateRow().
   */
  public static void adjustSalary (ResultSet rset, int raise) 
    throws SQLException
  {
    System.out.println ("Give everybody in the EMP table a $500 raise\n");

    int salary = 0;

    while (rset.next ())
    {
      // save the old value
      salary = rset.getInt (3);

      // update the row 
      rset.updateInt (3, salary + raise);
      
      // flush the changes to database
      rset.updateRow ();

      // show the changes
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          salary+" -> "+rset.getInt(3));
    }
    System.out.println ();
  }

  /**
   * Generic cleanup.
   */
  public static void cleanup (Connection conn) throws SQLException
  {
    Statement stmt = conn.createStatement ();
    stmt.execute ("UPDATE EMP SET SAL = SAL - 500");
    stmt.execute ("COMMIT");
    stmt.close ();
  }
}

Scroll-Sensitive Result Set--ResultSet5.java

This sample shows the functionality of a scroll-sensitive result. Such a result set can implicitly see updates to the database that were made externally.

For more information about scroll-sensitive result sets and how they are implemented, see "Oracle Implementation of Scroll-Sensitive Result Sets".

/**
 * A simple sample to demonstrate scroll sensitive result set.
 */

import java.sql.*;

public class ResultSet5
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a Statement
    Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, 
                                         ResultSet.CONCUR_UPDATABLE);

    // Set the statement fetch size to 1
    stmt.setFetchSize (1);

    // Query the EMP table
    ResultSet rset = stmt.executeQuery ("select EMPNO, ENAME, SAL from EMP");
 
    // List the result set's type, concurrency type, ..., etc
    showProperty (rset);
 
    // List the query result 
    System.out.println ("List ENO, ENAME and SAL from the EMP table: ");
    while (rset.next())
    {
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          rset.getInt(3));
    }
    System.out.println ();

    // Do some changes outside the result set
    doSomeChanges (conn);
    
    // Place the cursor right before the first row
    rset.beforeFirst ();

    // List the employee information again
    System.out.println ("List ENO, ENAME and SAL again: ");
    while (rset.next())
    {
      // We expect to see the changes made in "doSomeChanges()"
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          rset.getInt(3));
    }

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Cleanup
    cleanup(conn);

    // Close the connection
    conn.close();   
  }

  /**
   * Update the EMP table.
   */ 
  public static void doSomeChanges (Connection conn)
    throws SQLException
  {
    System.out.println ("Update the employee salary outside the result set\n");
   
    Statement otherStmt = conn.createStatement ();
    otherStmt.execute ("update emp set sal = sal + 500");
    otherStmt.execute ("commit");
    otherStmt.close ();
  }

  /**
   * Show the result set properties like type, concurrency type, fetch 
   * size,..., etc.
   */
  public static void showProperty (ResultSet rset) throws SQLException
  {
    // Verify the result set type
    switch (rset.getType())
    {
      case ResultSet.TYPE_FORWARD_ONLY:
        System.out.println ("Result set type: TYPE_FORWARD_ONLY");
        break;
      case ResultSet.TYPE_SCROLL_INSENSITIVE:
        System.out.println ("Result set type: TYPE_SCROLL_INSENSITIVE");
        break;
      case ResultSet.TYPE_SCROLL_SENSITIVE:
        System.out.println ("Result set type: TYPE_SCROLL_SENSITIVE");
        break;
      default: 
        System.out.println ("Invalid type");
        break;
    }

    // Verify the result set concurrency
    switch (rset.getConcurrency())
    {
      case ResultSet.CONCUR_UPDATABLE:
        System.out.println 
                   ("Result set concurrency: ResultSet.CONCUR_UPDATABLE");
        break;
      case ResultSet.CONCUR_READ_ONLY:
        System.out.println 
                   ("Result set concurrency: ResultSet.CONCUR_READ_ONLY");
        break;
      default: 
        System.out.println ("Invalid type");
        break;
    }

    // Verify the fetch size
    System.out.println ("fetch size: "+rset.getFetchSize ());

    System.out.println ();
  }

  /**
   * Generic cleanup.
   */
  public static void cleanup (Connection conn) throws SQLException
  {
    Statement stmt = conn.createStatement ();
    stmt.execute ("UPDATE EMP SET SAL = SAL - 500");
    stmt.execute ("COMMIT");
    stmt.close ();
  }
}

Refetching Rows in a Result Set--ResultSet6.java

This sample shows how to explicitly refetch data from the database to update the result set. This functionality is available in scroll-sensitive and scroll-insensitive/updatable result sets.

For more information, see "Refetching Rows".

/**
 * A simple sample to demonstrate ResultSet.refreshRow().
 */

import java.sql.*;

public class ResultSet6
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a Statement
    Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);

    // Set the statement fetch size to 1
    stmt.setFetchSize (1);
      
    // Query the EMP table
    ResultSet rset = stmt.executeQuery ("select EMPNO, ENAME, SAL from EMP");
 
    // List the result set's type, concurrency type, ..., etc
    showProperty (rset);

    // List the query result 
    System.out.println ("List ENO, ENAME and SAL from the EMP table: ");
    while (rset.next())
    {
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          rset.getInt(3));
    }
    System.out.println ();

    // Do some changes outside the result set
    doSomeChanges (conn);

    // Place the cursor right before the first row
    rset.beforeFirst ();

    // List the employee information again
    System.out.println ("List ENO, ENAME and SAL again: ");
    int salary = 0;
    while (rset.next())
    {
      // save the original salary
      salary = rset.getInt (3);

      // refresh the row
      rset.refreshRow ();
      
      // We expect to see the changes made in "doSomeChanges()"
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          salary+" -> "+rset.getInt(3));
    }

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Cleanup
    cleanup(conn);

    // Close the connection
    conn.close();   
  }

  /**
   * Update the EMP table.
   */ 
  public static void doSomeChanges (Connection conn)
    throws SQLException
  {
    System.out.println ("Update the employee salary outside the result set\n");
   
    Statement otherStmt = conn.createStatement ();
    otherStmt.execute ("update emp set sal = sal + 500");
    otherStmt.execute ("commit");
    otherStmt.close ();
  }

  /**
   * Show the result set properties like type, concurrency type, fetch 
   * size,..., etc.
   */
  public static void showProperty (ResultSet rset) throws SQLException
  {
    // Verify the result set type
    switch (rset.getType())
    {
      case ResultSet.TYPE_FORWARD_ONLY:
        System.out.println ("Result set type: TYPE_FORWARD_ONLY");
        break;
      case ResultSet.TYPE_SCROLL_INSENSITIVE:
        System.out.println ("Result set type: TYPE_SCROLL_INSENSITIVE");
        break;
      case ResultSet.TYPE_SCROLL_SENSITIVE:
        System.out.println ("Result set type: TYPE_SCROLL_SENSITIVE");
        break;
      default: 
        System.out.println ("Invalid type");
        break;
    }

    // Verify the result set concurrency
    switch (rset.getConcurrency())
    {
      case ResultSet.CONCUR_UPDATABLE:
        System.out.println 
                   ("Result set concurrency: ResultSet.CONCUR_UPDATABLE");
        break;
      case ResultSet.CONCUR_READ_ONLY:
        System.out.println 
                   ("Result set concurrency: ResultSet.CONCUR_READ_ONLY");
        break;
      default: 
        System.out.println ("Invalid type");
        break;
    }

    // Verify the fetch size
    System.out.println ("fetch size: "+rset.getFetchSize ());

    System.out.println ();
  }

  /**
   * Generic cleanup.
   */
  public static void cleanup (Connection conn) throws SQLException
  {
    Statement stmt = conn.createStatement ();
    stmt.execute ("UPDATE EMP SET SAL = SAL - 500");
    stmt.execute ("COMMIT");
    stmt.close ();
  }
}