13 Working with Oracle Object Types

13.2.1.1 Standard java.sql.Struct Methods

If your code must comply with standard JDBC 2.0, then use a java.sql.Struct instance and use the following standard methods:

• getAttributes(map): Retrieves the values of the attributes, using entries in the specified type map to determine the Java classes to use in materializing any attribute that is a structured object type. The Java types for other attribute values would be the same as for a getObject() call on data of the underlying SQL type (the default JDBC types).

• getAttributes(): This is the same as the preceding getAttributes(map) method, except it uses the default type map for the connection.

• getSQLTypeName(): Returns a Java String that represents the fully qualified name (schema.sql_type_name) of the Oracle object type that this Struct represents (such as SCOTT.EMPLOYEE).

13.2.1.2 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 and provides extended functionality beyond the JDBC 2.0 standard.

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

• getOracleAttributes(): Retrieves the values of the values array as oracle.sql.* objects.

• getDescriptor(): Returns the StructDescriptor object for the SQL type that corresponds to this STRUCT object.

• getJavaSQLConnection(): Returns the current connection instance (java.sql.Connection).

• toJdbc(): Consults the default type map of the connection, to determine what class to map to, and then uses toClass().

• toJdbc(map): Consults the specified type map to determine what class to map to, and then uses toClass().

13.2.1.3 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".

13.2.2.1 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[].

13.2.2.2 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:

• getName(): Returns the fully qualified SQL type name of the Oracle object (that is, in schema.sql_type_name format, such as CORPORATE.EMPLOYEE).

• getLength(): Returns the number of fields in the object type.

• getMetaData(): Returns the meta data regarding this type (like the getMetaData() method of a result set object). The returned ResultSetMetaData object contains the attribute name, attribute typecode, and attribute type precision information. The "column" index in the ResultSetMetaData object maps to the position of the attribute in the STRUCT, with the first attribute being at index 1.

  The getMetaData() method is further discussed in "Functionality for Getting Object Meta Data".

13.2.2.3 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 

13.2.3.1 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);

13.2.3.2 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. 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);

13.2.3.3 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();

13.2.3.4 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();

13.3.3.1 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. 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.

13.3.3.2 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.

13.3.3.3 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.

13.3.4.1 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.

13.3.4.2 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

• The readSQL() method takes as input a SQLInput stream and a string that indicates the SQL type name of the data (in other words, the name of the Oracle object type, such as EMPLOYEE).

  When your Java application calls getObject(), the JDBC driver creates a SQLInput stream object and populates it with data from the database. The driver can also determine the SQL type name of the data when it reads it from the database. When the driver calls readSQL(), it passes in these parameters.

• For each Java datatype that maps to an attribute of the Oracle object, readSQL() must call the appropriate readXXX() method of the SQLInput stream that is passed in.

  For example, if you are reading EMPLOYEE objects that have an employee name as a CHAR variable and an employee number as a NUMBER variable, you must have a readString() call and a readInt() call in your readSQL() method. JDBC calls these methods according to the order in which the attributes appear in the SQL definition of the Oracle object type.

• The readSQL() method takes the data that the readXXX() methods read and convert, and assigns them to the appropriate fields or elements of a custom object class instance.

You must implement writeSQL() as follows:

public void writeSQL(SQLOutput stream) throws SQLException

• The writeSQL() method takes as input a SQLOutput stream.

  When your Java application calls setObject(), the JDBC driver creates a SQLOutput stream object and populates it with data from a custom object class instance. When the driver calls writeSQL(), it passes in this stream parameter.

• For each Java datatype that maps to an attribute of the Oracle object, writeSQL() must call the appropriate writeXXX() method of the SQLOutput stream that is passed in.

  For example, if you are writing to EMPLOYEE objects that have an employee name as a CHAR variable and an employee number as a NUMBER variable, then you must have a writeString() call and a writeInt() call in your writeSQL() method. These methods must be called according to the order in which attributes appear in the SQL definition of the Oracle object type.

• The writeSQL() method then writes the data converted by the writeXXX() methods to the SQLOutput stream so that it can be written to the database once you execute the prepared statement.

13.3.5.1 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.

13.3.5.2 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(?) }"); 
   

1. 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(); 
   
   

2. 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); 
   

13.3.5.3 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(); 
   

13.3.5.4 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();
   
   

13.3.6.1 Understanding ORAData Features

The ORAData interface has these advantages:

• It recognizes Oracle extensions to the JDBC; ORAData uses oracle.sql.Datum types directly.

• It does not require a type map to specify the names of the Java custom classes you want to create.

• It provides better performance: ORAData works directly with Datum types, the internal format the driver uses to hold Oracle objects.

The ORAData and ORADataFactory interfaces do the following:

• The toDatum() method of the ORAData class transforms the data into an oracle.sql.* representation.

• ORADataFactory specifies a create() method equivalent to a constructor for your custom object class. It creates and returns a ORAData instance. The JDBC driver uses the create() method to return an instance of the custom object class to your Java application or applet. It takes as input an oracle.sql.Datum object and an integer indicating the corresponding SQL typecode as specified in the OracleTypes class.

ORAData and ORADataFactory have the following definitions:

public interface ORAData 
{ 
    Datum toDatum (OracleConnection 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.

13.3.6.2 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:

• Use the Oracle-specific OracleResultSet class getORAData() method (assume an OracleResultSet object ors):

  ors.getORAData (int col_index, ORADataFactory factory); 
  
  

  This method takes as input the column index of the data in your result set, and a ORADataFactory instance. For example, you can implement a getORAFactory() method in your custom object class to produce the ORADataFactory instance to input to getORAData(). The type map is not required when using Java classes that implement ORAData.

or:

• Use the standard getObject(index, map) method specified by the ResultSet interface to retrieve data as instances of ORAData. In this case, 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.

To insert object data:

• Use the Oracle-specific OraclePreparedStatement class setORAData() method (assume an OraclePreparedStatement object ops):

  ops.setORAData (int bind_index, ORAData custom_obj);
  
  

  This method takes as input the parameter index of the bind variable and the name of the object containing the variable.

or:

• Use the standard setObject() method specified by the PreparedStatement interface. You can also use this method, in its different forms, to insert ORAData instances without requiring a type map.

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.

13.3.7.1 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.

13.3.7.2 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).

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.

13.4.2.1 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:

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]); 
  } 
}

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]); 
  } 
}

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

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; 
  } 
}

CREATE TABLE tabl1 (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')); 

13.4.2.2 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:

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); 
  } 
}

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); 
  } 
}

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); 
  } 
}

13.4.2.3 JPublisher Utility

Even though you can manually create customized classes that implement the SQLData, ORAData, and ORADataFactory interfaces, it is recommended that you use Oracle JPublisher to automatically generate these classes. The customized classes generated by Oracle 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 Oracle Database JPublisher User's Guide.

13.4.3.1 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
}

13.4.3.2 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:

• getObject() method of the java.sql.ResultSet and java.sql.CallableStatement interfaces

• getAttribute() method of the java.sql.Struct interface

• getArray() method of the java.sql.Array interface

• getValue() method of the oracle.sql.REF interface

13.4.3.3 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 JDBC application uses the getORAData(int idx, ORADataFactory f) method to access database objects. The second parameter of the getORAData() method specifies an instance of the factory class that produces the customized class. The getORAData() method is available in the OracleResultSet and OracleCallableStatement classes.

• The JDBC application populates the connection type map with entries that specify what custom Java type corresponds to each Oracle object type. The getObject() method is used to access the Oracle object values.

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"); 
  } 
}

13.4.6.1 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 10-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.

13.4.6.2 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 10-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 ();

13.5.2.1 Categories of SQL Types

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

• user-defined types (UDT)—Oracle objects, references, and collections

  Use the JPublisher -usertypes option to specify the type-mapping implementation for UDTs—either a standard SQLData implementation or an Oracle-specific ORAData implementation.

• numeric types—anything stored in the database as SQL type NUMBER

  Use the JPublisher -numbertypes option to specify type-mapping for numeric types.

• LOB types—SQL types BLOB and CLOB

  Use the JPublisher -lobtypes option to specify type-mapping for LOB types.

• built-in types—anything stored in the database as a SQL type not covered by the preceding categories; for example: CHAR, VARCHAR2, LONG, and RAW

  Use the JPublisher -builtintypes option to specify type-mapping for built-in types.

13.5.2.2 Type-Mapping Modes

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

• JDBC mapping (setting jdbc)—Uses standard default mappings between SQL types and Java native types. For a custom object class, uses a SQLData implementation.

• Oracle mapping (setting oracle)—Uses corresponding oracle.sql types to map to SQL types. For a custom object, reference, or collection class, uses a ORAData implementation.

• object-JDBC mapping (for numeric types only) (setting objectjdbc)—This is an extension of JDBC mapping. Where relevant, object-JDBC mapping uses numeric object types from the standard java.lang package (such as java.lang.Integer, Float, and Double), instead of primitive Java types (such as int, float, and double). The java.lang types are nullable, while the primitive types are not.

• BigDecimal mapping (for numeric types only) (setting bigdecimal)—Uses java.math.BigDecimal to map to all numeric attributes; appropriate if you are dealing with large numbers but do not want to map to the oracle.sql.NUMBER class.

  
  

  Note: Using BigDecimal mapping can significantly degrade performance.

13.5.2.3 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:

• A setting of -usertypes=oracle (the default setting) instructs JPublisher to create a ORAData implementation for the custom object class.

  This will also result in JPublisher producing a ORAData implementation for the corresponding custom reference class.

• A setting of -usertypes=jdbc instructs JPublisher to create a SQLData implementation for the custom object class. No custom reference class can be created—you must use java.sql.Ref or oracle.sql.REF for the reference type.

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

13.5.2.4 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:

• For numeric attribute types, the default mapping is object-JDBC.

• For LOB attribute types, the default mapping is Oracle.

• For built-in type attribute types, the default mapping is JDBC.

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.

13.5.2.5 Summary of SQL Type Categories and Mapping Settings

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

13.6.2.1 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); 
    } 
  } 
}