Contents | Prev | Next | JDBCTM Guide: Getting Started |
A scroll-insensitive result set is generally not sensitive to changes that are made while it is open. A scroll-insensitive result set provides a static view of the underlying data it contains. The membership, order, and column values of rows in a scroll-insensitive result set are typically fixed when the result set is created.
On the other hand, a scroll-sensitive result set is sensitive to changes that are made while it is open, and provides a `dynamic' view of the underlying data. For example, when using a scroll-sensitive result set, changes in the underlying column values of rows are visible. The membership and ordering of rows in the result set may be fixed- this is implementation defined.
A result set that uses read-only concurrency does not allow updates of its contents. This can increase the overall level of concurrency between transactions, since any number of read-only locks may be held on a data item simultaneously.
A result set that is updatable allows updates and may use database write locks to mediate access to the same data item by different transactions. Since only a single write lock may be held at a time on a data item, this can reduce concurrency. Alternatively, an optimistic concurrency control scheme may be used if it is thought that conflicting accesses to data will be rare. Optimistic concurrency control implementations typically compare rows either by value or by a version number to determine if an update conflict has occurred.
Connection con = DriverManager.getConnection( "jdbc:my_subprotocol:my_subname"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT emp_no, salary FROM employees");
The next example creates a scrollable result set that is updatable and sensitive to updates. Rows of data are requested to be fetched twenty-five at-a-time from the database.
The example below creates a result set with the same attributes as the previous example, however, a prepared statement is used to produce the result set.Connection con = DriverManager.getConnection( "jdbc:my_subprotocol:my_subname"); Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.setFetchSize(25); ResultSet rs = stmt.executeQuery( "SELECT emp_no, salary FROM employees");
The methodPreparedStatement pstmt = con.prepareStatement( "SELECT emp_no, salary FROM employees where emp_no = ?", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); pstmt.setFetchSize(25); pstmt.setString(1, "100010"); ResultSet rs = pstmt.executeQuery();
DatabaseMetaData.supportsResultSetType()
can be called to see
which result set types are supported by a JDBC driver. However, an application may
still ask a JDBC driver to create a Statement
, PreparedStatement
, or CallableStatement
object using a result set type that the driver does not support. In this
case, the driver should issue an SQLWarning
on the Connection
that produces the
statement and choose an alternative value for the result set type of the statement according
to the following rules:
DatabaseMetaData.supportsResultSetConcurrency()
can
be called to determine which concurrency types are supported by a driver. If an application
asks a JDBC driver for a concurrency type that it does not support then the driver
should issue an SQLWarning
on the Connection
that produces the statement and
choose the alternative concurrency type. The choice of result set type should be made
first if an application specifies both an unsupported result set type and an unsupported
concurrency type.
In some instances, a JDBC driver may need to choose an alternate result set type or concurrency
type for a ResultSet
at statement execution time. For example, a SELECT
statement that contains a join over multiple tables may not produce a ResultSet
that is
updatable. The JDBC driver should issue an SQLWarning
in this case on the Statement
,
PreparedStatement
, or CallableStatement
that produces the ResultSet
and
choose an appropriate result set type or concurrency type as described above. An application
may determine the actual result set type and concurrency type of a ResultSet
by calling the ResultSet.getType()
and getConcurrency()
methods, respectively.
CONCUR_UPDATABLE
. Rows in an updatable
result set may be updated, inserted, and deleted. The example below updates the
first row of a result set. The ResultSet.updateXXX()
methods are used to modify the
value of an individual column in the current row, but do not update the underlying database.
When the ResultSet.updateRow()
method is called the database is updated.
Columns may be specified by name or number.
rs.first(); rs.updateString(1, "100020"); rs.updateFloat("salary", 10000.0f); rs.updateRow();
The updates that an application makes must be discarded by a JDBC driver if the application
moves the cursor from the current row before calling updateRow()
. In addition,
an application can call the ResultSet.cancelRowUpdates()
method to explicitly
cancel the updates that have been made to a row. The cancelRowUpdates()
method
must be called after calling updateXXX()
and before calling updateRow()
, otherwise
it has no effect.
The following example illustrates deleting a row. The fifth row in the result set is deleted from the database.
rs.absolute(5); rs.deleteRow();
The example below shows how a new row may be inserted into a result set. The JDBC
API defines the concept of an insert row that is associated with each result set and is
used as a staging area for creating the contents of a new row before it is inserted into
the result set itself. The ResultSet.moveToInsertRow()
method is used to position
the result set's cursor on the insert row. The ResultSet.updateXXX()
and ResultSet.getXXX()
methods are used to update and retrieve individual column values from
the insert row. The contents of the insert row is undefined immediately after calling ResultSet.moveToInsertRow()
. In other words, the value returned by calling a ResultSet.getXXX()
method is undefined after moveToInsertRow()
is called until the
value is set by calling ResultSet.updateXXX()
.
Calling ResultSet.updateXXX()
while on the insert row does not update the underlying
database or the result set. Once all of the column values are set in the insert row,
ResultSet.insertRow()
is called to update the result set and the database simultaneously.
If a column is not given a value by calling updateXXX()
while on the insert
row, or a column is missing from the result set, then that column must allow a null value.
Otherwise, calling insertRow()
throws an SQLException
.
A result set remembers the current cursor position "in the result set" while its cursor is temporarily positioned on the insert row. To leave the insert row, any of the usual cursor positioning methods may be called, including the special methodrs.moveToInsertRow(); rs.updateString(1, "100050"); rs.updateFloat(2, 1000000.0f); rs.insertRow(); rs.first();
ResultSet.moveToCurrentRow()
which returns the cursor to the row which was the current
row before ResultSet.moveToInsertRow()
was called. In the example above, ResultSet.first()
is called to leave the insert row and move to the first row of the result
set.
Due to differences in database implementations, the JDBC API does not specify an exact set of SQL queries which must yield an updatable result set for JDBC drivers that support updatability. Developers can, however, generally expect queries which meet the following criteria to produce an updatable result set:
Iterating forward through a result set is done by calling the ResultSet.next()
method,
as with the JDBC 1.0 API. In addition, scrollable result sets-any result set whose type
is not forward only-implement the method, beforeFirst()
, which may be called to
position the cursor before the first row in the result set.
The example below positions the cursor before the first row and then iterates forward
through the contents of the result set. The getXXX()
methods, which are JDBC 1.0 API
methods, are used to retrieve column values.
rs.beforeFirst(); while ( rs.next()) { System.out.println(rs.getString("emp_no") + " " + rs.getFloat("salary")); }
Of course, one may iterate backward through a scrollable result set as well, as is shown below.
In this example, thers.afterLast(); while (rs.previous()) { System.out.println(rs.getString("emp_no") + " " + rs.getFloat("salary")); }
ResultSet.afterLast()
method positions the scrollable result
set's cursor after the last row in the result set. The ResultSet.previous()
method is
called to move the cursor to the last row, then the next to last, and so on. ResultSet.previous()
returns false
when there are no more rows, so the loop ends after all
of the rows have been visited.
After examining the ResultSet
interface, the reader will no doubt recognize that there
is more than one way to iterate through the rows of a scrollable result set. It pays to be
careful, however, as is illustrated by the following example, which shows one alternative
that is incorrect.
This example attempts to iterate forward through a scrollable result set and is incorrect for several reasons. One error is that if// incorrect!!! while (!rs.isAfterLast()) { rs.relative(1); System.out.println(rs.getString("emp_no") + " " + rs.getFloat("salary")); }
ResultSet.isAfterLast()
is called when the
result set is empty, it will return a value of false since there is no last row, and the loop
body will be executed, which is not what is wanted. An additional problem occurs when
the cursor is positioned before the first row of a result set that contains data. In this case
calling rs.relative(1)
is erroneous since there is no current row.
The code sample below fixes the problems in the previous example. Here a call to ResultSet.first()
is used to distinguish the case of an empty result set from one which
contains data. Since ResultSet.isAfterLast()
is only called when the result set is
non-empty the loop control works correctly, and ResultSet.relative(1)
steps
through the rows of the result set since ResultSet.first()
initially positions the cursor
on the first row.
if (rs.first()) { while (!rs.isAfterLast()) { System.out.println(rs.getString("emp_no") + " " + rs.getFloat("salary")); rs.relative(1); } }
The different result set types-forward-only, scroll-insensitive, and scroll-sensitive- provided by the JDBC API vary greatly in their ability to make changes in the underlying data visible to an application. This aspect of result sets is particularly interesting for the result set types which support scrolling, since they allow a particular row to be visited multiple times while a result set is open.
where the variablecon.setTransactionIsolation(TRANSACTION_READ_COMMITTED);
con
has type Connection
. If all transactions in a system execute at
the TRANSACTION_READ_COMMITTED isolation level or higher, then a transaction will
only see the committed changes of other transactions. The changes that are visible to a
result set's enclosing transaction when a result set is opened are always visible through
the result set. In fact, this is what it means for an update made by one transaction to be
visible to another transaction.
But what about changes made while a result set is open? Are they visible through the
result set by, for example, calling ResultSet.getXXX()
? Whether a particular result
set exposes changes to its underlying data made by other transactions, other result sets
that are part of the same transaction (We refer to these two types of changes collectively
as `other's changes'.), or itself while the result set is open depends on the result set type.
Scroll-sensitive result sets lie at the opposite end of the spectrum. A scroll-sensitive result set makes all of the updates made by others that are visible to its enclosing transaction visible. Inserts and deletes may not be visible, however.
Let us define carefully what it means for updates to be visible. If an update made by another transaction affects where a row should appear in the result set-this is in effect a delete followed by an insert-the row may not move until the result set is reopened. If an update causes a row to fail to qualify for membership in a result set-this is in effect a delete-the row may remain visible until the result set is reopened. If a row is explicitly deleted by another transaction, a scroll-sensitive result set may maintain a placeholder for the row to permit logical fetching of rows by absolute position. Updated column values are always visible, however.
The DatabaseMetaData
interface provides a way to determine the exact capabilities
that are supported by a result set. For example, the new methods: othersUpdatesAreVisible
, othersDeletesAreVisible
, and othersInsertsAreVisible
may be used
for this purpose.
A forward-only result set is really a degenerate case of either a scroll-insensitive or scroll-sensitive result set- depending on how the DBMS evaluates the query that produces the result set. Most DBMSs have the ability to materialize query results incrementally for some queries. If a query result is materialized incrementally, then data values aren't actually retrieved until they are needed from the DBMS and the result set will behave like a sensitive result set. For some queries, however, incremental materialization isn't possible. For example, if the result set is sorted, the entire result set may need to be produced a priori before the first row in the result set is returned to the application by the DBMS. In this case a forward-only result set will behave like an insensitive result set.
For a TYPE_FORWARD_ONLY
result set the othersUpdatesAreVisible
, othersDeletesAreVisible
, and othersInsertsAreVisible
methods determine whether inserts,
updates, and deletes are visible when the result set is materialized incrementally by the
DBMS. If the result of a query is sorted then incremental materialization may not be
possible and changes will not be visible, even if the methods above return true.
DatabaseMetaData
methods: ownUpdatesAreVisible
, ownDeletesAreVisible
, and ownInsertsAreVisible
. These methods
are needed since this capability can vary between DBMSs and JDBC drivers.
One's own updates are visible if an updated column value can be retrieved by calling
getXXX()
following a call to updateXXX()
. Updates are not visible if getXXX()
still
returns the initial column value after updateXXX()
is called. Similarly, an inserted row
is visible if it appears in the result set following a call to insertRow()
. An inserted row
is not visible if it does not appear in the result set immediately after insertRow()
is
called-without closing and reopening the result set. Deletions are visible if deleted
rows are either removed from the result set or if deleted rows leave a hole in the result
set.
The following example, shows how an application may determine whether a
TYPE_SCROLL_SENSITIVE
result set can see its own updates.
DatabaseMetaData dmd; ... if (dmd.ownUpdatesAreVisible
(ResultSet.TYPE_SCROLL_INSENSITIVE)) { // changes are visible }
ResultSet.wasUpdated()
, wasDeleted()
, and wasInserted()
methods can be
called to determine whether a row has been effected by a visible update, delete, or insert
respectively since the result set was opened. The ability of a result set to detect changes
is orthogonal to its ability to make changes visible. In other words, visible changes are
not automatically detected.
The DatabaseMetaData
interface provides methods that allow an application to determine
whether a JDBC driver can detect changes for a particular result set type. For example,
boolean bool = dmd.deletesAreDetected( ResultSet.TYPE_SCROLL_SENSITIVE);
If deletesAreDetected returns true
, then ResultSet.wasDeleted()
can be used to
detect `holes' in a TYPE_SCROLL_SENSITIVE
result set.
ResultSet.setFetchSize()
), an application may not see
the very latest changes that have been made to a row, even when a sensitive result set
is used and updates are visible. The ResultSet.refreshRow()
method is provided to
allow an application to request that a driver refresh a row with the latest values stored
in the database. A JDBC driver may actually refresh multiple rows at once if the fetch
size is greater than one. Applications should exercise restraint in calling refreshRow()
, since calling this method frequently will likely slow performance.