JDK 5 Documentation v1.2.2, Java 2 SDK 英文文档

JDBC^TM Guide: Getting Started

9 Customizing SQL Types

This chapter describes the support that the JDBC 2.0 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 JDBC 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 Java 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 Java.

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 JDBC 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 JDBC 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 JDBC 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 JDBC types.

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 an SQL-to-Java mapping 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: JDBC 2.0 does not provide a standard API for accessing the metadata needed by a SQL-to-Java mapping tool. Providing this type of metadata introduces many subtle dependencies on the SQL3 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 Java

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

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


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

// Java 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 JavaBeans 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 of JDBC to map SQL types appropriately for different kinds of applications.

9.6 NULL data

A JDBC application uses the existing getObject() and setObject() mechanism of JDBC to retrieve and store SQLData values. We note that when the second parameter, x, of method PreparedStatement.setObject() has the Java value null, then JDBC 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. Java null carries no type information. For example, a null Java 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 JDBC 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 JDBC piecemeal.
Minimal mechanism is added to JDBC. A JDBC 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

jdbc@eng.sun.com or jdbc-business@eng.sun.com