3 JDBC Standards Support

Support for JDBC 2.0 Standard

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:

• Data Type Support

• Standard Feature Support

• Extended Feature Support

• Standard versus Oracle Performance Enhancement APIs

Support for JDBC 3.0 Standard

Standard JDBC 3.0 features are supported by JDK 1.4 and earlier versions. Table 3-1 lists the JDBC 3.0 features supported by Oracle Database 11g Release 1 (11.1) and gives references to a detailed discussion of each feature.

The following JDBC 3.0 features supported by Oracle JDBC drivers are covered in this section:

• Transaction Savepoints

• Retrieval of Auto-Generated Keys

• JDBC 3.0 LOB Interface Methods

• Result Set Holdability

/** 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 into 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.setNString(1, s);
pstmt.executeUpdate();
ResultSet rset = pstmt.getGeneratedKeys();
...

Support for JDBC 4.0 Standard

The JDBC 4.0 standard support is provided by JDK 1.6 and later versions. Oracle Database 11g Release 1 (11.1) JDBC drivers provide support for the JDBC 4.0 standard.

Some of the new features available in Oracle Database 11g Release 1 (11.1) JDBC drivers are the following:

• Wrapper Pattern Support

• Enhanced Exception Hierarchy and SQLException

• The RowId Data Type

• LOB Creation

• National Language Character Set Support

This document provides only an overview of these new features. For detailed information about these features, see "Java 2 Platform, Standard Edition (JSE) 6.0 specification" at

http://java.sun.com/javase/6/docs/

Wrapper Pattern Support

Wrapper pattern is a common coding pattern used in Java applications to provide extensions beyond the traditional JDBC API that are specific to a data source. You may need to use these extensions to access the resources that are wrapped as proxy class instances representing the actual resources. JDBC 4.0 introduces the Wrapper interface that describes a standard mechanism to access these wrapped resources represented by their proxy, to permit direct access to the resource delegates.

The Wrapper interface provides the following two methods:

• public boolean isWrapperFor(Class<?> iface) throws SQLException;

• public <T> T unwrap(Class<T> iface) throws SQLException;

The other JDBC 4.0 interfaces, except those that represent SQL data, all implement this interface. These include Connection, Statement and its subtypes, ResultSet, and the metadata interfaces.

Enhanced Exception Hierarchy and SQLException

JDBC 3.0 defines only a single exception, SQLException. However, there are large categories of errors and it is useful to distinguish them. This feature provides subclasses of SQLException to identify the different categories of errors. The primary distinction is between permanent errors and transient errors. Permanent errors are a result of the correct operation of the system and will always occur. Transient errors are the result of failures, including timeouts, of some part of the system and may not reoccur.

New exceptions have been added to represent transient and permanent errors and the different categories of these errors.

Also, the SQLException class and its subclasses have been enhanced to provide support for the J2SE chained exception functionality.

The RowId Data Type

JDBC 4.0 provides the java.sql.RowId data type to represent SQL ROWID values. You can retrieve a RowId value using the getter methods defined in the ResultSet and CallableStatement interfaces. You can also use a RowId value in a parameterized PreparedStatement to set a parameter with a RowId object or in an updatable result set to update a column with a specific RowId value.

A RowId object is valid until the identified row is not deleted. A RowId object may also be valid for the following:

• The duration of the transaction in which it is created

• The duration of the session in which it is created

• An undefined duration where by it is valid forever

The lifetime of the RowId object can be determined by calling the DatabaseMetaData.getRowIdLifetime method.

LOB Creation

In JDBC 4.0, the Connection interface has been enhanced to provide support for the creation of BLOB, CLOB, and NCLOB objects. The interface provides the createBlob, createClob, and createNClob methods that enable you to create Blob, Clob, and NClob objects.

The created large objects (LOBs) do not contain any data. You can add data in these objects by calling the appropriate setXXX methods. To retrieve the data from these objects, you can call the getBlob, getClob, and getNClob methods defined in the ResultSet and CallableStatement interfaces. You can either retrieve the entire content or a part of the content from these objects. The following code snippet illustrates how to retrieve 100 bytes of data from a BLOB object starting at offset 200:

...
Connection con = DriverManager.getConnection(url, props);
Blob aBlob = con.createBlob();
// Add data to the BLOB object.
...
// Retrieve part of the data from the BLOB object.
InputStream is = aBlob.getBinaryStream(200, 100);
...

You can also pass LOBs as input parameters to a PreparedStatement object using the setBlob, setClob, and setNClob methods. You can use the updateBlob, updateClob, and updateNClob methods to update a column value in an updatable result set.

LOBs remain valid for at least the duration of the transaction in which they are created. This may result in unwarranted use of memory during a long running transaction. You can release LOBs by calling their free method, as follows:

...
Clob aClob = con.createClob();
int numWritten = aClob.setString(1, val);
aClob.free();
...

National Language Character Set Support

JDBC 4.0 introduces the NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB JDBC types to access the national character set types. These types are similar to the CHAR, VARCHAR, LONGVARCHAR, and CLOB types, except that the values are encoded using the national character set.