Win a copy of Modern JavaScript for the Impatient this week in the Server-Side JavaScript and NodeJS forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Regarding apply JOIN by using JPQL.

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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.
 
Don't get me started about those stupid light bulbs.
    Bookmark Topic Watch Topic
  • New Topic