JDBC 4.0 support in Oracle Database

Support for JDBC 4.0 specification is a JDBC driver feature, and not a database feature. Oracle Database 11g JDBC drivers support JDBC 4.0 specification. Add the ojdbc6.jar file to the CLASSPATH environment variable to use the JDBC 4.0 features. JDK 6.0 is required for JDBC 4.0 support. Oracle database 11g JDBC drivers can be used with the Oracle database 9i and the later versions. Oracle database 11g JDBC drivers support all the JDBC 4.0 features except the SQLXML Java data type that is used to access the SQL data type XML. Oracle database 11g JDBC drivers support the wrapper pattern to access non-standard Oracle JDBC resources. Oracle extensions to the JDBC are available in the oracle.jdbc package.

The oracle.jdbc.OracleStatement interface can be unwrapped using the unwrap() method to create a oracle.jdbc.OracleStatement object. As the Statement interface extends the Wrapper interface, create a Statement object from a Connection object, conn. Check if the Statement object is a wrapper for the oracle.jdbc.OracleStatement interface using the isWrapperFor() method. Obtain a OracleStatement object from the interface using the unwrap() method to use the methods of the OracleStatement interface:

Statement stmt = conn.createStatement();
Class class = Class.forName("oracle.jdbc.OracleStatement");
if(stmt.isWrapperFor(class))
{
OracleStatement oracleStmt = (OracleStatement)stmt.unwrap(class);
oracleStmt.defineColumnType(1, Types.NUMBER);
}

Oracle database 11g JDBC drivers support the enhanced chained exceptions in the JDBC 4.0 SQLException class. JDBC 4.0 has added a distinction between the permanent errors and transient errors. Permanent errors are errors that occur in the correct operation of the database system and continue to recur, until the cause of the error is removed. Transient errors are errors occurring due to the failure of some segment of the system, or due to timeouts, and these may not recur if the operation that generated the error is retried. Oracle database 11g JDBC drivers support the different categories of SQLException.

Oracle database 11g JDBC drivers support the ROWID SQL data type. Each table in an Oracle database has a ROWID pseudocolumn that identifies a row in a table. The SQL data type of the ROWID column is ROWID. Usually a rowid uniquely identifies a row in a database. But rows in different tables that are stored in a cluster may have the same rowid. Rowids should not be used as the primary key for a database table. If a row is deleted and reinserted using an Import or Export utility, its rowid may get modified. If a row is deleted, its rowid can be assigned to a row added later. The ROWID pseudocolumn can be used in the SELECT and WHERE clauses. Rowid values have the following applications:

A ROWID column value can be retrieved using the getter methods in the ResultSet and CallableStatement interfaces. Retrieve the ROWID column value for the current row in a ResultSet object, rs, as shown below:

java.sql.RowId rowid=rs.getRowId();

A RowId object is valid till the identified row is not deleted. A RowId duration of the validity can be obtained using the getRowIdLifetime() method of the DatabaseMetaData interface. The duration of validity can be one of the int values in the following table:

A RowId value can be used with a parameterized PreparedStatement to set a parameter value with a RowId object. A RowId value can also be used with an updatable ResultSet to update a column with a RowId object.

Oracle database 11g JDBC drivers support the National Character Set (NCS) data types NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB. Oracle database 11g drivers also support Large Object data types (LOBs). The Connection interface provides createBlob, createClob, and createNClob methods to create Blob, Clob, and NClob objects. Create a Blob object as shown below:

String url="jdbc:oracle:thin:@localhost:1521:ORCL";
Connection connection = DriverManager.getConnection(url,"oe", "pw");
Blob aBlob = connection.createBlob();

The LOB objects created do not contain any data. Data can be added using the setter methods in the Blob, Clob, and NClob interfaces. To add data to the Blob object, obtain an OutputStream object from the Blob object:

OutputStream outputStream=aBlob.setBinaryStream(1);

LOB objects can be used as input parameters with a PreparedStatement object using the setBlob, setClob, and setNClob methods. The Blob object created previously can be set as a parameter value on a PreparedStatement object, pstmt, as follows:

pstmt.setBlob(2,aBlob);

For an updatable ResultSet, the updateBlob, updateClob, and updateNClob methods can be used to update a Blob, Clob, or NClob column value. Update a ResultSet object, rs, of column type, BLOB, with the Blob object already created:

rs.updateBlob(3,aBlob);

Blob, Clob, and NClob data can be retrieved using the getBlob, getClob, and getNClob methods in the ResultSet and CallableStatement interfaces. Retrieve a Blob object corresponding to a BLOB column from a ResultSet object, rs :

Blob blob=rs.getBlob(2);

Either the entire data in a Blob object can be retrieved using the getBinaryStream() method, or the partial data in the Blob object can be retrieved using the getBinaryStream(long pos,long length) method. Here, the parameter, pos, specifies the offset position for start of data retrieval and the length parameter specifies the length in bytes of the data to be retrieved. Retrieve 100bytes of data from the Blob object that was created previously with an offset position of 200:

InputStream inputStream = aBlob.getBinaryStream(200, 100);

LOBs are valid at least for the duration of the transaction in which it is created. For long running transactions, it can be better to release LOB resources using the free() method:

aBlob.free();