TYPE_SCROLL_SENSITIVE to TYPE_SCROLL_INSENSITIVE

简介: TYPE_SCROLL_SENSITIVE to TYPE_SCROLL_INSENSITIVE

相关开发要求和限制 参考:https://docs.oracle.com/database/121/JJDBC/resltset.htm#JJDBC28629:

17 Result Set

Resultset Limitations and Downgrade Rules

ome 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 run, then 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 run, 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.

Result Set Limitations

The following limitations are placed on queries for enhanced result sets. Failure to follow these guidelines results in the JDBC driver choosing an alternative result set type or concurrency type.

To produce an updatable result set:

A query can select from only a single table and cannot contain any join operations.

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.

A query cannot use SELECT * .

However, there is a workaround for this.

A query must select table columns only.

It cannot select derived columns or aggregates, such as the SUM or MAX of a set of columns.

To produce a scroll-sensitive result set:

A query cannot use SELECT * .

However, there is a workaround for this.

A query can select from only a single table.

Scrollable and updatable result sets cannot have any column as Stream. When the server has to fetch a Stream column, it reduces the fetch size to one and blocks all columns following the Stream column until the Stream column is read. As a result, columns cannot be fetched in bulk and scrolled through.

Workaround

As a workaround for the SELECT * limitation, you can use table aliases, as shown in the following example:

SELECT t.* FROM TABLE t ...
Note:

There is a simple way to determine if your query will probably produce a scroll-sensitive or updatable result set: If you can legally add a ROWID column to the query list, then the query is probably suitable for either a scroll-sensitive or an updatable result set.
Result Set Downgrade Rules

If the specified result set type or concurrency type is not feasible, then Oracle JDBC driver uses the following rules in choosing alternate types:

If the specified result set type is TYPE_SCROLL_SENSITIVE, but the JDBC driver cannot fulfill that request, then the driver attempts a downgrade to TYPE_SCROLL_INSENSITIVE.

If the specified or downgraded result set type is TYPE_SCROLL_INSENSITIVE, but the JDBC driver cannot fulfill that request, then the driver attempts a downgrade to TYPE_FORWARD_ONLY.

If the specified concurrency type is CONCUR_UPDATABLE, but the JDBC driver cannot fulfill that request, then the JDBC driver attempts a downgrade to CONCUR_READ_ONLY.

Note:

Any manipulations of the result set type and concurrency type by the JDBC driver are independent of each other.
Verifying Result Set Type and Concurrency Type

After a query has been run, 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.

Avoiding Update Conflicts
It is important to be aware of the following facts regarding updatable result sets with the JDBC drivers:

The drivers do not enforce write locks for an updatable result set.

The drivers do not check for conflicts with a result set 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.

Oracle JDBC drivers use the ROWID to uniquely identify a row in a database table. As long as the ROWID is valid when a driver tries to send an UPDATE or DELETE operation to the database, the operation will be run.

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

相关文章
|
6月前
|
前端开发
|
API
DataTables中的column().visible()
在数据表中显示和隐藏列非常方便,尤其是在显示信息密度较大的表时。此方法允许即时更改单个列的可见性,或读取列的可见性状态。
161 0
|
Java 微服务
Malformed markup: Attribute “prop“ appears more than once in element
Malformed markup: Attribute “prop“ appears more than once in element
271 0
how is opportunity detail page display first item by default
how is opportunity detail page display first item by default
91 0
how is opportunity detail page display first item by default
multiple context container - entry point for tile click
multiple context container - entry point for tile click
multiple context container - entry point for tile click
try to navigate from button to line item page
Created by Wang, Jerry, last modified on Feb 15, 2015
130 0
try to navigate from button to line item page
how is navigation list item click event handled - actually no logic done
how is navigation list item click event handled - actually no logic done
how is navigation list item click event handled - actually no logic done