java - 是否有 JSON_CONTAINS 谓词的 QueryDSL 表示？

Question

在我的 MySQL 数据库 (8.0.23) 上，我有一个 JSON 列是 multi value indexed .我想使用 QueryDSL 通过 JSON_CONTAINS 使用索引进行查询。我已验证该列已正确编制索引，并且在运行查询时正在使用该索引；例如，

EXPLAIN SELECT *
FROM user u
WHERE JSON_CONTAINS(JSON_EXTRACT(u.alias,'$'), JSON_QUOTE('John'));

表示多值索引正在按预期使用。

到目前为止，我已经尝试过

Expressions.booleanTemplate("JSON_CONTAINS(JSON_EXTRACT({0}, '$'), JSON_QUOTE({1})) = 1", expression, str)

并以 BooleanExpression 作为谓词，使用 QueryDSL 实现同样的效果；请注意，如果没有 = 1，执行它会抛出以下错误。

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362)
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
    at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:282)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:636)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:748)

确实，= 1 似乎阻止索引用于同一查询。例如，

EXPLAIN SELECT *
FROM user u
WHERE JSON_CONTAINS(JSON_EXTRACT(u.alias,'$'), JSON_QUOTE('John')) = 1;

表示没有使用索引。 != 0、IS TRUE 或 IS NOT FALSE

也是一样的

因此，我遇到的问题是，对于 QueryDSL，我还没有找到在 JSON_CONTAINS(JSON_EXTRACT(u.alias,'$'), JSON_QUOTE 上使用 BooleanExpression 的方法('John')) 末尾没有 = 1。但是，当 JSON_CONTAINS

末尾有 = 1 时，MySQL 似乎没有使用多值索引

我尝试了来自 https://stackoverflow.com/a/68684997/18476687 的建议但到目前为止运气不好。

有没有办法在 QueryDSL 上不使用 = 1 来表示 JSON_CONTAINS(JSON_EXTRACT(u.alias,'$'), JSON_QUOTE('John'))以便使用多值索引？

Answer 1

我找到了一个非常有效的解决方法。通过扩展 Hibernate 方言，您可以创建返回任意 SQL 的自定义 HQL 函数。

因此您可以使用自定义 HQL 函数 custom_json_contains 定义方言，例如:

public class CustomMySQL8Dialect extends MySQL8Dialect {
    public CustomMySQL8Dialect() {
        registerFunction("custom_json_contains", new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN, "JSON_CONTAINS(JSON_EXTRACT(?1, ?2), JSON_QUOTE(?3)) AND 1"));
    }
}

然后您可以在 QueryDSL where 子句(或 HQL)中使用此方法:

public static <T> BooleanExpression jsonContains(Expression<T> expression, String path, String str) {
    return Expressions.booleanTemplate("CUSTOM_JSON_CONTAINS({0}, {1}, {2}) = 1", expression, path, str);
}

这将使 Hibernate 呈现以下 SQL:

JSON_CONTAINS(JSON_EXTRACT(u.alias, '$'), JSON_QUOTE('John')) AND 1=1

= 1 仍然存在，但至少现在它什么也没做!