Is there a way to check query bind values in jOOQ?

Question

We have a problem with queries that contain IN conditions with many bind params, because they have many variations and quickly overflow the query cache.

I want to check in condition clause of my sql statement and throw an exception, if there more than 30 bind values specified for in clause.

Can I use VisitListener for that?

I can use org.jooq.VisitContext#clause to find in or not in condition, but I can’t check org.jooq.impl.InCondition#values size without reflection.

Now I'm forced to do something like this:

public class MyVisitListener extends DefaultVisitListener {
    @Override
    public void visitStart(VisitContext context) {
        if (context.clause() == Clause.CONDITION_IN || context.clause() == Clause.CONDITION_NOT_IN) {
            try {
                Field field = context.queryPart().getClass().getDeclaredField("values");
                field.setAccessible(true);
                Object value = field.get(context.queryPart());
                if (((Object[]) value).length > 30) {
                    throw new IllegalArgumentException("More than 30 bind values specified!");
                }
            } catch (NoSuchFieldException | IllegalAccessException e) {
                //throw new UnknownException("Can`t check size of field 'values' in " + context.queryPart().getClass().getName(), e);
            }
        }
    }
}

Is there a more convenient way (jOOQ 3.10.8 pro)?

Answer 1

Another option, an often acceptable workaround to your problem, is enabling the optional in-list padding in jOOQ:

<settings xmlns="http://www.jooq.org/xsd/jooq-runtime-3.10.8.xsd">
  <inListPadding>true</inListPadding>
</settings>

It's not exactly what you want, but it might be exactly what you need. In short, it makes jOOQ generate IN conditions that are always power-of-2 long. This makes it much easier on the query cache.

Instead of this (8 queries):

-- Original
SELECT * FROM AUTHOR WHERE ID IN (?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?, ?, ?, ?)

you'll see this (4 queries, and getting better as the length grows):

-- Padded
SELECT * FROM AUTHOR WHERE ID IN (?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?, ?, ?, ?)

Answer 2

I came here to suggest IN list padding (as Petr did very nicely, in his answer). Other than that, the proper VisitListener solution would be to listen to the CONDITION_IN clause, remember that information in some sort of stack, and then count all the FIELD clauses that come immediately afterwards, until the CONDITION_IN clause ends again. And keep in mind that instead of a bind value, there might also be an expression (again containing several FIELD clauses) in the IN list.

Clearly, Petr's suggestion is much easier.