AS/400 Toolbox for Java \ Access classes \ JDBC \ JDBC result set

ResultSet interface

You can use a ResultSet object to access a table of data that was generated by running a query. The table rows are retrieved in sequence. Within a row, column values can be accessed in any order.

The data stored in ResultSet is retrieved by using the various get methods, depending on the type of data being retrieved. The next() method is used to move to the next row.

Cursor movement

A cursor, which is an internal pointer, is used by a result set to point the row in the result set that is being accessed by the Java program. JDBC 2.0 provides additional methods for accessing specific positions within a database:

Scrollable cursor positions
absolute
afterLast
beforeFirst
first
getRow
isAfterLast
isBeforeFirst
isFirst
isLast
last
moveToCurrentRow
moveToInsertRow
previous
relative

Scrolling capabilities

If a result set is created by executing a statement, you can move (scroll) backward (last-to-first) or forward (first-to-last) through the rows in a table.

A result set that supports this movement is called a scrollable result set. Scrollable result sets also support relative and absolute positioning. Relative positioning allows you to move to a row in the result set by specifying a position that is relative to the current row. Absolute positioning allows you to move directly to a row by specifying its position in the result set.

With JDBC 2.0, you have two additional scrolling capabilities available to use when working with the ResultSet class: scroll-insensitive and scroll-sensitive result sets.

A scroll-insensitive result set is not usually sensitive to changes that are made while it is open, while the scroll-sensitive result set is sensitive to changes.

Example: Scrollable result sets

The following example shows how to use a scroll-insensitive result set that is read only:

                       // Connect to the AS/400.
     Connection c = DriverManager.getConnection("jdbc:as400://mySystem");

                       // Create a Statement object.  Set the result set
               // type to scroll insensitive.
     Statement s = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);

                       // Run a query. The result is placed
                       // in a ResultSet object.
     ResultSet rs = s.executeQuery ("SELECT NAME,ID FROM MYLIBRARY.MYTABLE");

                       // Iterate through the rows of the ResultSet.
               // Go to the next row if the user selected 'N' or
               // previous record if the user selected 'P'.  Assume
               // that getUserSelection() is defined elsewhere.
     boolean done = false;
     while (! done)
     {
        switch (getUserSelection ())
        {
        case 'N':
            rs.next ();
            break;
        case 'P':
            rs.previous ();
            break;
        default:
            done = true;
        }

                       // Get the values from the ResultSet.
                       // The first value is a string, and
                       // the second value is an integer.
         String name = rs.getString("NAME");
         int id = rs.getInt("ID");

         System.out.println("Name = " + name);
         System.out.println("ID = " + id);
     }

                       // Close the Statement and the
                       // Connection.
     s.close();
     c.close();

Updatable result sets

In your application, you can use result sets that use either read-only concurrency (no updates can be made to the data) or updatable concurrency (allows updates to the data and may use database write locks to control access to the same data item by different transactions). In an updatable result set, rows can be updated, inserted, and deleted. Numerous update methods are available for you to use in your program, for example:

See Method Index for a complete listing of the update methods available through the ResultSet interface.

Example: Updatable result sets

The following example shows how to use a result set that allows updates to the data (update concurrency) and allows changes to be made to the result set while it is open (scroll sensitive).

                       // Connect to the AS/400.
     Connection c = DriverManager.getConnection("jdbc:as400://mySystem");

                       // Create a Statement object.  Set the result set
               // concurrency to updatable.
     Statement s = c.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                 ResultSet.CONCUR_UPDATABLE);

                       // Run a query. The result is placed
                       // in a ResultSet object.
     ResultSet rs = s.executeQuery ("SELECT NAME,ID FROM MYLIBRARY.MYTABLE FOR UPDATE");

                       // Iterate through the rows of the ResultSet.
                   // As we read the row, we will update it with
                       // a new ID.
     int newId = 0;
     while (rs.next ())
     {

                       // Get the values from the ResultSet.
                       // The first value is a string, and
                       // the second value is an integer.
         String name = rs.getString("NAME");
         int id = rs.getInt("ID");

         System.out.println("Name = " + name);
         System.out.println("Old id = " + id);

               // Update the id with a new integer.
         rs.updateInt("ID", ++newId);
        
                       // Send the updates to the server.
         rs.updateRow ();

         System.out.println("New id = " + newId);
     }

                       // Close the Statement and the
                       // Connection.
     s.close();
     c.close();

ResultSetMetaData interface

The ResultSetMetaData interface determines the types and properties of the columns in a ResultSet.

The following example shows how to use the ResultSet interface:

                       // Connect to the AS/400.
     Connection c = DriverManager.getConnection("jdbc:as400://mySystem");

                       // Create a Statement object.
     Statement s = c.createStatement();

                       // Run a query. The result is placed
                       // in a ResultSet object.
     ResultSet rs = s.executeQuery ("SELECT NAME,ID FROM MYLIBRARY.MYTABLE");

                       // Iterate through the rows of the ResultSet.
     while (rs.next ())
     {

                       // Get the values from the ResultSet.
                       // The first value is a string, and
                       // the second value is an integer.
         String name = rs.getString("NAME");
         int id = rs.getInt("ID");

         System.out.println("Name = " + name);
         System.out.println("ID = " + id);
     }

                       // Close the Statement and the
                       // Connection.
     s.close();
     c.close();

[ Information Center Home Page | Feedback ] [ Legal | AS/400 Glossary ]