Working with Oracle Object Types

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 data. For example, your Java application might be a tool to manipulate 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 the information does not need to be in a user-friendly format.

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:

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

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:

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.
getConnection(): Returns the current 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().

STRUCT Descriptors

Creating and using a STRUCT object requires the existence of 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:

Create a StructDescriptor object (if one does not already exist) for the given Oracle object type.
Use the StructDescriptor to construct the STRUCT object.

A StructDescriptor is an instance of the oracle.sql.StructDescriptor class and describes a type of SQL structured object (Oracle 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:

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

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 else using default mapping.

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 from a STRUCT or Struct instance as standard Java types, 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);