|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
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 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
Basic ResultSet cursor functions
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
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 create
Statement() method are constants, defined in the JDBC API.
The full set of options is documented in the SDK docs for the
Cursor and Scrolling Features
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
|Forward-only vs. Scrollable
|| A scrollable
ResultSet allows you to move the table cursor backwards as well as forward.
ResultSet allows you to move through the
|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.