The Microsoft JDBC Driver for SQL Server supports the optional JDBC 3.0 APIs to retrieve automatically generated row identifiers. The main value of this feature is to provide a way to make IDENTITY values available to an application that is updating a database table without a requiring a query and a second round-trip to the server.
Because SQL Server doesn't support pseudo columns for identifiers, updates that have to use the auto-generated key feature must operate against a table that contains an IDENTITY column. SQL Server allows only a single IDENTITY column per table. The result set that is returned by getGeneratedKeys method of the SQLServerStatement class will have only one column, with the returned column name of GENERATED_KEYS. If generated keys are requested on a table that has no IDENTITY column, the JDBC driver will return a null result set.
As an example, create the following table in the sample database:
Using auto generated keys.; 2 minutes to read +2; In this article. Download JDBC Driver. The Microsoft JDBC Driver for SQL Server supports the optional JDBC 3.0 APIs to retrieve automatically generated row identifiers. The main value of this feature is to provide a way to make IDENTITY values available to an application that is. Oct 09, 2007 JDBC: Get autogenerated keys on a Oracle DB October 9, 2007 by Michael With the current and latest Oracle JDBC Drivers it’s possible to retrieve one automatically generated key based on a sequence or any other arbitrary value (autogenerated-keys or identity columns in other databases). The situation is a little bit complicated since one of the requirements I have is that the code should work both on mysql and oracle. I checked it against mysql and it works. I tried to resolve the problem by adding the jdbc driver for JDK1.4 ojdbc14.jar in my classpath but the result is the same. Oracle can generated keys by creating a sequence. This sequence is then incremented with each INSERT to provides a unique numeric key. While it's possible to do the INSERT and then a SELECT to RETRIEVE the last sequence, JDBC 3.0 provides a way to tell to the INSERT to return the specified generated keys. Dec 19, 2004 When will Oracle's JDBC driver support auto generated keys? Method: java.sql.Statement.getGeneratedKeys Oracle's JDBC FAQ says that auto-generated keys are not supported.
In the following example, an open connection to the sample database is passed in to the function, an SQL statement is constructed that will add data to the table, and then the statement is run and the IDENTITY column value is displayed.
Oracle Java Database Connectivity (JDBC) supports several different versions of JDBC, including JDBC 2.0 and 3.0. This chapter provides an overview of JDBC 2.0 and JDBC 3.0 support in the Oracle JDBC drivers. The chapter contains the following sections:
The Oracle JDBC drivers support all JDBC 3.0 features. These features are provided through the oracle.jdbc
and oracle.sql
packages. These packages support all Java Development Kit (JDK) releases from 1.2 through 1.4. JDBC 3.0 features that depend on JDK1.4 are made available to earlier JDK versions through Oracle extensions.
Standard JDBC 2.0 features are supported by JDK 1.2 and later versions. There are three areas to consider:
Support for data types, such as objects, arrays, and large objects (LOBs). This is handled through the standard java.sql
package.
Support for standard features, such as result set enhancements and update batching. This is handled through standard objects, such as Connection
, ResultSet
, and PreparedStatement
, under JDK 1.2.x and later.
Support for extended features, such as features of the JDBC 2.0 optional package, also known as the standard extension application programming interface (API), including data sources, connection pooling, and distributed transactions.
This section covers the following topics:
Note:
JDK1.1.x is no longer supported. The packageoracle.jdbc2
has been removed.Oracle JDBC fully supports JDK 1.2.x, which includes standard JDBC 2.0 functionality through implementation of interfaces in the standard java.sql
package. These interfaces are implemented as appropriate by classes in the oracle.sql
and oracle.jdbc
packages.
In a JDK 1.2.x environment, using the JDBC classes in classes12.jar
, JDBC 2.0 features, such as scrollable result sets, updatable result sets, and update batching, are supported through methods specified by standard JDBC 2.0 interfaces.
Features of the JDBC 2.0 optional package, including data sources, connection pooling, and distributed transactions, are supported in a JDK 1.2.x or later environment.
The standard javax.sql
package and classes that implement its interfaces are included in the Java Archive (JAR) files packaged with the Oracle Database.
The following performance enhancements are available under JDBC 2.0, which had previously been available as Oracle extensions:
Update batching
Fetch size or row prefetching
In each case, you have the option of using the standard model or the Oracle model. Do not, however, try to mix usage of the standard model and Oracle model within a single application for either of these features.
See Also:
Table 4-1 lists the interfaces and classes added or extended to support specific JDBC features.
Table 4-1 JDBC 3.0 Feature Support
New feature | JDK1.4 implementation | Pre-JDK1.4 implementation |
---|---|---|
Savepoints (new class) |
|
|
Savepoints (connection extensions) |
|
|
Querying parameter capacities (new class) |
|
|
Querying parameter capacities (interface change) | Not applicable |
|
Resource adapters |
|
|
RowSets |
|
|
LOB modification | Not applicable |
|
Retrieving auto-generated keys |
|
|
Result set holdability |
|
|
Table 4-2 lists the JDBC 3.0 features supported at this release and gives references to a detailed discussion of each feature.
Table 4-2 Key Areas of JDBC 3.0 Functionality
Feature | Comments and References |
---|---|
Transaction savepoints | See 'Transaction Savepoints' for information. |
Statement caching | Reuse of prepared statements by connection pools. See Chapter 22, 'Statement Caching'. |
Switching between local and global transactions | See 'Switching Between Global and Local Transactions'. |
LOB modification | See 'JDBC 3.0 LOB Interface Methods' . |
Named SQL parameters | See 'Interface oracle.jdbc.OracleCallableStatement' and 'Interface oracle.jdbc.OraclePreparedStatement' . |
RowSets | See Chapter 20, 'JDBC RowSets' |
Retrieving auto-generated keys | See 'Retrieval of Auto-Generated Keys' |
Result set holdability | See 'Result Set Holdability' |
The JDBC 3.0 specification supports savepoints, which offer finer demarcation within transactions. Applications can set a savepoint within a transaction and then roll back all work done after the savepoint. Savepoints relax the atomicity property of transactions. A transaction with a savepoint is atomic in the sense that it appears to be a single unit outside the context of the transaction, but code operating within the transaction can preserve partial states.
Note:
Savepoints are supported for local transactions only. Specifying a savepoint within a global transaction causesSQLException
to be thrown.JDK1.4 specifies a standard savepoint API. Oracle JDBC provides the following different savepoint interfaces:
java.sql.Savepoint
Works with JDK1.4
oracle.jdbc.OracleSavepoint
Works across all supported JDK versions.
JDK1.4 adds savepoint-related APIs to java.sql.Connection
. The Oracle JDK version-independent interface, oracle.jdbc.OracleConnection
, provides equivalent functionality.
You create a savepoint using either Connection.setSavepoint
, which returns a java.sql.Savepoint
instance, or OracleConnection.oracleSetSavepoint
, which returns an oracle.jdbc.OracleSavepoint
instance.
A savepoint is either named or unnamed. You specify the name of a savepoint by supplying a string to the setSavepoint
method. If you do not specify a name, then the savepoint is assigned an integer ID. You retrieve a name using getSavepointName()
. You retrieve an ID using getSavepointId()
.
Note:
Attempting to retrieve a name from an unnamed savepoint or attempting to retrieve an ID from a named savepoint throws anSQLException
.You roll back to a savepoint using Connection.rollback(Savepoint svpt)
or OracleConnection.oracleRollback(OracleSavepoint svpt)
. If you try to roll back to a savepoint that has been released, then SQLException
is thrown.
You remove a savepoint using one of the following methods:
Connection.releaseSavepoint(Savepoint svpt)
OracleConnection.oracleReleaseSavepoint(OracleSavepoint svpt)
Qr code generator enter key. Note:
As of Oracle Database 10g,releaseSavepoint
and oracleReleaseSavepoint
are not supported. If you call either of the methods, then SQLException
is thrown with the message 'Unsupported feature'.You query whether savepoints are supported by your database by calling oracle.jdbc.OracleDatabaseMetaData.supportsSavepoints()
, which returns true
if savepoints are available, false
otherwise.
When using savepoints, you must consider the following:
After a savepoint has been released, attempting to reference it in a rollback operation will cause an SQLException
to be thrown.
When a transaction is committed or rolled back, all savepoints created in that transaction are automatically released and become invalid.
Rolling a transaction back to a savepoint automatically releases and makes invalid any savepoints created after the savepoint in question.
The following methods are used to get information from savepoints. These methods are defined within both the java.sql.Connection
and oracle.jdbc.OracleSavepoint
interfaces:
public int getSavepointId() throws SQLException;
Returns the savepoint ID for an unnamed savepoint. Throws SQLException
if self
is a named savepoint.
public String getSavepointName() throws SQLException;
Returns the name of a named savepoint. Throws SQLException
if self
is an unnamed savepoint.
The following methods are defined within the java.sql.Connection
interface:
public Savepoint setSavepoint() throws SQLException;
Creates an unnamed savepoint. Throws SQLException
on database error or if connection is in the auto-commit mode or participating in a global transaction.
public Savepoint setSavepoint(String name) throws SQLException;
Creates a named savepoint. If a savepoint by this name already exists, then this instance replaces it. Throws SQLException
on database error or if connection is in the auto-commit mode or participating in a global transaction.
public void rollback(Savepoint savepoint) throws SQLException;
Removes specified savepoint from current transaction. Any references to the savepoint after it is removed cause an SQLException
to be thrown. Throws SQLException
on database error or if connection is in the auto-commit mode or participating in a global transaction.
public void releaseSavepoint(Savepoint savepoint) throws SQLException;
Not supported at this release. Always throws SQLException
.
The following methods are defined within the oracle.jdbc.OracleConnection
interface. Except for using OracleSavepoint
in the signatures, they are identical to the methods declared in the preceding section.
Many database systems automatically generate a unique key field when a row is inserted. Oracle Database provides the same functionality with the help of sequences and triggers. JDBC 3.0 introduces the retrieval of auto-generated keys feature that enables you to retrieve such generated values. In JDBC 3.0, the following interfaces are enhanced to support the retrieval of auto-generated keys feature:
These interfaces provide methods that support retrieval of auto-generated keys. However, this feature is supported only when INSERT
statements are processed. Other data manipulation language (DML) statements are processed, but without retrieving auto-generated keys.
Note:
The Oracle server-side internal driver does not support the retrieval of auto-generated keys feature.In JDBC 3.0, the java.sql.DatabaseMetaData
interface provides the following method:
The method indicates whether retrieval of auto-generated keys is supported or not by the JDBC driver and the underlying data source.
The java.sql.Statement
interface is enhanced with the following methods:
This interface provides new methods for processing SQL statements and retrieving auto-generated keys. These methods take a String object that contains a SQL statement. They also take either the flag, Statement.RETURN_GENERATED_KEYS
, indicating whether any generated columns are to be returned, or an array of column names or indexes specifying the columns that should be returned. The flag and the array values are considered only when an INSERT
statement is processed. If an UPDATE
or DELETE
statement is processed, these values are ignored.
The getGeneratedKeys()
method enables you to retrieve the auto-generated key fields. The auto-generated keys are returned as a ResultSet object.
If key columns are not explicitly indicated, then Oracle JDBC drivers cannot identify which columns need to be retrieved. When a column name or column index array is used, Oracle JDBC drivers can identify which columns contain auto-generated keys that you want to retrieve. However, when the Statement.RETURN_GENERATED_KEYS
integer flag is used, Oracle JDBC drivers cannot identify these columns. When the integer flag is used to indicate that auto-generated keys are to be returned, the ROWID
pseudo column is returned as key. The ROWID
can be then fetched from the ResultSet object and can be used to retrieved other columns.
The java.sql.Connection
interface is enhanced with the following methods:
These methods enable you to create a PreparedStatement
object that is capable of returning auto-generated keys.
The following code illustrates retrieval of auto-generated keys:
In the preceding example, a sequence, SEQ01
, is created to generate values for the ORDER_ID
column starting from 1000
and incrementing by 1
each time the sequence is processed to generated the next value. An OraclePreparedStatement
object is created to insert a row in to the ORDERS
table.
Before Oracle Database 10g release 2 (10.2), Oracle provided proprietary interfaces for modification of LOB data. JDBC 3.0 adds methods for these operations. In Oracle9i Database release 2, the JDBC 3.0 methods were present in ojdbc14.jar
, but were not functional. The JDBC 3.0 standard LOB methods differ slightly in name and function from the Oracle proprietary ones. In Oracle Database 10g release 2 (10.2), the JDBC 3.0 standard methods are implemented in both ojdbc14.jar
and classes12.jar
. In order to use these methods with JDK1.2 or 1.3, LOB variables must be typed as, or cast to, oracle.sql.BLOB
or oracle.sql.CLOB
as appropriate. With JDK1.4, LOB variables may be typed as java.sql.Blob
or java.sql.Clob
.
Table 4-3 and Table 4-4 show the conversions between Oracle proprietary methods and JDBC 3.0 standard methods.
Table 4-3 BLOB Method Equivalents
Oracle Proprietary Method | JDBC 3.0 Standard Method |
---|---|
|
|
|
|
|
|
|
|
Table 4-4 CLOB Method Equivalents
Oracle Proprietary Method | JDBC 3.0 Standard Method |
---|---|
|
|
not applicable |
|
|
|
|
|
|
|
Result set holdability is a new feature introduced in JDBC 3.0. This feature enables applications to decide whether the ResultSet
objects should be open or closed, when a commit operation is performed. The commit operation could be either implicit or explicit.
The default holdability property of a ResultSet
object is implementation defined. The default holdability of ResultSet
objects returned by the underlying data source can be determined using the APIs provided by JDBC 3.0.
In JDBC 3.0, the following APIs have been modified to support this feature:
java.sql.Connection
java.sql.DatabaseMetaData
Jan 30, 2011 Buttons labeled Copy, Save, and Generate key the program's functions. Other than an About button with a link to the developer, Bench Software, that's all there is to Wireless Key Generator, but that's all it needs; this program requires no instructions to operate. We selected WPA/WPA2 and 504 bits and pressed Generate. Wifi key generator software free download. WiFi Key Generator is a nice, free Windows program, that belongs to the category Networking software and has been published by Free Software Download. More about WiFi Key Generator About the download, WiFi Key Generator is a relatively light software that does not require as much free space than most programs in the section Networking software.
java.sql.Statement
JDBC 3.0 also provides two new ResultSet constants:
ResultSet.HOLD_CURSORS_OVER_COMMIT
ResultSet.CLOSE_CURSORS_AT_COMMIT
Oracle Database only supports HOLD_CURSORS_OVER_COMMIT
. Therefore, it is the default value for the Oracle JDBC drivers. Any attempt to change holdability will throw SQLException
.