JSON_CONTAINS 谓词是否有 QueryDSL 表示？

【问题标题】：JSON_CONTAINS 谓词是否有 QueryDSL 表示？(Is there a QueryDSL representation of JSON_CONTAINS predicate?)
【发布时间】：2022-06-11 04:19:37
【问题描述】：

在我的 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.(HQLQueryPlan.java:113)
    at org.hibernate.engine.query.spi.HQLQueryPlan.(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('John')) 上没有 = 1 结尾的 BooleanExpression 的方法。但是，当 JSON_CONTAINS 末尾有 = 1 时，MySQL 似乎没有使用多值索引

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

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

【解决方案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  BooleanExpression jsonContains(Expression 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 仍然存在，但至少现在它什么也没做！

分享于2022年07月17日 java mysql mysql-8.0 querydsl spring 问答