Available in versions: Dev (3.20) | Latest (3.19)

Synthetic enums

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

jOOQ's code generator supports generating synthetic enums from a variety of enumeration literal sources, including:

Explicit listing of literals
The results of an arbitrary 1-column projecting SQL query
A simple CHECK constraint of the form CHECK (col IN ('a', 'b')) or any other non-canonical form that can be reduced to an IN predicate using pattern-based transformation.

In particular, the CHECK constraint based configuration is very powerful!

enum TStatus implements EnumType {
  A, B, C;

  // [...]
}

CREATE TABLE t (
  status VARCHAR(10),

  CHECK (status IN ('A', 'B', 'C'))
)

A configuration example:

XML (standalone and maven)

Programmatic

Gradle (Kotlin)

Gradle (Groovy)

Gradle (third party)

<configuration>
  <generator>
    <database>
      <syntheticObjects>
        <enums>
          <enum>

            <!-- Optional name of the enum type in the schema. If left empty, the generated name
                 is concatenated as TABLE_COLUMN -->
            <name>EnumName</name>

            <!-- Optional regular expression matching all tables that have this enum type. -->
            <tables>SCHEMA\.TABLE</tables>

            <!-- Regular expression matching all fields that have this enum type. -->
            <fields>STATUS</fields>

            <!-- An optional, defining comment of the enum -->
            <comment>Ann enumeration for the TABLE.STATUS column.</comment>

            <!-- The following are mutually exclusive settings. If none are provided, and the
                 synthetic enum is named, then it will just reference a previously defined
                 synthetic or actual enum type by name.
              -->

            <!-- Specify enum literals explicitly -->
            <literals>
                <literal>A</literal>
                <literal>B</literal>
            </literals>

            <!-- Specify a query that returns the distinct enum literals -->
            <literalSql>SELECT DISTINCT status FROM schema.table</literalSql>

            <!-- Fetch distinct enum literals from the column's content.
                 This is the same as specifying literalSql to be

                 SELECT DISTINCT matched_column FROM matched_table
              -->
            <literalsFromColumnContent>true</literalsFromColumnContent>

            <!-- The list of literals is parsed from the applicable CHECK constraints
                 for the matched column, if possible. -->
            <literalsFromCheckConstraints>true</literalsFromCheckConstraints>
          </enum>
        </enums>
      </syntheticObjects>
    </database>
  </generator>
</configuration>

See the configuration XSD, standalone code generation, and maven code generation for more details.

new org.jooq.meta.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()
      .withSyntheticObjects(new SyntheticObjectsType()
        .withEnums(
          new SyntheticEnumType()

            // Optional name of the enum type in the schema. If left empty, the generated name
            // is concatenated as TABLE_COLUMN
            .withName("EnumName")

            // Optional regular expression matching all tables that have this enum type.
            .withTables("SCHEMA\\.TABLE")

            // Regular expression matching all fields that have this enum type.
            .withFields("STATUS")

            // An optional, defining comment of the enum
            .withComment("Ann enumeration for the TABLE.STATUS column.")

            // The following are mutually exclusive settings. If none are provided, and the
            // synthetic enum is named, then it will just reference a previously defined
            // synthetic or actual enum type by name.
            .withLiterals(
              "A",
              "B"
            )

            // Specify a query that returns the distinct enum literals
            .withLiteralSql("SELECT DISTINCT status FROM schema.table")

            // Fetch distinct enum literals from the column's content.
            // This is the same as specifying literalSql to be
            // 
            // SELECT DISTINCT matched_column FROM matched_table
            .withLiteralsFromColumnContent(true)

            // The list of literals is parsed from the applicable CHECK constraints
            // for the matched column, if possible.
            .withLiteralsFromCheckConstraints(true)
        )
      )
    )
  )

See the configuration XSD and programmatic code generation for more details.

import org.jooq.meta.jaxb.*


configuration {
  generator {
    database {
      syntheticObjects {
        enums {
          enum {

            // Optional name of the enum type in the schema. If left empty, the generated name
            // is concatenated as TABLE_COLUMN
            name = "EnumName"

            // Optional regular expression matching all tables that have this enum type.
            tables = "SCHEMA\\.TABLE"

            // Regular expression matching all fields that have this enum type.
            fields = "STATUS"

            // An optional, defining comment of the enum
            comment = "Ann enumeration for the TABLE.STATUS column."

            // The following are mutually exclusive settings. If none are provided, and the
            // synthetic enum is named, then it will just reference a previously defined
            // synthetic or actual enum type by name.
            literals {
              literal = "A"
              literal = "B"
            }

            // Specify a query that returns the distinct enum literals
            literalSql = "SELECT DISTINCT status FROM schema.table"

            // Fetch distinct enum literals from the column's content.
            // This is the same as specifying literalSql to be
            // 
            // SELECT DISTINCT matched_column FROM matched_table
            isLiteralsFromColumnContent = true

            // The list of literals is parsed from the applicable CHECK constraints
            // for the matched column, if possible.
            isLiteralsFromCheckConstraints = true
          }
        }
      }
    }
  }
}

See the configuration XSD and gradle code generation for more details.

configuration {
  generator {
    database {
      syntheticObjects {
        enums {
          enum {

            // Optional name of the enum type in the schema. If left empty, the generated name
            // is concatenated as TABLE_COLUMN
            name = "EnumName"

            // Optional regular expression matching all tables that have this enum type.
            tables = "SCHEMA\\.TABLE"

            // Regular expression matching all fields that have this enum type.
            fields = "STATUS"

            // An optional, defining comment of the enum
            comment = "Ann enumeration for the TABLE.STATUS column."

            // The following are mutually exclusive settings. If none are provided, and the
            // synthetic enum is named, then it will just reference a previously defined
            // synthetic or actual enum type by name.
            literals {
              literal = "A"
              literal = "B"
            }

            // Specify a query that returns the distinct enum literals
            literalSql = "SELECT DISTINCT status FROM schema.table"

            // Fetch distinct enum literals from the column's content.
            // This is the same as specifying literalSql to be
            // 
            // SELECT DISTINCT matched_column FROM matched_table
            literalsFromColumnContent = true

            // The list of literals is parsed from the applicable CHECK constraints
            // for the matched column, if possible.
            literalsFromCheckConstraints = true
          }
        }
      }
    }
  }
}

See the configuration XSD and gradle code generation for more details.

generationTool {
  generator {
    database {
      syntheticObjects {
        enums {
          enum {

            // Optional name of the enum type in the schema. If left empty, the generated name
            // is concatenated as TABLE_COLUMN
            name = "EnumName"

            // Optional regular expression matching all tables that have this enum type.
            tables = "SCHEMA\\.TABLE"

            // Regular expression matching all fields that have this enum type.
            fields = "STATUS"

            // An optional, defining comment of the enum
            comment = "Ann enumeration for the TABLE.STATUS column."

            // The following are mutually exclusive settings. If none are provided, and the
            // synthetic enum is named, then it will just reference a previously defined
            // synthetic or actual enum type by name.
            literals {
              literal = "A"
              literal = "B"
            }

            // Specify a query that returns the distinct enum literals
            literalSql = "SELECT DISTINCT status FROM schema.table"

            // Fetch distinct enum literals from the column's content.
            // This is the same as specifying literalSql to be
            // 
            // SELECT DISTINCT matched_column FROM matched_table
            literalsFromColumnContent = true

            // The list of literals is parsed from the applicable CHECK constraints
            // for the matched column, if possible.
            literalsFromCheckConstraints = true
          }
        }
      }
    }
  }
}

See the configuration XSD and gradle code generation for more details.

As always, when regular expressions are used, they are regular expressions with default flags.