Using native sql in java criteria predicate

Question

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)
..

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

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)
..

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?

StanislavL · Accepted Answer · 2015-07-28 06:11:14Z

1

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

Restrictions.in("theFormulaField", param)

answered Jul 28, 2015 at 6:11

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HAYMbl4 Over a year ago

thank. Result code follows: @Formula("CASE WHEN state = 'ARCHIVE' THEN 1 ELSE 0 END") private boolean archived; and using this formula: criteriaBuilder().equal(root().get(Act_.archived), false);

Collectives™ on Stack Overflow

Using native sql in java criteria predicate

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related