How to create a JPA query with LEFT OUTER JOIN

java jpa jpql
Bryan picture Bryan · Apr 18, 2012 · Viewed 117k times · Source

I am starting to learn JPA, and have implemented an example with JPA query, based on the following native SQL that I tested in SQL Server:

SELECT f.StudentID, f.Name, f.Age, f.Class1, f.Class2 
FROM Student f 
    LEFT OUTER JOIN ClassTbl s ON s.ClassID = f.Class1 OR s.ClassID = f.Class2
WHERE s.ClassName = 'abc'

From the above SQL I have constructed the following JPQL query:

SELECT f FROM Student f LEFT JOIN f.Class1 s;

As you can see, I still lack the condition OR s.ClassID = f.Class2 from my original query. My question is, how can I put it into my JPQL?

Answer

Sai Ye Yan Naing Aye picture Sai Ye Yan Naing Aye · Apr 18, 2012

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.

Related questions

Adding IN clause List to a JPA Query

I have built a NamedQuery that looks like this: @NamedQuery(name = "EventLog.viewDatesInclude", query = "SELECT el FROM EventLog el WHERE el.timeMark >= :dateFrom AND " + "el.timeMark <= :dateTo AND " + "el.name IN (:inclList)") What I want to do is …

Read More
IN-clause in HQL or Java Persistence Query Language

I have the following parametrised JPA, or Hibernate, query: SELECT entity FROM Entity entity WHERE name IN (?) I want to pass the parameter as an ArrayList<String>, is this possible? Hibernate current tells me, that java.lang.ClassCastException: …

Read More
JPA Native Query select and cast object

I have got an Object Admin which extends User. By default both Objects are in the table User_ of my Derby Database (included fields from Admin). Normally I'd select an User like this: CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<User&…

Read More