JPQL Left Outer Join IN One to Many mapping

UseCase Description

Retrieve the employees based on the below criteria where in  Departments and Employees entities are associated by One to Many mapping.

"DepartmentId value is NULL or passed in departmentId as parameter".

 

JPA by default does a inner join in between entities .The '.' notation to mean an inner join unless it is explicietly stated to use an outer join

select o from Employee o where o.departments.departmentId is null or o.departments.departmentId=:departmentId

But the above jpql translates to below Sql

select * from Employees t0,Departmemts t1 where (to.depratmentID is null) or (to.departmentId = :1 )) and (to.departmentId = t1.departmentId))

To achieve the above query criteria use left outer join as given below

"Select o from Employees o  LEFT OUTER JOIN o.departments as Dept where Dept is null or Dept.departmentId=:deptNo".

 The above Left outer JPQL will rsult in below SQL.

Select * from Employees t0 where (((t0.departmentId IS NULL) OR (t0.departmentId = :1))

Comments:

Pachunoori, this was handy. Thank you. Thanks google also.

Posted by aidar on June 20, 2011 at 09:51 AM IST #

Select * from Employees t0 where (((t0.departmentId IS NULL) OR (t0.departmentId = :1))

seems like you have extra opening bracket after where clause.

Posted by aidar on June 20, 2011 at 09:58 AM IST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Tips and Tricks from Oracle's JDeveloper & ADF QA

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today