24.6 Customizing Result Sets
It is possible to customize certain features of the result set. Such features may not necessarily be available across all databases and in some cases may result in performance degradation. In this section we discuss possible customizations and what can be achieved by them.
The following features of the result set can be customized:
- Result set type: This feature allows customization of the navigational direction of the result set traversal and the sensitivity of the result set to reflect changes made in the underlying data while it remains open.
- Result set concurrency: This feature allows customization of the updatability of the result set—that is, whether the ResultSet object can be updated using the ResultSet interface.
- Result set holdability: This feature allows customization of whether the result set is retained or closed when the current transaction is committed.
Valid values for these features are defined by static constants in the ResultSet interface, shown in Table 24.3.
Table 24.3 Selected Constants Defined in the ResultSet Interface
| Result set type constants | Description |
| TYPE_FORWARD_ONLY | The type for a ResultSet object whose cursor may only move forward. This is the default ResultSet type. |
| TYPE_SCROLL_INSENSITIVE | The type for a ResultSet object that is scrollable (i.e., the cursor can move forward and backward), but generally not sensitive to changes made to the underlying data while the ResultSet is open. |
| TYPE_SCROLL_SENSITIVE | The type for a ResultSet object that is scrollable and generally sensitive to changes made to the underlying data while the ResultSet is open. |
| Result set concurrency constants | Description |
| CONCUR_READ_ONLY | The concurrency for a ResultSet object that may not be updated—that is, it is read-only. In other words, updatability of the ResultSet object is not allowed. This is the default ResultSet concurrency. |
| CONCUR_UPDATABLE | The concurrency for a ResultSet object that may be updated. That is, updatability of the ResultSet is allowed. |
| Result set holdability constants | Description |
| CLOSE_CURSORS_AT_COMMIT | Open ResultSet objects with this holdability will be closed when the current transaction is committed. Such a ResultSet is said not to be holdable. |
| HOLD_CURSORS_OVER_COMMIT | Open ResultSet objects with this holdability will remain open when the current transaction is committed. Such a ResultSet said to be holdable. |
Result Set Type
The result set type refers to the direction of navigation in the result set and whether changes in the underlying data get reflected in an open result set.
By default, the result set type is set to ResultSet.TYPE_FORWARD_ONLY, which makes the cursor move in the forward direction only, from before the first row and successively to after the last row. This essentially means that the traversal of the rows in the result set is only possible with the next() method. This option is considered to be a safe default choice for all result sets because forward-only progression through rows does not require the Java application to download the whole database cursor (i.e., all rows that comprise the result of the query on the database). It also allows the fetch size to be customized to achieve better performance, especially in a situation where the query might result in a large number of rows.
With the option ResultSet.TYPE_FORWARD_ONLY, the result set may reflect changes in the underlying database that occur while the result set is being processed. Not all databases are capable of reflecting changes.
Two other options (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_ SENSITIVE) are also available for the result set type, both of which enable scrolling— that is, the arbitrary navigation direction in the result set using the previous(), next(), first(), last(), absolute(), and relative() methods (p. 1534). The cursor can move both forward and backward relative to the current position, or to an absolute position. In order to jump to a particular row in the result set, the entire result set may need to be downloaded first from the database, which can result in performance degradation when dealing with very large result sets returned by the query.
The options ResultSet.TYPE_SCROLL_INSENSITIVE and ResultSet.TYPE_SCROLL_SENSITIVE make the result set insensitive and sensitive, respectively, to changes made in the underlying data source while the result set is open.
The DatabaseMetaData.supportsResultSetType() method can be called to determine whether a particular result set type is actually supported by the database (p. 1543).
