This chapter describes Oracle extensions to standard Java Database Connectivity (JDBC) that let you access and manipulate object references. The following topics are discussed:
Oracle supports the use of references to database objects. Oracle JDBC provides support for object references as:
Columns in aSELECT clause
IN orOUT bind variables
Attributes in an Oracle object
Elements in a collection type object
In SQL, an object reference (REF) is strongly typed. For example, a reference to anEMPLOYEE object would be defined as anEMPLOYEE REF, not just aREF.
When you select an object reference in Oracle JDBC, be aware that you are retrieving only a pointer to an object, not the object itself. You have the choice of materializing the reference as a weakly typedoracle.sql.REF instance, or ajava.sql.Ref instance for portability, or materializing it as an instance of a custom Java class that you have created in advance, which is strongly typed. Custom Java classes used for object references are referred to ascustom reference classes and must implement theoracle.sql.ORAData interface. Theoracle.sql.REF class implements the standardjava.sql.Ref interface.
You can retrieve aREF instance through a result set or callable statement object, and pass an updatedREF instance back to the database through a prepared statement or callable statement object. TheREF class includes functionality to get and set underlying object attribute values, and get the SQL base type name of the underlying object.
Custom reference classes include this same functionality, as well as having the advantage of being strongly typed. This can help you find coding errors during compilation that might not otherwise be discovered until run time.
Note:
If you are using theoracle.sql.ORAData interface for custom object classes, then you will presumably useORAData for corresponding custom reference classes as well. However, if you are using the standardjava.sql.SQLData interface for custom object classes, then you can only use weak Java types for references. TheSQLData interface is for mapping SQL object types only.
You can create and retrieveREF objects in your JDBC application only by running SQL statements. There is no JDBC-specific functionality for creating and retrievingREF objects.
You cannot have a reference to an array, even though arrays, like objects, are structured types.
To access and update object data through an object reference, you must obtain the reference instance through a result set or callable statement and then pass it back as a bind variable in a prepared statement or callable statement. It is the reference instance that contains the functionality to access and update object attributes.
This section covers the following topics:
You can use the result set, callable statement, and prepared statement methods to retrieve and pass object references.
Result Set and Callable Statement Getter Methods
TheOracleResultSet andOracleCallableStatement classes supportgetREF andgetRef methods to retrieveREF objects as output parameters.REF objects can be retrieved either asoracle.sql.REF instances orjava.sql.Ref instances. You can also use thegetObject method. These methods take as input aString column name orint column index.
Prepared and Callable Statement Setter Methods
TheOraclePreparedStatement andOracleCallableStatement classes supportsetREF andsetRef methods to takeREF objects as bind variables and pass them to the database. You can also use thesetObject method. These methods take as input aString parameter name orint parameter index as well as anoracle.sql.REF instance or ajava.sql.Ref instance.
You can use the followingoracle.sql.REF class methods to retrieve the SQL object type name and retrieve and pass the underlying object data:
Retrieves the fully qualified SQL structured type name of the referenced object. This is a standard method specified by thejava.sql.Ref interface.
Retrieves the referenced object from the database, enabling you to access its attribute values. It optionally takes a type map object. You can use the default type map of the database connection object. This method is an Oracle extension.
Sets the referenced object in the database, allowing you to update its attribute values. It takes an instance of the object type, either aSTRUCT instance or an instance of a custom object class, as input. This method is an Oracle extension.
This section discusses JDBC functionality for retrieving and passing object references. It covers the following topics:
To demonstrate how to retrieve object references, the following example first defines an Oracle object typeADDRESS, which is then referenced in thePEOPLE table:
create type ADDRESS as object (street_name VARCHAR2(30), house_no NUMBER);create table PEOPLE (col1 VARCHAR2(30), col2 NUMBER, col3 REF ADDRESS);
TheADDRESS object type has two attributes: a street name and a house number. ThePEOPLE table has three columns: a column for character data, a column for numeric data, and a column containing a reference to anADDRESS object.
To retrieve an object reference, follow these general steps:
Use a standard SQLSELECT statement to retrieve the reference from a database tableREF column.
UsegetREF to get the address reference from the result set into aREF object.
LetAddress be the Java custom class corresponding to the SQL object typeADDRESS.
Add the correspondence between the Java classAddress and the SQL typeADDRESS to your type map.
Use thegetValue method to retrieve the contents of theAddress reference. Cast the output toAddress.
ThePEOPLE database table is defined earlier in this section. The code for the preceding steps, except the step of addingAddress to the type map, is as follows:
ResultSet rs = stmt.executeQuery("SELECT col3 FROM PEOPLE"); while (rs.next()){ REF ref = ((OracleResultSet)rs).getREF(1); Address a = (Address)ref.getValue();}Note:
In the preceding code,stmt is a previously defined statement object.As with other SQL types, you could retrieve the reference with thegetObject 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 usinggetObject instead ofgetREF; however, usinggetREF enables you to avoid casting the output.
To retrieve anobject reference as anOUT parameter in PL/SQL blocks, you must register the bind type for yourOUT parameter.
Cast your callable statement toOracleCallableStatement, as follows:
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? = call func()}");Register theOUT parameter with the following form of theregisterOutParameter method:
ocs.registerOutParameter (intparam_index, intsql_type, Stringsql_type_name);
param_index is the parameter index andsql_type is the SQL type code. Thesql_type_name is the name of the structured object type that this reference is used for. For example, if theOUT parameter is a reference to anADDRESS object, thenADDRESS is thesql_type_name that should be passed in.
Run the call, as follows:
ocs.execute();
Pass anobject reference to a prepared statement in the same way as you would pass any other SQL type. Use either thesetObject method or thesetREF method of a prepared statement object.
Use a prepared statement to update an address reference based onROWID, as follows:
PreparedStatement pstmt = conn.prepareStatement ("update PEOPLE set ADDR_REF = ? where ROWID = ?"); ((OraclePreparedStatement)pstmt).setREF (1, addr_ref);((OraclePreparedStatement)pstmt).setROWID (2, rowid);You can use theREF objectsetValue method to update the value of an object in the database through an object reference. To do this, you must first retrieve the reference to the database object and create a Java object that corresponds to the database object.
For example, you can use the code in"Retrieving and Passing an Object Reference", to retrieve the reference to a databaseADDRESS object, as follows:
ResultSet rs = stmt.executeQuery("SELECT col3 FROM PEOPLE"); if (rs.next()){ REF ref = rs.getREF(1); Address a = (Address)ref.getValue();}Then,you can create a JavaAddress object that corresponds to the databaseADDRESS object. Use thesetValue method of theREF class to set the value of the database object, as follows:
Address addr = new Address(...);ref.setValue(addr);
Here, thesetValue method updates the databaseADDRESS object immediately.
This chapter primarily describes the functionality of theoracle.sql.REF class, but it is also possible to access Oracle object references through custom Java classes or, more specifically, custom reference classes.
Custom reference classes offer all the functionality described earlier in this chapter, as well as the advantage of being strongly typed. A custom reference class must satisfy three requirements:
It must implement theoracle.sql.ORAData interface. Note that the standard JDBCSQLData interface, which is an alternative for custom object classes, is not intended for custom reference classes.
It, or a companion class, must implement theoracle.sql.ORADataFactory interface, for creating instances of the custom reference class.
It must provide a way to refer to the object data. JPublisher accomplishes this by using anoracle.sql.REF attribute.
You can create custom reference classes yourself, but the most convenient way to produce them is through the Oracle JPublisher utility. If you use JPublisher to generate a custom object class to map to an Oracle object and you specify that JPublisher use aORAData implementation, then JPublisher will also generate a customreference class that implementsORAData andORADataFactory and includes anoracle.sql.REF attribute. TheORAData implementation will be used if the JPublisher-usertypes mapping option is set tooracle, which is the default.
Custom reference classes are strongly typed. For example, if you define an Oracle objectEMPLOYEE, then JPublisher can generate anEmployee custom object class and anEmployeeRef custom reference class. UsingEmployeeRef instances instead of genericoracle.sql.REF instances makes it easier to catch errors during compilation instead of at run time. For example, if you accidentally assign some other kind of object reference into anEmployeeRef variable.
Be aware that the standardSQLData interface supports only SQL object mappings. For this reason, if you instruct JPublisher to implement the standardSQLData interface in creating a custom object class, then JPublisher willnot generate a custom reference class. In this case, your only option is to use standardjava.sql.Ref instances ororacle.sql.REF instances to map to your object references.