Contents | Prev | Next | JDBCTM Guide: Getting Started

# 5 - ResultSet

This overview is excerpted from JDBCTM Database Access from JavaTM: A Tutorial and Annotated Reference, currently in progress at JavaSoft. This book, both a tutorial and the definitive reference manual for JDBC, will be published in the spring of 1997 by Addison-Wesley Publishing Company as part of the Java series.

## 5.1 Overview

A `ResultSet` contains all of the rows which satisfied the conditions in an SQL statement, and it provides access to the data in those rows through a set of `get` methods that allow access to the various columns of the current row. The `ResultSet.next` method is used to move to the next row of the `ResultSet`, making the next row become the current row.

The general form of a result set is a table with column headings and the corresponding values returned by a query. For example, if your query is `SELECT a, b, c FROM Table1`, your result set will have the following form:

            a          b            c
            --------   ---------    --------
            12345      Cupertino    CA
            83472      Redmond      WA
            83492      Boston       MA

The following code fragment is an example of executing an SQL statement that will return a collection of rows, with column 1 as an `int`, column 2 as a `String`, and column 3 as an array of bytes:

        java.sql.Statement stmt = conn.createStatement();
        ResultSet r = stmt.executeQuery("SELECT a, b, c FROM Table1");
        while (r.next())
        {
          // print the values for the current row.
          int i = r.getInt("a");
          String s = r.getString("b");
          float f = r.getFloat("c");
          System.out.println("ROW = " + i + " " + s + " " + f);
        }

### 5.1.1 Rows and Cursors

A `ResultSet` maintains a cursor which points to its current row of data. The cursor moves down one row each time the method `next` is called. Initially it is positioned before the first row, so that the first call to `next` puts the cursor on the first row, making it the current row. `ResultSet` rows are retrieved in sequence from the top row down as the cursor moves down one row with each successive call to `next`.

A cursor remains valid until the `ResultSet` object or its parent `Statement` object is closed.

In SQL, the cursor for a result table is named. If a database allows positioned updates or positioned deletes, the name of the cursor needs to be supplied as a parameter to the update or delete command. This cursor name can be obtained by calling the method `getCursorName`.

Note that not all DBMSs support positioned update and delete. The `DatabaseMetaData.supportsPositionedDelete` and `supportsPositionedUpdate` methods can be used to discover whether a particular connection supports these operations. When they are supported, the DBMS/driver must ensure that rows selected are properly locked so that positioned updates do not result in update anomalies or other concurrency problems.

### 5.1.2 Columns

The `getXXX` methods provide the means for retrieving column values from the current row. Within each row, column values may be retrieved in any order, but for maximum portability, one should retrieve values from left to right and read column values only once.

Either the column name or the column number can be used to designate the column from which to retrieve data. For example, if the second column of a `ResultSet` object `rs` is named "title" and stores values as strings, either of the following will retrieve the value stored in that column:

        String s = rs.getString("title");
        String s = rs.getString(2);

Note that columns are numbered from left to right starting with column 1. Also, column names used as input to `getXXX` methods are case insensitive.

The option of using the column name was provided so that a user who specifies column names in a query can use those same names as the arguments to `getXXX` methods. If, on the other hand, the `select` statement does not specify column names (as in "`select * from table1`" or in cases where a column is derived), column numbers should be used. In such situations, there is no way for the user to know for sure what the column names are.

In some cases, it is possible for a SQL query to return a result set that has more than one column with the same name. If a column name is used as the parameter to a `getXXX` method, `getXXX` will return the value of the first matching column name. Thus, if there are multiple columns with the same name, one needs to use a column index to be sure that the correct column value is retrieved. It may also be slightly more efficient to use column numbers.

Information about the columns in a `ResultSet` is available by calling the method `ResultSet.getMetaData`. The `ResultSetMetaData` object returned gives the number, types, and properties of its `ResultSet` object's columns.

If the name of a column is known, but not its index, the method `findColumn` can be used to find the column number.

### 5.1.3 Data Types and Conversions

For the `getXXX` methods, the JDBC driver attempts to convert the underlying data to the specified Java type and then returns a suitable Java value. For example, if the `getXXX` method is `getString`, and the data type of the data in the underlying database is `VARCHAR`, the JDBC driver will convert `VARCHAR` to Java `String`. The return value of `getString` will be a Java `String` object.

The following table shows which JDBC types a `getXXX` method is allowed to retrieve and which JDBC types (generic SQL types) are recommended for it to retrieve. A small `x` indicates a legal `getXXX` method for a particular data type; a large `X` indicates the recommended `getXXX` method for a data type. For example, any `getXXX` method except `getBytes` or `getBinaryStream` can be used to retrieve the value of a `LONGVARCHAR`, but `getAsciiStream` or `getUnicodeStream` are recommended, depending on which data type is being returned. The method `getObject` will return any data type as a Java `Object` and is useful when the underlying data type is a database-specific abstract type or when a generic application needs to be able to accept any data type.

Use of ResultSet.getXXX methods to retrieve common JDBC data types.

An "x" indicates that the `getXXX` method may legally be used to retrieve the given JDBC type.

An "X" indicates that the `getXXX` method is recommended for retrieving the given JDBC type.

| T I N Y I N T | S M A L L I N T | I N T E G E R | B I G N T | R E A L | F L O A T | D O U B L E | D E C I M A L | N U M E R I C | B I T | C H A R | V A R C H A R | L O N G V A R C H A R | B I N A R Y | V A R B I N A R Y | L O N G V A R B I N A R Y | D A T E | T I M E | T I M E S T A M P
--- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | ---
getByte | X | x | x | x | x | x | x | x | x | x | x | x | x |  |  |  |  |  |
getShort | x | X | x | x | x | x | x | x | x | x | x | x | x |  |  |  |  |  |
getInt | x | x | X | x | x | x | x | x | x | x | x | x | x |  |  |  |  |  |
getLong | x | x | x | X | x | x | x | x | x | x | x | x | x |  |  |  |  |  |
getFloat | x | x | x | x | X | x | x | x | x | x | x | x | x |  |  |  |  |  |
getDouble | x | x | x | x | x | X | X | x | x | x | x | x | x |  |  |  |  |  |
getBigDecimal | x | x | x | x | x | x | x | X | X | x | x | x | x |  |  |  |  |  |
getBoolean | x | x | x | x | x | x | x | x | x | X | x | x | x |  |  |  |  |  |
getString | x | x | x | x | x | x | x | x | x | x | X | X | x | x | x | x | x | x | x
getBytes |  |  |  |  |  |  |  |  |  |  |  |  |  | X | X | x |  |  |
getDate |  |  |  |  |  |  |  |  |  |  | x | x | x |  |  |  | X |  | x
getTime |  |  |  |  |  |  |  |  |  |  | x | x | x |  |  |  |  | X | x
getTimestamp |  |  |  |  |  |  |  |  |  |  | x | x | x |  |  |  | x |  | X
getAsciiStream |  |  |  |  |  |  |  |  |  |  | x | x | X | x | x | x |  |  |
getUnicodeStream |  |  |  |  |  |  |  |  |  |  | x | x | X | x | x | x |  |  |
getBinaryStream |  |  |  |  |  |  |  |  |  |  |  |  |  | x | x | X |  |  |
getObject | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x

### 5.1.4 Using Streams for Very Large Row Values

`ResultSet` makes it possible to retrieve arbitrarily large `LONGVARBINARY` or `LONGVARCHAR` data. The methods `getBytes` and `getString` return data as one large chunk (up to the limits imposed by the return value of `Statement.getMaxFieldSize`). However, it may be more convenient to retrieve very large data in smaller, fixed-size chunks. This is done by having the `ResultSet` class return `java.io.Input` streams from which data can be read in chunks. Note that these streams must be accessed immediately because they will be closed automatically on the next `getXXX` call on `ResultSet`. (This behavior is imposed by underlying implementation constraints on large blob access.)

The JDBC API has three separate methods for getting streams, each with a different return value:

- `getBinaryStream` returns a stream which simply provides the raw bytes from the database without any conversion.
- `getAsciiStream` returns a stream which provides one-byte ASCII characters.
- `getUnicodeStream` returns a stream which provides two-byte Unicode characters.

Note that this differs from Java streams, which return untyped bytes and can (for example) be used for both ASCII and Unicode characters.

The following code gives an example of using `getAsciiStream`:

        java.sql.Statement stmt = con.createStatement();
        ResultSet r = stmt.executeQuery("SELECT x FROM Table2");
        // Now retrieve the column 1 results in 4 K chunks:
        byte buff = new byte[4096];
        while (r.next()) {
          Java.io.InputStream fin = r.getAsciiStream(1);
          for (;;) {
            int size = fin.read(buff);
            if (size == -1) { // at end of stream
                  break;
            }
            // Send the newly-filled buffer to some ASCII output stream:
            output.write(buff, 0, size);
          }
        }

### 5.1.5 NULL Result Values

To determine if a given result value is `JDBC` `NULL`, one must first read the column and then use the `ResultSet.wasNull` method to discover if the read returned a JDBC `NULL`.

When one has read a JDBC`NULL` using one of the `ResultSet.getXXX` methods, the method `wasNull` will return one of the following:

- A Java `null` value for those `getXXX` methods that return Java objects (methods such as `getString`, `getBigDecimal`, `getBytes`, `getDate`, `getTime`, `getTimestamp`, `getAsciiStream`, `getUnicodeStream`, `getBinaryStream`, `getObject`).
- A zero value for `getByte`, `getShort`, `getInt`, `getLong`, `getFloat`, and `getDouble.`
- A `false` value for `getBoolean`.

### 5.1.6 Optional or Multiple Result Sets

Normally SQL statements are executed using either `executeQuery` (which returns a single `ResultSet`) or `executeUpdate` (which can be used for any kind of database modification statement and which returns a count of the rows updated). However, under some circumstances an application may not know whether a given statement will return a result set until the statement has executed. In addition, some stored procedures may return several different result sets and/or update counts.

To accommodate these situations, JDBC provides a mechanism so that an application can execute a statement and then process an arbitrary collection of result sets and update counts. This mechanism is based on first calling a fully general `execute` method, and then calling three other methods, `getResultSet`, `getUpdateCount`, and `getMoreResults`. These methods allow an application to explore the statement results one at a time and to determine if a given result was a `ResultSet` or an update count.

You do not need to do anything to close a `ResultSet`; it is automatically closed by the `Statement` that generated it when that `Statement` is closed, is re-executed, or is used to retrieve the next result from a sequence of multiple results.

Contents | Prev | Next

Copyright © 1996, 1997 Sun Microsystems, Inc. All rights reserved.