Retrieving and Passing an Object Reference

This section discusses JDBC functionality for retrieving and passing object references.

Retrieving an Object Reference from a Result Set

To demonstrate how to retrieve object references, the following example first defines an Oracle object type ADDRESS, which is then referenced in the PEOPLE table:

create type ADDRESS as object
   (street_name     VARCHAR2(30),
    house_no        NUMBER);

create table PEOPLE 
    (col1 VARCHAR2(30),
     col2 NUMBER,
     col3 REF ADDRESS);

The ADDRESS object type has two attributes: a street name and a house number. The PEOPLE table has three columns: a column for character data, a column for numeric data, and a column containing a reference to an ADDRESS object.

To retrieve an object reference, follow these general steps:

1. Use a standard SQL SELECT statement to retrieve the reference from a database table REF column.

2. Use getREF() to get the address reference from the result set into a REF object.

3. Let Address be the Java custom class corresponding to the SQL object type ADDRESS.

4. Add the correspondence between the Java class Address and the SQL type ADDRESS to your type map.

5. Use the getValue() method to retrieve the contents of the Address reference. Cast the output to a Java Address object.

Here is the code for these steps (other than adding Address to the type map), where stmt is a previously defined statement object. The PEOPLE database table is defined earlier in this section:

ResultSet rs = stmt.executeQuery("SELECT col3 FROM PEOPLE"); 
while (rs.next())
{
   REF ref = ((OracleResultSet)rs).getREF(1);
   Address a = (Address)ref.getValue();
}

As with other SQL types, you could retrieve the reference with the getObject() method of your result set. Note that this would require you to cast the output. For example:

REF ref = (REF)rs.getObject(1);

There are no performance advantages in using getObject() instead of getREF(); however, using getREF() allows you to avoid casting the output.

Retrieving an Object Reference from a Callable Statement

To retrieve an object reference as an OUT parameter in PL/SQL blocks, you must register the bind type for your OUT parameter.

1. Cast your callable statement to an OracleCallableStatement:

   OracleCallableStatement ocs = 
      (OracleCallableStatement)conn.prepareCall("{? = call func()}");
   
   

2. Register the OUT parameter with this form of the registerOutParameter() method:

   ocs.registerOutParameter
               (int param_index, int sql_type, String sql_type_name);
   
   

   Where param_index is the parameter index and sql_type is the SQL typecode (in this case, OracleTypes.REF). The sql_type_name is the name of the structured object type that this reference is used for. For example, if the OUT parameter is a reference to an ADDRESS object (as in "Retrieving and Passing an Object Reference"), then ADDRESS is the sql_type_name that should be passed in.

3. Execute the call:

   ocs.execute();
   

Passing an Object Reference to a Prepared Statement

Pass an object reference to a prepared statement in the same way as you would pass any other SQL type. Use either the setObject() method or the setREF() method of a prepared statement object.

Continuing the example in "Retrieving and Passing an Object Reference", use a prepared statement to update an address reference based on ROWID, as follows:

PreparedStatement pstmt = 
   conn.prepareStatement ("update PEOPLE set ADDR_REF = ? where ROWID = ?"); 
((OraclePreparedStatement)pstmt).setREF (1, addr_ref);
((OraclePreparedStatement)pstmt).setROWID (2, rowid);

---