New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9

Forced types

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

The code generator allows users to override column data types in three different ways:

All of this can be done using forced types.

Matching of forced types

The <database> configuration's <forcedTypes> element can contain many <forcedType> elements and the generator will always pick the first of these definitions, where all given match predicates match the database column at hand. These are the available match predicates (all are optional) and the generator evaluates them each in turn in the given order:

  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 name; any definition's name matching the regular expression will be considered not matching this forced type
  4. <includeExpression>: This predicate is a regular expression which is matched against the definition's name
  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
  6. <includeTypes>: This predicate is a regular expression which is matched against the name of the definition's type (provided that it has one)
  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

Data type rewriting

XML (standalone and maven)
Programmatic
Gradle
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.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.
               If provided, both "includeExpression" and "includeTypes" must match. -->
          <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.

               If provided, both "includeExpression" and "includeTypes" must match. -->
          <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, including 
               ATTRIBUTE, COLUMN, ELEMENT, PARAMETER, SEQUENCE -->
          <objectType>ALL</objectType>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>
new org.jooq.meta.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.
          // If provided, both "includeExpression" and "includeTypes" must match.
          .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.
          // 
          // If provided, both "includeExpression" and "includeTypes" must match.
          .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, including 
          // 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.
          // If provided, both "includeExpression" and "includeTypes" must match.
          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.
          // 
          // If provided, both "includeExpression" and "includeTypes" must match.
          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, including 
          // ATTRIBUTE, COLUMN, ELEMENT, PARAMETER, SEQUENCE
          objectType = 'ALL'
        }
      }
    }
  }
}

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

Mapping to user type with a converter

Both user type mappings work the same way, regardless if you're using a org.jooq.Converter or a org.jooq.Binding.

XML (standalone and maven)
Programmatic
Gradle
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.xsd">
  <generator>
    <database>
      <!-- The first matching forcedType will be applied to the data type definition. -->
      <forcedTypes>
        <forcedType>

          <!-- Specify the Java type of your custom type. This corresponds to the Converter's <U> type. -->
          <userType>java.time.Instant</userType>

          <!-- Associate that custom type with your converter. -->
          <converter>com.example.LongToInstantConverter</converter>

          <!-- These are the same as for type rewriting -->
          <includeExpression>.*\.DATE_OF_.*</includeExpression>
          <includeTypes>.*</includeTypes>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>
new org.jooq.meta.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()

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

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

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

          // These are the same as for type rewriting
          .withIncludeExpression(".*\\.DATE_OF_.*")
          .withIncludeTypes(".*")
      )
    )
  )
myConfigurationName(sourceSets.main) {
  generator {
    database {

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

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

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

          // These are the same as for type rewriting
          includeExpression = '.*\\.DATE_OF_.*'
          includeTypes = '.*'
        }
      }
    }
  }
}

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

For more information about using converters, please refer to the manual's section about custom data type conversion.

Mapping to user type with an inline converter

For convenience, you can inline your converter code directly into the configuration instead of providing a class reference.

XML (standalone and maven)
Programmatic
Gradle
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.xsd">
  <generator>
    <database>
      <!-- The first matching forcedType will be applied to the data type definition. -->
      <forcedTypes>
        <forcedType>

          <!-- Specify the Java type of your custom type. This corresponds to the Converter's <U> type. -->
          <userType>com.example.MyEnum</userType>

          <!-- Associate that custom type with your inline converter. -->
          <converter>org.jooq.Converter.ofNullable(
            Integer.class, MyEnum.class, 
            i -&gt; MyEnum.values()[i], MyEnum::ordinal
          )</converter>

          <!-- These are the same as for type rewriting -->
          <includeExpression>.*\.DATE_OF_.*</includeExpression>
          <includeTypes>.*</includeTypes>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>
new org.jooq.meta.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()

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

          // Specify the Java type of your custom type. This corresponds to the Converter's <U> type.
          .withUserType("com.example.MyEnum")

          // Associate that custom type with your inline converter.
          .withConverter("org.jooq.Converter.ofNullable(" +
          "Integer.class, MyEnum.class, " +
          "i -> MyEnum.values()[i], MyEnum::ordinal" +
          ")")

          // These are the same as for type rewriting
          .withIncludeExpression(".*\\.DATE_OF_.*")
          .withIncludeTypes(".*")
      )
    )
  )
myConfigurationName(sourceSets.main) {
  generator {
    database {

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

          // Specify the Java type of your custom type. This corresponds to the Converter's <U> type.
          userType = 'com.example.MyEnum'

          // Associate that custom type with your inline converter.
          converter = '''org.jooq.Converter.ofNullable(
            Integer.class, MyEnum.class, 
            i -> MyEnum.values()[i], MyEnum::ordinal
          )'''

          // These are the same as for type rewriting
          includeExpression = '.*\\.DATE_OF_.*'
          includeTypes = '.*'
        }
      }
    }
  }
}

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

Mapping to an enum user type with a converter

If your user type is a Java enum, you can use the <enumConverter/> convenience flag instead of an explicit converter per enum type. This will apply the built-in org.jooq.impl.EnumConverter.

XML (standalone and maven)
Programmatic
Gradle
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.xsd">
  <generator>
    <database>
      <!-- The first matching forcedType will be applied to the data type definition. -->
      <forcedTypes>
        <forcedType>

          <!-- Specify the Java type of your custom type. This corresponds to the Converter's <U> type. -->
          <userType>com.example.MyEnum</userType>

          <!-- Apply the built in org.jooq.impl.EnumConverter. -->
          <enumConverter>true</enumConverter>

          <!-- These are the same as for type rewriting -->
          <includeExpression>.*\.MY_STATUS</includeExpression>
          <includeTypes>.*</includeTypes>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>
new org.jooq.meta.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()

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

          // Specify the Java type of your custom type. This corresponds to the Converter's <U> type.
          .withUserType("com.example.MyEnum")

          // Apply the built in org.jooq.impl.EnumConverter.
          .withEnumConverter(true)

          // These are the same as for type rewriting
          .withIncludeExpression(".*\\.MY_STATUS")
          .withIncludeTypes(".*")
      )
    )
  )
myConfigurationName(sourceSets.main) {
  generator {
    database {

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

          // Specify the Java type of your custom type. This corresponds to the Converter's <U> type.
          userType = 'com.example.MyEnum'

          // Apply the built in org.jooq.impl.EnumConverter.
          enumConverter = true

          // These are the same as for type rewriting
          includeExpression = '.*\\.MY_STATUS'
          includeTypes = '.*'
        }
      }
    }
  }
}

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

Mapping to user type with a binding

Just switch the converter configuration to a binding configuration.

XML (standalone and maven)
Programmatic
Gradle
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.xsd">
  <generator>
    <database>
      <!-- The first matching forcedType will be applied to the data type definition. -->
      <forcedTypes>
        <forcedType>

          <!-- Specify the Java type of your custom type. This corresponds to the Binding's <U> type. -->
          <userType>java.time.Instant</userType>

          <!-- Associate that custom type with your converter. -->
          <binding>com.example.LongToInstantBinding</binding>

          <!-- These are the same as for type rewriting -->
          <includeExpression>.*\.DATE_OF_.*</includeExpression>
          <includeTypes>.*</includeTypes>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>
new org.jooq.meta.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()

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

          // Specify the Java type of your custom type. This corresponds to the Binding's <U> type.
          .withUserType("java.time.Instant")

          // Associate that custom type with your converter.
          .withBinding("com.example.LongToInstantBinding")

          // These are the same as for type rewriting
          .withIncludeExpression(".*\\.DATE_OF_.*")
          .withIncludeTypes(".*")
      )
    )
  )
myConfigurationName(sourceSets.main) {
  generator {
    database {

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

          // Specify the Java type of your custom type. This corresponds to the Binding's <U> type.
          userType = 'java.time.Instant'

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

          // These are the same as for type rewriting
          includeExpression = '.*\\.DATE_OF_.*'
          includeTypes = '.*'
        }
      }
    }
  }
}

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.

XML (standalone and maven)
Programmatic
Gradle
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.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.meta.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.

For more information about using converters, please refer to the manual's section about custom data type bindings.

Feedback

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

The jOOQ Logo