Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## 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:

- Positioning in a Result Set--ResultSet2.java

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

- Updating Rows in a Result Set--ResultSet4.java

- Scroll-Sensitive Result Set--ResultSet5.java

- Refetching Rows in a Result Set--ResultSet6.java

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 ();
      }
    }

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index