Regarding apply JOIN by using JPQL.

[Amita Shiroya]

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:

Employee (Parent Table)
 
EmpID    Name    
   1    Tina
   2     Mina
   3    Gita
   4    Sita
 
Task (Child Table)
 
TaskID     ProjectID      EmpID
    1          3        1
    2          3        2
    3          3        3
    4          3        4
    5          5        1
    6          5        2
    7          5        3
    8          5        4

While apply *JOIN* query by using JPQL like,

 
public static <Employee> getEmpAsPerProjectId ( int projectId ) {
 
List<Employee> entity = null ;
try {
        StrSql = "SELECT emp FROM Employee emp INNER JOIN emp.taskCollection tsk WHERE tsk.ProjectID =   :-P	 rojectID"
        Query query = entityManager.createQuery (strSql).setParameter ("ProjectID",new Project ( projectId )) ;
        entity  = query.getResultList () ;
 
        for (Employee e : entity ) {
 
                    System.out.println ("EmpID :"+e.getEmpID ());
                    for ( Task task : e.getTaskCollection ()) {
                         System.out.println ("TaskID : "+task.getTaskID ());
                    }
 
        }
 } catch( Exception ex ) {
        ex.printStackTrace () ;
        entity = null ;
 }
 
    return entity ;
 
}

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,

 
"SELECT * FROM Employee emp INNER JOIN Task tsk ON emp.EmpID = tsk.EmpID WHERE tsk.ProjectID = " + projectId ;

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]

Hi Mita,
Are you coming from Indonesia? your name familiar with me

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

Rgds,
Masse Satriana

[Amita Shiroya]

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.