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 : Advanced topics : Custom data types and type conversionprevious : next

# Your custom type and its associated Converter

When using a custom type in jOOQ, you need to let jOOQ know about its associated org.jooq.Converter. A converter essentially has two generic type parameters:

The above conversion implies that you may want to use a GregorianCalendar for SQL timestamps, rather than the timestamp type itself. You could then write a Converter like this: 

package com.example;

import java.sql.Timestamp;
import java.util.Calendar;
import java.util.GregorianCalendar;

import org.jooq.Converter;

// Bind T to Timestamp and U to Gregorian calendar, here
public class CalendarConverter implements Converter<Timestamp, GregorianCalendar> {

    // Provide jOOQ with Class<?> objects of <U> and <T>. These are used by
    // jOOQ to discover your converter based on your custom type
    // --------------------------------------------------------------------
    @Override
    public Class<Timestamp> fromType() {
        return Timestamp.class;
    }

    @Override
    public Class<GregorianCalendar> toType() {
        return GregorianCalendar.class;
    }

    // Implement the type conversion methods. Convert your user-defined type
    // "from" the SQL type when reading "from" the database, or "to" the SQL
    // type when writing "to" the database.
    @Override
    public GregorianCalendar from(Timestamp databaseObject) {
        GregorianCalendar calendar = (GregorianCalendar) Calendar.getInstance();
        calendar.setTimeInMillis(databaseObject.getTime());
        return calendar;
    }

    @Override
    public Timestamp to(GregorianCalendar userObject) {
        return new Timestamp(userObject.getTime().getTime());
    }
}

Such a Converter can now be used in various places of the jOOQ API, especially when reading data from the database: 

List<GregorianCalendar> result =
create.select(T_AUTHOR.DATE_OF_BIRTH)
      .from(T_AUTHOR)
      .fetch(0, new CalendarConverter());

# Using Converters in generated code

A more common use-case, however, is to let jOOQ know about custom types at code generation time. Use the following configuration elements to specify, that you'd like to use GregorianCalendar for all database fields that start with DATE_OF_ 

<database>
  <!-- First, register your custom types here -->
  <customTypes>
    <customType>
      <!-- Specify the fully-qualified class name of your custom type -->
      <name>java.util.GregorianCalendar</name>

      <!-- Associate that custom type with your converter. Note, a
           custom type can only have one converter in jOOQ -->
      <converter>com.example.CalendarConverter</converter>
    </customType>
  </customTypes>

  <!-- Then, associate custom types with database columns -->
  <forcedTypes>
    <forcedType>
      <!-- Specify again he fully-qualified class name of your custom type -->
      <name>java.util.GregorianCalendar</name>

      <!-- Add a list of comma-separated regular expressions matching columns -->
      <expressions>.*\.DATE_OF_.*</expressions>
    </forcedType>
  </forcedTypes>
</database>

The above configuration will lead to T_AUTHOR.DATE_OF_BIRTH being generated like this: 

public class TAuthor extends UpdatableTableImpl<TAuthorRecord> {

    // [...]
    public final TableField<TAuthorRecord, GregorianCalendar> DATE_OF_BIRTH =    // [...]
    // [...]

}

This means that the bound of <T> will be GregorianCalendar, wherever you reference DATE_OF_BIRTH. jOOQ will use your custom converter when binding variables and when fetching data from java.util.ResultSet: 

// Get all date of births of authors born after 1980
List<GregorianCalendar> result =
create.selectFrom(T_AUTHOR)
      .where(T_AUTHOR.DATE_OF_BIRTH.greaterThan(new GregorianCalendar(1980, 0, 1)))
      .fetch(T_AUTHOR.DATE_OF_BIRTH);

Read more about advanced code generation configuration in the manual's section about advanced code generation configuration.

# Using Converters for enum types

Java's Enum types can be very useful in SQL too. Some databases support enumeration types natively (MySQL, Postgres). In other cases, you can use the above custom type configuration also to provide jOOQ with Converters for your custom Enum types. Instead of implementing org.jooq.Converter, you may choose to extend org.jooq.impl.EnumConverter instead, which provides some enum-specific default behaviour.


The jOOQ User Manual. Multiple Pages : Advanced topics : Custom data types and type conversionprevious : next

The jOOQ Logo