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, anENUMtype or aCHECK 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!