Objects and Collections

Serializing Java Objects

You may need to write and read instances of Java objects to or from the database. In some cases, it can be advantageous to define a SQL object type that corresponds to your Java class, and use the mechanisms of mapping Custom Java Classes described previously. This permits full SQL queryability on your Java objects.

However, in some cases, you may want to store Java objects "as-is" and retrieve them later. There are two ways to accomplish this:

You can use a non-standard extension to the type map facility to map a serializable Java class to database columns of type RAW or BLOB.
You can use the CustomDatum facility to define a wrapper class SerializableDatum that can be employed to store Java serializable objects in RAW columns.

Serializing Java Classes to RAW and BLOB Columns

This section discusses the steps in serializing Java classes.

Defining a Type Map for Serializable Classes

If you want to store instances of Java classes directly in RAW or BLOB columns, then the following non-standard requirements must be met. Assume that SAddress, pack.SPerson, and pack.Manager.InnerSPM (where InnerSPM is an inner class of Manager) are serializable Java classes. In other words, these classes implement java.io.Serializable.

You must employ the classes only in statements that use explicit connection context instances of a declared connection context type. Assume, for example, that this type is called SerContext. For example:
```
SAddress               a =...;
pack.SPerson           p =...;
pack.Manager.InnerSPM pm =...;
SerContext ctx = new SerContext(url,user,pwd,false);
#sql [ctx] { ... :a ... :OUT p ... :INOUT pm ... }
```
Note that in SQLJ statements the serializable Java objects can be transparently read and written as if they were built-in types.
The connection context type must have been declared using the WITH attribute typeMap that specifies an associated class implementing a java.util.PropertyResourceBundle. In our example, SerContext might have been declared as follows.
```
#sql public static context SerContext with (typeMap="SerMap");
```
The type map resource must provide non-standard mappings from RAW or BLOB columns to the serializable Java classes. This mapping is specified with entries of the following form--depending on whether the Java class is mapped to a RAW or a BLOB column.
```
oracle-class.<java_class_name>=JAVA_OBJECT RAW
oracle-class.<java_class_name>=JAVA_OBJECT BLOB
```
The keyword oracle-class marks this as an Oracle-specific extension. In our example, the resource file SerMap.properties might contain the following entries.
```
oracle-class.Address=JAVA_OBJECT RAW
oracle-class.pack.Person=JAVA_OBJECT BLOB
oracle-class.pack.Manager$InnerPM=JAVA_OBJECT RAW
```
Although "." separates package and classname, you must use the character "$" to separate an inner class name.

Note that this Oracle-specific extension, as well as standard SQLData type map entries, can be placed in the same type map resource.

Serializing in this manner works for all Oracle SQLJ runtime libraries, including runtime.zip, runtime11.zip, and runtime12.zip. This is unlike the SQLData support, which mandates runtime12.zip.

Limitations on Serializing Java Objects

You should be aware of the effect of serialization. If two objects, A and B, share the same object, C, then upon serialization and subsequent deserialization of A and B, each will point to its own clone of the object C, and sharing is broken.

In addition, note that for a given Java class, you can declare only one kind of serialization: either into RAW or into BLOB. The SQLJ translator can check only that the actual usage conforms to either RAW or BLOB.

RAW columns are limited in size--you may experience runtime errors if the actual size of the serialized Java object exceeds the size of the column.

Although column size is much less restrictive for BLOB columns, writing a serialized Java object to a BLOB column in the database is--as of JDBC release 8.1.7--supported only in the OCI JDBC driver. On the other hand, retrieving a serialized object from a BLOB column is supported by all Oracle JDBC drivers.

Finally, treating serialized Java objects this way is an Oracle-specific extension and requires the Oracle SQLJ runtime as well as Oracle-specific profile customization. Note that future versions of Oracle may support SQL types that directly encapsulate Java serialized objects -- these are described as JAVA_OBJECT SQL types in JDBC 2.0. At that point, you can replace each of the BLOB and RAW designations by the names of their corresponding JAVA_OBJECT SQL types, and you can drop the oracle- prefix on the entries.

Note:
The implementation of this particular serialization mechanism does not use JDBC type maps. The map (to BLOB or to RAW) is hardcoded in the Oracle profile customization at translate time.

SerializableDatum - A CustomDatum Implementation

"Additional Uses for CustomDatum Implementations" includes examples of situations where you might want to define a custom Java class that maps to some oracle.sql.* type other than the oracle.sql.STRUCT, oracle.sql.REF, or oracle.sql.ARRAY type.

An example of such a situation is if you want to serialize and deserialize Java objects into and out of RAW fields in the database, with a custom Java class that maps to the oracle.sql.RAW type.

This section presents an example of such an application, creating a class, SerializableDatum, that implements the CustomDatum interface and follows the general form of custom Java classes, as described in "Custom Java Classes".

The example starts with a step-by-step approach to the development of SerializableDatum, followed by the complete sample code.

Note:
This application uses classes from the java.io, java.sql, oracle.sql, and oracle.jdbc.driver packages. The import statements are not shown here.

Begin with a skeleton of the class.

public class SerializableDatum implements CustomDatum
{
   // <Client methods for constructing and accessing the Java object>

   public Datum toDatum(OracleConnection c) throws SQLException
   {
      // <Implementation of toDatum()>
   }

   public static CustomDatumFactory getFactory()
   {
      return FACTORY;
   }

   private static final CustomDatumFactory FACTORY =
           // <Implementation of a CustomDatumFactory for SerializableDatum>

   // <Construction of SerializableDatum from oracle.sql.RAW>

   public static final int _SQL_TYPECODE = OracleTypes.RAW;
}

SerializableDatum does not implement the CustomDatumFactory interface, but its getFactory() method returns a static member that implements this interface.

The _SQL_TYPECODE is set to OracleTypes.RAW because this is the datatype being read from and written to the database. The SQLJ translator needs this typecode information in performing online type-checking to verify compatibility between the user-defined Java type and the SQL type in the database.

Define client methods that perform the following:

Create a SerializableDatum object.
Populate a SerializableDatum object.

Retrieve data from a SerializableDatum object.

// Client methods for constructing and accessing a SerializableDatum

private Object m_data;
public SerializableDatum()
{
   m_data = null;
}
public void setData(Object data)
{
   m_data = data;
}
public Object getData()
{
   return m_data;
}

Implement a toDatum() method that serializes data from a SerializableDatum object to an oracle.sql.RAW object. The implementation of toDatum() must return a serialized representation of the object in the m_data field as an oracle.sql.RAW instance.

// Implementation of toDatum()

try {
   ByteArrayOutputStream os = new ByteArrayOutputStream();
   ObjectOutputStream oos = new ObjectOutputStream(os);
   oos.writeObject(m_data);
   oos.close();
   return new RAW(os.toByteArray());
} catch (Exception e) {
  throw new SQLException("SerializableDatum.toDatum: "+e.toString()); }

Implement data conversion from an oracle.sql.RAW object to a SerializableDatum object. This step deserializes the data.

// Constructing SerializableDatum from oracle.sql.RAW

private SerializableDatum(RAW raw) throws SQLException
{
   try {
      InputStream rawStream = new ByteArrayInputStream(raw.getBytes());
      ObjectInputStream is = new ObjectInputStream(rawStream);
      m_data = is.readObject();
      is.close();
   } catch (Exception e) {
     throw new SQLException("SerializableDatum.create: "+e.toString()); }
}

Implement a CustomDatumFactory. In this case, it is implemented as an anonymous class.

// Implementation of a CustomDatumFactory for SerializableDatum

new CustomDatumFactory()
{
   public CustomDatum create(Datum d, int sqlCode) throws SQLException
   {
      if (sqlCode != _SQL_TYPECODE)
      {
         throw new SQLException("SerializableDatum: invalid SQL type "+sqlCode);
      }
      return (d==null) ? null : new SerializableDatum((RAW)d);
   }
};

SerializableDatum in SQLJ Applications

Given the SerializableDatum class created in the preceding section, this section shows how to use an instance of it in a SQLJ application, both as a host variable and as an iterator column.

Presume the following table definition:

CREATE TABLE PERSONDATA (NAME VARCHAR2(20) NOT NULL, INFO RAW(2000));

SerializableDatum as Host Variable

Following is an example of using a SerializableDatum instance as a host variable.

...
SerializableDatum pinfo = new SerializableDatum();
pinfo.setData (
   new Object[] {"Some objects", new Integer(51), new Double(1234.27) } );
String pname = "MILLER";
#sql { INSERT INTO persondata VALUES(:pname, :pinfo) };
...

SerializableDatum in Iterator Column

Here is an example of using SerializableDatum as a named iterator column.

Declaration:

#sql iterator PersonIter (SerializableDatum info, String name);

Executable code:

PersonIter pcur;
#sql pcur = { SELECT * FROM persondata WHERE info IS NOT NULL };
while (pcur.next())
{
   System.out.println("Name:" + pcur.name() + " Info:" + pcur.info());
}
pcur.close();
...

SerializableDatum (Complete Class)

This section shows you the entire SerializableDatum class previously developed in step-by-step fashion.

import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class SerializableDatum implements CustomDatum
{
// Client methods for constructing and accessing a SerializableDatum

   private Object m_data;
   public SerializableDatum()
   {
      m_data = null;
   }
   public void setData(Object data)
   {
      m_data = data;
   }
   public Object getData()
   {
      return m_data;
   }

// Implementation of toDatum()

   public Datum toDatum(OracleConnection c) throws SQLException
   {

      try {
         ByteArrayOutputStream os = new ByteArrayOutputStream();
         ObjectOutputStream oos = new ObjectOutputStream(os);
         oos.writeObject(m_data);
         oos.close();
         return new RAW(os.toByteArray());
      } catch (Exception e) {
        throw new SQLException("SerializableDatum.toDatum: "+e.toString()); }
   }

   public static CustomDatumFactory getFactory()
   {
      return FACTORY;
   }

// Implementation of a CustomDatumFactory for SerializableDatum

   private static final CustomDatumFactory FACTORY =
   
      new CustomDatumFactory()
      {
         public CustomDatum create(Datum d, int sqlCode) throws SQLException
         {
            if (sqlCode != _SQL_TYPECODE)
            {
               throw new SQLException(
                  "SerializableDatum: invalid SQL type "+sqlCode);
            }
            return (d==null) ? null : new SerializableDatum((RAW)d);
         }
      };

// Constructing SerializableDatum from oracle.sql.RAW

   private SerializableDatum(RAW raw) throws SQLException
   {
      try {
         InputStream rawStream = new ByteArrayInputStream(raw.getBytes());
         ObjectInputStream is = new ObjectInputStream(rawStream);
         m_data = is.readObject();
         is.close();
      } catch (Exception e) {
        throw new SQLException("SerializableDatum.create: "+e.toString()); }
   }

   public static final int _SQL_TYPECODE = OracleTypes.RAW;
}