7 Using API Extensions in JDBC Drivers

This chapter describes how to set up and use third-party JDBC drivers, including using API extensions and batch processing with Oracle Thin Drivers.

Using API Extensions to JDBC Interfaces

Some database vendors, including Oracle, provide additional proprietary methods for working with data from a database that uses their DBMS. These methods extend the standard JDBC interfaces. WebLogic Server supports all extension methods exposed as a public interface in the vendor's JDBC driver.

To use the extension methods exposed in the JDBC driver, you must include these steps in your application code:

  • Import the driver interfaces from the JDBC driver used to create connections in the data source.

  • Get a connection from the data source.

  • Cast the connection object as the vendor's connection interface.

  • Use the API extensions as described in the vendor's documentation.

  • Wrap the JNDI lookup in a try/catch block in order to catch a failed look up and ensure the context is closed in a finally block.

The following sections provide details on using API extensions and supporting code examples. For information about specific extension methods for a particular JDBC driver, refer to the documentation from the JDBC driver vendor.

Sample Code for Accessing API Extensions to JDBC Interfaces

The following code examples use extension methods available in the Oracle Thin driver to illustrate how to use API extensions to JDBC. You can adapt these examples to fit methods exposed in your JDBC driver.

Import Packages to Access API Extensions

Import the interfaces from the JDBC driver used to create the connection in the data source. This example uses interfaces from the Oracle Thin Driver.

import java.sql.*;
import java.util.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import oracle.jdbc.*;
// Import driver interfaces. The driver must be the same driver
// used to create the database connection in the data source.

Get a Connection

Establish the database connection using JNDI, DataSource, and data source objects.

// Get a valid DataSource object for a data source.
// Here we assume that getDataSource() takes 
// care of those details.
javax.sql.DataSource ds = getDataSource(args);
// get a java.sql.Connection object from the DataSource
java.sql.Connection conn = ds.getConnection();

Cast the Connection as a Vendor Connection

Now that you have the connection, you can cast it as a vendor connection. This example uses the OracleConnection interface from the Oracle Thin Driver.

OracleConnection = (oracle.jdbc.OracleConnection)conn;

Use API Extensions

The following code fragment shows how to use the Oracle Row Prefetch method available from the Oracle Thin driver.

Example 7-1 Using an API Extension

// Cast to OracleConnection and retrieve the 
// default row prefetch value for this connection.
int default_prefetch = 
   ((oracle.jdbc.OracleConnection)conn).getDefaultRowPrefetch();
// Cast to OracleStatement and set the row prefetch
// value for this statement. Note that this
// prefetch value applies to the connection between
// WebLogic Server and the database.
      ((oracle.jdbc.OracleStatement)stmt).setRowPrefetch(20);
// Perform a normal sql query and process the results...
String query = "select empno,ename from emp";
java.sql.ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
   java.math.BigDecimal empno = rs.getBigDecimal(1);
   String ename = rs.getString(2);
   System.out.println(empno + "\t" + ename);
}
rs.close();
stmt.close();
conn.close();
conn = null;
. . .

Using API Extensions for Oracle JDBC Types

When Oracle implemented JDBC, concrete classes were used instead of using interfaces for Oracle JDBC Types. There are many of drawbacks in using concrete classes and in the 11.2.0.3 driver there are new interfaces corresponding to the Oracle types. The concrete classes now implement a public interface from the package oracle.jdbc. Programmers should use methods exposed in java.sql whenever possible and for Oracle extension methods use oracle.jdbc.

In the mean time, WebLogic Server implemented corresponding interfaces that could be used to work around the limitations of the concrete classes. These are now deprecated and should be replaced with the corresponding oracle.jdbc interfaces.

In Database version 11.2.0.3 the following types have interfaces.

Old Oracle types Deprecated WLS Interface New interfaces
oracle.sql.ARRAY weblogic.jdbc.vendor.oracle.OracleArray oracle.jdbc.OracleArray
oracle.sql.STRUCT weblogic.jdbc.vendor.oracle.OracleStruct oracle.jdbc.OracleStruct
oracle.sql.CLOB weblogic.jdbc.vendor.oracle.OracleThinClob oracle.jdbc.OracleClob
oracle.sql.BLOB weblogic.jdbc.vendor.oracle.OracleThinBlob oracle.jdbc.OracleBlob
oracle.sql.REF weblogic.jdbc.vendor.oracle.OracleRef oracle.jdbc.OracleRef

Changing the code to use new interfaces is not difficult, but should be handled with care. The below examples use oracle.sql.ARRAY and similar changes apply to other types as well. A list of suggested changes is mentioned below:

  • Import: Modify import statements to use the new interfaces (oracle.jdbc) instead of old interfaces (oracle.sql or weblogic.jdbc.vendor.oracle).

  • Declaration: Use standard Java interfaces for declaration whenever possible. If there is a need to use Oracle extension, use the new Oracle interfaces under oracle.jdbc.

  • Methods: Use standard Java interfaces whenever possible:

    • (Oracle Types): Use methods in standard Java interfaces whenever possible. If required use methods from Oracle interfaces under oracle.jdbc.

    • (Defines): Refrain from using Oracle specific methods such as getARRAY; instead use standard Java methods such as getArray or getObject for those that do have standard Java interfaces.

    • (Binds): Refrain from using Oracle specific methods such as setARRAY; instead use standard Java methods such as setArray or setObject for the ones that do have standard Java interfaces.

Replacing import statements can be done by a script that uses find and sed. For example:

find . -name "*.java" -exec egrep ... > files.list
 
for f in `cat files.list`; do
 
  cat $f |sed 's@^import oracle\.sql\.ARRAY@oracle\.jdbc.OracleArray@g' > /tmp/temp.txt
 
  mv /tmp/temp.txt $f
 
done

Programmers should use factory methods on oracle.jdbc.OracleConnection to create an instance of the types. For example:

int[] intArray = { 5, 7, 9};
 
oracle.sql.ArrayDescriptor aDescriptor = new   oracle.sql.ArrayDescriptor("SCOTT.TYPE1", connection);
 
oracle.sql.ARRAY array = new oracle.sql.ARRAY(aDescriptor, connection, intArray);

should be changed to:

int[] intArray = { 5, 7, 9};
java.sql.Array array = connection.createOracleArray("SCOTT.TYPE1", intArray);

Note:

Oracle does not support anonymous array types and so does not support the standard Connection.createArrayOf method. Instead, use createOracleArray as shown in the sample above.

There are some methods that are no longer available because:

  • There is a way to accomplish the same end using standard or already public methods.

  • The method refers to a deprecated type.

  • The method does not add significant value.

In these cases, the code needs to be modified to use standard API's.

Sample Code for Accessing Oracle Thin Driver Extensions to JDBC Interfaces

The following code examples show how to access the interfaces for Oracle extensions, including interfaces for:

If you selected the option to install server examples with WebLogic Server, see the JDBC examples for more code examples, see JDBC Samples and Tutorials..

Note:

You can use Arrays, Structs, and Refs in server-side applications only. You cannot access them in remote clients using the deprecated JDBC over RMI interface.

Programming with Arrays

In your WebLogic Server server-side applications, you can materialize an Oracle Collection (a SQL Array) in a result set or from a callable statement as a Java array.

To use an Array in WebLogic Server applications:

  1. Import the required classes.

  2. Get a connection and then create a statement for the connection.

  3. Create the Array type, a table that uses that type, and create some rows in the table with arrays.

  4. Get the Array using a result set or a callable statement.

  5. Use the standard Java methods (when used as a java.sql.Array) or Oracle extension methods (when cast as java.jdbc.OracleArray) to work with the data.

The following sections provide more details for these actions:

Import Packages to Access Oracle Extensions

Import the SQL and Oracle interfaces used in this example.

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import oracle.jdbc.*;

Establish the Connection

Establish the database connection using JNDI and DataSource objects.

// Get a valid DataSource object.
// Here we assume that getDataSource() takes 
// care of those details.
javax.sql.DataSource ds = getDataSource(args);
// get a java.sql.Connection object from the DataSource
java.sql.Connection conn = ds.getConnection();

Creating an Array in the Database

You must first create the array type and a table that uses the type. For example:

Statement stmt = conn.createStatement();
 stmt.execute("CREATE TYPE TEST_SCORES AS VARRAY(10)OF   INT");
stmt.execute("CREATE TABLE STUDENTS (STUDENT_ID INT, NAME VARCHAR2(100), SCORES TEST_SCORES)");

The following example creates an array of up to 10 test scores to be associated with a student:

  • Create a row with an Array. You can use a Statement or create the Array using OracleConnection.createOracleArray for use in a PreparedStatement.

    Note:

    You cannot use Connection.createArrayOf because Oracle does not support anonymous array types
  • Insert two rows. The first one uses a SQL statement. The second creates an Array and binds it into a PreparedStatement.

stmt.execute("INSERT INTO STUDENTS VALUES 1,'John Doe',TEST_SCORES(100,99))");
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO STUDENTS VALUES (?,?,?)");
pstmt.setInt(1,2);
pstmt.setString(2,"Jane Doe");
int scores[] = {94, 95};
Array array = ((OracleConnection)conn).createOracleArray("TEST_SCORES",scores);
pstmt.setArray(3,array);
pstmt.execute();

Getting an Array

You can use the getArray() methods for a callable statement or a result set to get a Java array. You can then use the array as a java.sql.array to use standard methods, or you can cast the array as a oracle.jdbc.OracleArray to use the Oracle extension methods for an array.

The following example shows how to get a java.sql.Array from a result set that contains an Array. In the example, the query returns a result set that contains an object column—an Array of test scores for a student.

ResultSet rs = null;
rs = stmt.executeQuery("SELECT * FROM STUDENTS");
while (rs.next()) {
  System.out.print("Name="+rs.getString(2)+": ");
  array = rs.getArray(3);
  BigDecimal scoresBD[] = (BigDecimal[])array.getArray();
  OracleArray oracleArray = (OracleArray)rs.getArray(3);
  scores = oracleArray.getIntArray();
  for (int i = 0; i < scores.length; i++) {
    System.out.print(""+scores[i]+" ");
  }
  System.out.println("");
}

Note:

The default return type for an integer is a BigDecimal. We can cast the Array to an OracleArray and use the Oracle extension method getIntArray() to get back integer values.

Updating an Array in the Database

To update an Array in a database, use the following steps:

  1. Create an array in the database, see Chapter 7, "Creating an Array in the Database."

  2. Update the array in the database using the setArray() method for a prepared statement or a callable statement. For example:

    String sqlUpdate = "UPDATE STUDENTS SET SCORES = ? WHERE STUDENT_ID = ?";
    int newscores[] = {94, 95, 96};
    pstmt = conn.prepareStatement(sqlUpdate);
    array = ((OracleConnection)conn).createOracleArray("TEST_SCORES",newscores);
    pstmt.setArray(1, array);
    pstmt.setInt(2, 1);
    pstmt.executeUpdate();
    

Using Oracle Array Extension Methods

To use the Oracle Thin driver extension methods for an Array, you must first cast the array as an oracle.jdbc.OracleArray. You can then make calls to the Oracle Thin driver extension methods for an Array in addition to the standard methods. For example:

OracleArray oracleArray = (OracleArray)rs.getArray(3);
String sqltype = oracleArray.getSQLTypeName();

Programming with Structs

In your WebLogic Server applications, you can access and manipulate objects from an Oracle database. When you retrieve objects from an Oracle database, you can cast them as either custom Java objects or as a Struct (java.sql.Struct or oracle.jdbc.OracleStruct). A Struct is a loosely typed data type for structured data that takes the place of custom classes in your applications. The Struct interface in the JDBC API includes several methods for manipulating the attribute values in a Struct. Oracle extends the Struct interface with additional methods.

To use a Struct in WebLogic Server applications:

  1. Import the required classes. (See Import Packages to Access Oracle Extensions.)

  2. Get a connection. (See Establish the Connection.)

  3. Create the Struct object type, a table that uses the object, and rows with Struct objects.

  4. Cast the object as a Struct, either java.sql.Struct (to use standard methods) or oracle.jdbc.OracleStruct (to use standard and Oracle extension methods).

  5. Use the standard or Oracle Thin driver extension methods to work with the data.

The following sections provide more details for steps 3 through 5:

Creating Objects in the Database

A Struct is typically used to materialize database objects in your Java application in place of custom Java classes that map to the database objects. You must first create the type and table that uses the type. For example (this snippet is poorly designed and used for demonstration purposes only):

conn = ds.getConnection();
Statement stmt = conn.createStatement();
stmt.execute("CREATE TYPE EMP_STRUCT AS OBJECT (DEPT INT, NAME VARCHAR2(100))");
stmt.execute("CREATE TABLE EMP (ID INT, EMPLOYEE EMP_STRUCT)");

To create a row with a Struct object, you can use a SQL Statement or create the Struct using Connection.createStruct and use it in a PreparedStatement.

Insert two rows. The first one row uses a SQL statement. The second creates a Struct and binds it into a PreparedStatement.

stmt.execute("INSERT INTO EMP VALUES (1001, EMP_STRUCT(10,'John Doe'))");
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO EMP VALUES (?,?)");
Object attrs[] = { new Integer(20), "Jane Doe"};
Struct struct = conn.createStruct("EMP_STRUCT", attrs);
pstmt.setInt(1,1002);
pstmt.setObject(2,struct);
pstmt.execute();

Note:

When creating a SQL structure using Connection.createStruct(), it is necessary to unwrap all data types (Clob, Blob, Struct, Ref, Array, NClob, and SQLXML). Once the structure is created, there is no way to re-wrap them before returning the structure to the application. The structure returned to the application has unwrapped values for the data types.

Getting Struct Attributes

To get the value for an individual attribute in a Struct, you can use the standard JDBC API methods getAttributes() and getAttributes(java.util.Dictionary map).

You can create a result set, get a Struct from the result set, and then use the getAttributes() method. The method returns an array of ordered attributes. You can assign the attributes from the Struct (object in the database) to an object in the application, including Java language types. You can then manipulate the attributes individually. For example:

conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM EMP WHERE ID = 1002");
//The second column uses an object data type.
if (rs.next()) {
  struct = (Struct)rs.getObject(2);
  attrs = struct.getAttributes();
  String name = attrs[1]; 
}

In the preceding example, the second column in the emp table uses an object data type. The example shows how to assign the results from the getObject method to a Java object that contains an array of values, and then use individual values in the array as necessary. Note that the type of the first integer attribute is actually a java.math.BigDecimal.

You can also use the getAttributes(java.util.Dictionary map) method to get the attributes from a Struct. When you use this method, you must provide a hash table to map the data types in the Oracle object to Java language data types. For example:

java.util.Hashtable map = new java.util.Hashtable();
map.put("INT", Class.forName("java.lang.Integer"));
map.put("VARCHAR2", Class.forName("java.lang.String"));
Object[] attrs = struct.getAttributes(map);
String name = (String)attrs[1];

In this example, the value is returned as an Integer instead of a BigDecimal.

Using OracleStruct Extension Methods

To use the Oracle Thin driver extension methods for a Struct, you must cast the java.sql.Struct (or the original getObject result) as a oracle.jdbc.OracleStruct. When you cast a Struct as an OracleStruct, you can use both the standard and extension methods. For example:

OracleStruct oracleStruct = 
  (OracleStruct)rs.getObject(2);
String n = oracleStruct.getSQLTypeName(); // Standard
oracle.jdbc.OracleTypeMetaData otmd =
  oracleStruct.getOracleMetaData();    // Extension

Using a Struct to Update Objects in the Database

To update an object in the database using a Struct, you can use the setObject method in a prepared statement. For example:

pstmt = conn.prepareStatement("UPDATE EMP SET EMPLOYEE = ? WHERE ID =?");
attrs[0] =  new Integer(30);
struct = conn.createStruct("EMP_STRUCT", attrs);
pstmt.setObject (1, struct);
pstmt.setInt (2, 1002);
pstmt.executeUpdate();

Programming with Refs

A Ref is a logical pointer to a row object. When you retrieve a Ref, you are actually getting a pointer to a value in another table (or recursively to the same table). The Ref target must be a row in an object table. You can use a Ref to examine or update the object it refers to. You can also change a Ref so that it points to a different object of the same object type or assign it a null value.

To use a Ref in WebLogic Server applications, use the following steps:

  1. Import the required classes. (See Import Packages to Access Oracle Extensions.)

  2. Get a database connection. (See Establish the Connection.)

  3. Create a Ref using a SQL Statement.

  4. Get the Ref using a result set or a callable statement.

  5. Use the extended Oracle methods by casting to OracleRef.

  6. Update a Ref in the database.

The following sections describe steps 3 through 6 in greater detail:

Creating a Ref in the Database

You cannot create Ref objects in your JDBC application—you can only retrieve existing Ref objects from the database. However, you can create a Ref in the database using statements or prepared statements. For example:

conn = ds.getConnection();
stmt = conn.createStatement();
stmt.execute("CREATE TYPE OB AS OBJECT (OB1 INT,  OB2 INT)");
stmt.execute("CREATE TABLE T1 OF OB");
stmt.execute("INSERT INTO T1 VALUES (5, 5)");
stmt.execute("CREATE TABLE T2 (COL REF OB)");
stmt.execute("INSERT INTO T2 SELECT REF(P) FROM T1 P WHERE P.OB1=5");

The preceding example creates an object type (OB), a table (T1) of that object type, a table (T2) with a Ref column that can point to instances of OB objects, and inserts a Ref into the Ref column. The Ref points to a row in T1 where the value in the first column is 5.

Getting a Ref

To get a Ref in an application, you can use a query to create a result set and then use the getRef method to get the Ref from the result set. For example:

rs = stmt.executeQuery("SELECT REF (S) FROM T1 S WHERE S.OB1=5");
rs.next();
Ref ref = rs.getRef(1);
String name = ref.getBaseTypeName();

The WHERE clause in the preceding example uses dot notation to specify the attribute in the referenced object. After you get the Ref, you can use the Java API method getBaseTypeName.

Using WebLogic OracleRef Extension Methods

In order to use the Oracle Thin driver extension methods for Refs, you must cast the Ref as an OracleRef. For example:

OracleTypeMetaData mdata = ((OracleRef)ref). getOracleMetaData();

Updating Ref Values

To update a Ref, you change the location to which the Ref points with a PreparedStatement or a CallableStatement.

To update the location to which a Ref points using a prepared statement, you can follow these basic steps:

  1. Get a Ref that points to the new location. You use this Ref to replace the value of another Ref.

  2. Create a string for the SQL command to replace the location of an existing Ref with the value of the new Ref.

  3. Create and execute a prepared statement.

For example:

//Get the Ref
rs = stmt.executeQuery("SELECT REF (S) FROM T1 S WHERE S.OB1=5");
rs.next();
ref = rs.getRef(1); 
//Create and execute the prepared statement.
String sqlUpdate = "UPDATE T2 S2 SET COL = ?  WHERE S2.COL.OB1 = 20";
pstmt = conn.prepareStatement(sqlUpdate);
pstmt.setRef(1, ref);
pstmt.executeUpdate();

To use a callable statement to update the location to which a REF points, you prepare the stored procedure, set any IN parameters and register any OUT parameters, and then execute the statement. The stored procedure updates the REF value, which is actually a location. For example:

rs = stmt.executeQuery("SELECT REF (S) FROM T1 S where S.OB1=5");
rs.next();
ref = rs.getRef(1);
// Prepare the stored procedure
String sql = "{call SP1 (?,?)}";
CallableStatement cstmt = conn.prepareCall(sql);
// Set IN and register OUT params
cstmt.setRef(1, ref);
cstmt.registerOutParameter(2, Types.STRUCT, "OB");
// Execute
cstmt.execute();

Programming with with Large Objects

This section contains information, including sample code, on how to work with Blob and Clob objects. For additional information, refer to "Working with LOBs" in Database SecureFiles and Large Objects Developer's Guide.

Creating Blobs in the Database

The following code presumes the Connection is already established. It creates a table with a Blob as the second column.

ResultSet rs = null; 
Statement stmt = null; 
java.sql.Blob blob = null; 
java.io.InputStream is = null;
stmt = conn.createStatement();
stmt.execute("CREATE TABLE TESTBLOB (ID INT, COL2 BLOB )");

The following code inserts a Blob value using a string converted to a byte array as the data.

String insertsql2 = "INSERT INTO TESTBLOB VALUES (?,?)";
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO TESTBLOB VALUES (?,?)");
pstmt.setInt(1, 1);
pstmt.setBytes(2, "initialvalue".getBytes());
pstmt.executeUpdate();

Updating Blobs in the Database

The following code updates the Blob value.

rs = stmt.executeQuery("SELECT COL2 FROM TESTBLOB WHERE ID = 1 FOR UPDATE");
rs.next();
Blob blob = rs.getBlob(1);
blob.setBytes(1, "newdata".getBytes());

Note that you need the FOR UPDATE to be able to update the Blob value.

Using OracleBlob Extension Methods

The following code casts the Blob to an OracleBlob so that you can use an extension method.

rs = stmt.executeQuery("SELECT COL1, COL2 FROM TESTBLOB");
rs.next();
Blob blob = rs.getBlob(2);
is = blob.getBinaryStream();                // Standard
is.close();
is = ((OracleBlob)blob).getBinaryStream(0); // Extended

Once you cast to the OracleBlob interface, you can access the Oracle supported methods in addition to the standard methods.

Programming with Clob Values

Using Clob values is similar to using Blob values except that the data is a string instead of a binary array (use setString instead of setBytes, getClob instead of getBlob, and getCharacterStream instead of getBinaryStream).

If you use a prepared statement to update a Clob and the new value is shorter than the previous value, the Clob retains the characters that were not specifically replaced during the update. For example, if the current value of a Clob is abcdefghij and you update the Clob using a prepared statement with zxyw, the value in the Clob is updated to zxywefghij. To correct values updated with a prepared statement, you should use the dbms_lob.trim procedure to remove the excess characters left after the update. See "DBMS_LOB" in Oracle Database PL/SQL Packages and Types Reference for more information about the dbms_lob.trim procedure.

Transaction Boundaries Using LOBs

When using LOBs, you must take transaction boundaries into account; for example, direct all read/writes to a particular LOB within a transaction.

Recovering LOB Space

To free up space used by a LOB, it's necessary to call lob.close(). This is not automatically done when a ResultSet, Statement, or Connection is closed. For Oracle data bases only, it is also necessary to execute alter session set events '60025 trace name context forever'; on the session so that other sessions can use the freed memory.

Using Batching with the Oracle Thin Driver

In some situations, the Oracle Thin driver may not send updates to the DBMS if a batch size has not been reached and waits until the statement is closed.

When a Prepared Statement is closed, WebLogic Server returns the statement to a standard JDBC state rather than closing it. It is then put back into the pool for the connection so it can be redelivered the next time it is needed.

To make sure all your updates are delivered, you need to call OraclePreparedStatement.sendBatch() explicitly after the last use of the statement, before closing it or closing the connection.