JPA query with subselect

Question

I would like to express the following query

select * from(
            select pI, max(pAs) as pAs from mytable
            and pAs>=?1 and pAs<=?2
            and pI like 'DE%%'
            and pE like ?6
            group by pI
            ) as x
            inner join mytable as a
            on a.pI=x.pI
            and a.pAs=x.pAs

using the criteriabuilder from hibernate. I did not succeed and thus far use a nativeQuery instead.

However, the crux of the matter (which i could not resolve) appears to be that I need two properties returned in the subselect. All the examples i found only return one property.

Is this really a limitation of jpa/hibernate or is there a way to do what i want? Any help/pointers are appreciated.

Why do you want to use Criteria API when the SQL statement does what you want — Simon Martinelli, Oct 25 '19 at 10:18
@alexvaluiskyi I do have an entity for the final query. Will having "temporary" entities allow me to return multiple properties from the subselect? Do you happen to have a working example? — matze999, Oct 25 '19 at 10:43
Do you want to get subselect properties as query result? Or do you need it only for using in clauses? — Oleksii Valuiskyi, Oct 25 '19 at 12:00
@alexvaluiskyi In this case I really only need them for the self-join. — matze999, Oct 25 '19 at 12:02
@SimonMartinelli Well, i suppose i would simply like to know whether the limitation is with JPA or with myself ... :) — matze999, Oct 25 '19 at 12:20
I personally hate Criteria API but have a look here https://thoughts-on-java.org/hibernate-tip-subquery-criteriaquery/ — Simon Martinelli, Oct 25 '19 at 12:30
You should create subquery and use native-query-ON-clauses as predicates in subquery. Then use builder.exists(subquery) as predicate in main query — Oleksii Valuiskyi, Oct 25 '19 at 13:38
I have answered question like this here https://stackoverflow.com/questions/58096427/how-to-join-multiple-columns-using-specification-in-spring-data/58099828#58099828 Try to use subquery code example from UsersNotInSelectedOperations method — Oleksii Valuiskyi, Oct 25 '19 at 13:49
@alexvaluiskyi Hi Alex, i am not following how i am to use our suggestion in the context of my self-join above. I think the issue between the problem you pointed to and this one is that they are not equivalent. — matze999, Oct 28 '19 at 07:36

score 0 · Answer 1 · answered Oct 28 '19 at 13:53

You did not provide any info about your entity. So I suspect that you have Mytable entity class and it has Long field pAs

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Mytable> query = builder.createQuery(Mytable.class);
Root<Mytable> root = query.from(Mytable.class);

Subquery<Long> subquery = query.subquery(Long.class); // max(pAs)
Root<Mytable> subRoot = subquery.from(Mytable.class);

Predicate predicate1 = 
         builder.equal(root.get("pI"), subRoot.get("pI"));

Predicate predicate2 = 
         builder.greaterThan(subRoot.get("pAs"), 0);

Predicate predicate3 = 
         builder.lessThan(subRoot.get("pAs"), 100);

//There is a simple example. But you can add as many predicates as you need
Predicate fullPredicate = 
         builder.and(predicate1, predicate2, predicate3);

Predicate havingPredicate = 
         builder.equal(root.get("pAs"), builder.max(subRoot.get("pAs")));

subquery.select(builder.max(subRoot.get("pAs"))).where(fullPredicate)
    .groupBy(subRoot.get("pI")).having(havingPredicate);

query.select(root).where(builder.exists(subquery));

List<Mytable> result = entityManager.createQuery(query).getResultList();

JPA query with subselect

1 Answers1