| Contents | Prev | Next | JDBCTM Guide: Getting Started |
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);
}
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.
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.
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 |
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.
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);
}
}
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:
null value for those getXXX methods that return Java objects (methods
such as getString, getBigDecimal, getBytes, getDate, getTime, getTimestamp, getAsciiStream, getUnicodeStream, getBinaryStream, getObject).
getByte, getShort, getInt, getLong, getFloat, and getDouble.
false value for getBoolean.
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.