SQL-J Language Reference
Page 112 of 121

Java Data Types (User-Defined Data Types)

You store Java objects in columns using serialization. You define a column to hold a serialized form of Java class or interface, which allows objects of that Java type and any of its implementors or subtypes to be serialized into the column.

To store Java objects, you can insert a row of values using dynamic parameters and pass in the objects from the Java application, copy objects from other tables, or invoke methods that return Java objects.

For general information about dynamic parameters, see Dynamic Parameters. When using dynamic parameters with Java data types, use the setObject method of JDBC's java.sql.PreparedStatement to set the values of the parameters.

Once objects are stored, you can apply method invocation or field access on the stored value of a column holding a Java class to access the methods or fields of the object it holds, respectively.

Syntax for Column Definition

SERIALIZE ( JavaClassName | ClassAlias )

Inserting an object or directly updating the column serializes the object. Accessing the column deserializes the object.

Requirements for Serialization

The class or interface must:

  • Implement the interface java.io.Serializable.
  • Be declared public.
  • Be available to the JVM in which it is running (i.e., be installed and accessible on the deployment machine's class path at the time the column is created; see Deploying Java Classes for Use as Java Data Types in the Cloudscape Developer's Guide).

For detailed information on how to create serializable Java data types that you can store, see Programming Serializable Classes

Built-In Java Data Types

Cloudscape comes with several Java data types already built into the system and used in the system tables. These built-in Java data types come with class aliases and have public interfaces available to you. For more information, see Types Used in System Tables and Chapter 4, "Cloudscape System Tables".

Assignability

You can store any object that is assignable to the named class or interface in the column. In general, if Java allows a reference to the named class to point to an object, you can store that object in the column. For example, you can store a subclass of the named class in the column.

Java Data Type Examples

CREATE TABLE Cities
    (city SERIALIZE(JBMSTours.serializabletypes.City) )

-- The dynamic parameter ? is expected to be
-- a JBMSTours.City object when the INSERT is executed.
-- use setObject in Java to set the value for the question mark
INSERT INTO Cities VALUES(?)

--Use a method call on a stored object:
SELECT city.isTropical()
FROM Cities
WHERE city.getName() = 'Seattle'

Implications of Serialization

For information about serialization and the java.io.Serializable API, see Programming Serializable Classes in the Cloudscape Developer's Guide.

When an object stored in the database is serialized into memory, the resulting in-memory object is a copy of the object in the database. Subsequent serializations of the now-in-memory object make separate copies of the object. Changes that are made to a copy of an object through method invocation are not automatically written back to the database. You can update an object stored in a column using the UPDATE statement, or delete the old row and insert a new row with a new object in its place, but doing so does not affect the value of any copy of the original object that is currently in memory.

Cloudscape stores the serialized object, not the Java class definition. If you change a class definition, the next time you start Cloudscape, Cloudscape uses the new class definition for method invocation.

Modifying classes that are used for column types must follow Java's serialization rules for such modifications. Changes that do not follow these rules generate the same exceptions that come out of serialization. For further information about versioning of Java classes, see Modifying Classes (Java Versioning) in the Cloudscape Developer's Guide

Non-Serializable Java Data Types

Cloudscape implicitly requires that Java data types be serializable. In most cases, a Java data type must be serializable:

  • to be stored in a column
  • to be returned to a remote client in a client/server environment
  • to be used in any operation that requires sorting, such as aggregates, the DISTINCT operation, and self-referencing inserts and updates

Cloudscape enforces the serialization requirement only for values stored in tables. It does not enforce the serialization requirement for other values. For example, the following statement does not generate an error, even though the object is not serializable:

VALUES NEW java.lang.Object()

In an embedded environment, the calling application would be able to access the value returned by the statement, because it happens to be running in the same JVM as Cloudscape. However, in a client/server environment, the application would not be able to access the value returned by the statement.

It is technically possible to work with non-serializable objects, and sometimes useful. For example, you might want use Cloudscape's virtual table interface to work with java.awt.Images and then display them locally in Cloudview. This is a very rare case, and, of course, you would not be able to store them, sort them, or access them remotely.

You can always execute methods on non-serializable objects that return built-in or serializable data types. See Method Invocation.

Java Data Types and Equality Operations

The operators = and <> are allowed on all Java data types.

NOTE: Correct results require that the Java data types override equals(Object o) and hashCode() from java.lang.Object. If a Java data type does not correctly implement these methods, the results of expressions using the = and <> operators with objects of that type are not guaranteed to be correct.

For more information, see Notes on Implementing the equals and hashCode Methods in the Cloudscape Developer's Guide.

To perform the equality or non equality operation, Cloudscape calls the either of the following methods off the object:

  • equals (Object o)

    if the Java data type is not orderable.

  • compareTo (Object o)

    if the Java data type is orderable

(A description of orderable Java data types follows).

Orderable Java Data Types

Orderable Java data types are Java data types that fulfill some minimal requirements. If a Java data type is orderable, you can perform some SQL ordering and comparison operations on them and define indexes on them. (Those requirements are listed in Programming Orderable Classes in the Cloudscape Developer's Guide). You cannot perform these operations on columns that store non-orderable Java data types.

(In the examples that follow, customized_tour stores a non-orderable Java data type, and city an orderable Java data type.)

These operations include the following items:

  • Ordering and MAX/MIN aggregate operations

    Ordering operations include DISTINCT, GROUP BY, UNION, and ORDER BY.

    -- doesn't work; the Java data type is not orderable
    SELECT customized_tour
    FROM CustomizedTours
    ORDER BY customized_tour

    -- works; the Java data type is orderable
    SELECT city FROM Cities
    ORDER BY city

    -- works
    SELECT MAX(city) FROM Cities

    Note that you can always order by a method call if it returns a built-in or orderable data type, however.

    SELECT DISTINCT customized_tour.toString()
    FROM CustomizedTours

  • Comparisons <, >, <=, >=

    -- doesn't work; the Java data type is not orderable
    SELECT * FROM CustomizedTours WHERE customized_tour < ?

    -- works; the type is orderable
    SELECT city
    FROM Cities
    WHERE city < (SELECT city FROM cities WHERE city_id = 35)

  • Creating indexes or primary, foreign key, or unique constraints

    A non-orderable Java data type cannot be the declared type for a column in an index, primary key, foreign key or unique constraint. For example, the following statement throws an exception:

    -- doesn't work; the type is not orderable
    CREATE TABLE Tours (tour SERIALIZE(tour) PRIMARY KEY)

-- works
CREATE TABLE Cities (city SERIALIZE(City) PRIMARY KEY)

Orderable Java Data Types and Indexes

NOTE: As stated in Programming Orderable Classes in the Cloudscape Developer's Guide, orderable Java data types require correct implementation of the compareTo, equals, and hashCode methods. If those methods are not implemented correctly, Cloudscape does not guarantee the correctness of indexes created on those types. Incorrect indexes lead to bizarre behavior and incorrect results. If you suspect a problem with the implementation of any of these methods, drop the index first. Re-create the index only when you are sure the methods are implemented correctly.

You cannot have indexes on columns that store orderable Java data types if those Java classes are loaded from the database. They must be loaded from the class path, instead.

NOTE: Cloudscape indexes have the restriction that the length of the key columns for an index must be less than half the page size of the index. Be sure to make the page size of an index large enough to accommodate this restriction.

CREATE BTREE INDEX c1 ON Cities(City)
PROPERTIES cloudscape.storage.pageSize=16384