Customized Type Mapping Examples:JAVA3

This section gives examples of SQL code as well as code in the Java programming
language. SQL code is used for creating structured types, creating tables for
instances of those types, populating the tables with instances of the structured types,
and creating an SQL DISTINCT type. This code sets up the SQL values that will be
mapped to classes in the Java programming language.
The examples of code in the Java programming language create implementations of
the SQLData interface for the newly created SQL UDTs and also show how a class in
the Java programming language can mirror SQL inheritance for structured types.

An SQL Structured Type

CODE EXAMPLE
which defines the structured types PERSON, FULLNAME, and
RESIDENCE, shows that it is possible for an attribute to be a REF value or another
structured type. PERSON and RESIDENCE each have an attribute that is a REF value,
and the REF value in one structured type references the other structured type. Note
also that FULLNAME is used as an attribute of PERSON.


CREATE TYPE RESIDENCE AS
(
DOOR NUMERIC(6),
STREET VARCHAR(100),
CITY VARCHAR(50),
OCCUPANT REF(PERSON)
) NOT FINAL
CREATE TYPE FULLNAME AS
(
FIRST VARCHAR(50),
LAST VARCHAR(50)
) NOT FINAL
CREATE TYPE PERSON AS
(
NAME FULLNAME,
Chapter 17 Customized Type Mapping 152
HEIGHT NUMERIC,
WEIGHT NUMERIC,
HOME REF(RESIDENCE)
) NOT FINAL


CODE EXAMPLE
Creating SQL structured types The types created in CODE EXAMPLE 17-4 are presumed to be created in the current schema for the following examples.

CODE EXAMPLE creates two tables that are maintained by the DBMS automatically. The CREATE statements do two things:

1. Create tables that store instances of the structured types named in the OF part of
the statement (RESIDENCE in the first one, PERSON in the second). Each of the subsequent INSERT INTO statements adds a new row representing an instance of the UDT.
2. Create a REF value that is a pointer to each instance that is inserted into the table. As indicated in the CREATE statement, the REF value is generated by the system, which is done implicitly. Because REF values are stored in the table, they are persistent pointers. This contrasts with LOCATOR types, which are logical pointers but exist only as long as the transactions in which they are created.

CREATE TABLE HOMES OF RESIDENCE
(REF IS OID SYSTEM GENERATED,
OCCUPANT WITH OPTIONS SCOPE PEOPLE)
CREATE TABLE PEOPLE OF PERSON
(REF IS OID SYSTEM GENERATED,
OCCUPANT WITH OPTIONS SCOPE HOMES)


CODE EXAMPLE Creating tables to store instances of a structured type

CODE EXAMPLE uses INSERT INTO statements to populate the tables created in

CODE EXAMPLE For example, the INSERT INTO PEOPLE statement inserts an

instance of the UDT PERSON into the table PEOPLE. When this command is executed, the DBMS will also automatically generate a REF value that is a pointer to this instance of PERSON and store it in the column OID (the column name specified in the CREATE statement that created the table PEOPLE). Each column value in these special tables is an attribute of the UDT, which may itself be a UDT. For example, the first attribute of the UDT PERSON is the value in the column NAME, which must be an instance of the UDT FULLNAME. The example assumes that the UDT FULLNAME has an additional two parameter constructor.
A column value may also be a reference to an SQL structured type. For example, the attribute OCCUPANT of the UDT RESIDENCE is of type REF(PERSON). It takes an SQL SELECT statement to retrieve the REF value from the table HOMES and use it as the value for OCCUPANT, which is shown at the end of

CODE EXAMPLE
INSERT INTO PEOPLE (NAME, HEIGHT, WEIGHT) VALUES
(
NEW 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'

CODE EXAMPLE Populating and updating tables that store instances of structured
types


SQLData Implementations

The Java classes defined in CODE EXAMPLE are mappings of the SQL structured types used in the examples . 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 in the example. In each implementation of SQLData, the method SQLData.readSQL reads the attributes in the order in which they appear in the SQL definition of the structured type. Attributes are also read in "row order, depth-first" order, where the complete structure of each attribute is read recursively before the next attribute is read. The method SQLData.writeSQL writes each attribute to the output stream in the same 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; }
Chapter 17 Customized Type Mapping 155
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);
Chapter 17 Customized Type Mapping 156
stream.writeRef(home);
}
}

CODE EXAMPLE 17-7 Classes implementing the SQLData interface

CODE EXAMPLE puts entries for custom mappings in the connection’s type map. Then it retrieves the Ref instance stored in the OCCUPANT column of the table HOMES. This Ref instance is set as a parameter in the where clause of the query to get the name of the occupant. When the method getObject is called to retrieve an instance of FULLNAME, the driver looks in the connections type map and uses the SQLData implementation, Fullname, to custom map the FULLNAME value.

// set up 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);

CODE EXAMPLE 17-8 Retrieving a custom mapping

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, which inherits from PERSON:

CREATE TYPE PERSON AS (NAME VARCHAR(20), BIRTH DATE) NOT FINAL;
CREATE TYPE STUDENT UNDER PERSON AS (GPA NUMERIC(4,2)) NOT FINAL;

CODE EXAMPLE Creating a hierarchy of SQL types 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 cascade each call to the corresponding method in its superclass in order to read or write the superclass 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)
Chapter 17 Customized Type Mapping 158
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);
}
}

CODE EXAMPLE Mirroring SQL type hierarchies in Java classes The Java class hierarchy need not mirror the SQL inheritance hierarchy. For example, the class Student above could have been declared without a superclass. 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.

Example Mapping of SQL DISTINCT Type

CODE EXAMPLE illustrates creating an SQL DISTINCT type, MONEY, and
CODE EXAMPLE illustrates a Java class, Money, that represents it.

CREATE TYPE MONEY AS NUMERIC(10,2) FINAL;

CODE EXAMPLE Creating an SQL DISTINCT type

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);
}
}

CODE EXAMPLE Java class that represents a DISTINCT type