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 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.

The basic ResultSet cursor only provides for movement in one direction: forward. Once you have read past a row, you cannot 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( .

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

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.