JDBC^TM Guide: Getting Started

8 New SQL Types

The next two chapters discuss additions to the JDBC 2.0 API that allow a Java application to access new SQL data types, such as binary large objects and structured types. JDBC drivers that do not support the new SQL types need not implement the methods and interfaces described in these chapters.

8.1 Taxonomy of SQL Types

The next version of the ANSI/ISO SQL standard is commonly referred to as SQL3. The working document specifying that standard is called the SQL3 draft. That draft is now in a stage of the standardization process where no major additions or changes may be made to it-only minor changes and corrections can be made. The basic kinds of data types included in SQL3 will not change in the end-game of the standardization process.

The JDBC 2.0 API incorporates a model of the new SQL3 types that includes only those properties that are essential to exchanging data between Java applications and databases. The JDBC 2.0 API should not be affected if some details of the syntax and server-side semantics of the new SQL3 types are altered before the draft becomes an official standard.

The SQL3 draft specifies these data types:

SQL2 built-in types-the familiar SQL `column types'
- CHAR
- FLOAT
- DATE
- etc.
New built-in types-new types added by SQL3
- BLOB-a Binary Large OBject
- CLOB-a Character Large OBject
Structured types, for example:
- CREATE TYPE PLANE_POINT (X FLOAT, Y FLOAT)
Distinct types-based on the representation of a built-in type, for example:
- CREATE TYPE MONEY AS NUMERIC(10,2)
Constructed types-based on a given base type:
- REF(structured-type)-designates row containing a structured type instance
- base-type ARRAY[n]-an array of n base-type elements
Locator types-designate a datum that resides on the server
- LOCATOR(structured-type)-locator to structured instance in server
- LOCATOR(array)-locator to array in server
- LOCATOR(blob)-locator to Binary Large Object in server
- LOCATOR(clob)-locator to Character Large Object in server

A REF value persistently denotes an instance of a structured type that resides in the database. A LOCATOR exists only in the client environment and is a transient, logical pointer to data that resides on the database server. A locator typically refers to data that is too large to materialize on the client, for example, images or audio. There are operators defined at the SQL level to retrieve random-access pieces of the data denoted by the locator.

The remainder of this chapter discusses the default mechanism provided by the JDBC 2.0 API for accessing each of the new SQL types mentioned above. The JDBC 2.0 API also provides a means for customizing the mapping of SQL distinct and structured types into Java classes. This mechanism is discussed in the Chapter 9.

8.2 Blobs and clobs

8.2.1 Retrieving blobs and clobs

The binary large object (blob) and character large object (clob) data types are treated similarly to the existing, built-in JDBC types. Values of these types can be retrieved by calling the getBlob() and getClob() methods that appear on the ResultSet and CallableStatement interfaces. For example,

Blob blob = rs.getBlob(1);
Clob clob = rs.getClob(2);

retrieves a blob value from the first column of the result set and a clob value from the second column. The Blob interface contains operations for returning the length of the blob, a specific range of bytes contained in the blob, etc. The Clob interface contains corresponding operations that are character based. See the accompanying API documentation for more details.

A JDBC application does not deal directly with the LOCATOR(blob) and LOCATOR(clob) types that are defined in SQL. By default, a JDBC driver should implement the Blob and Clob interfaces using the appropriate locator type. Also, by default Blob and Clob objects only remain valid during the transaction in which they are created. A JDBC driver may allow these defaults to be changed. For example, the lifetime of Blob and Clob objects could be changed to session-scoped. However, the JDBC 2.0 API does not specify how this is done.

8.2.2 Storing blobs and clobs

A Blob or Clob value can be passed as an input parameter to a PreparedStatement object just like other JDBC data types by calling the setBlob() and setClob() methods respectively. The setBinaryStream(), and setObject() methods may be used to input a stream value as a blob. The setAsciiStream(), setUnicodeStream(), and setObject() methods may be used to input a stream as a clob value.

8.2.3 Metadata additions

Two new type codes, BLOB and CLOB, have been added to java.sql.Types. These values are returned by methods such as DatabaseMetaData.getTypeInfo() and

DatabaseMetaData.getColumns()

when a JDBC driver supports these data types.

8.3 Arrays

8.3.1 Retrieving arrays

Data of type SQL array can be retrieved by calling the getArray() method of the

ResultSet

and CallableStatement interfaces. For example,

Array a = rs.getArray(1);

retrieves an Array value from the first column of the result set. By default, a JDBC driver should implement the Array interface using an SQL LOCATOR(array) internally. Also, by default Array objects only remain valid during the transaction in which they are created. These defaults may be changed as for the Blob and Clob types, but the JDBC 2.0 API does not specify how this is done.

The Array interface provides several methods which return the contents of the array to the client as a materialized Java array or ResultSet object. These methods are getArray()and getResultSet(), respectively. See the separate API documentation for details.

8.3.2 Storing arrays

The PreparedStatement.setArray() method may be called to pass an Array value as an input parameter to a prepared statement. A Java programming language array may be passed as an input parameter by calling PreparedSatement.setObject().

8.3.3 Metadata additions

A new type code, ARRAY, has been added to java.sql.Types. This value is returned by methods such as DatabaseMetaData.getTypeInfo() and

DatabaseMetaData.getColumns()

when a JDBC driver supports the Array data type.

8.4 Refs

8.4.1 Retrieving refs

An SQL reference can be retrieved by calling the getRef() method of the ResultSet and CallableStatement interfaces. For example,

Ref ref = rs.getRef(1);

retrieves a Ref value from the first column of the result set. By default, retrieving a Ref value does not materialize the data to which the Ref refers. Also, by default a Ref value remains valid while the session or connection on which it is created is open. These defaults may be overridden, but again the JDBC 2.0 API does not specify how this is done.

The Ref interface does not provide methods for dereferencing. Instead, a Ref can be passed as an input parameter to an appropriate SQL statement that fetches the object that it references. See the separate JDBC API documentation for details.

8.4.2 Storing refs

The PreparedStatement.setRef() method may be called to pass a Ref as an input parameter to a prepared statement.

8.4.3 Metadata additions

A new type code, REF, has been added to java.sql.Types. This value is returned by methods such as DatabaseMetaData.getTypeInfo() and

DatabaseMetaData.getColumns()

when a JDBC driver supports the Ref data type.

8.5 Distinct types

8.5.1 Retrieving distinct types

By default, a datum of SQL type DISTINCT is retrieved by calling any getXXX() method that is appropriate to the underlying type that the distinct type is based on. For example, given the following type declaration:

CREATE TYPE MONEY AS NUMERIC(10,2)

a value of type MONEY could be retrieved as follows:

java.math.BigDecimal bd = rs.getBigDecimal(1);

since the underlying SQL NUMERIC type is mapped to the java.math.BigDecimal type by JDBC.

8.5.2 Storing distinct types

Any PreparedStatement.setXXX() method that is appropriate to the underlying type of an SQL DISTINCT type may be used to pass an input parameter of that distinct type to a prepared statement. For example, given the definition of type MONEY above

PreparedStatement.setBigDecimal()

would be used.

8.5.3 Metadata additions

A new type code, DISTINCT, has been added to java.sql.Types. This value is returned by methods such as DatabaseMetaData.getTypeInfo() and

DatabaseMetaData.getColumns()

when a JDBC driver supports this data type.

An SQL DISTINCT type must be defined as part of a particular database schema before it is used in a schema table definition. Information on schema-specific user-defined types-of which DISTINCT types are one particular kind-can be retrieved by calling the DatabaseMetaData.getUDTs() method. For example,

int[] types = {Types.DISTINCT};
ResultSet rs = dmd.getUDTs("catalog-name", "schema-name", 
	"%", types);

returns descriptions of all the SQL DISTINCT types defined in the catalog- name.schema-nameschema. If the driver does not support UDTs or no matching UDTs are found then an empty result set is returned.

Each type description has the following columns:

TYPE_CAT String => the type's catalog (may be null)
TYPE_SCHEM String => the type's schema (may be null)
TYPE_NAME String => the database type name
JAVA_CLASS String => a Java class or interface name
DATA_TYPE short => value defined in java.sql.Types, e.g. DISTINCT
REMARKS String => explanatory comment on the type

TYPE_CAT	String => the type's catalog (may be null)
TYPE_SCHEM	String => the type's schema (may be null)
TYPE_NAME	String => the database type name
JAVA_CLASS	String => a Java class or interface name
DATA_TYPE	short => value defined in `java.sql.Types`, e.g. DISTINCT
REMARKS	String => explanatory comment on the type

Most of the columns above should be self-explanatory. The TYPE_NAME is the SQL type name given to the DISTINCT type-MONEY in the example above. This is the name used in a CREATE TABLE statement to specify a column of this type.

When DATA_TYPE is Types.DISTINCT, the JAVA_CLASS column contains a fully qualified Java class name. Instances of this class will be created if getObject() is called on a column of this DISTINCT type. For example, JAVA_CLASS would default to java.math.BigDecimal in the case of MONEY above. The JDBC 2.0 API does not prohibit a driver from returning a subtype of the class named by JAVA_CLASS. The JAVA_CLASS value reflects a custom type mapping when one is used. See Chapter 9 for details.

8.6 Structured types

8.6.1 Retrieving structured types

A value of an SQL structured type is always retrieved by calling method getObject(). By default, getObject() returns a value of type Struct for a structured type. For example,

Struct struct = (Struct)rs.getObject(1);

retrieves a Struct value from the first column of the current row of result set rs. The Struct interface contains methods for retrieving the attributes of a structured type as an array of java.lang.Object values. By default, a JDBC driver should materialize the contents of a Struct prior to returning a reference to it to the application. Also, by default a Struct object is considered valid as long as the Java application maintains a reference to it. A JDBC driver may allow these defaults to be changed-to allow an SQL LOCATOR to be used, for example-but the JDBC 2.0 API does not specify how this is done.

8.6.2 Storing structured types

The PreparedStatement.setObject() method may be called to pass a Struct as an input parameter to a prepared statement.

8.6.3 Metadata additions

A new type code, STRUCT, has been added to java.sql.Types. This value is returned by methods such as DatabaseMetaData.getTypeInfo() and

DatabaseMetaData.getColumns()

when a JDBC driver supports structured data types.

A structured SQL type must be defined as part of a particular database schema before it can be used in a schema table definition. Information on schema-specific user-defined types-of which STRUCT types are one particular kind-can be retrieved by calling the DatabaseMetaData.getUDTs() method. For example,

int[] types = {Types.STRUCT};
ResultSet rs = dmd.getUDTs("catalog-name", "schema-name", 
	"%", types);

returns descriptions of all the structured SQL types defined in the catalog- name.schema-nameschema. If the driver does not support UDTs or no matching UDTs are found then an empty result set is returned. See section 8.5.3 for a description of the result set returned by getUDTs().

When the DATA_TYPE returned by getUDTs() is Types.STRUCT, the JAVA_CLASS column contains the fully qualified Java class name of a Java class. Instances of this class are manufactured by the JDBC driver when getObject() is called on a column of this STRUCT type. Thus, JAVA_CLASS defaults to java.sql.Struct for structured types. Chapter 9 discusses how this default can be modified by a Java application. We note here only that the JDBC 2.0 API does not prohibit a driver from returning a subtype of the class named by JAVA_CLASS.

Contents | Prev | Next

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