Contents | Prev | Next | JDBCTM Guide: Getting Started

## 9 Customizing SQL Types

This chapter describes the support that the JDBC API provides for customizing the mapping of SQL structured and distinct types into Java classes. The customization mechanism involves minimal extensions to the JDBC API. The new functionality is an extension of the existing `getObject()` and `setObject()` mechanism.

### 9.1 The type mapping

An instance of `java.util.Map` is used to hold a custom mapping between SQL user- defined types-structured and distinct types-and Java classes. The`java.util.Map` interface is new in the JDK 1.2 and replaces `java.util.Dictionary`. Such an object is termed a type-map object. A type-map object implements a function from SQL names of user-defined types to objects of type `java.lang.Class`. A type-map object determines the class from which to construct an object to contain data of a given SQL user-defined type.

Each `Connection` has an associated type-map object. The type-map object contains type-mappings for translating data of SQL user-defined types in operations on that connection. Methods are provided for getting and setting a connection's type map. For example,

>     java.util.Map map = con.getTypeMap();
>     con.setTypeMap(map);

The `Connection.getTypeMap()` method returns the type-map object associated with a connection, while `Connection.setTypeMap()` can be used to set a new type mapping.

The mapping mechanism is quite flexible. If a connection's type mapping is not explicitly initialized by the JDBC application, then the default mappings described in Chapter 8 are used by operations on the connection. If a custom mapping is inserted into the type-map for SQL type `type-name`, then all operations on the connection will use this custom mapping for values of type `type-name`. Finally, we note that type-map objects may even be provided explicitly when calling certain `getXXX()` and`setXXX()` methods to override the custom or default mapping associated with a `Connection`.

### 9.2 Java class conventions

A Java class which appears in a custom type-map must implement a new interface- `java.sql.SQLData`. The `SQLData` interface contains methods that convert instances of SQL user-defined types to Java class instances, and vice versa. For example, the method `SQLData.readSQL()` reads a stream of data values and builds a Java object, while method `SQLData.writeSQL()` writes a sequence of values from a Java object to a stream. We anticipate that these methods will typically be generated by a tool which understands the database schema.

This stream-based approach for exchanging data between SQL and the Java programming language is conceptually similar to Java object Serialization. The data are read from and written to an SQL data stream provided by the JDBC driver. The SQL data stream may be implemented on various network protocols and data-formats. It may be implemented on any logical data-representation in which the leaf SQL data items (of which SQL structured types are composed) can be read from (written to) the data stream in a "depth-first" traversal of the structured types. That is, the attributes of an SQL structured type appear in the stream in the order in which they are declared in that type, and each (perhaps structured) attribute value appears fully (its structure recursively elaborated) in the stream before the next attribute. For data of SQL structured types that use inheritance, the attributes must appear in the stream in the order that they are inherited. That is, the attributes of a super-type must appear before attributes of a sub- type. If multiple inheritance is used, then the attributes of super-types should appear in the stream in the order in which the super-types are listed in the type declaration. This protocol does not require the database server to have any knowledge of the Java programming language.

### 9.3 Streams of SQL data

This section describes the stream interfaces, SQLInput and SQLOutput, which support customization of the SQL to Java type mapping.

#### 9.3.1 Retrieving data

When data of SQL structured and distinct types are retrieved from the database, they "arrive" in a stream implementing the `SQLInput` interface. The `SQLInput` interface contains methods for reading individual data values sequentially from the stream. The example below illustrates how a `SQLInput` stream can be used to provide values for the fields of an `SQLData` object. The `SQLData` object-the `this` object in the example- contains three persistent fields: a `String s`, a `Blob blob`, and an `Employee emp`.

>     this.str = sqlin.readString();
>     this.blob = sqlin.readBlob();
>     this.emp = (Employee)sqlin.readObject();

The `SQLInput.readString()` method reads a `String` value from the stream. The `SQLInput.readBlob() method` can be used to retrieve a `Blob` value from the stream. By default, the `Blob` interface is implemented using an SQL locator, so calling `readBlob()` doesn't materialize the blob contents on the client. The `SQLInput.readObject()` method can be used to return an object reference from the stream. In the example, the `Object` returned is narrowed to an `Employee`.

There are a number of additional `readXXX()` methods defined on the `SQLInput` interface for reading each of the types. The `SQLInput.wasNull()` method can be called to check if the value returned by a `readXXX()` method was null.

#### 9.3.2 Storing data

When an `SQLData` object is passed to a driver as an input parameter via a `setXXX()` method, the JDBC driver calls the object's `SQLData.writeSql()` method to obtain a stream representation of the contents of the object. Method `writeSQL()` writes data from the object to an `SQLOutput` stream as the representation of an SQL user-defined type. Method `writeSQL()` will typically have been generated by some tool from an SQL type definition. The example below illustrates use of the `SQLOutput` stream object.

>     sqlout.writeString(this.str);
>     sqlout.writeBlob(this.blob);
>     sqlout.writeObject(this.emp);

The example shows how the contents of an `SQLData` object can be written to an `SQLOutput` stream. The `SQLData` object-the `this` object in the example-contains three persistent fields: a `String s`, a `Blob blob`, and an `Employee emp`. Each field is written in turn to the `SQLOutput` stream, `sqlout`. The `SQLOutput` interface contains additional methods for writing each of the types defined in the JDBC API.

### 9.4 Examples

#### 9.4.1 Example of SQL structured type

The following SQL example defines structured types PERSON, FULLNAME, and RESIDENCE. It defines tables with rows of types PERSON and RESIDENCE, and inserts a row into each, so that one row references another. Finally, it queries the table.

>     CREATE TYPE RESIDENCE
>     (
>     	DOOR NUMERIC(6),
>     	STREET VARCHAR(100),
>     	CITY VARCHAR(50),
>     	OCCUPANT REF(PERSON)
>     );

>     CREATE TYPE FULLNAME
>     (
>     	FIRST VARCHAR(50),
>     	LAST VARCHAR(50)
>     );

>     CREATE TYPE PERSON
>     (
>     	NAME FULLNAME,
>     	HEIGHT NUMERIC,
>     	WEIGHT NUMERIC,
>     	HOME REF(RESIDENCE)
>     );

>     CREATE TABLE HOMES OF RESIDENCE (OID REF(RESIDENCE)
>     	VALUES ARE SYSTEM GENERATED);

>     CREATE TABLE PEOPLE OF PERSON (OID REF(PERSON)
>     	VALUES ARE SYSTEM GENERATED);

>     INSERT INTO PEOPLE (SURNAME, HEIGHT, WEIGHT) VALUES
>     (
>     	FULLNAME('DAFFY', 'DUCK'),
>     	4,
>     	58
>     );

>     INSERT INTO HOMES (DOOR, STREET, CITY, OCCUPANT) VALUES
>     (
>     	1234,
>     	'CARTOON LANE',
>     	'LOS ANGELES',
>     	(SELECT OID FROM PEOPLE P WHERE P.NAME.FIRST = 'DAFFY')
>     );

>     UPDATE PEOPLE SET HOME = (SELECT OID FROM HOMES H WHERE
>             H.OCCUPANT->NAME.FIRST = 'DAFFY') WHERE
>             FULLNAME.FIRST = 'DAFFY'

The example above constructs three structured type instances, one each of types PERSON, FULLNAME, and RESIDENCE. A FULLNAME attribute is embedded in a PERSON. The PERSON and RESIDENCE instances are stored as rows of tables, and reference each other via Ref attributes.

The Java classes below represent the SQL structured types given above. We expect that such classes will typically be generated by a tool that reads the definitions of those structured types from the catalog tables, and, subject to customizations that a user of the tool may provide for name-mappings and type-mappings of primitive fields, will generate Java classes like those shown below.

Note: The JDBC API does not provide a standard API for accessing the metadata needed by a mapping tool. Providing this type of metadata introduces many subtle dependencies on the SQL99 type model, so it has been left out for now.

In each class below, method `SQLData.readSQL()` reads the attributes in the order that they appear in the definition of the corresponding structured types in the database (i.e., in "row order, depth-first" order, where the complete structure of each attribute is read, recursively, before the next attribute is read). Similarly, `SQLData.writeSQL()` writes the data to the stream in that order.

>     public class Residence implements SQLData {
>         public int door;
>         public String street;
>         public String city;
>         public Ref occupant;

>         private String sql_type;
>         public String getSQLTypeName() { return sql_type; }

>         public void readSQL (SQLInput stream, String type)
>     	throws SQLException {
>           sql_type = type;
>           door = stream.readInt();
>           street = stream.readString();
>           city = stream.readString();
>           occupant = stream.readRef();
>         }

>         public void writeSQL (SQLOutput stream) throws SQLException {
>           stream.writeInt(door);
>           stream.writeString(street);
>           stream.writeString(city);
>           stream.writeRef(occupant);
>         }
>     }

>     public class Fullname implements SQLData {
>         public String first;
>         public String last;

>         private String sql_type;
>         public String getSQLTypeName() { return sql_type; }

>         public void readSQL (SQLInput stream, String type)
>     	throws SQLException {
>           sql_type = type;
>           first = stream.readString();
>           last = stream.readString();
>         }

>         public void writeSQL (SQLOutput stream) throws SQLException {
>           stream.writeString(first);
>           stream.writeString(last);
>         }
>     }

>     public class Person implements SQLData {
>         Fullname name;
>         float height;
>         float weight;
>         Ref home;

>         private String sql_type;
>         public String getSQLTypeName() { return sql_type; }

>         public void readSQL (SQLInput stream, String type)
>     	throws SQLException {
>           sql_type = type;
>           name = (Fullname)stream.readObject();
>           height = stream.readFloat();
>           weight = stream.readFloat();
>           home = stream.readRef();
>         }

>         public void writeSQL (SQLOutput stream)
>         	throws SQLException {
>           stream.writeObject(name);
>           stream.writeFloat(height);
>           stream.writeFloat(weight);
>           stream.writeRef(home);
>         }
>     }

The following method uses those classes to materialize data from the tables HOMES and PEOPLE that were defined earlier:

>     import java.sql.*;
>     .
>     .
>     .

>     public void demo (Connection con) throws SQLException {

>       // setup mappings for the connection
>       try {
>         java.util.Map map = con.getTypeMap();
>         map.put("S.RESIDENCE", Class.forName("Residence"));
>         map.put("S.FULLNAME", Class.forName("Fullname"));
>         map.put("S.PERSON", Class.forName("Person"));
>       }
>       catch (ClassNotFoundException ex) {}

>       PreparedStatement pstmt;
>       ResultSet rs;

>       pstmt = con.prepareStatement("SELECT OCCUPANT FROM HOMES");
>       rs = pstmt.executeQuery();
>       rs.next();
>       Ref ref = rs.getRef(1);

>       pstmt = con.prepareStatement(
>                 "SELECT FULLNAME FROM PEOPLE WHERE OID = ?");
>       pstmt.setRef(1, ref);
>       rs = pstmt.executeQuery();
>       rs.next();
>       Fullname who = (Fullname)rs.getObject(1);

>       // prints "Daffy Duck"
>       System.out.println(who.first + " " + who.last);
>     }

#### 9.4.2 Mirroring SQL inheritance in the Java programming language

SQL structured types may be defined to form an inheritance hierarchy. For example, consider SQL type STUDENT that inherits from PERSON:

>     CREATE TYPE PERSON AS OBJECT (NAME VARCHAR(20), BIRTH DATE);

>     CREATE TYPE STUDENT AS OBJECT EXTENDS PERSON (GPA NUMERIC(4,2));

The following Java classes can represent data of those SQL types. Class `Student` extends `Person`, mirroring the SQL type hierarchy. Methods `SQLData.readSQL()` and `SQLData.writeSQL()` of the subclass cascades each call to the corresponding method in its super-class, in order to read or write the super-class attributes before reading or writing the subclass attributes.

>        import java.sql.*;
>        ...
>        public class Person implements SQLData {
>          public String name;
>          public Date birth;

>          private String sql_type;
>          public String getSQLTypeName() { return sql_type; }

>          public void readSQL (SQLInput data, String type)
>     	throws SQLException {
>            sql_type = type;
>            name = data.readString();
>            birth = data.readDate();
>          }

>          public void writeSQL (SQLOutput data)
>          	throws SQLException {
>            data.writeString(name);
>            data.writeDate(birth);
>          }
>        }

>        public class Student extends Person {
>          public float GPA;

>          private String sql_type;
>          public String getSQLTypeName() { return sql_type; }

>          public void readSQL (SQLInput data, String type)
>     	throws SQLException {
>            sql_type = type;
>            super.readSQL(data, type);
>            GPA = data.readFloat();
>          }

>          public void writeSQL (SQLOutput data)
>          throws SQLException {
>            super.writeSQL(data);
>            data.writeFloat(GPA);
>          }
>        }

The Java class hierarchy need not mirror the SQL inheritance hierarchy. For example, class `Student` above could have been declared without a super-class. In this case, `Student` could contain fields to hold the inherited attributes of the SQL type STUDENT as well as the attributes declared by STUDENT itself..

#### 9.4.3 Example mapping of SQL distinct type

An SQL distinct type, MONEY, and a Java class Money that represents it:

>     -- SQL definition
>     CREATE TYPE MONEY AS NUMERIC(10,2);

>     // definition
>     public class Money implements SQLData {

>       public java.math.BigDecimal value;

>       private String sql_type;
>       public String getSQLTypeName() { return sql_type; }

>       public void readSQL (SQLInput stream, String type)
>     	throws SQLException {
>         sql_type = type;
>         value = stream.readBigDecimal();
>       }

>       public void writeSQL (SQLOutput stream) throws SQLException {
>         stream.writeBigDecimal(value);
>       }
>     }

### 9.5 Generality of the approach

Users have great flexibility to customize the Java classes that represent SQL structured and distinct types. They control the mappings of built-in SQL attribute types to Java field types. They control the mappings of SQL names (of types and attributes) to Java names (of classes and fields). Users may add (to Java classes that represent SQL types) fields and methods that implement domain-specific functionality. Users can generate beans as the classes that represent SQL types.

A user can even map a single SQL type to different Java classes, depending on arbitrary conditions. To do that, the user must customize the implementation of `SQLData.readSQL()` to construct and return objects of different classes under different conditions.

Similarly, the user can map a single SQL value to a graph of Java objects. Again, that is accomplished by customizing the implementation of `SQLData.readSQL()` to construct multiple objects and distribute the SQL attributes into fields of those objects.

A customization of the `SQLData.readSQL()` method could populate the type-map object incrementally. And so on. We believe that these kinds of flexibility will allow users to map SQL types appropriately for different kinds of applications.

### 9.6 NULL data

An application uses the existing `getObject()` and `setObject()` mechanism to retrieve and store `SQLData` values. We note that when the second parameter, `x`, of method`PreparedStatement.setObject()` has the value `null`, then the driver executes the SQL statement as if the SQL literal NULL had appeared in place of that parameter of the statement:

>       void setObject (int i, Object x) throws SQLException;

When parameter `x` is null, there is no enforcement that the corresponding argument expression is of a Java type that could successfully be passed to that SQL statement if its value were non-null. The Java programming language null carries no type information. For example, a null Java programming language variable of class `AntiMatter` could be passed as an argument to an SQL statement that requires a value of SQL type `MATTER`, and no error would result, even though the relevant type-map object did not permit the translation of `MATTER` to `AntiMatter`.

### 9.7 Summary

Chapters 8 and 9 presented extensions to support new categories of SQL types. The extensions have these properties:

- All of the new SQL types are handled with uniform and extensible interfaces, which may be staged into the JDBC API piecemeal.
- Minimal mechanism is added to the API. An implementation does little more than transfer control to methods `SQLData.readSQL()` and `SQLData.writeSQL()` of classes that have been generated to represent the SQL types.
- The extensions are based on existing interfaces `java.io.Serializable`, `java.io.DataInput`, `java.io.DataOutput`, `java.sql.ResultSet`, and `java.sql.PreparedStatement`
- Great flexibility is given to writers of database tools to add value by customizing the Java classes that represent SQL data.

Contents | Prev | Next

Copyright © 1996-1999 Sun Microsystems, Inc. All rights reserved.