Oracle9i JDBC Developer's Guide and Reference
Release 1 (9.0.1)
Part Number A90211-01
Go To Documentation Library
Home		 Go To Product List
Solution Area		 Go To Table Of Contents
Contents		 Go To Index
Index

Go to previous page Go to next page

8
Working with Oracle Object Types

This chapter describes JDBC support for user-defined object types. It discusses functionality of the generic, weakly typed oracle.sql.STRUCT class, as well as how to map to custom Java classes that implement either the JDBC standard SQLData interface or the Oracle ORAData interface. This chapter also describes how JDBC drivers access SQLJ object types in SQL representation.

The following topics are covered:

Mapping Oracle Objects

Oracle object types provide support for composite data structures in the database. For example, you can define a type Person that has attributes such as name (type CHAR), phone number (type CHAR), and employee number (type NUMBER).

Oracle provides tight integration between its Oracle object features and its JDBC functionality. You can use a standard, generic JDBC type to map to Oracle objects, or you can customize the mapping by creating custom Java type definition classes. In this book, Java classes that you create to map to Oracle objects will be referred to as custom Java classes or, more specifically, custom object classes. This is as opposed to custom references classes to map to object references, and custom collection classes to map to Oracle collections. Custom object classes can implement either a standard JDBC interface or an Oracle extension interface to read and write data.

JDBC materializes Oracle objects as instances of particular Java classes. Two main steps in using JDBC to access Oracle objects are: 1) creating the Java classes for the Oracle objects, and 2) populating these classes. You have two options:

or:

Using the Default STRUCT Class for Oracle Objects

If you choose not to supply a custom Java class for your SQL-Java mapping for an Oracle object, then Oracle JDBC will materialize the object as an instance of the oracle.sql.STRUCT class.

You would typically want to use STRUCT objects, instead of custom Java objects, in situations where you are manipulating SQL data. For example, your Java application might be a tool to manipulate arbitrary object data within the database, as opposed to being an end-user application. You can select data from the database into STRUCT objects and create STRUCT objects for inserting data into the database. STRUCT objects completely preserve data, because they maintain the data in SQL format. Using STRUCT objects is more efficient and more precise in these situations where you don't need the information in a convenient form.

For a complete sample application using the STRUCT class to access and manipulate SQL object data, see "Weakly Typed Objects--PersonObject.java".

STRUCT Class Functionality

This section discusses standard versus Oracle-specific features of the oracle.sql.STRUCT class, introduces STRUCT descriptors, and lists methods of the STRUCT class to give an overview of its functionality.

Standard java.sql.Struct Methods

If your code must comply with standard JDBC 2.0, then use a java.sql.Struct instance (oracle.jdbc2.Struct under JDK 1.1.x), and use the following standard methods:

Oracle oracle.sql.STRUCT Class Methods

If you want to take advantage of the extended functionality offered by Oracle-defined methods, then use an oracle.sql.STRUCT instance.

The oracle.sql.STRUCT class implements the java.sql.Struct interface (oracle.jdbc2.Struct interface under JDK 1.1.x) and provides extended functionality beyond the JDBC 2.0 standard.

The STRUCT class includes the following methods in addition to standard Struct functionality:

STRUCT Descriptors

Creating and using a STRUCT object requires a descriptor--an instance of the oracle.sql.StructDescriptor class--to exist for the SQL type (such as EMPLOYEE) that will correspond to the STRUCT object. You need only one StructDescriptor object for any number of STRUCT objects that correspond to the same SQL type.

STRUCT descriptors are further discussed in "Creating STRUCT Objects and Descriptors".

Creating STRUCT Objects and Descriptors

This section describes how to create STRUCT objects and descriptors and lists useful methods of the StructDescriptor class.

Steps in Creating StructDescriptor and STRUCT Objects

This section describes how to construct an oracle.sql.STRUCT object for a given Oracle object type. To create a STRUCT object, you must:

  1. Create a StructDescriptor object (if one does not already exist) for the given Oracle object type.

  2. Use the StructDescriptor to construct the STRUCT object.

A StructDescriptor is an instance of the oracle.sql.StructDescriptor class and describes a type of Oracle object (SQL structured object). Only one StructDescriptor is necessary for each Oracle object type. The driver caches StructDescriptor objects to avoid recreating them if the type has already been encountered.

Before you can construct a STRUCT object, a StructDescriptor must first exist for the given Oracle object type. If a StructDescriptor object does not exist, you can create one by calling the static StructDescriptor.createDescriptor() method. This method requires you to pass in the SQL type name of the Oracle object type and a connection object: 

StructDescriptor structdesc = StructDescriptor.createDescriptor
   (sql_type_name, connection);

Where sql_type_name is a Java string containing the name of the Oracle object type (such as EMPLOYEE) and connection is your connection object.

Once you have your StructDescriptor object for the Oracle object type, you can construct the STRUCT object. To do this, pass in the StructDescriptor, your connection object, and an array of Java objects containing the attributes you want the STRUCT to contain. 

STRUCT struct = new STRUCT(structdesc, connection, attributes);

Where structdesc is the StructDescriptor created previously, connection is your connection object, and attributes is an array of type java.lang.Object[].

Using StructDescriptor Methods

A StructDescriptor can be thought of as a "type object". This means that it contains information about the object type, including the typecode, the type name, and how to convert to and from the given type. Remember, there should be only one StructDescriptor object for any one Oracle object type. You can then use that descriptor to create as many STRUCT objects as you need for that type.

The StructDescriptor class includes the following methods:

Serializable STRUCT Descriptors

As "Steps in Creating StructDescriptor and STRUCT Objects" explains, when you create a STRUCT object, you first must create a StructDescriptor object. Do this by calling the StructDescriptor.createDescriptor() method. The oracle.sql.StructDescriptor class is serializable, meaning that you can write the complete state of a StructDescriptor object to an output stream for later use. Recreate the StructDescriptor object by reading its serialized state from an input stream. This is referred to as deserializing. With the StructDescriptor object serialized, you do not need to call the StructDescriptor.createDescriptor() method--you simply deserialize the StructDescriptor object.

It is advisable to serialize a StructDescriptor object when the object type is complex but not changed often.

If you create a StructDescriptor object through deserialization, you must supply the appropriate database connection instance for the StructDescriptor object, using the setConnection() method.

The following code provides the connection instance for a StructDescriptor object: 

public void setConnection (Connection conn) throws SQLException

Note:

The JDBC driver does not verify that the connection object from the setConnection() method connects to the same database from which the type descriptor was initially derived. 

Retrieving STRUCT Objects and Attributes

This section discusses how to retrieve and manipulate Oracle objects and their attributes, using either Oracle-specific features or JDBC 2.0 standard features.

Note:

The JDBC driver seamlessly handles embedded objects (STRUCT objects that are attributes of STRUCT objects) in the same way that it normally handles objects. When the JDBC driver retrieves an attribute that is an object, it follows the same rules of conversion, using the type map if it is available, or using default mapping if it is not. 

Retrieving an Oracle Object as an oracle.sql.STRUCT Object

You can retrieve an Oracle object directly into an oracle.sql.STRUCT instance. In the following example, getObject() is used to get a NUMBER object from column 1 (col1) of the table struct_table. Because getObject() returns an Object type, the return is cast to an oracle.sql.STRUCT. This example assumes that the Statement object stmt has already been created. 

String cmd;
cmd = "CREATE TYPE type_struct AS object (field1 NUMBER,field2 DATE)";
stmt.execute(cmd);

cmd = "CREATE TABLE struct_table (col1 type_struct)";
stmt.execute(cmd);

cmd = "INSERT INTO struct_table VALUES (type_struct(10,'01-apr-01'))";
stmt.execute(cmd);

cmd = "INSERT INTO struct_table VALUES (type_struct(20,'02-may-02'))";
stmt.execute(cmd);

ResultSet rs= stmt.executeQuery("SELECT * FROM struct_table");
oracle.sql.STRUCT oracleSTRUCT=(oracle.sql.STRUCT)rs.getObject(1);

Another way to return the object as a STRUCT object is to cast the result set to an OracleResultSet object and use the Oracle extension getSTRUCT() method: 

oracle.sql.STRUCT oracleSTRUCT=((OracleResultSet)rs).getSTRUCT(1);

Retrieving an Oracle Object as a java.sql.Struct Object

Alternatively, referring back to the previous example, you can use standard JDBC functionality such as getObject() to retrieve an Oracle object from the database as an instance of java.sql.Struct (oracle.jdbc2.Struct under JDK 1.1.x). Because getObject() returns a java.lang.Object, you must cast the output of the method to a Struct. For example: 

ResultSet rs= stmt.executeQuery("SELECT * FROM struct_table");
java.sql.Struct jdbcStruct = (java.sql.Struct)rs.getObject(1);

Retrieving Attributes as oracle.sql Types

If you want to retrieve Oracle object attributes from a STRUCT or Struct instance as oracle.sql types, use the getOracleAttributes() method of the oracle.sql.STRUCT class (for a Struct instance, you will have to cast to a STRUCT instance):

Referring back to the previous examples: 

oracle.sql.Datum[] attrs = oracleSTRUCT.getOracleAttributes();

or: 

oracle.sql.Datum[] attrs =
        ((oracle.sql.STRUCT)jdbcStruct).getOracleAttributes();

Retrieving Attributes as Standard Java Types

If you want to retrieve Oracle object attributes as standard Java types from a STRUCT or Struct instance, use the standard getAttributes() method: 

Object[] attrs = jdbcStruct.getAttributes();

Binding STRUCT Objects into Statements

To bind an oracle.sql.STRUCT object to a prepared statement or callable statement, you can either use the standard setObject() method (specifying the typecode), or cast the statement object to an Oracle statement object and use the Oracle extension setOracleObject() method. For example: 

PreparedStatement ps= conn.prepareStatement("text_of_prepared_statement");
STRUCT mySTRUCT = new STRUCT (...);
ps.setObject(1, mySTRUCT, Types.STRUCT); //OracleTypes.STRUCT under JDK 1.1.x

or: 

PreparedStatement ps= conn.prepareStatement("text_of_prepared_statement");
STRUCT mySTRUCT = new STRUCT (...);
((OraclePreparedStatement)ps).setOracleObject(1, mySTRUCT);

STRUCT Automatic Attribute Buffering

The Oracle JDBC driver furnishes public methods to enable and disable buffering of STRUCT attributes. (See "ARRAY Automatic Element Buffering" for a discussion of how to buffer ARRAY elements.)

The following methods are included with the oracle.sql.STRUCT class:

The setAutoBuffering(boolean) method enables or disables auto-buffering. The getAutoBuffering() method returns the current auto-buffering mode. By default, auto-buffering is disabled.

It is advisable to enable auto-buffering in a JDBC application when the STRUCT attributes will be accessed more than once by the getAttributes() and getArray() methods (presuming the ARRAY data is able to fit into the JVM memory without overflow).

Important:

Buffering the converted attributes may cause the JDBC application to consume a significant amount of memory. 

When you enable auto-buffering, the oracle.sql.STRUCT object keeps a local copy of all the converted attributes. This data is retained so that a second access of this information does not require going through the data format conversion process.

Creating and Using Custom Object Classes for Oracle Objects

If you want to create custom object classes for your Oracle objects, then you must define entries in the type map that specify the custom object classes that the drivers will instantiate for the corresponding Oracle objects.

You must also provide a way to create and populate instances of the custom object class from the Oracle object and its attribute data. The driver must be able to read from a custom object class and write to it. In addition, the custom object class can provide getXXX() and setXXX() methods corresponding to the Oracle object's attributes, although this is not necessary. To create and populate the custom classes and provide these read/write capabilities, you can choose between these two interfaces:

The custom object class you create must implement one of these interfaces. The ORAData interface can also be used to implement the custom reference class corresponding to the custom object class. If you are using the SQLData interface, however, you can only use weak reference types in Java (java.sql.Ref or oracle.sql.REF). The SQLData interface is for mapping SQL objects only.

As an example, assume you have an Oracle object type, EMPLOYEE, in the database that consists of two attributes: Name (which is type CHAR) and EmpNum (employee number, which is type NUMBER). You use the type map to specify that the EMPLOYEE object should map to a custom object class that you call JEmployee. You can implement either the SQLData or ORAData interface in the JEmployee class.

You can create custom object classes yourself, but the most convenient way to create them is to employ the Oracle JPublisher utility to create them for you. JPublisher supports the standard SQLData interface as well as the Oracle-specific ORAData interface, and is able to generate classes that implement either one. See "Using JPublisher to Create Custom Object Classes" for more information.

Note:

If you need to create a custom object class in order to have object-type inheritance, then see "Object-Type Inheritance". 

The following section compares ORAData and SQLData functionality.

Relative Advantages of ORAData versus SQLData

In deciding which of these two interface implementations to use, consider the following:

Advantages of ORAData:

Advantages of SQLData:

The SQLData interface is for mapping SQL objects only. The ORAData interface is more flexible, enabling you to map SQL objects as well as any other SQL type for which you want to customize processing. You can create a ORAData object from any datatype found in an Oracle database. This could be useful, for example, for serializing RAW data in Java.

Understanding Type Maps for SQLData Implementations

If you use the SQLData interface in a custom object class, then you must create type map entries that specify the custom object class to use in mapping the Oracle object type (SQL object type) to Java. You can either use the default type map of the connection object, or a type map that you specify when you retrieve the data from the result set. The ResultSet interface getObject() method has a signature that lets you specify a type map: 

rs.getObject(int columnIndex);

or: 

rs.getObject(int columnIndex, Map map);

For a description of how to create these custom object classes with SQLData, see "Creating and Using Custom Object Classes for Oracle Objects".

When using a SQLData implementation, if you do not include a type map entry, then the object will map to the oracle.sql.STRUCT class by default. (ORAData implementations, by contrast, have their own mapping functionality so that a type map entry is not required. When using a ORAData implementation, use the Oracle getORAData() method instead of the standard getObject() method.)

The type map relates a Java class to the SQL type name of an Oracle object. This one-to-one mapping is stored in a hash table as a keyword-value pair. When you read data from an Oracle object, the JDBC driver considers the type map to determine which Java class to use to materialize the data from the Oracle object type (SQL object type). When you write data to an Oracle object, the JDBC driver gets the SQL type name from the Java class by calling the getSQLTypeName() method of the SQLData interface. The actual conversion between SQL and Java is performed by the driver.

The attributes of the Java class that corresponds to an Oracle object can use either Java native types or Oracle native types (instances of the oracle.sql.* classes) to store attributes.

Creating a Type Map Object and Defining Mappings for a SQLData Implementation

When using a SQLData implementation, the JDBC applications programmer is responsible for providing a type map, which must be an instance of a class as follows:

or:

You have the option of creating your own class to accomplish this, but under either JDK 1.2.x or JDK 1.1.x, the standard class java.util.Hashtable meets the requirement.

Note:

If you are migrating from JDK 1.1.x to JDK 1.2.x, you must ensure that your code uses a class that implements the Map interface. If you were using the java.util.Hashtable class under 1.1.x, then no change is necessary. 

Hashtable and other classes used for type maps implement a put() method that takes keyword-value pairs as input, where each key is a fully qualified SQL type name and the corresponding value is an instance of a specified Java class.

A type map is associated with a connection instance. The standard java.sql.Connection interface and the Oracle-specific oracle.jdbc.OracleConnection interface include a getTypeMap() method. Under JDK 1.2.x, both return a Map object; under JDK 1.1.x, both return a Dictionary object.

The remainder of this section covers the following topics:

Adding Entries to an Existing Type Map

When a connection instance is first established, the default type map is empty. You must populate it to use any SQL-Java mapping functionality.

Follow these general steps to add entries to an existing type map.

  1. Use the getTypeMap() method of your OracleConnection object to return the connection's type map object. The getTypeMap() method returns a java.util.Map object (or java.util.Dictionary under JDK 1.1.x). For example, presuming an OracleConnection instance oraconn: 

    java.util.Map myMap = oraconn.getTypeMap();

    Note:

    If the type map in the OracleConnection instance has not been initialized, then the first call to getTypeMap() returns an empty map.  

  2. Use the type map's put() method to add map entries. The put() method takes two arguments: a SQL type name string and an instance of a specified Java class that you want to map to. 

    myMap.put(sqlTypeName, classObject);

    The sqlTypeName is a string that represents the fully qualified name of the SQL type in the database. The classObject is the Java class object to which you want to map the SQL type. Get the class object with the Class.forName() method, as follows: 

    myMap.put(sqlTypeName, Class.forName(className));

    For example, if you have a PERSON SQL datatype defined in the CORPORATE database schema, then map it to a Person Java class defined as Person with this statement: 

    myMap.put("CORPORATE.PERSON", Class.forName("Person"));

    The map has an entry that maps the PERSON SQL datatype in the CORPORATE database to the Person Java class.

    Note:

    SQL type names in the type map must be all uppercase, because that is how the Oracle database stores SQL names. 

Creating a New Type Map

Follow these general steps to create a new type map. This example uses an instance of java.util.Hashtable, which extends java.util.Dictionary and, under JDK 1.2.x, also implements java.util.Map.

  1. Create a new type map object. 

    Hashtable newMap = new Hashtable();
  2. Use the put() method of the type map object to add entries to the map. For more information on the put() method, see Step 2 under "Adding Entries to an Existing Type Map". For example, if you have an EMPLOYEE SQL type defined in the CORPORATE database, then you can map it to an Employee class object defined by Employee.java, with this statement: 

    newMap.put("CORPORATE.EMPLOYEE", class.forName("Employee"));
  3. When you finish adding entries to the map, use the OracleConnection object's setTypeMap() method to overwrite the connection's existing type map. For example: 

    oraconn.setTypeMap(newMap);

    In this example, setTypeMap() overwrites the oraconn connection's original map with newMap.

    Note:

    The default type map of a connection instance is used when mapping is required but no map name is specified, such as for a result set getObject() call that does not specify the map as input. 

Materializing Object Types not Specified in the Type File

If you do not provide a type map with an appropriate entry when using a getObject() call, then the JDBC driver will materialize an Oracle object as an instance of the oracle.sql.STRUCT class. If the Oracle object type contains embedded objects, and they are not present in the type map, the driver will materialize the embedded objects as instances of oracle.sql.STRUCT as well. If the embedded objects are present in the type map, a call to the getAttributes() method will return embedded objects as instances of the specified Java classes from the type map.

Understanding the SQLData Interface

One of the choices in making an Oracle object and its attribute data available to Java applications is to create a custom object class that implements the SQLData interface. Note that if you use this interface, you must supply a type map that specifies the Oracle object types in the database and the names of the corresponding custom object classes that you will create for them.

The SQLData interface defines methods that translate between SQL and Java for Oracle database objects. Standard JDBC provides a SQLData interface and companion SQLInput and SQLOutput interfaces in the java.sql package (oracle.jdbc2 package under JDK 1.1.x).

If you create a custom object class that implements SQLData, then you must provide a readSQL() method and a writeSQL() method, as specified by the SQLData interface.

The JDBC driver calls your readSQL() method to read a stream of data values from the database and populate an instance of your custom object class. Typically, the driver would use this method as part of an OracleResultSet object getObject() call.

Similarly, the JDBC driver calls your writeSQL() method to write a sequence of data values from an instance of your custom object class to a stream that can be written to the database. Typically, the driver would use this method as part of an OraclePreparedStatement object setObject() call.

Understanding the SQLInput and SQLOutput Interfaces

The JDBC driver includes classes that implement the SQLInput and SQLOutput interfaces. It is not necessary to implement the SQLOutput or SQLInput objects--the JDBC drivers will do this for you.

The SQLInput implementation is an input stream class, an instance of which must be passed in to the readSQL() method. SQLInput includes a readXXX() method for every possible Java type that attributes of an Oracle object might be converted to, such as readObject(), readInt(), readLong(), readFloat(), readBlob(), and so on. Each readXXX() method converts SQL data to Java data and returns it into an output parameter of the corresponding Java type. For example, readInt() returns an integer.

The SQLOutput implementation is an output stream class, an instance of which must be passed in to the writeSQL() method. SQLOutput includes a writeXXX() method for each of these Java types. Each writeXXX() method converts Java data to SQL data, taking as input a parameter of the relevant Java type. For example, writeString() would take as input a string attribute from your Java class.

Implementing readSQL() and writeSQL() Methods

When you create a custom object class that implements SQLData, you must implement the readSQL() and writeSQL() methods, as described here.

You must implement readSQL() as follows: 

public void readSQL(SQLInput stream, String sql_type_name) throws SQLException

You must implement writeSQL() as follows: 

public void writeSQL(SQLOutput stream) throws SQLException

"SQLData Implementation--SQLDataExample.java" contains a sample implementation of the SQLData interface for a given SQL object definition.

Reading and Writing Data with a SQLData Implementation

This section describes how to read data from an Oracle object or write data to an Oracle object if your corresponding Java class implements SQLData.

Reading SQLData Objects from a Result Set

This section summarizes the steps to read data from an Oracle object into your Java application when you choose the SQLData implementation for your custom object class.

These steps assume you have already defined the Oracle object type, created the corresponding custom object class, updated the type map to define the mapping between the Oracle object and the Java class, and defined a statement object stmt.

  1. Query the database to read the Oracle object into a JDBC result set. 

    ResultSet rs = stmt.executeQuery("SELECT emp_col FROM personnel");

    The PERSONNEL table contains one column, EMP_COL, of SQL type EMP_OBJECT. This SQL type is defined in the type map to map to the Java class Employee.

  2. Use the getObject() method of your result set to populate an instance of your custom object class with data from one row of the result set. The getObject() method returns the user-defined SQLData object because the type map contains an entry for Employee. 

    if (rs.next())
   Employee emp = (Employee)rs.getObject(1);

    Note that if the type map did not have an entry for the object, then getObject() would return an oracle.sql.STRUCT object. Cast the output to type STRUCT, because the getObject() method signature returns the generic java.lang.Object type. 

    if (rs.next())
   STRUCT empstruct = (STRUCT)rs.getObject(1);

    The getObject() call triggers readSQL() and readXXX() calls from the SQLData interface, as described above.

    Note:

    If you want to avoid using a type map, then use the getSTRUCT() method. This method always returns a STRUCT object, even if there is a mapping entry in the type map. 

  3. If you have get methods in your custom object class, then use them to read data from your object attributes. For example, if EMPLOYEE has an EmpName (employee name) of type CHAR, and an EmpNum (employee number) of type NUMBER, then provide a getEmpName() method that returns a Java String and a getEmpNum() method that returns an integer (int). Then invoke them in your Java application, as follows: 

    String empname = emp.getEmpName();
int empnumber = emp.getEmpNum();

    Note:

    Alternatively, fetch data by using a callable statement object, which also has a getObject() method.  

Retrieving SQLData Objects from a Callable Statement OUT Parameter

Suppose you have an OracleCallableStatement ocs that calls a PL/SQL function GETEMPLOYEE(). The program passes an employee number (empnumber) to the function; the function returns the corresponding Employee object.

  1. Prepare an OracleCallableStatement to call the GETEMPLOYEE() function. 

    OracleCallableStatement ocs = 
  (OracleCallableStatement)conn.prepareCall("{ ? = call GETEMPLOYEE(?) }");
  2. Declare the empnumber as the input parameter to GETEMPLOYEE(). Register the SQLData object as the OUT parameter, with typecode OracleTypes.STRUCT. Then, execute the statement. 

    ocs.setInt(2, empnumber); 
ocs.registerOutParameter(1, OracleTypes.STRUCT, "EMP_OBJECT"); 
ocs.execute();
  3. Use the getObject() method to retrieve the employee object. The following code assumes that there is a type map entry to map the Oracle object to Java type Employee: 

    Employee emp = (Employee)ocs.getObject(1);

    If there is no type map entry, then getObject() would return an oracle.sql.STRUCT object. Cast the output to type STRUCT, because the getObject() method signature returns the generic java.lang.Object type: 

    STRUCT emp = (STRUCT)ocs.getObject(1);

Passing SQLData Objects to a Callable Statement as an IN Parameter

Suppose you have a PL/SQL function addEmployee(?) that takes an Employee object as an IN parameter and adds it to the PERSONNEL table. In this example, emp is a valid Employee object.

  1. Prepare an OracleCallableStatement to call the addEmployee(?) function. 

    OracleCallableStatement ocs = 
  (OracleCallableStatement) conn.prepareCall("{ call addEmployee(?) }");
  2. Use setObject() to pass the emp object as an IN parameter to the callable statement. Then, execute the statement. 

    ocs.setObject(1, emp); 
ocs.execute();

Writing Data to an Oracle Object Using a SQLData Implementation

This section describes the steps in writing data to an Oracle object from your Java application when you choose the SQLData implementation for your custom object class.

This description assumes you have already defined the Oracle object type, created the corresponding Java class, and updated the type map to define the mapping between the Oracle object and the Java class.

  1. If you have set methods in your custom object class, then use them to write data from Java variables in your application to attributes of your Java datatype object. 

    emp.setEmpName(empname);
emp.setEmpNum(empnumber);

    This statement uses the emp object and the empname and empnumber variables assigned in "Reading SQLData Objects from a Result Set".

  2. Prepare a statement that updates an Oracle object in a row of a database table, as appropriate, using the data provided in your Java datatype object. 

    PreparedStatement pstmt = conn.prepareStatement
                          ("INSERT INTO PERSONNEL VALUES (?)");

    This assumes conn is your connection object.

  3. Use the setObject() method of the prepared statement to bind your Java datatype object to the prepared statement. 

    pstmt.setObject(1, emp);
  4. Execute the statement, which updates the database. 

    pstmt.executeUpdate();

Understanding the ORAData Interface

One of the choices in making an Oracle object and its attribute data available to Java applications is to create a custom object class that implements the oracle.sql.ORAData and oracle.sql.ORADataFactory interfaces (or you can implement ORADataFactory in a separate class). The ORAData and ORADataFactory interfaces are supplied by Oracle and are not a part of the JDBC standard.

Note:

The JPublisher utility supports the generation of classes that implement the ORAData and ORADataFactory interfaces. See "Using JPublisher to Create Custom Object Classes". 

Understanding ORAData Features

The ORAData interface has these advantages:

The ORAData and ORADataFactory interfaces do the following:

ORAData and ORADataFactory have the following definitions: 

public interface ORAData 
{ 
    Datum toDatum (java.sql.Connection conn) throws SQLException;
} 
 
public interface ORADataFactory 
{ 
    ORAData create (Datum d, int sql_Type_Code) throws SQLException; 
}

Where conn represents the Connection object, d represents an object of type oracle.sql.Datum, and sql_Type_Code represents the SQL typecode (from the standard Types or OracleTypes class) of the Datum object.

Retrieving and Inserting Object Data

The JDBC drivers provide the following methods to retrieve and insert object data as instances of ORAData.

To retrieve object data:

or:

To insert object data:

or:

The following sections describe the getORAData() and setORAData() methods.

To continue the example of an Oracle object EMPLOYEE, you might have something like the following in your Java application: 

ORAData datum = ors.getORAData(1, Employee.getORAFactory());

In this example, ors is an Oracle result set, getORAData() is a method in the OracleResultSet class used to retrieve a ORAData object, and the EMPLOYEE is in column 1 of the result set. The static Employee.getORAFactory() method will return a ORADataFactory to the JDBC driver. The JDBC driver will call create() from this object, returning to your Java application an instance of the Employee class populated with data from the result set.

Notes:

  • ORAData and ORADataFactory are defined as separate interfaces so that different Java classes can implement them if you wish (such as an Employee class and an EmployeeFactory class).

  • To use the ORAData interface, your custom object classes must import oracle.sql.* (or at least ORAData, ORADataFactory, and Datum).

 

"ORAData Implementation--ORADataExample.java" contains an example implementation of the ORAData interface for a given SQL object definition.

Reading and Writing Data with a ORAData Implementation

This section describes how to read data from an Oracle object or write data to an Oracle object if your corresponding Java class implements ORAData.

Reading Data from an Oracle Object Using a ORAData Implementation

This section summarizes the steps in reading data from an Oracle object into your Java application. These steps apply whether you implement ORAData manually or use JPublisher to produce your custom object classes.

These steps assume you have already defined the Oracle object type, created the corresponding custom object class or had JPublisher create it for you, and defined a statement object stmt.

  1. Query the database to read the Oracle object into a result set, casting to an Oracle result set. 

    OracleResultSet ors = (OracleResultSet)stmt.executeQuery
                      ("SELECT Emp_col FROM PERSONNEL");

    Where PERSONNEL is a one-column table. The column name is Emp_col of type Employee_object.

  2. Use the getORAData() method of your Oracle result set to populate an instance of your custom object class with data from one row of the result set. The getORAData() method returns an oracle.sql.ORAData object, which you can cast to your specific custom object class. 

    if (ors.next())
   Employee emp = (Employee)ors.getORAData(1, Employee.getORAFactory());

    or: 

    if (ors.next())
   ORAData datum = ors.getORAData(1, Employee.getORAFactory());

    This example assumes that Employee is the name of your custom object class and ors is the name of your OracleResultSet object.

    In case you do not want to use getORAData(), the JDBC drivers let you use the getObject() method of a standard JDBC ResultSet to retrieve ORAData data. However, you must have an entry in the type map that identifies the factory class to be used for the given object type, and its corresponding SQL type name.

    For example, if the SQL type name for your object is EMPLOYEE, then the corresponding Java class is Employee, which will implement ORAData. The corresponding Factory class is EmployeeFactory, which will implement ORADataFactory.

    Use this statement to declare the EmployeeFactory entry for your type map: 

    map.put ("EMPLOYEE", Class.forName ("EmployeeFactory"));

    Then use the form of getObject() where you specify the map object: 

    Employee emp = (Employee) rs.getObject (1, map);

    If the connection's default type map already has an entry that identifies the factory class to be used for the given object type, and its corresponding SQL type name, then you can use this form of getObject(): 

    Employee emp = (Employee) rs.getObject (1);
  3. If you have get methods in your custom object class, use them to read data from your object attributes into Java variables in your application. For example, if EMPLOYEE has EmpName of type CHAR and EmpNum (employee number) of type NUMBER, provide a getEmpName() method that returns a Java string and a getEmpNum() method that returns an integer. Then invoke them in your Java application as follows: 

    String empname = emp.getEmpName();
int empnumber = emp.getEmpNum();

    Note:

    Alternatively, you can fetch data into a callable statement object. The OracleCallableStatement class also has a getORAData() method. 

Writing Data to an Oracle Object Using a ORAData Implementation

This section summarizes the steps in writing data to an Oracle object from your Java application. These steps apply whether you implement ORAData manually or use JPublisher to produce your custom object classes.

These steps assume you have already defined the Oracle object type and created the corresponding custom object class (or had JPublisher create it for you).

Note:

The type map is not used when you are performing database INSERT and UPDATE operations. 

  1. If you have set methods in your custom object class, then use them to write data from Java variables in your application to attributes of your Java datatype object. 

    emp.setEmpName(empname);
emp.setEmpNum(empnumber);

    This statement uses the emp object and the empname and empnumber variables defined in "Reading Data from an Oracle Object Using a ORAData Implementation".

  2. Write an Oracle prepared statement that updates an Oracle object in a row of a database table, as appropriate, using the data provided in your Java datatype object. 

    OraclePreparedStatement opstmt = conn.prepareStatement
   ("UPDATE PERSONNEL SET Employee = ? WHERE Employee.EmpNum = 28959);

    This assumes conn is your Connection object.

  3. Use the setORAData() method of the Oracle prepared statement to bind your Java datatype object to the prepared statement. 

    opstmt.setORAData(1, emp);

    The setORAData() method calls the toDatum() method of the custom object class instance to retrieve an oracle.sql.STRUCT object that can be written to the database.

    In this step you could also use the setObject() method to bind the Java datatype. For example: 

    opstmt.setObject(1,emp);

    Note:

    You can use your Java datatype objects as either IN or OUT bind variables. 

Additional Uses for ORAData

The ORAData interface offers far more flexibility than the SQLData interface. The SQLData interface is designed to let you customize the mapping of only Oracle object types (SQL object types) to Java types of your choice. Implementing the SQLData interface lets the JDBC driver populate fields of a custom Java class instance from the original SQL object data, and the reverse, after performing the appropriate conversions between Java and SQL types.

The ORAData interface goes beyond supporting the customization of Oracle object types to Java types. It lets you provide a mapping between Java object types and any SQL type supported by the oracle.sql package.

It might be useful to provide custom Java classes to wrap oracle.sql.* types and perhaps implement customized conversions or functionality as well. The following are some possible scenarios:

For example, use ORAData to store instances of Java objects that do not correspond to a particular SQL Oracle object type in the database in columns of SQL type RAW. The create() method in ORADataFactory would have to implement a conversion from an object of type oracle.sql.RAW to the desired Java object. The toDatum() method in ORAData would have to implement a conversion from the Java object to an oracle.sql.RAW object. This can be done, for example, by using Java serialization.

Upon retrieval, the JDBC driver transparently retrieves the raw bytes of data in the form of an oracle.sql.RAW and calls the ORADataFactory's create() method to convert the oracle.sql.RAW object to the desired Java class.

When you insert the Java object into the database, you can simply bind it to a column of type RAW to store it. The driver transparently calls the ORAData.toDatum() method to convert the Java object to an oracle.sql.RAW object. This object is then stored in a column of type RAW in the database.

Support for the ORAData interfaces is also highly efficient because the conversions are designed to work using oracle.sql.* formats, which happen to be the internal formats used by the JDBC drivers. Moreover, the type map, which is necessary for the SQLData interface, is not required when using Java classes that implement ORAData. For more information on why classes that implement ORAData do not need a type map, see "Understanding the ORAData Interface".

The Deprecated CustomDatum Interface

As a result of the oracle.jdbc interfaces being introduced in Oracle9i as an alternative to the oracle.jdbc.driver classes, the oracle.sql.CustomDatum and oracle.sql.CustomDatumFactory interfaces, formerly used to access customized objects, have been deprecated by the new interfaces--oracle.sql.ORAData and oracle.sql.ORADataFactory.

The following are the specifications for the CustomDatum and CustomDatumFactory interfaces: 

public interface CustomDatum
{
  oracle.sql.Datum toDatum(
    oracle.jdbc.driver.OracleConnection c
  ) throws SQLException ;

// The following is expected to be present in an
// implementation:
//
// - Definition of public static fields for
//   _SQL_TYPECODE, _SQL_NAME and  _SQL_BASETYPE.
//   (See Oracle Jdbc documentation for details.)
//
// - Definition of
//   public static CustomDatumFactory
//     getFactory();
//
}

public interface CustomDatumFactory
{
  oracle.sql.CustomDatum create(
    oracle.sql.Datum d, int sqlType
    ) throws SQLException;
}

Object-Type Inheritance

Object-type inheritance is an Oracle9i feature which allows a new object type to be created by extending another object type. (While Oracle9i does not yet support JDBC 3.0, object-type inheritance is supported and documented.) The new object type is then a subtype of the object type from which it extends. The subtype automatically inherits all the attributes and methods defined in the supertype. The subtype can add attributes and methods, and overload or override methods inherited from the supertype.

Object-type inheritance introduces substitutability. Substitutability is the ability of a slot declared to hold a value of type T to do so in addition to any subtype of type T. Oracle9i JDBC drivers handle substitutability transparently.

A database object is returned with its most specific type without losing information. For example, if the STUDENT_T object is stored in a PERSON_T slot, the Oracle JDBC driver returns a Java object that represents the STUDENT_T object.

Creating Subtypes

Create custom object classes if you want to have Java classes that explicitly correspond to the Oracle object types. (See "Creating and Using Custom Object Classes for Oracle Objects".) If you have a hierarchy of object types, you may want a corresponding hierarchy of Java classes.

The most common way to create a database subtype in JDBC is to pass the extended SQL CREATE TYPE command to the execute() method of the java.sql.Statement interface. For example, to create a type inheritance hierarchy for: 

PERSON_T 
| 
STUDENT_T 
| 
PARTTIMESTUDENT_T

the JDBC code can be: 

statement s = conn.createStatement();
s.execute ("CREATE TYPE Person_T (SSN NUMBER, name VARCHAR2(30),
  address VARCHAR2(255))");
s.execute ("CREATE TYPE Student_T UNDER Person_t (deptid NUMBER,
  major VARCHAR2(100))");
s.execute ("CREATE TYPE PartTimeStudent_t UNDER Student_t (numHours NUMBER)");

In the following code, the "foo" member procedure in type ST is overloaded and the member procedure "print" overwrites the copy it inherits from type T. 

CREATE TYPE T AS OBJECT (..., 
  MEMBER PROCEDURE foo(x NUMBER), 
  MEMBER PROCEDURE Print(), 
  ... 
  NOT FINAL; 

CREATE TYPE ST UNDER T (..., 
  MEMBER PROCEDURE foo(x DATE),         <-- overload "foo" 
  OVERRIDING MEMBER PROCEDURE Print(),   <-- override "print" 
  STATIC FUNCTION bar(...) ... 
  ... 
  );

Once the subtypes have been created, they can be used as both columns of a base table as well as attributes of a object type. For complete details on the syntax to create subtypes, see the Oracle9i Application Developer's Guide - Object-Relational Features for details.

Implementing Customized Classes for Subtypes

In most cases, a customized Java class represents a database object type. When you create a customized Java class for a subtype, the Java class can either mirror the database object type hierarchy or not.

You can use either the ORAData or SQLData solution in creating classes to map to the hierarchy of object types.

Use of ORAData for Type Inheritance Hierarchy

Customized mapping where Java classes implement the oracle.sql.ORAData interface is the recommended mapping. (See "Relative Advantages of ORAData versus SQLData".) ORAData mapping requires the JDBC application to implement the ORAData and ORADataFactory interfaces. The class implementing the ORADataFactory interface contains a factory method that produces objects. Each object represents a database object.

The hierarchy of the class implementing the ORAData interface can mirror the database object type hierarchy. For example, the Java classes mapping to PERSON_T and STUDENT_T are as follows:

Person.java using ORAData

Code for the Person.java class which implements the ORAData and ORADataFactory interfaces: 

class Person implements ORAData, ORADataFactory 
{ 
  static final Person _personFactory = new Person(); 

  public NUMBER ssn; 
  public CHAR name; 
  public CHAR address; 

  public static ORADataFactory getORADataFactory() 
  { 
    return _personFactory; 
  } 

  public Person () {} 

  public Person(NUMBER ssn, CHAR name, CHAR address) 
  { 
    this.ssn = ssn; 
    this.name = name; 
    this.address = address; 
  } 

  public Datum toDatum(OracleConnection c) throws SQLException 
  { 
    StructDescriptor sd =
      StructDescriptor.createDescriptor("SCOTT.PERSON_T", c); 
    Object [] attributes = { ssn, name, address }; 
    return new STRUCT(sd, c, attributes); 
  } 

  public ORAData create(Datum d, int sqlType) throws SQLException 
  { 
    if (d == null) return null; 
    Object [] attributes = ((STRUCT) d).getOracleAttributes(); 
    return new Person((NUMBER) attributes[0], 
                      (CHAR) attributes[1], 
                      (CHAR) attributes[2]); 
  } 
}
Student.java extending Person.java

Code for the Student.java class which extends the Person.java class: 

class Student extends Person 
{ 
  static final Student _studentFactory = new Student (); 

  public NUMBER deptid; 
  public CHAR major; 

  public static ORADataFactory getORADataFactory() 
  { 
    return _studentFactory; 
  } 

  public Student () {} 

  public Student (NUMBER ssn, CHAR name, CHAR address, 
                  NUMBER deptid, CHAR major) 
  { 
    super (ssn, name, address); 
    this.deptid = deptid; 
    this.major = major; 
  } 

  public Datum toDatum(OracleConnection c) throws SQLException 
  { 
    StructDescriptor sd = 
      StructDescriptor.createDescriptor("SCOTT.STUDENT_T", c); 
    Object [] attributes = { ssn, name, address, deptid, major }; 
    return new STRUCT(sd, c, attributes); 
  } 

  public CustomDatum create(Datum d, int sqlType) throws SQLException 
  { 
    if (d == null) return null; 
    Object [] attributes = ((STRUCT) d).getOracleAttributes(); 
    return new Student((NUMBER) attributes[0], 
                       (CHAR) attributes[1], 
                       (CHAR) attributes[2], 
                       (NUMBER) attributes[3], 
                       (CHAR) attributes[4]); 
  } 
}

Customized classes that implement the ORAData interface do not have to mirror the database object type hierarchy. For example, you could have declared the above class, Student, without a superclass. In this case, Student would contain fields to hold the inherited attributes from PERSON_T as well as the attributes declared by STUDENT_T.

ORADataFactory Implementation

The JDBC application uses the factory class in querying the database to return instances of Person or its subclasses, as in the following example: 

ResultSet rset = stmt.executeQuery ("select person from tab1"); 
while (rset.next()) 
{ 
  Object s = rset.getORAData (1, PersonFactory.getORADataFactory()); 
  ... 
}

A class implementing the ORADataFactory interface should be able to produce instances of the associated custom object type, as well as instances of any subtype, or at least all the types you expect to support.

In the following example, the PersonFactory.getORADataFactory() method returns a factory that can handle PERSON_T, STUDENT_T, and PARTTIMESTUDENT_T objects (by returning person, student, or parttimestudent Java instances). 

class PersonFactory implements ORADataFactory 
{ 
  static final PersonFactory _factory = new PersonFactory (); 

  public static ORADataFactory getORADataFactory() 
  { 
    return _factory; 
  } 

  public ORAData create(Datum d, int sqlType) throws SQLException 
  { 
    STRUCT s = (STRUCT) d; 
    if (s.getSQLTypeName ().equals ("SCOTT.PERSON_T")) 
      return Person.getORADataFactory ().create (d, sqlType); 
    else if (s.getSQLTypeName ().equals ("SCOTT.STUDENT_T")) 
      return Student.getORADataFactory ().create(d, sqlType); 
    else if (s.getSQLTypeName ().equals ("SCOTT.PARTTIMESTUDENT_T")) 
      return ParttimeStudent.getORADataFactory ().create(d, sqlType); 
    else 
      return null; 
  } 
}

The following example assumes a table tab1, such as the following: 

CREATE TABLE tab1 (idx NUMBER, person PERSON_T); 
INSERT INTO tabl1 VALUES (1, PERSON_T (1000, 'Scott', '100 Oracle Parkway')); 
INSERT INTO tabl1 VALUES (2, STUDENT_T (1001, 'Peter', '200 Oracle Parkway', 
101, 'CS')); 
INSERT INTO tabl1 VALUES (3, PARTTIMESTUDENT_T (1002, 'David', '300 Oracle 
Parkway', 102, 'EE'));

Use of SQLData for Type Inheritance Hierarchy

The customized classes that implement the java.sql.SQLData interface can mirror the database object type hierarchy. The readSQL() and writeSQL() methods of a subclass cascade each call to the corresponding methods in the superclass in order to read or write the superclass attributes before reading or writing the subclass attributes. For example, the Java classes mapping to PERSON_T and STUDENT_T are as follows:

Person.java using SQLData

Code for the Person.java class which implements the SQLData interface: 

import java.sql.*; 

public class Person implements SQLData 
{ 
  private String sql_type; 
  public int ssn; 
  public String name; 
  public String address; 

  public Person () {} 

  public String getSQLTypeName() throws SQLException { return sql_type; } 

  public void readSQL(SQLInput stream, String typeName) throws SQLException 
  { 
    sql_type = typeName; 
    ssn = stream.readInt(); 
    name = stream.readString(); 
    address = stream.readString(); 
  } 

  public void writeSQL(SQLOutput stream) throws SQLException 
  { 
    stream.writeInt (ssn); 
    stream.writeString (name); 
    stream.writeString (address); 
  } 
}
Student.java extending Student.java

Code for the Student.java class which extends the Person.java class: 

import java.sql.*; 

public class Student extends Person 
{ 
  private String sql_type; 
  public int deptid; 
  public String major; 

  public Student () { super(); } 

  public String getSQLTypeName() throws SQLException { return sql_type; } 

  public void readSQL(SQLInput stream, String typeName) throws SQLException 
  { 
    super.readSQL (stream, typeName);    // read supertype attributes 
    sql_type = typeName;
    deptid = stream.readInt(); 
    major = stream.readString(); 
  } 

  public void writeSQL(SQLOutput stream) throws SQLException 
  { 
    super.writeSQL (stream);             // write supertype
                                         // attributes 
    stream.writeInt (deptid); 
    stream.writeString (major); 
  } 
}

Customized classes that implement the SQLData interface do not have to mirror the database object type hierarchy. For example, you could have declared the above class, Student, without a superclass. In this case, Student would contain fields to hold the inherited attributes from PERSON_T as well as the attributes declared by STUDENT_T.

Student.java using SQLData

Code for the Student.java class which does not extend the Person.java class, but implements the SQLData interface directly: 

import java.sql.*; 

public class Student implements SQLData 
{ 
  private String sql_type; 

  public int ssn; 
  public String name; 
  public String address; 
  public int deptid; 
  public String major; 

  public Student () {} 

  public String getSQLTypeName() throws SQLException { return sql_type; } 

  public void readSQL(SQLInput stream, String typeName) throws SQLException 
  { 
    sql_type = typeName; 
    ssn = stream.readInt(); 
    name = stream.readString(); 
    address = stream.readString(); 
    deptid = stream.readInt(); 
    major = stream.readString(); 
  } 

  public void writeSQL(SQLOutput stream) throws SQLException 
  { 
    stream.writeInt (ssn); 
    stream.writeString (name); 
    stream.writeString (address); 
    stream.writeInt (deptid); 
    stream.writeString (major); 
  } 
}

JPublisher Utility

Even though you can manually create customized classes that implement the SQLData, ORAData, and ORADataFactory interfaces, it is recommended that you use Oracle9i JPublisher to automatically generate these classes. The customized classes generated by JPublisher that implement the SQLData, ORAData, and ORADataFactory interfaces, can mirror the inheritance hierarchy.

To learn more about JPublisher, see "Using JPublisher to Create Custom Object Classes" and the Oracle9i JPublisher User's Guide.

Retrieving Subtype Objects

In a typical JDBC application, a subtype object is returned as one of the following:

You can use either the default (oracle.sql.STRUCT), ORAData, or SQLData mapping to retrieve a subtype.

Using Default Mapping

By default, a database object is returned as an instance of the oracle.sql.STRUCT class. This instance may represent an object of either the declared type or subtype of the declared type. If the STRUCT class represents a subtype object in the database, then it contains the attributes of its supertype as well as those defined in the subtype.

The Oracle JDBC driver returns database objects in their most specific type. The JDBC application can use the getSQLTypeName() method of the STRUCT class to determine the SQL type of the STRUCT object. The following code shows this: 

// tab1.person column can store PERSON_T, STUDENT_T and PARTIMESTUDENT_T objects 
ResultSet rset = stmt.executeQuery ("select person from tab1"); 
while (rset.next()) 
{ 
  oracle.sql.STRUCT s = (oracle.sql.STRUCT) rset.getObject(1); 
  if (s != null) 
    System.out.println (s.getSQLTypeName());    // print out the type name which 
                                                // may be SCOTT.PERSON_T, 
                                                // SCOTT.STUDENT_T or
                                                // SCOTT.PARTTIMESTUDENT_T 
}

Using SQLData Mapping

With SQLData mapping, the JDBC driver returns the database object as an instance of the class implementing the SQLData interface.

To use SQLData mapping in retrieving database objects, do the following:

  1. Implement the wrapper classes that implement the SQLData interface for the desired object types.

  2. Populate the connection type map with entries that specify what custom Java type corresponds to each Oracle object type (SQL object type).

  3. Use the getObject() method to access the SQL object values.

    The JDBC driver checks the type map for a entry match. If one exists, the driver returns the database object as an instance of the class implementing the SQLData interface.

The following code shows the whole SQLData customized mapping process: 

// The JDBC application developer implements Person.java for PERSON_T, 
// Student.java for STUDENT_T 
// and ParttimeStudent.java for PARTTIMESTUDEN_T. 

Connection conn = ...;  // make a JDBC connection 

// obtains the connection typemap 
java.util.Map map = conn.getTypeMap (); 

// populate the type map 
map.put ("SCOTT.PERSON_T", Class.forName ("Person")); 
map.put ("SCOTT.STUDENT_T", Class.forName ("Student")); 
map.put ("SCOTT.PARTTIMESTUDENT_T", Class.forName ("ParttimeStudent")); 

// tab1.person column can store PERSON_T, STUDENT_T and PARTTIMESTUDENT_T 
objects 
ResultSet rset = stmt.executeQuery ("select person from tab1"); 
while (rset.next()) 
{ 
  // "s" is instance of Person, Student or ParttimeStudent 
  Object s = rset.getObject(1); 

  if (s != null) 
  { 
    if (s instanceof Person) 
      System.out.println ("This is a Person"); 
    else if (s instanceof Student) 
      System.out.println ("This is a Student"); 
    else if (s instanceof ParttimeStudent) 
      System.out.pritnln ("This is a PartimeStudent"); 
    else 
      System.out.println ("Unknown type"); 
  } 
}

The JDBC drivers check the connection type map for each call to the following:

Using ORAData Mapping

With ORAData mapping, the JDBC driver returns the database object as an instance of the class implementing the ORAData interface.

The Oracle JDBC driver needs to be informed of what Java class is mapped to the Oracle object type. The following are the two ways to inform the Oracle JDBC drivers:

The first approach avoids the type-map lookup and is therefore more efficient. However, the second approach involves the use of the standard getObject() method. The following code example demonstrates the first approach: 

// tab1.person column can store both PERSON_T and STUDENT_T objects 
ResultSet rset = stmt.executeQuery ("select person from tab1"); 
while (rset.next()) 
{ 
  Object s = rset.getORAData (1, PersonFactory.getORADataFactory()); 
  if (s != null) 
  { 
    if (s instanceof Person) 
      System.out.println ("This is a Person"); 
    else if (s instanceof Student) 
      System.out.println ("This is a Student"); 
    else if (s instanceof ParttimeStudent) 
      System.out.pritnln ("This is a PartimeStudent"); 
    else 
      System.out.println ("Unknown type"); 
  } 
}

Creating Subtype Objects

There are cases where JDBC applications create database subtype objects with JDBC drivers. These objects are sent either to the database as bind variables or are used to exchange information within the JDBC application.

With customized mapping, the JDBC application creates either SQLData- or ORAData-based objects (depending on which approach you choose) to represent database subtype objects. With default mapping, the JDBC application creates STRUCT objects to represent database subtype objects. All the data fields inherited from the supertype as well as all the fields defined in the subtype must have values. The following code demonstrates this: 

Connection conn = ...    // make a JDBC connection 
StructDescriptor desc = StructDescriptor.createDescriptor 
("SCOTT.PARTTIMESTUDENT", conn); 
Object[] attrs = { 
  new Integer(1234), "Scott", "500 Oracle Parkway", // data fields defined in
                                                    // PERSON_T 
  new Integer(102), "CS",                           // data fields defined in
                                                    // STUDENT_T 
  new Integer(4)                                    // data fields defined in
                                                    // PARTTIMESTUDENT_T 
}; 
STRUCT s = new STRUCT (desc, conn, attrs);

s is initialized with data fields inherited from PERSON_T and STUDENT_T, and data fields defined in PARTTIMESTUDENT_T.

Sending Subtype Objects

In a typical JDBC application, a Java object that represents a database object is sent to the databases as one of the following:

The Java object can be an instance of the STRUCT class or an instance of the class implementing either the SQLData or ORAData interface. The Oracle JDBC driver will convert the Java object into the linearized format acceptable to the database SQL engine. Binding a subtype object is the same as binding a normal object.

Accessing Subtype Data Fields

While the logic to access subtype data fields is part of the customized class, this logic for default mapping is defined in the JDBC application itself. The database objects are returned as instances of the oracle.sql.STRUCT class. The JDBC application needs to call one of the following access methods in the STRUCT class to access the data fields:

Subtype Data Fields from the getAttribute() Method

The getAttribute() method of the java.sql.Struct interface is used in JDBC 2.0 to access object data fields. This method returns a java.lang.Object array, where each array element represents an object attribute. You can determine the individual element type by referencing the corresponding attribute type in the JDBC conversion matrix, as listed in Table 5-1, "Oracle Datatype Classes". For example, a SQL NUMBER attribute is converted to a java.math.BigDecimal object. The getAttribute() method returns all the data fields defined in the supertype of the object type as well as data fields defined in the subtype. The supertype data fields are listed first followed by the subtype data fields.

Subtype Data Fields from the getOracleAttribute() Method

The getOracleAttribute() method is an Oracle extension method and is more efficient than the getAttribute() method. The getOracleAttribute() method returns an oracle.sql.Datum array to hold the data fields. Each element in the oracle.sql.Datum array represents an attribute. You can determine the individual element type by referencing the corresponding attribute type in the Oracle conversion matrix, as listed in Table 5-1, "Oracle Datatype Classes". For example, a SQL NUMBER attribute is converted to an oracle.sql.NUMBER object. The getOracleAttribute() method returns all the attributes defined in the supertype of the object type, as well as attributes defined in the subtype. The supertype data fields are listed first followed by the subtype data fields.

The following code shows the use of the getAttribute() method: 

// tab1.person column can store PERSON_T, STUDENT_T and PARTIMESTUDENT_T objects 
ResultSet rset = stmt.executeQuery ("select person from tab1"); 
while (rset.next()) 
{ 
  oracle.sql.STRUCT s = (oracle.sql.STRUCT) rset.getObject(1); 
  if (s != null) 
  { 
    String sqlname = s.getSQLTypeName(); 

    Object[] attrs = s.getAttribute(); 

    if (sqlname.equals ("SCOTT.PERSON") 
    { 
      System.out.println ("ssn="+((BigDecimal)attrs[0]).intValue()); 
      System.out.println ("name="+((String)attrs[1])); 
      System.out.println ("address="+((String)attrs[2])); 
    } 
    else if (sqlname.equals ("SCOTT.STUDENT")) 
    { 
      System.out.println ("ssn="+((BigDecimal)attrs[0]).intValue()); 
      System.out.println ("name="+((String)attrs[1])); 
      System.out.println ("address="+((String)attrs[2])); 
      System.out.println ("deptid="+((BigDecimal)attrs[3]).intValue()); 
      System.out.println ("major="+((String)attrs[4])); 
    } 
    else if (sqlname.equals ("SCOTT.PARTTIMESTUDENT")) 
    { 
      System.out.println ("ssn="+((BigDecimal)attrs[0]).intValue()); 
      System.out.println ("name="+((String)attrs[1])); 
      System.out.println ("address="+((String)attrs[2])); 
      System.out.println ("deptid="+((BigDecimal)attrs[3]).intValue()); 
      System.out.println ("major="+((String)attrs[4])); 
      System.out.println ("numHours="+((BigDecimal)attrs[5]).intValue()); 
    } 
    else 
      throw new Exception ("Invalid type name: "+sqlname); 
  } 
} 
rset.close (); 
stmt.close (); 
conn.close ();

Inheritance Meta Data Methods

Oracle9i JDBC drivers provide a set of meta data methods to access inheritance properties. The inheritance meta data methods are defined in the oracle.sql.StructDescriptor and oracle.jdbc.StructMetaData classes.

The oracle.sql.StructDescriptor class provides the following inheritance meta data methods:

The StructMetaData class provides inheritance meta data methods for subtype attributes; the getMetaData() method of the StructDescriptor class returns an instance of StructMetaData of the type. The StructMetaData class contains the following inheritance meta data methods:

Using JPublisher to Create Custom Object Classes

A convenient way to create custom object classes, as well as other kinds of custom Java classes, is to use the Oracle JPublisher utility. It generates a full definition for a custom Java class, which you can instantiate to hold the data from an Oracle object. JPublisher-generated classes include methods to convert data from SQL to Java and from Java to SQL, as well as getter and setter methods for the object attributes.

This section offers a brief overview. For more information, see the Oracle9i JPublisher User's Guide.

JPublisher Functionality

You can direct JPublisher to create custom object classes that implement either the SQLData interface or the ORAData interface, according to how you set the JPublisher type mappings.

If you use the ORAData interface, JPublisher will also create a custom reference class to map to object references for the Oracle object type. If you use the SQLData interface, JPublisher will not produce a custom reference class; you would use standard java.sql.Ref instances instead.

If you want additional functionality, you can subclass the custom object class and add features as desired. When you run JPublisher, there is a command-line option for specifying both a generated class name and the name of the subclass you will implement. For the SQL-Java mapping to work properly, JPublisher must know the subclass name, which is incorporated into some of the functionality of the generated class.

Note:

Hand-editing the JPublisher-generated class, instead of subclassing it, is not recommended. If you hand-edit this class and later have to re-run JPublisher for some reason, you would have to re-implement your changes.  

JPublisher Type Mappings

JPublisher offers various choices for how to map user-defined types and their attribute types between SQL and Java. The rest of this section lists categories of SQL types and the mapping options available for each category.

For general information about SQL-Java type mappings, see "Datatype Mappings".

For more information about JPublisher features or options, see the Oracle9i JPublisher User's Guide.

Categories of SQL Types

JPublisher categorizes SQL types into the following groups, with corresponding JPublisher options as noted:

Type-Mapping Modes

JPublisher defines the following type-mapping modes, two of which apply to numeric types only:

Mapping the Oracle object type to Java

Use the JPublisher -usertypes option to determine how JPublisher will implement the custom Java class that corresponds to a Oracle object type:

The next section discusses type mapping options that you can use for object attributes.

Note:

You can also use JPublisher with a -usertypes=oracle setting in creating ORAData implementations to map SQL collection types.

The -usertypes=jdbc setting is not valid for mapping SQL collection types. (The SQLData interface is intended only for mapping Oracle object types.) 

Mapping Attribute Types to Java

If you do not specify mappings for the attribute types of the Oracle object type, JPublisher uses the following defaults:

If you want alternate mappings, use the -numbertypes, -lobtypes, and -builtintypes options as necessary, depending on the attribute types you have and the mappings you desire.

If an attribute type is itself an Oracle object type, it will be mapped according to the -usertypes setting.

Important:

Be especially aware that if you specify a SQLData implementation for the custom object class and want the code to be portable, you must be sure to use portable mappings for the attribute types. The defaults for numeric types and built-in types are portable, but for LOB types you must specify -lobtypes=jdbc. 

Summary of SQL Type Categories and Mapping Settings

Table 8-1 summarizes JPublisher categories for SQL types, the mapping settings relevant for each category, and the default settings.

Table 8-1 JPublisher SQL Type Categories, Supported Settings, and Defaults 
SQL Type Category  JPublisher Mapping Option  Mapping Settings  Default 

UDT types 

-usertypes 

oracle, jdbc 

oracle 

numeric types 

-numbertypes 

oracle, jdbc, objectjdbc, bigdecimal 

objectjdbc 

LOB types 

-lobtypes 

oracle, jdbc 

oracle 

built-in types 

-builtintypes 

oracle, jdbc 

jdbc 

Note:

The JPublisher -mapping option used in previous releases will be deprecated but is currently still supported. For information about how JPublisher converts -mapping option settings to settings for the new mapping options, see the Oracle9i JPublisher User's Guide. 

Describing an Object Type

Oracle JDBC includes functionality to retrieve information about a structured object type regarding its attribute names and types. This is similar conceptually to retrieving information from a result set about its column names and types, and in fact uses an almost identical method.

Functionality for Getting Object Meta Data

The oracle.sql.StructDescriptor class, discussed earlier in "STRUCT Descriptors" and "Steps in Creating StructDescriptor and STRUCT Objects", includes functionality to retrieve meta data about a structured object type.

The StructDescriptor class has a getMetaData() method with the same functionality as the standard getMetaData() method available in result set objects. It returns a set of attribute information such as attribute names and types. Call this method on a StructDescriptor object to get meta data about the Oracle object type that the StructDescriptor object describes. (Remember that each structured object type must have an associated StructDescriptor object.)

The signature of the StructDescriptor class getMetaData() method is the same as the signature specified for getMetaData() in the standard ResultSet interface:

However, this method actually returns an instance of oracle.jdbc.StructMetaData, a class that supports structured object meta data in the same way that the standard java.sql.ResultSetMetaData interface specifies support for result set meta data.

The StructMetaData class includes the following standard methods that are also specified by ResultSetMetaData:

As well as the following method, supported only by StructMetaData:

Steps for Retrieving Object Meta Data

Use the following steps to obtain meta data about a structured object type:

  1. Create or acquire a StructDescriptor instance that describes the relevant structured object type.

  2. Call the getMetaData() method on the StructDescriptor instance.

  3. Call the meta data getter methods as desired--getColumnName(), getColumnType(), and getColumnTypeName().

    Note:

    If one of the structured object attributes is itself a structured object, repeat steps 1 through 3. 

Example

The following method shows how to retrieve information about the attributes of a structured object type. This includes the initial step of creating a StructDescriptor instance. 

// 
// Print out the ADT's attribute names and types 
// 
void getAttributeInfo (Connection conn, String type_name) throws SQLException 
{ 
  // get the type descriptor 
  StructDescriptor desc = StructDescriptor.createDescriptor (type_name, conn); 

  // get type meta data 
  ResultSetMetaData md = desc.getMetaData (); 

  // get # of attrs of this type 
  int numAttrs = desc.length (); 

  // temporary buffers 
  String attr_name; 
  int attr_type; 
  String attr_typeName; 

  System.out.println ("Attributes of "+type_name+" :"); 
  for (int i=0; i<numAttrs; i++) 
  { 
    attr_name = md.getColumnName (i+1); 
    attr_type = md.getColumnType (i+1); 
    System.out.println (" index"+(i+1)+" name="+attr_name+" type="+attr_type); 

    // drill down nested object 
    if (attrType == OracleTypes.STRUCT) 
    { 
      attr_typeName = md.getColumnTypeName (i+1); 

      // recursive calls to print out nested object meta data 
      getAttributeInfo (conn, attr_typeName); 
    } 
  } 
}

SQLJ Object Types

This section describes how to use Oracle9i JDBC drivers to access SQLJ object types, SQL types for user-defined object types according to the Information Technology - SQLJ - Part 2: SQL Types using the JavaTM Programming Language document (ANSI NCITS 331.2-2000).

Note:

SQLJ object types can either be in serialized or SQL representation. Because Oracle does not support SQLJ object types in serialized representation, this manual describes only SQLJ object types in SQL representation. 

According to the Information Technology - SQLJ - Part 2 document, a SQLJ object type is a database object type designed for Java. A SQLJ object type maps to a Java class. Once the mapping is "registered" through the extended SQL CREATE TYPE command (a DDL statement), the Java application can insert or select the Java objects directly into or from the database through an Oracle9i JDBC driver. The database SQL engine can access the data fields of these Java objects, stored as SQL attributes in the database, as well as invoke the methods defined in these Java objects.

The extended SQL CREATE TYPE command is further discussed in "Creating a Java Class Definition for a SQLJ Object Type".

SQLJ object type functionality has the following features:

You can obtain additional information on SQLJ object types at the ANSI Web site: 

http://www.ansi.org/

Creating a SQLJ Object Type in SQL Representation

There are three general steps involved in creating a SQLJ object type in a database:

  1. Create the Java class whose instances will be accessed by the database.

    See "Creating a Java Class Definition for a SQLJ Object Type" below.

  2. Load the class definition into the database.

    See "Loading the Java Class into the Database".

  3. Use the extended SQL CREATE TYPE command in Oracle9i to create a SQLJ object type that represents the Java type.

    See "Creating the SQLJ Object Type in the Database".

Creating a Java Class Definition for a SQLJ Object Type

To use SQLJ object type functionality, the Java class must implement one of the following Java interfaces:

The Java class corresponding to a SQLJ object type implements the SQLData interface or the ORAData and ORADataFactory interfaces, as is the case for custom Java classes that correspond to user-defined Oracle object types in previous Oracle JDBC implementations. The Java class provides methods for moving data between SQL and Java--either using the readSQL() and writeSQL() methods for classes implementing the SQLData interface, or the toDatum() method for classes implementing the ORAData interface.

The following code shows how the Person class for the SQLJ object type, PERSON_T, implements the SQLData interface: 

import java.sql.*;
import java.io.*;

public class Person implements SQLData
{ 
  private String sql_type = "SCOTT.PERSON_T";
  private int ssn; 
  private String name;
  private Address address; 
  public String getName() {return name;}
  public void setName(String nam) {name = nam;}; 
  public Address getAddress() {return address;}
  public void setAddress(Address addr) {address = addr;}

  public Person () {} 

  public String getSQLTypeName() throws SQLException { return sql_type; } 

  public void readSQL(SQLInput stream, String typeName) throws SQLException 
  { 
    sql_type = typeName; 
    ssn = stream.readInt(); 
    name = stream.readString();
    address = stream.readObject(); 
  } 

    public void writeSQL(SQLOutput stream) throws SQLException 
  { 
    stream.writeInt (ssn); 
    stream.writeString (name); 
    stream.writeObject (address); 
  }

  // other methods 
  public int length () { ... } 
}

Loading the Java Class into the Database

Once you create the Java class, the next step is to make it available to the database. To do this, use the Oracle loadjava tool to load the Java class into the database. See the Oracle9i Java Tools Reference for a complete description of the loadjava tool.

Note:

You can also invoke the loadjava tool by calling the dbms_java.loadjava ('...') procedure from SQL*Plus, specifying the loadjava command line as the input string. 

The following command shows the loadjava tool loading the Person class into the database:

% loadjava -u SCOTT/TIGER -r -f -v Person.class

Creating the SQLJ Object Type in the Database

The final step in creating a SQLJ object type is to use the extended SQL CREATE TYPE command to create the type, specifying the corresponding Java class in the EXTERNAL NAME clause.

The follow code shows that PERSON_T is the SQLJ object type and Person is the corresponding Java class: 

CREATE TYPE person_t AS OBJECT EXTERNAL NAME 'Person' LANGUAGE JAVA
USING SQLData
  (ss_no number (9) external name 'ssn',
  name VARCHAR2(200) external name 'name',
  address Address_t external name 'address',
  member function length return number external name 'length () return int');
/

The extended SQL CREATE TYPE command performs the following functions:

See the Oracle9i SQL Reference for a complete description of the extended SQL CREATE TYPE command.

Once a SQLJ object type is created, it can be used for the column type of a database table as well as for attributes of other object types. The database SQL engine can access the attributes of the SQLJ object type as well as invoke methods. For example, in SQL*Plus you can do the following: 

SQL> select col2.ss_no from tab2; 
... 
SQL> select col2.length() from tab2; 
...
External Attribute Names

The extended SQL CREATE TYPE command validates the compatibility between SQLJ object type attributes and corresponding Java fields by comparing the external attribute names (external name variables) to the corresponding Java fields. An external attribute name specifies a field in the Java class. For example, in the following code, the ssn external name specifies the ss_no field in the Person Java class: 

CREATE TYPE person_t AS OBJECT EXTERNAL NAME 'Person' LANGUAGE JAVA
USING SQLData
  (ss_no number (9) external name 'ssn',
  name VARCHAR2(200) external name 'name',
  address Address_t external name 'address',
  member function length return number external name 'length () return int');
/

Though optional, external attribute names are good to use when one-to-one correspondences exists between the attributes of a SQLJ object type and the fields of a corresponding Java class. If you choose to use this feature and a declared external attribute name does not exist in the Java class or the SQL attribute is not compatible with the external attribute type, then a SQL error occurs upon executing the extended SQL CREATE TYPE command. Or if the provided SQLData or ORAData interface implementation does not support compatible mapping between a SQL attribute and its corresponding Java field, then an exception may occur.

Note:

A SQL attribute declared with an external attribute name may refer to a private Java field. 

External SQL Functions

The extended SQL CREATE TYPE command validates the compatibility between SQLJ object type functions and corresponding Java methods by comparing the external SQL function (MEMBER FUNCTION or STATIC FUNCTION) to the corresponding Java method. An external SQL function specifies a method in the Java class.

Note:

Unlike an external attribute name, an external SQL function is mandatory. 

When creating a SQLJ object type in the database, you can declare one or more external SQL functions along with the attributes. Table 8-2 describes the possible kinds of functions that you can use in the creation of a SQLJ object type:

Table 8-2 Kinds of External SQL Functions for a SQLJ Object Type  
Function  Kind  Syntax 

Static functions 

Oracle-
specific 

STATIC FUNCTION foo (...) RETURN NUMBER EXTERNAL NAME 'bar (...) return double' 

Member function 

SQLJ Part2
Standard 

MEMBER FUNCTION foo (...) RETURN NUMBER EXTERNAL NAME 'todo (...) return double' 

Static function that returns the value of a static Java field, which can only be public 

Oracle-
specific 

STATIC FUNCTION foo RETURN NUMBER EXTERNAL VARIABLE NAME 'max_length' 

Static function that calls a constructor in Java 

Oracle-
specific 

STATIC FUNCTION foo (...) RETURN person_t EXTERNAL NAME 'Person (...) return Person' 

Member function that has a side effect (changes the state of an object) 

SQLJ Part2
Standard 

MEMBER FUNCTION foo (...) RETURN SELF AS RESULT EXTERNAL NAME 'dump (...) return Person' 
Code Examples

The following code shows some typical external SQL functions being declared for a SQLJ object type: 

CREATE TYPE person_t AS OBJECT EXTERNAL NAME 'Person' LANGUAGE JAVA
USING SQLData
(
  num number external name 'foo',

  STATIC function construct (num number) return person_t
  external name   'Person.Person (int) return Person',
  STATIC function maxvalue return number external variable name 'max_length',
  MEMBER function selfish (num number) return self as result
    external name 'Person.dump (java.lang.Integer) return Person'
)

The following code shows how to create the SQLJ object type PERSON_T to represent the Java class Person: 

CREATE TYPE person_t AS OBJECT EXTERNAL NAME 'Person' LANGUAGE JAVA 
USING SQLData 
( 
  ss_no NUMBER(9) EXTERNAL NAME 'ssn', 
  name VARCHAR2(100) EXTERNAL NAME 'name', 
  address address_t EXTERNAL NAME 'address', 
  MEMBER FUNCTION length RETURN integer EXTERNAL NAME 'length() return int'
);
Creating SQLJ Object Types Using JDBC

As an alternative to creating a SQLJ object type directly in SQL, using a tool such as SQL*Plus, you can create a SQLJ object type using JDBC code. The following code shows this: 

Connection conn = .... 
Statement stmt = conn.createStatement(); 
String sql = 
  "CREATE TYPE person_t as object external name 'Person' language java 
  "  using SQLData "+ 
  "( "+ 
  "  ss_no number(9), "+ 
  "  name varchar2(100), "+ 
  "  address address_t "+ 
  ")"; 
stmt.execute(sql); 
stmt.close();     // release the resource 
conn.close();     // close the database connection

Inserting an Instance of a SQLJ Object Type

To create a SQLJ object type instance, the JDBC application creates a corresponding Java instance and then inserts it into the database using the INSERT statement. The Java instance can be inserted in one of the following ways:

Before sending the Java object to the database, the Oracle JDBC driver converts it into a format acceptable to the database SQL engine.

To create a SQLJ object type of person_t, as described in previous sections, the JDBC application creates a Person object and then inserts it into the database. The following code binds the person_t SQLJ object type instance in a SQL insert statement: 

Person person = new Person(); 
person.ssn = 1000; 
person.name = "SCOTT"; 
person.address = new Address ("some street", "some city", "CA", 12345); 

// insert a SQLJ Object "person_t" 
PreparedStatement pstmt = conn.prepareStatement ("insert into tab1 (1, ?)"); 
pstmt.setObject (1, person); 
pstmt.execute ();

Binding a Java instance of a SQLJ object type is equivalent to binding a Java instance of a regular Oracle object type.

Retrieving Instances of a SQLJ Object Type

In a typical JDBC application, Java instances of a SQLJ object type are returned from one of the following:

In each case, the Oracle JDBC driver materialize the database SQLJ object type instances as instances of the corresponding Java class.

See "Code Examples", to learn how the SQLJ object type person_t and a database table are created.

Retrieving a SQLJ Object Type Instance Through Database Queries

When a JDBC application queries a column of SQLJ object types in a table, the column values are returned as instances of the Java class that corresponds to the SQLJ object type.

Assume that you have table tab1 containing column col1 of SQLJ object type PERSON_T. If PERSON_T was created to map to the Java class Person, then querying col1 through the Oracle JDBC driver will return the data as instances of the Person class. The following code shows this: 

ResultSet rset = stmt.executeQuery ("select col1 from tab1"); 
while (rset.next())
  Person value = (Person) rset.getObject(1);

Notes:

  • If the Java class does not exist on the client when the SQLJ object type is returned, a run-time exception occurs.

  • If the Java class exists on the client but has been modified, then the SQLJ object type will only be read or written properly if the readSQL() and writeSQL() methods for the SQLData interface, or the create() and toDatum() methods for the ORAData interface, remain compatible with the original set of SQL attributes.

 

Retrieving a SQLJ Object Type Instance as an Output Parameter

Use the OracleTypes.JAVA_STRUCT typecode as input to the registerOutParameter() method to register a SQLJ object type as a PL/SQL OUT parameter. The following code shows this: 

CallableStatement cstmt = conn.prepareCall (...); 
cstmt.registerOutParameter (1, OracleTypes.JAVA_STRUCT, "SCOTT.PERSON_T"); 
... 
cstmt.execute(); 
Person value = (Person) cstmt.getObject (1);

Meta Data Methods for SQLJ Object Types

Meta data methods are used to query the properties of a datatype. The meta data methods for SQLJ object types are defined in the oracle.sql.StructDescriptor class and the oracle.jdbc.StructMetaData interface.

To obtain the type descriptor, use the static createDescriptor() factory method of the oracle.sql.StructDescriptor class as follows: 

public static StructDescriptor createDescriptor(String name, Connection conn) 
    throws SQLException

Where name is the SQLJ object type and conn is the connection to the database.

The oracle.sql.StructDescriptor class defines the following meta data (instance) methods:

The oracle.jdbc.StructMetaData interface provides the following method:

SQLJ Object Types and Custom Object Types Compared

This section describes the differences between SQLJ object types and Oracle object types (custom object types).

Table 8-3 SQLJ Object Type and Custom Object Type Features Compared  
Feature  SQLJ Object Type Behavior  Custom Object Type Behavior 

Typecodes 

Use the OracleTypes.JAVA_STRUCT typecode to register a SQLJ object type as a SQL OUT parameter. The OracleTypes.JAVA_STRUCT typecode is also used in the _SQL_TYPECODE field of a class implementing the ORAData or SQLData interface. This typecode is reported in a ResultSetMetaData instance and meta data or stored procedure. 

Use the OracleTypes.STRUCT typecode to register a custom object type as a SQL OUT parameter. The OracleTypes.STRUCT typecode is also used in the _SQL_TYPECODE field of a class implementing the ORAData or SQLData interface. (See "ORAData Implementation--ORADataExample.java" for a code example.) The OracleTypes.STRUCT typecode is reported in a ResultSetMetaData instance and meta data or stored procedure.  

Creation 

Create a Java class implementing the SQLData or ORAData and ORADataFactory interfaces first and then load the Java class into the database. Next, issue the extended SQL CREATE TYPE command to create the SQLJ object type. 

Issue the extended SQL CREATE TYPE command for a custom object type and then create the SQLData or ORAData Java wrapper class using JPublisher, or do this manually. See "Using JPublisher to Create Custom Object Classes" for complete details. 

Method Support 

Supports external names, constructor calls, and calls for member functions with side effects. See Table 8-2, "Kinds of External SQL Functions for a SQLJ Object Type" for a complete description. 

There is no default class for implementing type methods as Java methods. Some methods may also be implemented in SQL. 

Type Mapping 

Type mapping is automatically done by the extended SQL CREATE TYPE command. However, the SQLJ object type must have a defining Java class on the client. 

Register the correspondence between SQL and Java in a type map. Otherwise, the type is materialized as oracle.sql.STRUCT. 

Corresponding Java Class is Missing 

If the corresponding Java class is missing when a SQLJ object type is returned to the client, you will receive an exception. 

If the corresponding Java class is missing when a custom object type is returned to the client, then oracle.sql.STRUCT is used. 

Inheritance 

There are rules for mapping SQL hierarchy to a Java class hierarchy. See the Oracle9i SQL Reference for a complete description of these rules. 

There are no mapping rules. 

Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.
All Rights Reserved.
Go To Documentation Library
Home		 Go To Product List
Solution Area		 Go To Table Of Contents
Contents		 Go To Index
Index