Contents | Prev | Next | JDBCTM Guide: Getting Started

## 8 New SQL Types

The next two chapters discuss additions to the JDBC 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 latest version of the ANSI/ISO SQL standard is commonly referred to as SQL99. The JDBC API incorporates a model of the new SQL99 types that includes only those properties that are essential to exchanging data between Java applications and databases. The JDBC API should not be affected if some details of the syntax and server-side semantics of the new SQL99 types are altered before the draft becomes an official standard.

The SQL99 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 SQL99
  - 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 API for accessing each of the new SQL types mentioned above. The JDBC 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 types defined in the JDBC API. 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.

An 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 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 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 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 programming language array (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 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 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.

#### 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-name` schema`.` 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

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 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 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-name` schema`.` 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 API does not prohibit a driver from returning a subtype of the class named by JAVA_CLASS.

Contents | Prev | Next

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