|Oracle8i JDBC Developer's Guide and Reference
Release 3 (8.1.7)
Part Number A83724-01
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
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".
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.
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
Stringthat represents the fully qualified name (
schema.sql_type_name) of the Oracle object type that this
Structrepresents (such as
If you want to take advantage of the extended functionality offered by Oracle-defined methods, then use an
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.
STRUCT class includes the following methods in addition to standard
getOracleAttributes(): Retrieves the values of the values array as
getDescriptor(): Returns the
StructDescriptorobject for the SQL type that corresponds to this
getConnection(): Returns the current connection.
toJdbc(): Consults the default type map of the connection, to determine what class to map to, and then uses
toJdbc(map): Consults the specified type map to determine what class to map to, and then uses
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".
This section describes how to create
STRUCT objects and descriptors and lists useful methods of the
This section describes how to construct an
oracle.sql.STRUCT object for a given Oracle object type. To create a
STRUCT object, you must:
StructDescriptorobject (if one does not already exist) for the given Oracle object type.
StructDescriptorto construct the
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:
sql_type_name is a Java string containing the name of the Oracle object type (such as
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.
structdesc is the
StructDescriptor created previously,
connection is your connection object, and
attributes is an array of type
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.
StructDescriptor class includes the following methods:
getName(): Returns the fully qualified SQL type name of the Oracle object (that is, in
schema.sql_type_nameformat, such as
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
ResultSetMetaDataobject contains the attribute name, attribute typecode, and attribute type precision information. The "column" index in the
ResultSetMetaDataobject maps to the position of the attribute in the
STRUCT, with the first attribute being at index 1.
getMetaData() method is further discussed in "Functionality for Getting Object Meta Data".
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. In Oracle8i release 8.1.7, 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
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
The following code provides the connection instance for a
This section discusses how to retrieve and manipulate Oracle objects and their attributes, using either Oracle-specific features or JDBC 2.0 standard features.
The JDBC driver seamlessly handles embedded objects (
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
getObject() returns an
Object type, the return is cast to an
oracle.sql.STRUCT. This example assumes that the
stmt has already been created.
String cmd; cmd = "CREATE TYPE type_
structAS object (field1 NUMBER,field2 DATE)"; stmt.execute(cmd); cmd = "CREATE TABLE
struct_table(col1 type_struct)"; stmt.execute(cmd); cmd = "INSERT INTO
struct_tableVALUES (type_struct(10,'01-apr-01'))"; stmt.execute(cmd); cmd = "INSERT INTO
struct_tableVALUES (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
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
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);
If you want to retrieve Oracle object attributes from a
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
Referring back to the previous examples:
If you want to retrieve Oracle object attributes as standard Java types from a
Struct instance, use the standard
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
PreparedStatement ps= conn.prepareStatement("
text_of_prepared_statement"); STRUCT mySTRUCT = new STRUCT (...); ((OraclePreparedStatement)ps).setOracleObject(1, mySTRUCT);
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
The following methods are included with 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
getArray() methods (presuming the
ARRAY data is able to fit into the JVM memory without overflow).
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.