How to Make a JPA Query with Left Outer Join

How to create a JPA query with LEFT OUTER JOIN

Write this;

 SELECT f from Student f LEFT JOIN f.classTbls s WHERE s.ClassName = 'abc'

Because your Student entity has One To Many relationship with ClassTbl entity.

How to make a JPA query with LEFT OUTER JOIN

This query can be expressed in JPQL as follows:

SELECT p, AVG(i.rank) as rank
FROM Item i RIGHT JOIN i.profile p
GROUP BY p
ORDER BY rank DESC

Note that you can't write arbitrary JOINs in JPA, only JOINs over relationships, therefore you need a RIGHT JOIN here, because your relationship is unidirectional and source of the relationship should be at the left side.

This query could be translated in JPA 2.0 Criteria API in pretty straightforward way, however, the last time I checked it Hibernate's implementation of Criteria API didn't support right joins (and Play Framework uses Hibernate underneath). So, I think you have to use JPQL query in this case.

how to create jpql with left outer join

If you have some kind of following structure:

Class EntityA
--------
long Id
Set<EntityB> Bs

Class EntityB
-------
long Id
EntityA A

I think following should work

SELECT a FROM EntityA a WHERE a.Bs IS EMPTY

---

Related Topics