java - 有没有办法在 jOOQ 中检查查询绑定(bind)值？

Question

我们遇到包含 IN 条件和许多绑定(bind)参数的查询问题，因为它们有很多变体并且会很快溢出查询缓存。

如果为 in 子句指定的绑定(bind)值超过 30 个，我想检查我的 sql 语句的条件子句并抛出异常。

我可以为此使用 VisitListener 吗？

我可以使用 org.jooq.VisitContext#clause 来查找 in 或 not in 条件，但我无法检查 org.jooq.impl。 InCondition#values 没有反射的大小。

现在我被迫做这样的事情:

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);
            }
        }
    }
}

有没有更方便的方法(jOOQ 3.10.8 pro)？

Answer 1

另一种选择是启用 optional in-list padding in jOOQ，这是您的问题通常可以接受的解决方法。 :

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

这不一定是您想要的，但可能正是您需要的。简而言之，它使 jOOQ 生成始终为 2 的幂次方的 IN 条件。这使得查询缓存更容易。

而不是这个(8 个查询):

-- 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 (?, ?, ?, ?, ?, ?, ?, ?)

你会看到这个(4 个查询，并且随着长度的增长而变得更好):

-- 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 (?, ?, ?, ?, ?, ?, ?, ?)