Oracle9i JDBC Developer's Guide and Reference Release 1 (9.0.1) Part Number A90211-01 |
|
This chapter describes Oracle extensions to standard JDBC that let you access and manipulate object references. The following topics are discussed:
Oracle supports the use of references (pointers) to Oracle database objects. Oracle JDBC provides support for object references as:
IN
or OUT
bind variables
In SQL, an object reference (REF
) is strongly typed. For example, a reference to an EMPLOYEE
object would be defined as an EMPLOYEE REF
, not just a REF
.
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 typed oracle.sql.REF
instance (or a java.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 as custom reference classes in this manual and must implement the oracle.sql.ORAData
interface.
The oracle.sql.REF
class implements the standard java.sql.Ref
interface (oracle.jdbc2.Ref
under JDK 1.1.x).
You can retrieve a REF
instance through a result set or callable statement object, and pass an updated REF
instance back to the database through a prepared statement or callable statement object. The REF
class includes functionality to get and set underlying object attribute values, and get the SQL base type name of the underlying object (for example, EMPLOYEE
).
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 runtime.
For more information about custom reference classes, see "Custom Reference Classes with JPublisher".
For a complete sample application using the REF class to access SQL object data, see "Weakly Typed Object References--StudentRef.java".
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 summarizes the following:
REF
instances from and to the database
REF
class functionality to get and set object attributes
Remember that you can use custom reference classes instead of the ARRAY
class. See "Custom Reference Classes with JPublisher".
Use the following result set, callable statement, and prepared statement methods to retrieve and pass object references. Code examples are provided later in the chapter.
The OracleResultSet
and OracleCallableStatement
classes support getREF()
and getRef()
methods to retrieve REF
objects as output parameters--either as oracle.sql.REF
instances or java.sql.Ref
instances (oracle.jdbc2.Ref
under JDK 1.1.x). You can also use the getObject()
method. These methods take as input a String
column name or int
column index.
The OraclePreparedStatement
and OracleCallableStatement
classes support setREF()
and setRef()
methods to take REF
objects as bind variables and pass them to the database. You can also use the setObject()
method. These methods take as input a String
parameter name or int
parameter index as well as, respectively, an oracle.sql.REF
instance or a java.sql.Ref
instance (oracle.jdbc2.Ref
under JDK 1.1.x).
Use the following oracle.sql.REF
class methods to retrieve the SQL object type name and retrieve and pass the underlying object data.
getBaseTypeName()
: Retrieves the fully-qualified SQL structured type name of the referenced object (for example, EMPLOYEE
).
This is a standard method specified by the java.sql.Ref
interface.
getValue()
: Retrieves the referenced object from the database, allowing you to access its attribute values. It optionally takes a type map object, or else you can use the default type map of the database connection object.
This method is an Oracle extension.
setValue()
: Sets the referenced object in the database, allowing you to update its attribute values. It takes an instance of the object type as input (either a STRUCT
instance or an instance of a custom object class).
This method is an Oracle extension.
This section discusses JDBC functionality for retrieving and passing object references.
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:
SELECT
statement to retrieve the reference from a database table REF
column.
getREF()
to get the address reference from the result set into a REF
object.
Address
be the Java custom class corresponding to the SQL object type ADDRESS
.
Address
and the SQL type ADDRESS
to your type map.
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.
To retrieve an object reference as an OUT
parameter in PL/SQL blocks, you must register the bind type for your OUT
parameter.
OracleCallableStatement
:
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? = call func()}");
OUT
parameter with this form of the registerOutParameter()
method:
ocs.registerOutParameter (intparam_index
, intsql_type
, Stringsql_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.
ocs.execute();
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);
You can use the REF
object setValue()
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 (if one does not already exist) that corresponds to the database object.
For example, you can use the code in the section "Retrieving and Passing an Object Reference" to retrieve the reference to a database ADDRESS
object:
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 Java Address
object (this example omits the content for the constructor of the Address
class) that corresponds to the database ADDRESS
object. Use the setValue()
method of the REF
class to set the value of the database object:
Address addr = new Address(...); ref.setValue(addr);
Here, the setValue()
method updates the database ADDRESS
object immediately.
This chapter primarily describes the functionality of the oracle.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:
oracle.sql.ORAData
interface described under "Creating and Using Custom Object Classes for Oracle Objects". Note that the standard JDBC SQLData
interface, which is an alternative for custom object classes, is not intended for custom reference classes.
oracle.sql.ORADataFactory
interface, for creating instances of the custom reference class.
oracle.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 a ORAData
implementation, then JPublisher will also generate a custom reference class that implements ORAData
and ORADataFactory
and includes an oracle.sql.REF
attribute. (The ORAData
implementation will be used if JPublisher's -usertypes
mapping option is set to oracle
, which is the default.)
Custom reference classes are strongly typed. For example, if you define an Oracle object EMPLOYEE
, then JPublisher can generate an Employee
custom object class and an EmployeeRef
custom reference class. Using EmployeeRef
instances instead of generic oracle.sql.REF
instances makes it easier to catch errors during compilation instead of at runtime--for example, if you accidentally assign some other kind of object reference into an EmployeeRef
variable.
Be aware that the standard SQLData
interface supports only SQL object mappings. For this reason, if you instruct JPublisher to implement the standard SQLData
interface in creating a custom object class, then JPublisher will not generate a custom reference class. In this case your only option is to use standard java.sql.Ref
instances (or oracle.sql.REF
instances) to map to your object references. (Specifying the SQLData
implementation is accomplished by setting JPublisher's UDT attributes mapping option to jdbc
.)
For more information about JPublisher, see "Using JPublisher to Create Custom Object Classes", or refer to the Oracle9i JPublisher User's Guide.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|