JPA criteria query + self join on rowid

Question

I'm trying to write JPA criteria query.

Select * from classA t1 
inner join

  (SELECT rowid
     FROM classA 
      where  conditions...
      ORDER BY clause
   )t2 
 on t1.rowid = t2.rowid

ORDER BY clause
where  rownum <= 500

I'm having problems in joining the main criteria query with inner criteria query(with predicates)? .Is there a possibilty to do join on criteria queries(not on roots)?

Any help is much appreciated. note:domain class already having composite PK- annoted with embeddedId.

Are you just trying to limit the result to 500 records? If so, write the query as you normally would without worrying about limiting the results, and then call [`Query#setMaxResults(500)`](http://docs.oracle.com/javaee/7/api/javax/persistence/Query.html#setMaxResults%28int%29). Your JPA provider should translate the query into something reasonable to limit the results for you. — DannyMo, Sep 16 '13 at 16:00
yes for the final typed query i can set the max results. But i'm facing problem in self joining the table. i.e., predicates are to be applied within the inner query before joining. We can't join criteria queries. How do i go about it? — Amit Yatagiri, Sep 18 '13 at 06:27
I am facing same issue. I know its old thread. But did you find any solution for this? — user613114, Feb 21 '15 at 14:33

score 0 · Answer 1 · answered Nov 08 '16 at 14:22

CriteriaQuery joins can only be defined on explicitly defined relationships between entities. E.g. in your example for ClassA to join to itself there would need an explicit field such as this:

@ManyToOne
@JoinColumn(name = "linked_class_a")
private ClassA linkedClassA

Is there a possibilty to do join on criteria queries(not on roots)?

The simple answer is no - as you've alluded to, it's possible for a CriteriaQuery to define multiple roots but these end up as cartesian products (CROSS JOINs), which can be very inefficient.

JPA criteria query + self join on rowid

1 Answers1