ResultSets::Creating and Manipulating ResultSet

Creating ResultSet Objects

A ResultSet object is most often created as the result of executing a Statement
object. The Statement methods executeQuery and getResultSet both return a
ResultSet object, as do various DatabaseMetaData methods. CODE EXAMPLE 14-2
executes an SQL statement returning a ResultSet object.

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(“select author, title, isbn " +
"from booklist”);

CODE EXAMPLE Executing a query returning a ResultSet object For each book in the table booklist, the ResultSet object will contain a row consisting of three columns, author, title, and isbn. The following sections detail how these rows and columns can be retrieved.

Cursor Movement

A ResultSet object maintains a cursor, which points to its current row of data.
When a ResultSet object is first created, the cursor is positioned before the first
row. The following methods can be used to move the cursor:

• next() — moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row.
• previous() — moves the cursor backwards one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row.
• first() — moves the cursor to the first row in the ResultSet object. Returns true if the cursor is now positioned on the first row and false if the ResultSet object does not contain any rows.
• last() — moves the cursor to the last row in the ResultSet object. Returns true if the cursor is now positioned on the last row and false if the ResultSet object does not contain any rows.
• beforeFirst() — positions the cursor at the start of the ResultSet object, before the first row. If the ResultSet object does not contain any rows, this method has no effect.
• afterLast() — positions the cursor at the end of the ResultSet object, after the last row. If the ResultSet object does not contain any rows, this method has no effect.
• relative(int rows)— moves the cursor relative to its current position.
  

If rows is 0 (zero), the cursor is unchanged. If rows is positive, the cursor is moved forward rows rows. If the cursor is less than the specified number of rows from the last row, the cursor is positioned after the last row. If rows is negative, the cursor is moved backward rows rows. If the cursor is less than rows rows from the first row, the cursor is positioned before the first row.
The method relative returns true if the cursor is positioned on a valid row and false otherwise.
If rows is 1, relative is identical to the method next. If rows is -1, relative is identical to the method previous.

• absolute(int row)— positions the cursor on the row-th row of the

ResultSet object.If row is positive, the cursor is moved row rows from the beginning of the
ResultSet object. The first row is 1, the second 2, and so on. If row is greater
than the number of rows in the ResultSet object, the cursor is positioned after
the last row.

If row is negative, the cursor is moved row rows from the end of the ResultSet
object. The last row is -1, the penultimate -2, and so on. If row is greater than the
number of rows in the ResultSet object, the cursor is positioned before the first
row.

Calling absolute(0) moves the cursor before the first row.
For a ResultSet object that is of type TYPE_FORWARD_ONLY, the only valid cursor
movement method is next. All other cursor movement methods throw an
SQLException.


Retrieving Values

The ResultSet interface provides methods for retrieving the values of columns
from the row where the cursor is currently positioned.
Two getter methods exist for each JDBC type: one that takes the column index as its
first parameter and one that takes the column name or label.
The columns are numbered from left to right, as they appear in the select list of the
query, starting at 1.

Column names supplied to getter methods are case insensitive. If a select list
contains the same column more than once, the first instance of the column will be
returned.

The index of the first instance of a column name can be retrieved using the method
findColumn. If the specified column is not found, the method findColumn throws
an SQLException.

ResultSet rs = stmt.executeQuery(sqlstring);
int colIdx = rs.findColumn(“ISBN”);

CODE EXAMPLE Mapping a column name to a column index

Data Type Conversions

The recommended ResultSet getter method for each JDBC type is shown in
This table also shows all of the possible conversions that a JDBC driver may support. The method DataBaseMetaData.supportsConvert(int fromType, int toType)
returns true if the driver supports the given conversion.

ResultSet Metadata

When the ResultSet method getMetaData is called on a ResultSet object, it
returns a ResultSetMetaData object describing the columns of that ResultSet
object. In cases where the SQL statement being executed is unkown until runtime,
the result set metadata can be used to determine which of the getter methods should
be used to retrieve the data.
In CODE EXAMPLE result set metadata is used to determine the type of each column in the result set.

ResultSet rs = stmt.executeQuery(sqlString);
ResultSetMetaData rsmd = rs.getMetaData();
int colType [] = new int[rsmd.getColumnCount()];
for (int idx = 0, int col = 1; idx <>
colType[idx] = rsmd.getColumnType(col);

CODE EXAMPLE Retrieving result set metadata

Retrieving NULL values

The method wasNull can be called to determine if the last value retrieved was a
JDBC NULL in the database.
When the column value in the database is JDBC NULL, it may be returned to the Java
application as null, 0, or false, depending on the type of the column value.
Column values that map to Java Object types are returned as a Java null; those
that map to numeric types are returned as 0; those that map to a Java boolean are
returned as false. Therefore, it may be necessary to call the wasNull method to
determine whether the last value retrieved was a JDBC NULL.