Available in versions: Dev (3.18) | Latest (3.17)

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

Auto-inline bind values

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

Bind values are an important concept in SQL, for performance reasons, as they simplify caching of prepared statements in some RDBMS. jOOQ always creates bind values by default, when you write this:

-- Normally, a bind parameter marker is generated
AUTHOR.ID = ?
// This is the same as AUTHOR.ID.eq(val(1, AUTHOR.ID))
AUTHOR.ID.eq(1);

In some cases, however, it is better not to use a bind variable, but to create inline values, instead, so the optimiser can better apply its statistics. This is useful mainly when:

  • The column is a constant discriminator column in a view, for example
  • The column has very skewed statistics and only few possible values (e.g. a BOOLEAN, an ENUM type or a CHECK COL IN (1, 2, 3)) constraint.

In those cases, it can be useful to enable Settings.transformInlineBindValuesForFieldComparisons and implement a org.jooq.TransformProvider as follows:

Configuration configuration = ...
configuration.settings().setTransformInlineBindValuesForFieldComparisons(true);
configuration.set(new TransformProvider() {
    @Override
    public boolean inlineBindValuesForFieldComparisons(Field<?> field) {
        return field.getType() == Boolean.class
            || field.getDataType().isEnum(); // Or, perhaps, limit this only to certain enums
    }
});

Now, all queries whose predicates match the above TransformProvider content will have their relevant bind values inlined. For example:

-- Inlining applies to some columns now, not all
AUTHOR.ID = ? AND AUTHOR.STATUS = 'ACTIVE'
AUTHOR.ID.eq(1).and(
    AUTHOR.STATUS.eq(Status.ACTIVE));

Related settings include:

Feedback

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

The jOOQ Logo