New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6

Custom data types and type conversion

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

When using a custom type in jOOQ, you need to let jOOQ know about its associated org.jooq.Converter. Ad-hoc usages of such converters has been discussed in the chapter about data type conversion. However, when mapping a custom type onto a standard JDBC type, a more common use-case is to let jOOQ know about custom types at code generation time (if you're using non-standard JDBC types, like for example JSON or HSTORE, see the manual's section about custom data type bindings). Use the following configuration elements to specify, that you'd like to use GregorianCalendar for all database fields that start with DATE_OF_

XML (standalone and maven)
Programmatic
Gradle
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.10.0.xsd">
  <generator>
    <database>
    
      <!-- Then, associate custom types with database columns -->
      <forcedTypes>
        <forcedType>
        
          <!-- Specify the Java type of your custom type. This corresponds to the Converter's <U> type. -->
          <userType>java.util.GregorianCalendar</userType>
    
          <!-- Associate that custom type with your converter. -->
          <converter>com.example.CalendarConverter</converter>
          
          <!-- A Java regex matching fully-qualified columns, attributes, parameters. Use the pipe to separate several expressions.
               
               If provided, both "expressions" and "types" must match. -->
          <expression>.*\.DATE_OF_.*</expression>
          
          <!-- A Java regex matching data types to be forced to
               have this type.
          
               Data types may be reported by your database as:
               - NUMBER              regexp suggestion: NUMBER
               - NUMBER(5)           regexp suggestion: NUMBER\(5\)
               - NUMBER(5, 2)        regexp suggestion: NUMBER\(5,\s*2\)
               - any other form
               
               It is thus recommended to use defensive regexes for types.
               
               If provided, both "expressions" and "types" must match. -->
          <types>.*</types>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>
new org.jooq.meta.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()

      // Then, associate custom types with database columns
      .withForcedTypes(
        new ForcedType()

          // Specify the Java type of your custom type. This corresponds to the Converter's <U> type.
          .withUserType("java.util.GregorianCalendar")

          // Associate that custom type with your converter.
          .withConverter("com.example.CalendarConverter")

          // A Java regex matching fully-qualified columns, attributes, parameters. Use the pipe to separate several expressions.
          // 
          // If provided, both "expressions" and "types" must match.
          .withExpression(".*\\.DATE_OF_.*")

          // A Java regex matching data types to be forced to
          // have this type.
          // 
          // Data types may be reported by your database as:
          // - NUMBER              regexp suggestion: NUMBER
          // - NUMBER(5)           regexp suggestion: NUMBER\(5\)
          // - NUMBER(5, 2)        regexp suggestion: NUMBER\(5,\s*2\)
          // - any other form
          // 
          // It is thus recommended to use defensive regexes for types.
          // 
          // If provided, both "expressions" and "types" must match.
          .withTypes(".*")
      )
    )
  )
myConfigurationName(sourceSets.main) {
  generator {
    database {

      // Then, associate custom types with database columns
      forcedTypes {
        forcedType {

          // Specify the Java type of your custom type. This corresponds to the Converter's <U> type.
          userType = 'java.util.GregorianCalendar'

          // Associate that custom type with your converter.
          converter = 'com.example.CalendarConverter'

          // A Java regex matching fully-qualified columns, attributes, parameters. Use the pipe to separate several expressions.
          // 
          // If provided, both "expressions" and "types" must match.
          expression = '.*\\.DATE_OF_.*'

          // A Java regex matching data types to be forced to
          // have this type.
          // 
          // Data types may be reported by your database as:
          // - NUMBER              regexp suggestion: NUMBER
          // - NUMBER(5)           regexp suggestion: NUMBER\(5\)
          // - NUMBER(5, 2)        regexp suggestion: NUMBER\(5,\s*2\)
          // - any other form
          // 
          // It is thus recommended to use defensive regexes for types.
          // 
          // If provided, both "expressions" and "types" must match.
          types = '.*'
        }
      }
    }
  }
}

See also the section about data type rewrites to learn about an alternative use of <forcedTypes/>.

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

public class TAuthor extends TableImpl<TAuthorRecord> {

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

}

This means that the bound type 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(AUTHOR)
      .where(AUTHOR.DATE_OF_BIRTH.gt(new GregorianCalendar(1980, 0, 1)))
      .fetch(AUTHOR.DATE_OF_BIRTH);

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo