postgresql - 如何为标准 jpa2 (hibernate) 创建自定义谓词？

Question

我正在尝试从旧 API 迁移到 JPA2 Criteria API(Hibernate 作为 JPA 提供程序)。我对 PostgreSQL JSONB 类型的自定义条件有疑问。在之前的 Criteria 实现中，它可以通过创建 SQLCriterion 来完成，我可以在其中直接对 native SQL 进行操作。在 JPA2 实现中，我找不到类似的解决方案，所以我决定扩展 AbstractSimplePredicate。

这里是简单版:

   class JsonEqual<Y> extends AbstractSimplePredicate {

    private static final String JSON_NESTED_FIELD_EXTRACTOR = "->>";
    private static final String JSON_EQUAL_OPERATOR = "=";
    private final Expression<?> field;
    private final Expression<?> value;
    private final Path<Y> path;

    public JsonEqual(CriteriaBuilderImpl cb, Path<Y> path, Expression<?> field, Expression<?> value) {
        super(cb);
        this.path = path;
        this.field = field;
        this.value = value;
    }

    @Override
    public void registerParameters(ParameterRegistry registry) {
        Helper.possibleParameter(path, registry);
        Helper.possibleParameter(field, registry);
        Helper.possibleParameter(value, registry);
    }

    @Override
    public String render(boolean isNegated, RenderingContext renderingContext) {
        return ((Renderable) path).render(renderingContext)
                        + JSON_NESTED_FIELD_EXTRACTOR
                        + ((Renderable) field).render(renderingContext)
                        + JSON_EQUAL_OPERATOR
                        + ((Renderable) value).render(renderingContext);
    }
}

将 jpa2 条件编译为 HQL 时出现问题。谓词呈现的查询部分格式不正确(无法识别“->>”运算符)。

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: > near line 1, column 515 [select new com.hibernate.VenueEntity(generatedAlias0.id) from com.hibernate.VenueEntity as generatedAlias0 where ( generatedAlias0.metadata->>:param3=:param4 ) order by generatedAlias0.created asc]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:291) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:186) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:77) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:553) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:662) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:3324) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.query.criteria.internal.CriteriaQueryImpl$1.buildCompiledQuery(CriteriaQueryImpl.java:318) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.query.criteria.internal.compile.CriteriaCompiler.compile(CriteriaCompiler.java:149) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:3617) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:203) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_152]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_152]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_152]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_152]

在这种情况下是否有可能为 JPA2 实现自定义谓词？

Answer 1

我没有尝试用 JSONB 做这样的事情，但我用数组和 && 运算符做了。因此，我认为以下解决方案将解决您的问题:

1. 创建并注册扩展标准方言的自定义 Postgres 方言。
2. 方言 - registerFunction("json_extract",
new SQLFunctionTemplate(HibernateTypeWhichYouNeed.INSTANCE, "(?1 ->> ?2)"));
3. 在客户端代码中 -cb.equal(
cb.function("json_extract", JavaClassWhichYouNeed.class, expr1, expr2),
expr3);