CREATE TYPE statement

The CREATE TYPE statement creates a user-defined type (UDT). A UDT is a serializable Java class whose instances are stored in columns. The class must implement the java.io.Serializable interface.

Syntax

CREATE TYPE [ schemaName. ] SQL92Identifier
EXTERNAL NAME singleQuotedJavaClassName
LANGUAGE JAVA

The type name is composed of an optional schemaName and a SQL92Identifier. If a schemaName is not provided, the current schema is the default schema. If a qualified type name is specified, the schema name cannot begin with SYS.

If the Java class does not implement java.io.Serializable, or if it is not public and visible on the classpath, Derby raises an exception when preparing statements which refer to the UDT.

A UDT cannot be cast explicitly to any other type, and no other type can be cast to a UDT.

A UDT has no ordering. This means that you cannot compare and sort UDTs. You cannot use them in expressions involving the <, =, >, IN, BETWEEN, and LIKE operators. You cannot use UDTs in aggregates, DISTINCT expressions, and GROUP/ORDER BY clauses. You cannot build indexes on them.

You can use subtypes in UDTs. That is, if you use the CREATE TYPE statement to bind a class named C to a UDT, you can populate that UDT value with an instance of any subclass of C.

Example

CREATE TYPE price
EXTERNAL NAME 'com.acme.types.Price'
LANGUAGE JAVA

Using user-defined types

You can create tables and views with columns that have UDTs. For example:

CREATE TABLE order
(
    orderID INT GENERATED ALWAYS AS IDENTITY,
    customerID INT REFERENCES customer( customerID ),
    totalPrice typeSchema.price
);

Although UDTs have no natural order, you can use generated columns to provide useful sort orders:

ALTER TABLE order 
  ADD COLUMN normalizedValue DECIMAL( 31, 5 ) GENERATED ALWAYS AS 
    ( convert( 'EUR', TIMESTAMP('2005-01-01 09:00:00'), totalPrice ) );
CREATE INDEX normalizedOrderPrice ON order( normalizedValue );

You can use factory functions to construct UDTs. For example:

INSERT INTO order( customerID, totalPrice )
  VALUES ( 12345, 
           makePrice( 'USD', 
                      CAST( 9.99 AS DECIMAL( 31, 5 ) ), 
                      TIMESTAMP('2009-10-16 14:24:43') ) );

Once a UDT column has been populated, you can use it in other INSERT and UPDATE statements. For example:

INSERT INTO backOrder SELECT * from order;

UPDATE order SET totalPrice = ( SELECT todaysDiscount FROM discount );
UPDATE order SET totalPrice = adjustForInflation( totalPrice );

Using functions, you can access fields inside UDTs in a SELECT statement:

SELECT getCurrencyCode( totalPrice ) from order;

You can use JDBC API setObject() and getObject() methods to store and retrieve values of UDTs. For example:

PreparedStatement ps = conn.prepareStatement( "SELECT * from order" );
ResultSet rs = ps.executeQuery();

while( rs.next() )
{
    int    orderID = rs.getInt( 1 );
    int    customerID = rs.getInt( 2 );
    Price  totalPrice = (Price) rs.getObject( 3 );
    ...
}