4 JDBC Standards Support

Data Type Support

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.

Standard Feature Support

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.

Extended Feature Support

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.

Standard versus Oracle Performance Enhancement APIs

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.

Creating a Savepoint

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

Rolling back to a Savepoint

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.

Releasing a Savepoint

You remove a savepoint using one of the following methods:

• Connection.releaseSavepoint(Savepoint svpt)

• OracleConnection.oracleReleaseSavepoint(OracleSavepoint svpt)

Checking Savepoint Support

You query whether savepoints are supported by your database by calling oracle.jdbc.OracleDatabaseMetaData.supportsSavepoints(), which returns true if savepoints are available, false otherwise.

Savepoint Notes

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.

Savepoint Interfaces

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.

Pre-JDK1.4 Savepoint Support

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.

public OracleSavepoint oracleSetSavepoint() throws SQLException;
public OracleSavepoint oracleSetSavepoint(String name) throws SQLException;
public void oracleRollback(OracleSavepoint savepoint)  throws SQLException;
public void oracleReleaseSavepoint(OracleSavepoint savepoint) throws SQLException;

java.sql.DatabaseMetaData

In JDBC 3.0, the java.sql.DatabaseMetaData interface provides the following method:

public boolean supportsGetGeneratedKeys();

The method indicates whether retrieval of auto-generated keys is supported or not by the JDBC driver and the underlying data source.

java.sql.Statement

The java.sql.Statement interface is enhanced with the following methods:

public boolean execute(String sql, int autoGeneratedKeys) throws SQLException;
public boolean execute(String sql, int[] columnIndexes) throws SQLException;
public boolean execute(String sql, String[] columnNames) throws SQLException;
public boolean executeUpdate(String sql, int autoGeneratedKeys) throws SQLException;
public boolean executeUpdate(String sql, int[] columnIndexes) throws SQLException;
public boolean executeUpdate(String sql, String[] columnNames) throws SQLException;
public ResultSet getGeneratedKeys() throws SQLException;

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.

java.sql.Connection

The java.sql.Connection interface is enhanced with the following methods:

public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException;
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException;
public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException;

These methods enable you to create a PreparedStatement object that is capable of returning auto-generated keys.

Sample Code

The following code illustrates retrieval of auto-generated keys:

/** SQL statements for creating an ORDERS table and a sequence for generating the
  * ORDER_ID.
  *
  * CREATE TABLE ORDERS (ORDER_ID NUMBER, CUSTOMER_ID NUMBER, ISBN NUMBER,
  * DESCRIPTION NCHAR(5))
  *
  * CREATE SEQUENCE SEQ01 INCREMENT BY 1 START WITH 1000
  */

...
String cols[] = {"ORDER_ID", "DESCRIPTION"};

// Create a PreparedStatement for inserting a row in to the ORDERS table.
OraclePreparedStatement pstmt = (OraclePreparedStatement) conn.prepareStatement("INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ISBN,  DESCRIPTION) VALUES (SEQ01.NEXTVAL, 101, 966431502, ?)", cols);

char c[] = {'a', '\u5185', 'b'};
String s = new String(c);
pstmt.setFormOfUse(1, OraclePreparedStatement.FORM_NCHAR);
pstmt.setString(1, s);
pstmt.executeUpdate();
ResultSet rset = pstmt.getGeneratedKeys();
...

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.