Distinct Types in JAVA

An SQL DISTINCT type is a new user defined data type that is based on one of the
primitive types. C and C++ programmers can think of it as being similar to a
typedef.

Retrieving Distinct Types

By default, a column of SQL type DISTINCT is retrieved by calling any getter
method that is appropriate to the type on which it is based. For example, the
following type declaration creates the type MONEY, which is based on the SQL type
NUMERIC.

CREATE TYPE MONEY AS NUMERIC(10,2) FINAL

CODE EXAMPLE 16-9 Creating a distinct type
CODE EXAMPLE 16-10 uses the method getBigDecimal to retrieve a MONEY value

because the underlying SQL NUMERIC type is mapped to the
java.math.BigDecimal type.

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

CODE EXAMPLE 16-10 Retrieving a distinct type

Storing Distinct Types

Any setter method in the PreparedStatement interface that is appropriate for the
base 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 in
CODE EXAMPLE 16-9,

the method PreparedStatement.setBigDecimal would be used.

Metadata

The type code DISTINCT is defined in the class 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 can be 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,

CODE EXAMPLE 16-11 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, the getUDTs method returns an empty result set.

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

CODE EXAMPLE 16-11 Querying a DatabaseMetaData object for distinct types
Each row in the ResultSet object returned by the method getUDTs describes a
UDT. Each row contains the following columns:

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 CLASS_NAME 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, CLASS_NAME 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 CLASS_NAME. The CLASS_NAME value reflects a custom type mapping when one is used. See


TYPE_CAT String => the type's catalog (may be null)
TYPE_SCHEM String => the type's schema (may be null)
TYPE_NAME String => the SQL type name
CLASS_NAME String => a Java class name
DATA_TYPE short => value defined in java.sql.Types, such as DISTINCT
REMARKS String => explanatory comment on the type
BASE_TYPE short => value defined in java.sql.Types, for DISTINCT