Oracle9i JDBC Developer's Guide and Reference Release 1 (9.0.1) Part Number A90211-01 |
|
Standard JDBC 2.0 features in JDK 1.2.x include enhancements to result set functionality--processing forward or backward, positioning relatively or absolutely, seeing changes to the database made internally or externally, and updating result set data and then copying the changes to the database.
This chapter discusses these features, including the following topics:
The Oracle JDBC drivers also include extensions to support these features in a JDK 1.1.x environment.
For more general and conceptual information about JDBC 2.0 result set enhancements, refer to the Sun Microsystems JDBC 2.0 API specification.
This section provides an overview of JDBC 2.0 result set functionality and categories, and some discussion of implementation requirements for the Oracle JDBC drivers.
Result set functionality in JDBC 2.0 includes enhancements for scrollability and positioning, sensitivity to changes by others, and updatability.
Specify the desired result set type and concurrency type when you create the statement object that will produce the result set.
Together, the various result set types and concurrency types provide for six different categories of result set.
This section provides an overview of these enhancements, types, and categories.
Scrollability refers to the ability to move backward as well as forward through a result set. Associated with scrollability is the ability to move to any particular position in the result set, through either relative positioning or absolute positioning.
Relative positioning allows you to move a specified number of rows forward or backward from the current row. Absolute positioning allows you to move to a specified row number, counting from either the beginning or the end of the result set.
Under JDBC 1.0 (in JDK 1.1.x) you can scroll only forward, using the next()
method as described in "Process the Result Set", and there is no positioning functionality. You can start only at the beginning and iterate row-by-row until the end.
Under JDBC 2.0 (in JDK 1.2.x), scrollable/positionable result sets are also available.
When creating a scrollable/positionable result set, you must also specify sensitivity. This refers to the ability of a result set to detect and reveal changes made to the underlying database from outside the result set.
A sensitive result set can see changes made to the database while the result set is open, providing a dynamic view of the underlying data. Changes made to the underlying columns values of rows in the result set are visible.
An insensitive result set is not sensitive to changes made to the database while the result set is open, providing a static view of the underlying data. You would need to retrieve a new result set to see changes made to the database.
Sensitivity is not an option in a JDBC 1.0/non-scrollable result set.
When you create a result set under JDBC 2.0 functionality, you must choose a particular result set type to specify whether the result set is scrollable/positional and sensitive to underlying database changes.
If the JDBC 1.0 functionality is all you desire, JDBC 2.0 continues to support this through the forward-only result set type. A forward-only result set cannot be sensitive.
If you want a scrollable result set, you must also specify sensitivity. Specify the scroll-sensitive type for the result set to be scrollable and sensitive to underlying changes. Specify the scroll-insensitive type for the result set to be scrollable but not sensitive to underlying changes.
To summarize, the following three result set types are available with JDBC 2.0:
The sensitivity of a scroll-sensitive result set (how often it is updated to see external changes) is affected by fetch size. See Fetch Size and "Oracle Implementation of Scroll-Sensitive Result Sets".
Note:
Updatability refers to the ability to update data in a result set and then (presumably) copy the changes to the database. This includes inserting new rows into the result set or deleting existing rows.
Updatability might also require database write locks to mediate access to the underlying database. Because you cannot have multiple write locks concurrently, updatability in a result set is associated with concurrency in database access.
Result sets can optionally be updatable under JDBC 2.0, but not under JDBC 1.0.
The concurrency type of a result set determines whether it is updatable. Under JDBC 2.0, the following concurrency types are available:
Because scrollability and sensitivity are independent of updatability, the three result set types and two concurrency types combine for a total of six result set categories:
This section discusses key aspects of the Oracle JDBC implementation of result set enhancements for scrollability--through use of a client-side cache--and for updatability--through use of ROWIDs.
It is permissible for customers to implement their own client-side caching mechanism, and Oracle provides an interface to use in doing so.
Because the underlying server does not support scrollable cursors, Oracle JDBC must implement scrollability in a separate layer.
It is important to be aware that this is accomplished by using a client-side memory cache to store rows of a scrollable result set.
Scrollable cursors in the Oracle server, and therefore a server-side cache, will be supported in a future Oracle release.
To support updatability, Oracle JDBC uses ROWIDs to uniquely identify database rows that appear in a result set. For every query into an updatable result set, the Oracle JDBC driver automatically retrieves the ROWID along with the columns you select.
There is some flexibility in how to implement client-side caching in support of JDBC 2.0 scrollable result sets.
Although Oracle JDBC provides a complete implementation, it also supplies an interface, OracleResultSetCache
, that you can implement as desired:
public interface OracleResultSetCache { /** * Save the data in the i-th row and j-th column. */ public void put (int i, int j, Object value) throws IOException; /** * Return the data stored in the i-th row and j-th column. */ public Object get (int i, int j) throws IOException; /** * Remove the i-th row. */ public void remove (int i) throws IOException; /** * Remove the data stored in i-th row and j-th column */ public void remove (int i, int j) throws IOException; /** * Remove all data from the cache. */ public void clear () throws IOException; /** * Close the cache. */ public void close () throws IOException; }
If you implement this interface with your own class, your application code must instantiate your class and then use the setResultSetCache()
method of an OracleStatement
, OraclePreparedStatement
, or OracleCallableStatement
object to set the caching mechanism to use your implementation. Following is the method signature:
Call this method prior to executing a query. The result set produced by the query will then use your specified caching mechanism.
Under JDBC 1.0, no special attention is required in creating and using a result set. A result set is produced automatically to store the results of a query, and no result set types or categories must be specified, because there is only one kind of result set available--forward-only/read-only. For example (given a connection object conn
):
Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp");
In using JDBC 2.0 result set enhancements, however, you may specify the result set type (for scrollability and sensitivity) and the concurrency type (for updatability) when you create a generic statement or prepare a prepared statement or callable statement that will execute a query.
(Note, however, that callable statements are intended to execute stored procedures and functions and rarely return a result set. Still, the callable statement class is a subclass of the prepared statement class and so inherits this functionality.)
This section discusses the creation of result sets to use JDBC 2.0 enhancements.
Under JDBC 2.0, Connection
classes have createStatement()
, prepareStatement()
, and prepareCall()
method signatures that take a result set type and a concurrency type as input:
Statement createStatement
(int resultSetType, int resultSetConcurrency)
PreparedStatement prepareStatement
(String sql, int resultSetType, int resultSetConcurrency)
CallableStatement prepareCall
(String sql, int resultSetType, int resultSetConcurrency)
The statement objects created will have the intelligence to produce the appropriate kind of result sets.
You can specify one of the following static constant values for result set type:
ResultSet.TYPE_FORWARD_ONLY
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.TYPE_SCROLL_SENSITIVE
See "Oracle Implementation of Scroll-Sensitive Result Sets" for information about possible performance impact.
Note:
And you can specify one of the following static constant values for concurrency type:
ResultSet.CONCUR_READ_ONLY
ResultSet.CONCUR_UPDATABLE
After creating a Statement
, PreparedStatement
, or CallableStatement
object, you can verify its result set type and concurrency type by calling the following methods on the statement object:
Following is an example of a prepared statement object that specifies a scroll-sensitive and updatable result set for queries executed through that statement (where conn
is a connection object):
... PreparedStatement pstmt = conn.prepareStatement ("SELECT empno, sal FROM emp WHERE empno = ?", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); pstmt.setString(1, "28959"); ResultSet rs = pstmt.executeQuery(); ...
Some types of result sets are not feasible for certain kinds of queries. If you specify an unfeasible result set type or concurrency type for the query you execute, the JDBC driver follows a set of rules to determine the best feasible types to use instead.
The actual result set type and concurrency type are determined when the statement is executed, with the driver issuing a SQLWarning
on the statement object if the desired result set type or concurrency type is not feasible. The SQLWarning
object will contain the reason why the requested type was not feasible. Check for warnings to verify whether you received the type of result set that you requested, or call the methods described in "Verifying Result Set Type and Concurrency Type".
A query cannot have the FOR UPDATE
clause in the SELECT
statement if you are using an updatable result set. If you use the FOR UPDATE
clause and try to update a result set, an SQLException will be thrown.
The following limitations are placed on queries for enhanced result sets. Failure to follow these guidelines will result in the JDBC driver choosing an alternative result set type or concurrency type.
To produce an updatable result set:
In addition, for inserts to be feasible, the query must select all non-nullable columns and all columns that do not have a default value.
SELECT *
". (But see the workaround below.)
SUM
or MAX
of a set of columns.
ORDER BY
.
To produce a scroll-sensitive result set:
SELECT *
". (But see the workaround below.)
ORDER BY
.
In fact, you cannot use ORDER BY
for any result set where you will want to refetch rows. This applies to scroll-insensitive/updatable result sets as well as scroll-sensitive result sets. (See "Summary of New Methods for Result Set Enhancements" for general information about refetching.)
As a workaround for the "SELECT *
" limitation, you can use table aliases as in the following example:
SELECT t.* FROM TABLE t ...
If the specified result set type or concurrency type is not feasible, the Oracle JDBC driver uses the following rules in choosing alternate types:
TYPE_SCROLL_SENSITIVE
, but the JDBC driver cannot fulfill that request, then the driver attempts a downgrade to TYPE_SCROLL_INSENSITIVE
.
TYPE_SCROLL_INSENSITIVE
, but the JDBC driver cannot fulfill that request, then the driver attempts a downgrade to TYPE_FORWARD_ONLY
.
Furthermore:
CONCUR_UPDATABLE
, but the JDBC driver cannot fulfill that request, then the JDBC driver attempts a downgrade to CONCUR_READ_ONLY
.
Notes:
After a query has been executed, you can verify the result set type and concurrency type that the JDBC driver actually used, by calling methods on the result set object.
int getType() throws SQLException
This method returns an int
value for the result set type used for the query. ResultSet.TYPE_FORWARD_ONLY
, ResultSet.TYPE_SCROLL_SENSITIVE
, or ResultSet.TYPE_SCROLL_INSENSITIVE
are the possible values.
int getConcurrency() throws SQLException
This method returns an int
value for the concurrency type used for the query. ResultSet.CONCUR_READ_ONLY
or ResultSet.CONCUR_UPDATABLE
are the possible values.
Scrollable result sets (result set type TYPE_SCROLL_SENSITIVE
or TYPE_SCROLL_INSENSITIVE
) allow you to iterate through, them either forward or backward, and to position the result set to any desired row.
This section discusses positioning within a scrollable result set and how to process a scrollable result set backward, instead of forward.
For a complete sample application demonstrating this functionality, see "Positioning in a Result Set--ResultSet2.java".
In a scrollable result set, you can use several result set methods to move to a desired position and to check the current position.
The following result set methods are available for moving to a new position in a scrollable result set:
void beforeFirst() throws SQLException
void afterLast() throws SQLException
boolean first() throws SQLException
boolean last() throws SQLException
boolean absolute(int row) throws SQLException
boolean relative(int row) throws SQLException
Positions to before the first row of the result set, or has no effect if there are no rows in the result set.
This is where you would typically start iterating through a result set to process it going forward, and is the default initial position for any kind of result set.
You are outside the result set bounds after a beforeFirst()
call. There is no valid current row, and you cannot position relatively from this point.
Positions to after the last row of the result set, or has no effect if there are no rows in the result set.
This is where you would typically start iterating through a result set to process it going backward.
You are outside the result set bounds after an afterLast()
call. There is no valid current row, and you cannot position relatively from this point.
Positions to the first row of the result set, or returns false
if there are no rows in the result set.
Positions to the last row of the result set, or returns false
if there are no rows in the result set.
Positions to an absolute row from either the beginning or end of the result set. If you input a positive number, it positions from the beginning; if you input a negative number, it positions from the end. This method returns false
if there are no rows in the result set.
Attempting to move forward beyond the last row, such as an absolute(11)
call if there are 10 rows, will position to after the last row, having the same effect as an afterLast()
call.
Attempting to move backward beyond the first row, such as an absolute(-11)
call if there are 10 rows, will position to before the first row, having the same effect as a beforeFirst()
call.
Moves to a position relative to the current row, either forward if you input a positive number or backward if you input a negative number, or returns false
if there are no rows in the result set.
The result set must be at a valid current row for use of the relative()
method.
Attempting to move forward beyond the last row will position to after the last row, having the same effect as an afterLast()
call.
Attempting to move backward beyond the first row will position to before the first row, having the same effect as a beforeFirst()
call.
A relative(0)
call is valid but has no effect.
The following result set methods are available for checking the current position in a scrollable result set:
boolean isBeforeFirst() throws SQLException
Returns true
if the position is before the first row.
boolean isAfterLast() throws SQLException
Returns true
if the position is after the last row.
boolean isFirst() throws SQLException
Returns true
if the position is at the first row.
boolean isLast() throws SQLException
Returns true
if the position is at the last row.
int getRow() throws SQLException
Returns the row number of the current row, or returns 0 if there is no valid current row.
In a scrollable result set you can iterate backward instead of forward as you process the result set. The following methods are available:
The previous()
method works similarly to the next()
method, in that it returns true
as long as the new current row is valid, and false
as soon as it runs out of rows (has passed the first row).
You can process the entire result set going forward, using the next()
method as in JDBC 1.0. This is documented in "Process the Result Set". The default initial position in the result set is before the first row, appropriately, but you can call the beforeFirst()
method if you have moved elsewhere since the result set was created.
To process the entire result set going backward, call afterLast()
, then use the previous()
method. For example (where conn
is a connection object):
... /* NOTE: The specified concurrency type, CONCUR_UPDATABLE, is not relevant to this example. */ Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp"); rs.afterLast(); while (rs.previous()) { System.out.println(rs.getString("empno") + " " + rs.getFloat("sal")); } ...
Unlike relative positioning, you can (and typically do) use next()
from before the first row and previous()
from after the last row. You do not have to be at a valid current row to use these methods.
The JDBC 2.0 standard allows the ability to pre-specify the direction, known as the fetch direction, for use in processing a result set. This allows the JDBC driver to optimize its processing. The following result set methods are specified:
void setFetchDirection(int direction) throws SQLException
int getFetchDirection() throws SQLException
The Oracle JDBC drivers support only the forward preset value, which you can specify by inputting the ResultSet.FETCH_FORWARD
static constant value.
The values ResultSet.FETCH_REVERSE
and ResultSet.FETCH_UNKNOWN
are not supported--attempting to specify them causes a SQL warning, and the settings are ignored.
A concurrency type of CONCUR_UPDATABLE
allows you to update rows in the result set, delete rows from the result set, or insert rows into the result set.
After you perform an UPDATE
or INSERT
operation in a result set, you propagate the changes to the database in a separate step that you can skip if you want to cancel the changes.
A DELETE
operation in a result set, however, is immediately executed (but not necessarily committed) in the database as well.
For sample applications demonstrating this functionality, see "Inserting and Deleting Rows in a Result Set--ResultSet3.java" and "Updating Rows in a Result Set--ResultSet4.java".
The result set deleteRow()
method will delete the current row. Following is the method signature:
void deleteRow() throws SQLException
Presuming the result set is also scrollable, you can position to a row using any of the available positioning methods (except beforeFirst()
and afterLast()
, which do not go to a valid current row), and then delete that row, as in the following example (presuming a result set rs
):
... rs.absolute(5); rs.deleteRow(); ...
See "Positioning in a Scrollable Result Set" for information about the positioning methods.
Important: The deleted row remains in the result set object even after it has been deleted from the database.
In a scrollable result set, by contrast, a Refer to "Seeing Internal Changes" for more information. |
Performing a result set UPDATE
operation requires two separate steps to first update the data in the result set and then copy the changes to the database.
Presuming the result set is also scrollable, you can position to a row using any of the available positioning methods (except beforeFirst()
and afterLast()
, which do not go to a valid current row), and then update that row as desired.
See "Positioning in a Scrollable Result Set" for information about the positioning methods.
Here are the steps for updating a row in the result set and database:
updateXXX()
methods to update the data in the columns you want to change.
With JDBC 2.0, a result set object has an updateXXX()
method for each datatype, as with the setXXX()
methods previously available for updating the database directly.
Each of these methods takes an int
for the column number or a string for the column name and then an item of the appropriate datatype to set the new value. Following are a couple of examples for a result set rs
:
rs.updateString(1, "mystring"); rs.updateFloat(2, 10000.0f);
updateRow()
method to copy the changes to the database (or the cancelRowUpdates()
method to cancel the changes).
Once you call updateRow()
, the changes are executed and will be made permanent with the next transaction COMMIT
operation. Be aware that by default, the auto-commit flag is set to true
so that any executed operation is committed immediately.
If you choose to cancel the changes before copying them to the database, call the cancelRowUpdates()
method instead. This will also revert to the original values for that row in the local result set object. Note that once you call the updateRow()
method, the changes are written to the transaction and cannot be canceled unless you roll back the transaction (auto-commit must be disabled to allow a ROLLBACK
operation).
Positioning to a different row before calling updateRow()
also cancels the changes and reverts to the original values in the result set.
Before calling updateRow()
, you can call the usual getXXX()
methods to verify that the values have been updated correctly. These methods take an int
column index or string column name as input. For example:
float myfloat = rs.getFloat(2); ...process myfloat to see if it's appropriate...
Note:
Result set Refer to "Seeing Internal Changes" for more information. |
Following is an example of a result set UPDATE
operation that is also copied to the database. The tenth row is updated. (The column number is used to specify column 1, and the column name--sal
-- is used to specify column 2.)
... Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp"); if (rs.absolute(10)) // (returns false if row does not exist) { rs.updateString(1, "28959"); rs.updateFloat("sal", 100000.0f); rs.updateRow(); } // Changes will be made permanent with the next COMMIT operation. ...
Result set INSERT
operations use what is called the result set insert-row, which is a staging area that holds the data for the inserted row until it is copied to the database. You must explicitly move to this row to write the data that will be inserted.
As with UPDATE
operations, result set INSERT
operations require separate steps to first write the data to the insert-row and then copy it to the database .
Following are the steps in executing a result set INSERT
operation.
moveToInsertRow()
method.
UPDATE
operations, use the appropriate updateXXX()
methods to write data to the columns. For example:
rs.updateString(1, "mystring"); rs.updateFloat(2, 10000.0f);
(Note that you can specify a string for column name, instead of an integer for column number.)
insertRow()
method.
Once you call insertRow()
, the insert is executed and will be made permanent with the next transaction COMMIT
operation.
Positioning to a different row before calling insertRow()
cancels the insert and clears the insert-row.
Before calling insertRow()
you can call the usual getXXX()
methods to verify that the values have been set correctly in the insert-row. These methods take an int
column index or string column name as input. For example:
float myfloat = rs.getFloat(2); ...process myfloat to see if it's appropriate...
Note:
No result set type (neither scroll-sensitive, scroll-insensitive, nor forward-only) can see a row inserted by a result set Refer to "Seeing Internal Changes" for more information. |
The following example performs a result set INSERT
operation, moving to the insert-row, writing the data, copying the data into the database, and then returning to what was the current row prior to going to the insert-row. (The column number is used to specify column 1, and the column name--sal
-- is used to specify column 2.)
... Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp"); rs.moveToInsertRow(); rs.updateString(1, "28959"); rs.updateFloat("sal", 100000.0f); rs.insertRow(); // Changes will be made permanent with the next COMMIT operation. rs.moveToCurrentRow(); // Go back to where we came from... ...
It is important to be aware of the following facts regarding updatable result sets with the JDBC drivers:
DELETE
or UPDATE
operation.
A conflict will occur if you try to perform a DELETE
or UPDATE
operation on a row updated by another committed transaction.
The Oracle JDBC drivers use the ROWID to uniquely identify a row in a database table. As long as the ROWID is still valid when a driver tries to send an UPDATE
or DELETE
operation to the database, the operation will be executed.
The driver will not report any changes made by another committed transaction. Any conflicts are silently ignored and your changes will overwrite the previous changes.
To avoid such conflicts, use the Oracle FOR UPDATE
feature when executing the query that produces the result set. This will avoid conflicts, but will also prevent simultaneous access to the data. Only a single write lock can be held concurrently on a data item.
By default, when Oracle JDBC executes a query, it receives the result set 10 rows at a time from the database cursor. This is the default Oracle row-prefetch value. You can change the number of rows retrieved with each trip to the database cursor by changing the row-prefetch value (see "Oracle Row Prefetching" for more information).
JDBC 2.0 also allows you to specify the number of rows fetched with each database round trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries executed through that statement object.
Fetch size is also used in a result set. When the statement object executes a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it. (Also note that changes made to a statement object's fetch size after a result set is produced will have no affect on that result set.)
The result set fetch size, either set explicitly, or by default equal to the statement fetch size that was passed to it, determines the number of rows that are retrieved in any subsequent trips to the database for that result set. This includes any trips that are still required to complete the original query, as well as any refetching of data into the result set. (Data can be refetched, either explicitly or implicitly, to update a scroll-sensitive or scroll-insensitive/updatable result set. See "Refetching Rows".)
The following methods are available in all Statement
, PreparedStatement
, CallableStatement
, and ResultSet
objects for setting and getting the fetch size:
To set the fetch size for a query, call setFetchSize()
on the statement object prior to executing the query. If you set the fetch size to N, then N rows are fetched with each trip to the database.
After you have executed the query, you can call setFetchSize()
on the result set object to override the statement object fetch size that was passed to it. This will affect any subsequent trips to the database to get more rows for the original query, as well as affecting any later refetching of rows. (See "Refetching Rows".)
Using the JDBC 2.0 fetch size is fundamentally similar to using the Oracle row-prefetch value, except that with the row-prefetch value you do not have the flexibility of distinct values in the statement object and result set object. The row prefetch value would be used everywhere.
Furthermore, JDBC 2.0 fetch size usage is portable and can be used with other JDBC drivers. Oracle row-prefetch usage is vendor-specific.
See "Oracle Row Prefetching" for a general discussion of this Oracle feature.
The result set refreshRow()
method is supported for some types of result sets for refetching data. This consists of going back to the database to re-obtain the database rows that correspond to N rows in the result set, starting with the current row, where N is the fetch size (described above in "Fetch Size"). This lets you see the latest updates to the database that were made outside of your result set, subject to the isolation level of the enclosing transaction.
Because refetching re-obtains only rows that correspond to rows already in your result set, it does nothing about rows that have been inserted or deleted in the database since the original query. It ignores rows that have been inserted, and rows will remain in your result set even after the corresponding rows have been deleted from the database. When there is an attempt to refetch a row that has been deleted in the database, the corresponding row in the result set will maintain its original values.
Following is the refreshRow()
method signature:
You must be at a valid current row when you call this method, not outside the row bounds and not at the insert-row.
The refreshRow()
method is supported for the following result set categories:
Oracle JDBC might support additional result set categories in future releases.
For a code sample that explicitly refetches data using the refreshRow()
method, see "Refetching Rows in a Result Set--ResultSet6.java".
Note:
Scroll-sensitive result set functionality is implemented through implicit calls to |
This section discusses the ability of a result set to see the following:
DELETE
, UPDATE
, or INSERT
operations within the result set), referred to as internal changes
Near the end of the section is a summary table.
The ability of an updatable result set to see its own changes depends on both the result set type and the kind of change (UPDATE
, DELETE
, or INSERT
). This is discussed at various points throughout the "Updating Result Sets" section beginning on , and is summarized as follows:
DELETE
operations are visible for scrollable result sets (scroll-sensitive or scroll-insensitive), but are not visible for forward-only result sets.
After you delete a row in a scrollable result set, the preceding row becomes the new current row, and subsequent row numbers are updated accordingly.
UPDATE
operations are always visible, regardless of the result set type (forward-only, scroll-sensitive, or scroll-insensitive).
INSERT
operations are never visible, regardless of the result set type (neither forward-only, scroll-sensitive, nor scroll-insensitive).
An internal change being "visible" essentially means that a subsequent getXXX()
call will see the data changed by a preceding updateXXX()
call on the same data item.
JDBC 2.0 DatabaseMetaData
objects include the following methods to verify this. Each takes a result set type as input (ResultSet.TYPE_FORWARD_ONLY
, ResultSet.TYPE_SCROLL_SENSITIVE
, or ResultSet.TYPE_SCROLL_INSENSITIVE
).
boolean ownDeletesAreVisible(int) throws SQLException
boolean ownUpdatesAreVisible(int) throws SQLException
boolean ownInsertsAreVisible(int) throws SQLException
Only a scroll-sensitive result set can see external changes to the underlying database, and it can only see the changes from external UPDATE
operations. Changes from external DELETE or INSERT
operations are never visible.
For implementation details of scroll-sensitive result sets, including exactly how and how soon external updates become visible, see "Oracle Implementation of Scroll-Sensitive Result Sets".
JDBC 2.0 DatabaseMetaData
objects include the following methods to verify this. Each takes a result set type as input (ResultSet.TYPE_FORWARD_ONLY
, ResultSet.TYPE_SCROLL_SENSITIVE
, or ResultSet.TYPE_SCROLL_INSENSITIVE
).
boolean othersDeletesAreVisible(int) throws SQLException
boolean othersUpdatesAreVisible(int) throws SQLException
boolean othersInsertsAreVisible(int) throws SQLException
Explicit use of the
Note:
refreshRow()
method, described in "Refetching Rows", is distinct from this discussion of visibility. For example, even though external updates are "invisible" to a scroll-insensitive result set, you can explicitly refetch rows in a scroll-insensitive/updatable result set and retrieve external changes that have been made. "Visibility" refers only to the fact that the scroll-insensitive/updatable result set would not see such changes automatically and implicitly.
Regarding changes made to the underlying database by external sources, there are two similar but distinct concepts with respect to visibility of the changes from your local result set:
A change being "visible" means that when you look at a row in the result set, you can see new data values from changes made by external sources to the corresponding row in the database.
A change being "detected", however, means that the result set is aware that this is a new value since the result set was first populated.
Even when an Oracle result set sees new data (as with an external UPDATE
in a scroll-sensitive result set), it has no awareness that this data has changed since the result set was populated. Such changes are not "detected".
JDBC 2.0 DatabaseMetaData
objects include the following methods to verify this. Each takes a result set type as input (ResultSet.TYPE_FORWARD_ONLY
, ResultSet.TYPE_SCROLL_SENSITIVE
, or ResultSet.TYPE_SCROLL_INSENSITIVE
).
boolean deletesAreDetected(int) throws SQLException
boolean updatesAreDetected(int) throws SQLException
boolean insertsAreDetected(int) throws SQLException
It follows, then, that result set methods specified by JDBC 2.0 to detect changes--rowDeleted()
, rowUpdated()
, and rowInserted()
--will always return false
with the Oracle JDBC drivers. There is no use in calling them.
Table 11-1 summarizes the discussion in the preceding sections regarding whether a result set object in the Oracle JDBC implementation can see changes made internally through the result set itself, and changes made externally to the underlying database from elsewhere in your transaction or from other committed transactions.
For implementation details of scroll-sensitive result sets, including exactly how and how soon external updates become visible, see "Oracle Implementation of Scroll-Sensitive Result Sets".
Notes:
|
The Oracle implementation of scroll-sensitive result sets involves the concept of a window, with a window size that is based on the fetch size. The window size affects how often rows are updated in the result set.
Once you establish a current row by moving to a specified row (as described in "Positioning in a Scrollable Result Set"), the window consists of the N rows in the result set starting with that row, where N is the fetch size being used by the result set (see "Fetch Size"). Note that there is no current row, and therefore no window, when a result set is first created. The default position is before the first row, which is not a valid current row.
As you move from row to row, the window remains unchanged as long as the current row stays within that window. However, once you move to a new current row outside the window, you redefine the window to be the N rows starting with the new current row.
Whenever the window is redefined, the N rows in the database corresponding to the rows in the new window are automatically refetched through an implicit call to the refreshRow()
method (described in "Refetching Rows"), thereby updating the data throughout the new window.
So external updates are not instantaneously visible in a scroll-sensitive result set; they are only visible after the automatic refetches just described.
For a sample application that demonstrates the functionality of a scroll-sensitive result set, see "Scroll-Sensitive Result Set--ResultSet5.java".
This section summarizes all the new connection, result set, statement, and database meta data methods added for JDBC 2.0 result set enhancements. These methods are more fully discussed throughout this chapter.
Following is an alphabetical summary of modified connection methods that allow you to specify result set and concurrency types when you create statement objects.
Statement createStatement
(int resultSetType, int resultSetConcurrency)
This method now allows you to specify result set type and concurrency type when you create a generic Statement
object.
CallableStatement prepareCall
(String sql, int resultSetType, int resultSetConcurrency)
This method now allows you to specify result set type and concurrency type when you create a PreparedStatement
object.
PreparedStatement prepareStatement
(String sql, int resultSetType, int resultSetConcurrency)
This method now allows you to specify result set type and concurrency type when you create a CallableStatement
object.
Following is an alphabetical summary of new result set methods for JDBC 2.0 result set enhancements.
boolean absolute(int row) throws SQLException
Move to an absolute row position in the result set.
void afterLast() throws SQLException
Move to after the last row in the result set (you will not be at a valid current row after this call).
void beforeFirst() throws SQLException
Move to before the first row in the result set (you will not be at a valid current row after this call).
void cancelRowUpdates() throws SQLException
Cancel an UPDATE
operation on the current row. (Call this after the updateXXX()
calls but before the updateRow()
call.)
void deleteRow() throws SQLException
Delete the current row.
boolean first() throws SQLException
Move to the first row in the result set.
int getConcurrency() throws SQLException
Returns an int
value for the concurrency type used for the query (either ResultSet.CONCUR_READ_ONLY
or ResultSet.CONCUR_UPDATABLE
).
int getFetchSize() throws SQLException
Check the fetch size to determine how many rows are fetched in each database round trip (also available in statement objects).
int getRow() throws SQLException
Returns the row number of the current row. Returns 0 if there is no valid current row.
int getType() throws SQLException
Returns an int
value for the result set type used for the query (either ResultSet.TYPE_FORWARD_ONLY
, ResultSet.TYPE_SCROLL_SENSITIVE
, or ResultSet.TYPE_SCROLL_INSENSITIVE
).
void insertRow() throws SQLException
Write a result set INSERT
operation to the database. Call this after calling updateXXX()
methods to set the data values.
boolean isAfterLast() throws SQLException
Returns true
if the position is after the last row.
boolean isBeforeFirst() throws SQLException
Returns true
if the position is before the first row.
boolean isFirst() throws SQLException
Returns true
if the position is at the first row.
boolean isLast() throws SQLException
Returns true
if the position is at the last row.
boolean last() throws SQLException
Move to the last row in the result set.
void moveToCurrentRow() throws SQLException
Move from the insert-row staging area back to what had been the current row prior to the moveToInsertRow()
call.
void moveToInsertRow() throws SQLException
Move to the insert-row staging area to set up a row to be inserted.
boolean next() throws SQLException
Iterate forward through the result set.
boolean previous() throws SQLException
Iterate backward through the result set.
void refreshRow() throws SQLException
Refetch the database rows corresponding to the current window in the result set, to update the data. This method is called implicitly for scroll-sensitive result sets.
boolean relative(int row) throws SQLException
Move to a relative row position, either forward or backward from the current row.
void setFetchSize(int rows) throws SQLException
Set the fetch size to determine how many rows are fetched in each database round trip when refetching (also available in statement objects).
void updateRow() throws SQLException
Write an UPDATE
operation to the database after using updateXXX()
methods to update the data values.
void updateXXX() throws SQLException
Set or update data values in a row to be updated or inserted. There is an updateXXX()
method for each datatype. After calling all the appropriate updateXXX()
methods for the columns to be updated or inserted, call updateRow()
for an UPDATE
operation or insertRow()
for an INSERT
operation.
Following is an alphabetical summary of new statement methods for JDBC 2.0 result set enhancements. These methods are available in generic statement, prepared statement, and callable statement objects.
int getFetchSize() throws SQLException
Check the fetch size to determine how many rows are fetched in each database round trip when executing a query (also available in result set objects).
void setFetchSize(int rows) throws SQLException
Set the fetch size to determine how many rows are fetched in each database round trip when executing a query (also available in result set objects).
void setResultSetCache(OracleResultSetCache cache)
throws SQLException
Use your own client-side cache implementation for scrollable result sets. Create your own class that implements the OracleResultSetCache
interface, then use the setResultSetCache()
method to input an instance of this class to the statement object that will create the result set.
int getResultSetType() throws SQLException
Check the result set type of result sets produced by this statement object (which was specified when the statement object was created).
int getResultSetConcurrency() throws SQLException
Check the concurrency type of result sets produced by this statement object (which was specified when the statement object was created).
Following is an alphabetical summary of new database meta data methods for JDBC 2.0 result set enhancements.
boolean ownDeletesAreVisible(int) throws SQLException
Returns true
if, in this JDBC implementation, the specified result set type can see the effect of its own internal DELETE
operations.
boolean ownUpdatesAreVisible(int) throws SQLException
Returns true
if, in this JDBC implementation, the specified result set type can see the effect of its own internal UPDATE
operations.
boolean ownInsertsAreVisible(int) throws SQLException
Returns true
if, in this JDBC implementation, the specified result set type can see the effect of its own internal INSERT
operations.
boolean othersDeletesAreVisible(int) throws SQLException
Returns true
if, in this JDBC implementation, the specified result set type can see the effect of an external DELETE
operation in the database.
boolean othersUpdatesAreVisible(int) throws SQLException
Returns true
if, in this JDBC implementation, the specified result set type can see the effect of an external UPDATE
operation in the database.
boolean othersInsertsAreVisible(int) throws SQLException
Returns true
if, in this JDBC implementation, the specified result set type can see the effect of an external INSERT
operation in the database.
boolean deletesAreDetected(int) throws SQLException
Returns true
if, in this JDBC implementation, the specified result set type can detect when an external DELETE
operation occurs in the database. This method always returns false
.
boolean updatesAreDetected(int) throws SQLException
Returns true
if, in this JDBC implementation, the specified result set type can detect when an external UPDATE
operation occurs in the database. This method always returns false
.
boolean insertsAreDetected(int) throws SQLException
Returns true
if, in this JDBC implementation, the specified result set type can detect when an external INSERT
operation occurs in the database. This method always returns false
.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|