JDBC ResultSet and Cursors

The rows that satisfy a particular query are called the result set. The number of rows returned in a result set can be zero or more. A user can access the data in a result set using a cursor one row at a time from top to bottom. A cursor can be thought of as a pointer to the rows of the result set that has the ability to keep track of which row is currently being accessed. The JDBC API supports a cursor to move both forward and backward and also allowing it to move to a specified row or to a row whose position is relative to another row. The JDBC Resultset example is shown in the next sections to follow.

Types of Result Sets

The ResultSet interface provides methods for retrieving and manipulating the results of executed queries, and ResultSet objects can have different functionality and characteristics. These characteristics are result set type, result set concurrency, and cursor holdability.

The type of a ResultSet object determines the level of its functionality in two areas: the ways in which the cursor can be manipulated, and how concurrent changes made to the underlying data source are reflected by the ResultSet object.

The sensitivity of the ResultSet object is determined by one of three different ResultSet types:

·  TYPE_FORWARD_ONLY — the result set is not scrollable i.e. the cursor moves only forward, from before the first row to after the last row.

·  TYPE_SCROLL_INSENSITIVE — the result set is scrollable; its cursor can move both forward and backward relative to the current position,
and it can move to an absolute position.

·  TYPE_SCROLL_SENSITIVE — the result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.

Before you can take advantage of these features, however, you need to create a scrollable ResultSet object. The following line of code illustrates one way to create a scrollable ResultSet object:

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery(“…..”);

The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.

Result Set Methods

When a ResultSet object is first created, the cursor is positioned before the first row. To move the cursor, you can use the following methods:

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

·  absolute(int n) – positions the cursor on the n-th row of the ResultSet object.