New versions: | Old versions: 2.6

Master data and enumeration tables

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

NOTE: This feature is deprecated in jOOQ 2.5.0 and will be removed as of jOOQ 3.0

Only MySQL and Postgres databases support true ENUM types natively. Some other RDBMS allow you to map the concept of an ENUM data type to a CHECK constraint, but those constraints can contain arbitrary SQL. With jOOQ, you can "emulate" ENUM types by declaring a table as a "master data table" in the configuration. At code-generation time, this table will be treated specially, and a Java enum type is generated from its data.

Configure master data tables

As previously discussed, you can configure master data tables as follows:

<!-- These properties can be added to the database element: -->
      <!-- The name of a master data table -->
      <name>[a table name]</name>

      <!-- The column used for enum literals -->
      <literal>[a column name]</literal>

      <!-- The column used for documentation -->
      <description>[a column name]</description>

    [ <masterDataTable>...</masterDataTable> ... ]

The results of this will be a Java enum that looks similar to this:

public enum Language implements MasterDataType<Integer> {

   * English
  en(1, "en", "English"),

   * Deutsch
  de(2, "de", "Deutsch"),

   * Français
  fr(3, "fr", "Français"),

   * null
  pt(4, "pt", null),

  private final Integer id;
  private final String cd;
  private final String description;

  // [ ... constructor and getters for the above properties ]

In the above example, you can see how the configured primary key is mapped to the id member, the configured literal column is mapped to the cd member and the configured description member is mapped to the description member and output as Javadoc. In other words, LANGUAGE is a table with 4 rows and at least three columns.

The general contract is that there must be

  • A single-column primary key column of character or integer type
  • An optional unique literal column of character or integer type (otherwise, the primary key is used as enum literal)
  • An optional description column of any type

Using MasterDataTypes

The point of MasterDataTypes in jOOQ is that they behave exactly like true ENUM types. When the above LANGUAGE table is referenced by BOOK, instead of generating foreign key navigation methods and a LANGUAGE_ID Field<Integer>, a Field<TLanguage> is generated:

public class Book extends UpdatableTableImpl<BookRecord> {

  // [...]
  public static final TableField<BookRecord, Language> LANGUAGE_ID =
                  new TableFieldImpl<BookRecord, Language>( /* ... */ );

Which can then be used in the BookRecord directly:

public class BookRecord extends UpdatableRecordImpl<BookRecord> {

  // [...]
  public Language getLanguageId() { // [...]
  public void setLanguageId(Language value) { // [...]

When to use MasterDataTypes

You can use master data types when you're actually mapping master data to a Java enum. When the underlying table changes frequently, those updates will not be reflected by the statically generated code. Also, be aware that it will be difficult to perform actual JOIN operations on the underlying table with jOOQ, once the master data type is generated.


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

The jOOQ Logo