9 Customizing SQL Types

9.1     The type mapping

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 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

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

9.3.1 Retrieving data

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

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

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

9.4     Examples

9.4.1 Example of SQL structured type

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

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

-- 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

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

  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

• 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.