File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Regarding apply JOIN by using JPQL. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Regarding apply JOIN by using JPQL." Watch "Regarding apply JOIN by using JPQL." New topic
Author

Regarding apply JOIN by using JPQL.

Amita Shiroya
Greenhorn

Joined: Sep 20, 2008
Posts: 4
Hi All,
I am facing problem while apply *JOIN* on 2 Tables(Parent-Child) by using Java Persistence Query Language (JPQL).
Code is given below :

Tables:

While apply *JOIN* query by using JPQL like,



Here, Employee,Task and Project are entity classes.
taskCollection is a collection that is declared on Employee entity class and entityManager is an object of EntityManager.
Here, if I am passing projectId = 3,
So I get result like :

EmpID : 1
TaskID : 1
TaskID : 5

EmpID : 2
TaskID : 2
TaskID : 6

EmpID : 3
TaskID : 3
TaskID : 7

EmpID : 4
TaskID : 4
TaskID : 8

Instead of get,
EmpID : 1
TaskID : 1

EmpID : 2
TaskID : 2

EmpID : 3
TaskID : 3

EmpID : 4
TaskID : 4

If I write JOIN query by using SQL like,


and pass projectId = 3 so I get result like :
EmpID : 1
TaskID : 1

EmpID : 2
TaskID : 2

EmpID : 3
TaskID : 3
EmpID : 4
TaskID : 4

This one is correct result.

Here *JPQL* and *SQL* query, both are same but the query output is different.

Can anybody will help me to get my expected result by using *JPQL* query or give me suggestion how can I write *JPQL* query to get my expected result .
Expected result is : (Consider above tables)
If I pass projectId = 3
Output is :
EmpID : 1
TaskID : 1

EmpID : 2
TaskID : 2

EmpID : 3
TaskID : 3

EmpID : 4
TaskID : 4

Tell me if any attribute that I have to set on entity classes while apply JOIN using JPQL query.

Waiting for your positive reply,
Thanks,
Amita.
[ September 21, 2008: Message edited by: Amita Shiroya ]
masse Satriana
Greenhorn

Joined: Sep 21, 2008
Posts: 8
Hi Mita,
Are you coming from Indonesia? your name familiar with me

I suggest not using createQuery() but createNativeQuery().

Rgds,
Masse Satriana


A warm family is worthy enough than hard working.
Amita Shiroya
Greenhorn

Joined: Sep 20, 2008
Posts: 4
Hi
i don't want to use createNativeQuery() becz if i use it, i have to write database specific query.i want to create only JPQL query to get expected result.

Thanks,
Amita.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Regarding apply JOIN by using JPQL.