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