• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Regarding apply JOIN by using JPQL.

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Mita,
Are you coming from Indonesia? your name familiar with me

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

Rgds,
Masse Satriana
 
Amita Shiroya
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic