Available in versions: Dev (3.18) | Latest (3.17) | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9

Matching of forced types

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

The <database> configuration's <forcedTypes> element can contain many <forcedType> elements and the generator will always pick the first of these definitions (orderered by lexical appearance in the configuration), where all given match predicates match a given database attribute, column, array element, parameter, or sequence.

These are the available match predicates, all are optional:

  1. <objectType>: Must be one of ATTRIBUTE, COLUMN, ELEMENT, PARAMETER, SEQUENCE, or ALL and specifies what type of database objects this forced type is applicable to
  2. <nullability>: Must be one of NULL, NOT_NULL, or ALL and specifies if this forced type is applicable to NULL, NOT NULL, or all definitions
  3. <excludeExpression>: This exclude predicate is a regular expression which is matched against the definition's fully qualified, partially qualified, or unqualified name; any definition's name matching the regular expression will be considered not matching this forced type. If left empty, then nothing is excluded.
  4. <includeExpression>: This predicate is a regular expression which is matched against the definition's fully qualified, partially qualified, or unqualified name. If left empty, then everything is included.
  5. <excludeTypes>: This exclude predicate is a regular expression which is matched against the name of the definition's type (provided that it has one); any definition's type name matching the regular expression will be considered not matching this forced type. If left empty, then nothing is excluded.
  6. <includeTypes>: This predicate is a regular expression which is matched against the name of the definition's type (provided that it has one). If left empty, then everything is included.
  7. <sql>: An SQL query returning all (qualified or unqualified) object names which should be considered to match this forced type; an example is given at the end of this section.

An example using various attributes

The following example is a forced type that applies a data type rewrite of all matched objects to the type BOOLEAN:

XML (standalone and maven)
Programmatic
Gradle
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.9.2.xsd">
  <generator>
    <database>

      <!-- The first matching forcedType will be applied to the data type definition. -->
      <forcedTypes>
        <forcedType>

          <!-- Specify any data type that is supported in your database, or if unsupported, 
               a type from org.jooq.impl.SQLDataType -->
          <name>BOOLEAN</name>

          <!-- A Java regex matching fully-qualified columns, attributes, parameters. Use the pipe to separate several expressions. -->
          <includeExpression>.*\.IS_VALID</includeExpression>
          
          <!-- 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. -->
          <includeTypes>.*</includeTypes>
          
          <!-- Force a type depending on data type nullability. Default is ALL.
          
                - ALL - Force a type regardless of whether data type is nullable or not (default)
                - NULL - Force a type only when data type is nullable
                - NOT_NULL - Force a type only when data type is not null -->
          <nullability>ALL</nullability>
          
          <!-- Force a type on ALL or specific object types. Default is ALL. Options include: 
               ATTRIBUTE, COLUMN, ELEMENT, PARAMETER, SEQUENCE -->
          <objectType>ALL</objectType>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>
new org.jooq.util.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()

      // The first matching forcedType will be applied to the data type definition.
      .withForcedTypes(
        new ForcedType()

          // Specify any data type that is supported in your database, or if unsupported, 
          // a type from org.jooq.impl.SQLDataType
          .withName("BOOLEAN")

          // A Java regex matching fully-qualified columns, attributes, parameters. Use the pipe to separate several expressions.
          .withIncludeExpression(".*\\.IS_VALID")

          // 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.
          .withIncludeTypes(".*")

          // Force a type depending on data type nullability. Default is ALL.
          // 
          // - ALL - Force a type regardless of whether data type is nullable or not (default)
          // - NULL - Force a type only when data type is nullable
          // - NOT_NULL - Force a type only when data type is not null
          .withNullability(Nullability.ALL)

          // Force a type on ALL or specific object types. Default is ALL. Options include: 
          // ATTRIBUTE, COLUMN, ELEMENT, PARAMETER, SEQUENCE
          .withObjectType(ForcedTypeObjectType.ALL)
      )
    )
  )
myConfigurationName(sourceSets.main) {
  generator {
    database {

      // The first matching forcedType will be applied to the data type definition.
      forcedTypes {
        forcedType {

          // Specify any data type that is supported in your database, or if unsupported, 
          // a type from org.jooq.impl.SQLDataType
          name = 'BOOLEAN'

          // A Java regex matching fully-qualified columns, attributes, parameters. Use the pipe to separate several expressions.
          includeExpression = '.*\\.IS_VALID'

          // 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.
          includeTypes = '.*'

          // Force a type depending on data type nullability. Default is ALL.
          // 
          // - ALL - Force a type regardless of whether data type is nullable or not (default)
          // - NULL - Force a type only when data type is nullable
          // - NOT_NULL - Force a type only when data type is not null
          nullability = 'ALL'

          // Force a type on ALL or specific object types. Default is ALL. Options include: 
          // ATTRIBUTE, COLUMN, ELEMENT, PARAMETER, SEQUENCE
          objectType = 'ALL'
        }
      }
    }
  }
}

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

Using SQL to match column names

If you want to match your column names based on more complex criteria not supported by jOOQ yet, you can supply the matching column names using a SQL query that runs against your dictionary views. The following example uses a SQL query to find all columns that are "probably boolean", and applies a data type converter to them:

XML (standalone and maven)
Programmatic
Gradle
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.9.2.xsd">
  <generator>
    <database>
      <forcedTypes>
        <forcedType>

          <!-- Rewrite types to BOOLEAN -->
          <userType>java.lang.Boolean</userType>
          <converter>com.example.YNBooleanConverter</converter>

          <!-- All Oracle columns that have a default of 'Y' or 'N' are probably boolean -->
          <sql>
            SELECT owner || '.' || table_name || '.' || column_name
            FROM all_tab_cols
            WHERE data_default IN ('Y', 'N')
          </sql>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>
new org.jooq.util.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()
      .withForcedTypes(
        new ForcedType()

          // Rewrite types to BOOLEAN
          .withUserType("java.lang.Boolean")
          .withConverter("com.example.YNBooleanConverter")

          // All Oracle columns that have a default of 'Y' or 'N' are probably boolean
          .withSql("" +
          "SELECT owner || '.' || table_name || '.' || column_name" +
          "FROM all_tab_cols" +
          "WHERE data_default IN ('Y', 'N')" +
          "")
      )
    )
  )
myConfigurationName(sourceSets.main) {
  generator {
    database {
      forcedTypes {
        forcedType {

          // Rewrite types to BOOLEAN
          userType = 'java.lang.Boolean'
          converter = 'com.example.YNBooleanConverter'

          // All Oracle columns that have a default of 'Y' or 'N' are probably boolean
          sql = '''
            SELECT owner || '.' || table_name || '.' || column_name
            FROM all_tab_cols
            WHERE data_default IN ('Y', 'N')
          '''
        }
      }
    }
  }
}

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

Please see the relevant sections of the manual to get more information about using converters or bindings.

Feedback

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

The jOOQ Logo