Objects and Collections

Custom Java Classes

The purpose of custom Java classes is to provide a way to convert data between the database and your Java application and make the data accessible, particularly in supporting objects and collections or if you want to perform custom data conversions.

It is generally advisable to provide custom Java classes for all user-defined types (objects and collections) that you use in a SQLJ application. The Oracle JDBC driver will use instances of these classes in converting data, which is more convenient and less error-prone than using the weakly typed oracle.sql.STRUCT, REF, and ARRAY classes.

Custom Java classes are first-class types that you can use to read from and write to user-defined SQL types transparently.

To be used in SQLJ iterators or host expressions, a custom Java class must implement either the oracle.sql.CustomDatum (and CustomDatumFactory) interface or the standard java.sql.SQLData interface. This section provides an overview of these interfaces and custom Java class functionality, covering the following topics:

Custom Java Class Interface Specifications

This section discusses specifications of the CustomDatum and CustomDatumFactory interfaces and of the SQLData interface.

CustomDatum and CustomDatumFactory Specifications

Oracle provides the interface oracle.sql.CustomDatum and the related interface oracle.sql.CustomDatumFactory to use in mapping and converting Oracle object types, reference types, and collection types to custom Java classes.

Data is passed to or from the database in the form of an oracle.sql.Datum object, with the underlying data being in the format of the appropriate oracle.sql.Datum subclass--oracle.sql.STRUCT, for example. This data is still in its codified database format; the oracle.sql.Datum object is just a wrapper. (For information about classes in the oracle.sql package that support Oracle type extensions, see the Oracle8i JDBC Developer's Guide and Reference.)

The CustomDatum interface specifies a toDatum() method for data conversion from Java format to database format. This method takes as input your OracleConnection object (which is required by the Oracle JDBC drivers) and converts data to the appropriate oracle.sql.* representation. The OracleConnection object is necessary so that the JDBC driver can perform appropriate type checking and type conversions at runtime. Here is the CustomDatum and toDatum() specification:

interface oracle.sql.CustomDatum
{
   oracle.sql.Datum toDatum(OracleConnection c);
}

The CustomDatumFactory interface specifies a create() method that constructs instances of your custom Java class, converting from database format to Java format. This method takes as input a Datum object containing data from the database, and a typecode, such as OracleTypes.RAW, indicating the SQL type of the underlying data. It returns an object of your custom Java class, which implements the CustomDatum interface. This object receives its data from the Datum object that was input. Here is the CustomDatumFactory and create() specification:

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

To complete the relationship between the CustomDatum and CustomDatumFactory interfaces, you must implement a static getFactory() method in any custom Java class that implements the CustomDatum interface. This method returns an object that implements the CustomDatumFactory interface and that, therefore, can be used to create instances of your custom Java class. This returned object can itself be an instance of your custom Java class, and its create() method is used by the Oracle JDBC driver to produce further instances of your custom Java class, as necessary.

Note:
JPublisher implements the CustomDatum and its toDatum() method and the CustomDatumFactory and its create() method in a single custom Java class; however, toDatum() and create() are specified in different interfaces to allow the option of implementing them in separate classes. You can have one custom Java class that implements CustomDatum, its toDatum() method, and the getFactory() method, and have a separate factory class that implements CustomDatumFactory and its create() method. Although, for purposes of discussion here, the assumption is that both interfaces are implemented in a single class.

For information about Oracle SQLJ requirements of a class that implements CustomDatum, see "Oracle Requirements for Classes Implementing CustomDatum".

For more information about the CustomDatum and CustomDatumFactory interfaces, the oracle.sql classes, and the OracleTypes class, see the Oracle8i JDBC Developer's Guide and Reference.

If you use JPublisher, specifying -usertypes=oracle will result in JPublisher generating custom Java classes that implement the CustomDatum and CustomDatumFactory interfaces and the getFactory() method.

SQLData Specification

Standard JDBC 2.0 supplies the interface java.sql.SQLData to use in mapping and converting structured object types to Java classes. This interface is intended for mapping structured object types only, not object references, collections/arrays, or other SQL types.

The SQLData interface is a JDBC 2.0 standard, specifying a readSQL() method to read data from the database into a Java object, and a writeSQL() method to write data to the database from a Java object.

For information about functionality that is required of a class that implements SQLData, see "Requirements for Classes Implementing SQLData".

For additional information about standard SQLData functionality, refer to the Sun Microsystems JDBC 2.0 API Specification.

If you use JPublisher, specifying -usertypes=jdbc will result in JPublisher generating custom Java classes that implement the SQLData interface.

Custom Java Class Support for Object Methods

Methods of Oracle objects can be implemented as wrappers in custom Java classes. Whether the underlying stored procedure is written in PL/SQL or is written in Java and published to SQL is invisible to the user.

A Java wrapper method used to invoke a server method requires a connection to communicate with the server. The connection object can be provided as an explicit parameter or can be associated in some other way (as an attribute of your custom Java class, for example).

If the connection object used by the wrapper method is a non-static attribute, then the wrapper method must be an instance method of the custom Java class in order to have access to the connection. Custom Java classes generated by JPublisher use this technique.

There are also issues regarding output and input-output parameters in methods of Oracle objects. In the database, if a stored procedure (SQL object method) modifies the internal state of one of its arguments, then the actual argument passed to the stored procedure is modified. In Java this is not possible. When a JDBC output parameter is returned from a stored procedure call, it is stored in a newly created object. The original object identity is lost.

One way to return an output or input-output parameter to the caller is to pass the parameter as an element of an array. If the parameter is input-output, the wrapper method takes the array element as input; after processing, the wrapper assigns the output to the array element. Custom Java classes generated by JPublisher use this technique--each output or input-output parameter is passed in a one-element array.

When you use JPublisher, it implements wrapper methods by default. This is true for generated classes implementing either the SQLData interface or the CustomDatum interface. To disable this feature, set the JPublisher -methods flag to false. See the Oracle8i JPublisher User's Guide for more information.

Note:
If you are implementing a custom Java class yourself, there are various ways that you can implement wrapper methods. Data processing can be done either in the server in the SQL object method, or on the client in the wrapper method. To see how JPublisher implements wrapper methods, and whether this may meet your needs, see "JPublisher Implementation of Wrapper Methods".

Custom Java Class Requirements

Custom Java classes must satisfy certain requirements to be recognized by the Oracle SQLJ translator as valid host variable types, and to allow type-checking by the translator.

This section discusses Oracle-specific requirements of custom Java classes so they can support this functionality. Requirements for both CustomDatum implementations and SQLData implementations are covered.

Oracle Requirements for Classes Implementing CustomDatum

Oracle requirements for CustomDatum implementations are primarily the same for any kind of custom Java class but vary slightly depending on whether the class is for mapping to objects, object references, collections, or some other SQL type.

These requirements are as follows:

The class implements the oracle.sql.CustomDatum interface.
The class implements a method getFactory() that returns an oracle.sql.CustomDatumFactory object as follows:
```
public static oracle.sql.CustomDatumFactory getFactory();
```
The class has a constant _SQL_TYPECODE initialized to the oracle.jdbc.driver.OracleTypes typecode of the Datum subclass that toDatum() returns.

For custom object classes:
```
public static final int _SQL_TYPECODE = OracleTypes.STRUCT;
```
For custom reference classes:
```
public static final int _SQL_TYPECODE = OracleTypes.REF;
```
For custom collection classes:
```
public static final int _SQL_TYPECODE = OracleTypes.ARRAY;
```
For other uses, some other typecode might be appropriate. For example, for using a custom Java class to serialize and deserialize Java objects into or out of RAW fields in the database, a _SQL_TYPECODE of OracleTypes.RAW is used. See "Serializing Java Objects".

(The OracleTypes class simply defines a typecode, which is an integer constant, for each Oracle datatype. For standard SQL types, the OracleTypes entry is identical to the entry in the standard java.sql.Types type definitions class.)

For custom Java classes with _SQL_TYPECODE of STRUCT, REF, or ARRAY (in other words, for custom Java classes that represent objects, object references, or collections), the class has a constant that indicates the relevant user-defined type name.

Custom object classes and custom collection classes must have a constant (string) _SQL_NAME initialized to the SQL name you declared for the user-defined type, as follows:

public static final String _SQL_NAME = UDT name;

Custom object class example for a user-defined PERSON object:

public static final String _SQL_NAME = "PERSON";

or (to specify the schema, if that is appropriate):

public static final String _SQL_NAME = "SCOTT.PERSON";

Custom collection class example for a collection of PERSON objects, which you have declared as PERSON_ARRAY:

public static final String _SQL_NAME = "PERSON_ARRAY";

Custom reference classes must have a constant (string) _SQL_BASETYPE initialized to the SQL name you declared for the user-defined type being referenced, as follows:

public static final String _SQL_BASETYPE = UDT name;

Custom reference class example (for PERSON references):

public static final String _SQL_BASETYPE = "PERSON";

For other CustomDatum uses, specifying a UDT name is not applicable.

Notes:
The collection type name reflects the collection type, not the base type. For example, if you have declared a VARRAY or nested table type PERSON_ARRAY for PERSON objects, then the name of the collection type that you specify for the _SQL_NAME entry is PERSON_ARRAY, not PERSON.

Requirements for Classes Implementing SQLData

Classes that implement SQLData must satisfy the requirements for type map definitions as outlined in the SQLJ ISO standard. Alternatively, SQLData wrapper classes can identify the associated SQL object type through a public static final field. This non-standard functionality was introduced in SQLJ version 8.1.6 and continues to be supported. In both cases, you must run your SQLJ application under JDK 1.2 or later.

Notes:

SQLData, unlike CustomDatum, is for mapping structured object types only. It is not for object references, collections/arrays, or any other SQL types. If you are not using CustomDatum, then your only choices for mapping object references and collections are the weak types java.sql.Ref and java.sql.Array, respectively (or oracle.sql.REF and oracle.sql.ARRAY).

SQLData implementations require a JDK 1.2.x environment. Although Oracle JDBC supports JDBC 2.0 extensions under JDK 1.1.x through the oracle.jdbc2 package, Oracle SQLJ does not.

Mapping Specified in Type Map Resource

First, consider the mapping representation according to the SQLJ ISO-standard. Assume that Address, pack.Person, and pack.Manager.InnerPM (where InnerPM is an inner class of Manager) are three "wrapper classes" that implement java.sql.SQLData.

You must employ these classes only in statements that use explicit connection context instances of a declared connection context type. Assume, for example, that this type is called SDContext. Example:
```
Address               a =...;
pack.Person           p =...;
pack.Manager.InnerPM pm =...;
SDContext ctx = new SDContext(url,user,pwd,false);
#sql [ctx] { ... :a ... :p ... :pm ... }
```
The connection context type must have been declared using the WITH attribute typeMap that specifies an associated class implementing a java.util.PropertyResourceBundle. In the preceding example, SDContext might have been declared as follows.
```
#sql public static context SDContext with (typeMap="SDMap");
```
The type map resource must provide the mapping from SQL object types to corresponding Java classes that implement the java.sql.SQLData interface. This mapping is specified with entries of the following form:
```
class.<java_class_name>=STRUCT <sql_type_name>
```
The keyword STRUCT can also be omitted. In our example, the resource file SDMap.properties might contain the following entries.
```
class.Address=STRUCT SCOTT.ADDRESS
class.pack.Person=PERSON
class.pack.Manager$InnerPM=STRUCT PRODUCT_MANAGER
```
Although "." separates package and classname, you must use the character "$" to separate an inner class name.

This mechanism is more complicated than the non-standard alternative (discussed next). Furthermore, it is not possible to associate a type map resource with the default connection context. The advantage is that all the mapping information is placed in a single location--the type map resource.This means that the type mapping in an already compiled application can be easily adjusted at a later time, for example to accommodate new SQL types and Java wrappers in an expanding SQL-Java type hierarchy. Note, however, that in release 8.1.7, Oracle does not yet support inheritance for SQL object types.

Notes:

You must employ the SQLJ runtime library runtime12.zip to use this feature. Type maps are represented as java.util.Map objects. These are exposed in the SQLJ runtime API and, thus, cannot be supported by the JDK-independent generic library runtime.zip.

You must use the Oracle SQLJ runtime and Oracle-specific profile customization if your SQLData wrapper classes occur as OUT or INOUT parameters in SQLJ statements. This is because the SQL type of such parameters is required for registerOutParameter() by the Oracle JDBC driver. Furthermore, for OUT parameter type registration, the SQL type is "frozen in" by the type map in effect during translation.

The SQLJ type map is independent from any JDBC type map you may be using on the underlying connection. Thus, you must be careful if you are mixing SQLJ and JDBC code that both use SQLData wrappers. However, you can easily extract the type map in effect on a given SQLJ connection context:

ctx.getTypeMap();

Mapping Specified in Static Field of Wrapper Class

Alternatively, a class that implements SQLData can satisfy the following non-standard requirement.

The Java class declares the public static final String-valued field _SQL_NAME. This field defines the name of the SQL type that is being wrapped by the Java class.

In our example, the Address class would have the following field declaration:
```
public static final String _SQL_NAME="SCOTT.ADDRESS";
```
The following declaration would be in pack.Person:
```
public static final String _SQL_NAME="PERSON";
```
And the class pack.Manager.InnerPM would hold the following:
```
public static final String _SQL_NAME="PRODUCT_MANAGER";
```

Note that JPublisher always generates SQLData wrapper classes with the _SQL_NAME field. However, this field is ignored in SQLJ statements that reference a type map.

Notes:

If a class that implements the _SQL_NAME field is used in a SQLJ statement with an explicit connection context type and associated type map, then that type map is used, and the _SQL_NAME field is ignored, thereby simplifying migration of existing SQLJ programs to the new SQLJ ISO standard.

The static SQL-Java type correspondence specified in the _SQL_NAME field is independent from any JDBC type map you may be using on the underlying connection. Thus, you must be careful if you are mixing SQLJ and JDBC code that both use SQLData wrappers.

Compiling Custom Java Classes

You can include the .java file names for your custom Java classes (whether CustomDatum or SQLData implementations) on the SQLJ command line, together with your .sqlj file names. However, this is not necessary if the SQLJ -checksource flag is set to true (the default) and your CLASSPATH includes the directory where the custom Java source is located.

For example, if ObjectDemo.sqlj uses Oracle object types ADDRESS and PERSON and you have run JPublisher or otherwise produced custom Java classes for these objects, then you can run SQLJ as follows.

If -checksource=true (default) and CLASSPATH includes custom Java source location:

% sqlj ObjectDemo.sqlj

Or, if -checksource=false:

% sqlj ObjectDemo.sqlj Address.java AddressRef.java Person.java PersonRef.java

Alternatively, you can use your Java compiler to compile the custom Java source files directly. If you do this, you must do it prior to translating the .sqlj file.

Running the SQLJ translator is discussed in Chapter 8, "Translator Command Line and Options". For more information about the -checksource flag, see "Source Check for Type Resolution (-checksource)".

Note:
Because CustomDatum implementations rely on Oracle-specific features, SQLJ will report numerous portability warnings if you do not use the translator portability setting -warn=noportable (the default). For information about the -warn flag, see "Translator Warnings (-warn)".

Reading and Writing Custom Data

Through the use of custom Java class instances, Oracle SQLJ and JDBC allow you to read and write user-defined types as though they are built-in types. Exactly how this is accomplished is transparent to the user.

For the mechanics of how data is read and written, for both CustomDatum implementations and SQLData implementations, see the Oracle8i JDBC Developer's Guide and Reference.

Additional Uses for CustomDatum Implementations

To this point, discussion of custom Java classes has been for use as one of the following:

wrappers for SQL objects--custom object classes, for use with oracle.sql.STRUCT instances from the database
wrappers for SQL references--custom reference classes, for use with oracle.sql.REF instances from the database
wrappers for SQL collections--custom collection classes, for use with oracle.sql.ARRAY instances from the database

It might be useful, however, to provide custom Java classes to wrap other oracle.sql.* types as well, for customized conversions or processing. You can accomplish this with classes that implement CustomDatum (but not SQLData), as in the following examples:

Perform encryption and decryption or validation of data.
Perform logging of values that have been read or are being written.
Parse character columns (such as character fields containing URL information) into smaller components.
Map character strings into numeric constants.
Map data into more desirable Java formats (such as mapping a DATE field to java.util.Date format).
Customize data representation (for example, data in a table column is in feet, but you want it represented in meters after it is selected).
Serialize and deserialize Java objects--into or out of RAW fields, for example

This last use is further discussed in "Serializing Java Objects".

"General Use of CustomDatum--BetterDate.java" provides an example of a class (BetterDate) that implements CustomDatum and can be used instead of java.sql.Date to represent dates.

Note:
This sort of functionality is not possible through the SQLData interface, as SQLData implementations can wrap only structured object types.