This version of the manual is outdated. For the latest version, follow this link: http://www.jooq.org/doc/3.0/manual.

The jOOQ User Manual. Multiple Pages : Meta model code generation : UDT's including ARRAY and ENUM typesprevious : next

# Increased RDBMS support for UDT's

In recent years, most RDBMS have started to implement some support for advanced data types. This support has not been adopted very well by database users in the Java world, for several reasons:

On the other hand, especially with stored procedures, these data types are likely to become more and more useful in the future. If you have a look at Postgres' capabilities of dealing with advanced data types (ENUMs, ARRAYs, UDT's), this becomes more and more obvious.

It is a central strategy for jOOQ, to standardise access to these kinds of types (as well as to stored procedures, of course) across all RDBMS, where these types are supported.

# UDT types

User Defined Types (UDT) are helpful in major RDMBS with lots of proprietary functionality. The biggest player is clearly Oracle. Currently, jOOQ provides UDT support for only two databases:

Apart from that,

In Oracle, you would define UDTs like this: 

CREATE TYPE u_street_type AS OBJECT (
  street VARCHAR2(100),
  no VARCHAR2(30)
)

CREATE TYPE u_address_type AS OBJECT (
  street u_street_type,
  zip VARCHAR2(50),
  city VARCHAR2(50),
  country VARCHAR2(50),
  since DATE,
  code NUMBER(7)
)

These types could then be used in tables and/or stored procedures like such: 

CREATE TABLE t_author (
  id NUMBER(7) NOT NULL PRIMARY KEY,
  -- [...]
  address u_address_type
)

CREATE OR REPLACE PROCEDURE p_check_address (address IN OUT u_address_type);

Standard JDBC UDT support encourages JDBC-driver developers to implement interfaces such as java.sql.SQLData, java.sql.SQLInput and java.sql.SQLOutput. Those interfaces are non-trivial to implement, or to hook into. Also access to java.sql.Struct is not really simple. Due to the lack of a well-defined JDBC standard, Oracle's JDBC driver rolls their own proprietary methods of dealing with these types. jOOQ goes a different way, it hides those facts from you entirely. With jOOQ, the above UDT's will be generated in simple UDT meta-model classes and UDT record classes as such: 

// There is an analogy between UDT/Table and UDTRecord/TableRecord...
public class UAddressType extends UDTImpl<UAddressTypeRecord> {

    // The UDT meta-model singleton instance
    public static final UAddressType U_ADDRESS_TYPE = new UAddressType();

    // UDT attributes are modeled as static members. Nested UDT's
    // behave similarly
    public static final UDTField<UAddressTypeRecord, UStreetTypeRecord> STREET = // [...]
    public static final UDTField<UAddressTypeRecord, String> ZIP =               // [...]
    public static final UDTField<UAddressTypeRecord, String> CITY =              // [...]
    public static final UDTField<UAddressTypeRecord, String> COUNTRY =           // [...]
    public static final UDTField<UAddressTypeRecord, Date> SINCE =               // [...]
    public static final UDTField<UAddressTypeRecord, Integer> CODE =             // [...]
}

Now, when you interact with entities or procedures that hold UDT's, that's very simple as well. Here is an example: 

// Fetch any author from the T_AUTHOR table
TAuthorRecord author = create.selectFrom(T_AUTHOR).fetchAny();

// Print out the author's address's house number
System.out.println(author.getAddress().getStreet().getNo());

A similar thing can be achieved when interacting with the example stored procedure: 

// Create a new UDTRecord of type U_ADDRESS_TYPE
UAddressTypeRecord address = new UAddressTypeRecord();
address.setCountry("Switzerland");

// Call the stored procedure with IN OUT parameter of type U_ADDRESS_TYPE
address = Procedures.pCheckAddress(connection, address);

# ARRAY types

The notion of ARRAY types in RDBMS is not standardised at all. Very modern databases (especially the Java-based ones) have implemented ARRAY types exactly as what they are. "ARRAYs of something". In other words, an ARRAY OF VARCHAR would be something very similar to Java's notion of String[]. An ARRAY OF ARRAY OF VARCHAR would then be a String[][] in Java. Some RDMBS, however, enforce stronger typing and need the explicit creation of types for every ARRAY as well. These are example String[] ARRAY types in various SQL dialects supported by jOOQ 1.5.4:

Soon to be supported:

From jOOQ's perspective, the ARRAY types fit in just like any other type wherever the <T> generic type parameter is existent. It integrates well with tables and stored procedures.

# Example: General ARRAY types

An example usage of ARRAYs is given here for the Postgres dialect 

CREATE TABLE t_arrays (
  id integer not null primary key,
  string_array VARCHAR(20)[],
  number_array INTEGER[]
)

CREATE FUNCTION f_arrays(in_array IN text[]) RETURNS text[]

When generating source code from the above entities, these artefacts will be created in Java: 

public class TArrays extends UpdatableTableImpl<TArraysRecord> {

    // The generic type parameter <T> is bound to an array of a matching type
    public static final TableField<TArraysRecord, String[]> STRING_ARRAY =  // [...]
    public static final TableField<TArraysRecord, Integer[]> NUMBER_ARRAY = // [...]
}

// The convenience class is enhanced with these methods
public final class Functions {
    public static String[] fArrays(Connection connection, String[] inArray) { // [...]
    public static Field<String[]> fArrays(String[] inArray) {                                     // [...]
    public static Field<String[]> fArrays(Field<String[]> inArray) {                              // [...]
}

# Example: Oracle VARRAY types

In Oracle, a VARRAY type is something slightly different than in other RDMBS. It is a type that encapsules the actual ARRAY and creates a new type from it. While all text[] types are equal and thus compatible in Postgres, this does not apply for all VARRAY OF VARCHAR2 types. Hence, it is important to provide access to VARRAY types and generated objects from those types as well. The example above would read like this in Oracle: 

CREATE TYPE u_string_array AS VARRAY(4) OF VARCHAR2(20)
CREATE TYPE u_number_array AS VARRAY(4) OF NUMBER(7)

CREATE TABLE t_arrays (
  id NUMBER(7) not null primary key,
  string_array u_string_array,
  number_array u_number_array
)

CREATE OR REPLACE FUNCTION f_arrays (in_array u_string_array)
RETURN u_string_array

Note that it becomes clear immediately, that a mapping from U_STRING_ARRAY to String[] is obvious. But a mapping from String[] to U_STRING_ARRAY is not. These are the generated org.jooq.ArrayRecord and other artefacts in Oracle: 

public class UStringArrayRecord extends ArrayRecordImpl<String> {  // [...]
public class UNumberArrayRecord extends ArrayRecordImpl<Integer> { // [...]

public class TArrays extends UpdatableTableImpl<TArraysRecord> {
    public static final TableField<TArraysRecord, UStringArrayRecord> STRING_ARRAY = // [...]
    public static final TableField<TArraysRecord, UNumberArrayRecord> NUMBER_ARRAY = // [...]
}

public final class Functions {
    public static UStringArrayRecord fArrays3(Connection connection, UStringArrayRecord inArray) { // [...]
    public static Field<UStringArrayRecord> fArrays3(UStringArrayRecord inArray) {                 // [...]
    public static Field<UStringArrayRecord> fArrays3(Field<UStringArrayRecord> inArray) {          // [...]
}

# ENUM types

True ENUM types are a rare species in the RDBMS world. Currently, MySQL and Postgres are the only RDMBS supported by jOOQ, that provide ENUM types.

Some examples: 

-- An example enum type
CREATE TYPE u_book_status AS ENUM ('SOLD OUT', 'ON STOCK', 'ORDERED')

-- An example useage of that enum type
CREATE TABLE t_book (
  id INTEGER NOT NULL PRIMARY KEY,

  -- [...]
  status u_book_status
)

The above Postgres ENUM type will be generated as 

public enum UBookStatus implements EnumType {
    ORDERED("ORDERED"),
    ON_STOCK("ON STOCK"),
    SOLD_OUT("SOLD OUT");

    // [...]
}

Intuitively, the generated classes for the T_BOOK table in Postgres would look like this: 

// The meta-model class
public class TBook extends UpdatableTableImpl<TBookRecord> {

    // The TableField STATUS binds <T> to UBookStatus
    public static final TableField<TBookRecord, UBookStatus> STATUS = // [...]

    // [...]
}

// The record class
public class TBookRecord extends UpdatableRecordImpl<TBookRecord> {

    // Corresponding to the Table meta-model, also setters and getters
    // deal with the generated UBookStatus
    public void setStatus(UBookStatus value) { // [...]
    public UBookStatus getStatus() {           // [...]
}

Note that jOOQ allows you to simulate ENUM types where this makes sense in your data model. See the section on master data for more details.


The jOOQ User Manual. Multiple Pages : Meta model code generation : UDT's including ARRAY and ENUM typesprevious : next

Fork me on GitHub
The jOOQ Logo