在Java条件谓词中使用本机SQL [英] Using native sql in java criteria predicate

问题描述

我有一个谓词（ javax.persistence.criteria.Predicate ），该谓词如下过滤原始数据：

I have a predicate(javax.persistence.criteria.Predicate) which filters raw data as follows:

    public Predicate byAccountsId(Collection<Long> accountsId) {
        ParameterExpression<?> param = createParam(AOraArrayUserType.class, new AOraArrayUserType(accountsId));
        return criteriaBuilder().or(
                criteriaBuilder()
                        .equal(criteriaBuilder().function("in_ex", Long.class, actSourceJoin().get(Account_.id),
                                param), 1),
                criteriaBuilder().equal(
                        criteriaBuilder().function("in_ex", Long.class, actDestinationJoin().get(Account_.id),
                                param), 1));
    }

此谓词构建查询的下一部分：

This predicate builds the next part of the query:

..
where
..
act.source_id in (accountsId.values())
or act.destination_id in (accountsId.values() - array ids)
..

它工作正常，但原始数据可能过多。我想使用 Oracle功能索引，该功能可从查询结果中删除不必要的数据。我尝试按如下方式重写我的谓词：

It works fine but there may be too much raw data.I want to use "Oracle functional index", which removes unnecessary data from the query results. I tried to rewrite my predicate as follows:

    public Predicate byAccountsId(Collection<Long> accountsId) {
        ParameterExpression<?> param = createParam(AOraArrayUserType.class, new AOraArrayUserType(accountsId));
        return criteriaBuilder().or(
                criteriaBuilder().literal(
                        Restrictions.sqlRestriction("case when state != 'ARCHIVE' then source_id else null end"))
                        .in(param));
    }

重新启动查询可正常运行，但不返回任何结果。但是，当我将结果查询复制到sql Developer并运行查询时，它将返回预期结果。

Resurlt query builds fine but returns no result. But when I copy result query to sql developer and run the query it returns the expected result.

部分查询是由新谓词构建的：

Part of the query which is build by new predicate:

..
where 
..
(case when act.state != 'ARCHIVE' then act.source_id else null end) in (accountsId.values() - array ids)
..

有人知道我在使用新谓词时为什么没有得到正确的结果吗？
并且我可以将 hibernate.criterion.Restrictions 与 javax.persistence.criteria.Predicate 一起使用吗？

Does anybody know why I don't get the correct result when I'm using the new predicate? And can I use hibernate.criterion.Restrictions with javax.persistence.criteria.Predicate?

推荐答案

您可以为实体中的表达式定义 @Formula （AOraArrayUserType）并使用条件中的字段

You can define @Formula for the expression in your Entity (AOraArrayUserType) and use the field in criteria

Restrictions.in("theFormulaField", param)

这篇关于在Java条件谓词中使用本机SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！