J2EEOnline J2EE


JDBC  «Prev  Next»
Lesson 3 Examining the results from a query
Objective Describe the features of the ResultSet.

Examining Query Results (JDBC)

You have seen that the object returned from an invocation of an executeQuery() method is a two-dimensional matrix of row and columns known as the ResultSet. You have also seen that the basic syntax to execute a SQL expression and return a ResultSet is:
String qs = "select * from names";
ResultSet rs = stmt.executeQuery(qs);

You next task is to learn how to work with a ResultSet and pull out the data it contains.
Navigation and updates in the ResultSet
You learned that the Statement object can send a SQL Statement to produce a ResultSet, using an execute method. Since the results of a query are returned in a table form, there has to be some means of navigating in that table. You need some way of knowing where you are positioned in that table. That location is marked with a cursor. A cursor points to the current row of data in a ResultSet.
Basic ResultSet cursor functions
The basic ResultSet cursor only provides for movement in one direction: forward. Once you've read past a row, you can't back up and re-read that row. The cursor is also initially set before the first row and doesn't support any means to update the data retrieved in the ResultSet.
The basic cursor also does not allow a program to update or change the data that is contained in the ResultSet. Though this is can be restrictive, there are other options available. The JDBC 2.0 specification allows a Statement object to return a ResultSet that is scrollable or updateable. The support for this capability is provided by the database driver. It is requested by options selected when the Statement is created from the connection object. You will see details about these options in a later module. If a cursor is scrollable, direction can be set with the setFetchDirection method on the ResultSet. Arguments for that method can be ResultSet.FETCH_FORWARD, ResultSet.FETCH_REVERSE, ResultSet.FETCH_UNKNOWN, as well as several others. There are methods that accomplish the actual movement, such as moveToCurrentRow(), previous(), next() and relative().

View the code below to see how to make a result set that supports scrolling, updates, and will not show changes made by concurrent users.
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM hospitals");
// rs will be scrollable, will not show changes made by others,
// and will be updateable

(Assume con is a valid database connection.)
The arguments passed to the createStatement() method are constants, defined in the JDBC API. The full set of options is documented in the SDK docs for the java.sql.ResultSet interface.

The cursor

You can determine if the cursor your program is using will support updates and scrolling features. You do that by using a method supported from an interface called DatabaseMetaData. This and similar interfaces will be discussed shortly. The table below compares and contrasts the different choices available for scrolling and updating. As you saw above, these choices are set on the creation of a Statement.
Cursor capabilities Description
Forward-only vs. Scrollable A scrollable ResultSet allows you to move the table cursor backwards as well as forward. A forward-only ResultSet allows you to move through the ResultSet using the next() method.
Read-only vs. Updateable An updateable ResultSet allows you to update data in the result set, with the update being sent back to the data source, updating the corresponding data. Prior to the JDBC 2.0 API, this was not possible, as all result sets were returned as read-only. Even today, read-only is the default; updateable is an option.

In the next lesson, you will examine the methods that allow you to refer to specific columns in a row.