JPQL query on joined tables

[Gul Onural <on...@nortel.com>]

EntityA has a 1-1 relationship to EntityB. The EntityB has a array list
of EntityCs, so Entity B has a 1-to-many relationship to EntityC.

Let say EntityA is employee. EntityB is the personal data record of the
employee. EntityC is the phone numbers of the employee.

One employee can have only one personal record but can have multiple
phone numbers. An employee's personal record has 1-to-many
relationship to phone numbers.

What I am trying to do with JPQL is to query uniqueID of the employee
(which is defined in EntityA) by giving one of his phone numbers.

In database terms, this is a join of all three tables, but I am not sure
how can you write such a query in JPQL.

Is it possible to write queries operating on joins in JPQL ?

Gul

Re: JPQL query on joined tables

[Michael Bouschen <mb...@spree.de>]

Hi Gul,

I'm assuming three entity classes: Employee, PersonalRecord and 
PhoneNumber with
- Employee having a field personalRecord which is a OneToOne 
relationship to PersonalRecord
- PersonalRecord having a collection field called phoneNumbers which is 
a OneToMany relationship to PhoneNumber

Then the following query might do the job:
  SELECT e FROM Employee e JOIN e.personalRecord r JOIN r.phoneNumbers p
  WHERE p.number = :number

Regards Michael

> EntityA has a 1-1 relationship to EntityB. The EntityB has a array list
> of EntityCs, so Entity B has a 1-to-many relationship to EntityC.
>
> Let say EntityA is employee. EntityB is the personal data record of the
> employee. EntityC is the phone numbers of the employee.
>
> One employee can have only one personal record but can have multiple
> phone numbers. An employee's personal record has 1-to-many
> relationship to phone numbers.
>
> What I am trying to do with JPQL is to query uniqueID of the employee
> (which is defined in EntityA) by giving one of his phone numbers.
>
> In database terms, this is a join of all three tables, but I am not sure
> how can you write such a query in JPQL.
>
> Is it possible to write queries operating on joins in JPQL ?
>
> Gul
>
>   


-- 
*Michael Bouschen*
*Prokurist*

akquinet tech@spree GmbH
Tempelhofer Ufer 23-24, D-10963 Berlin
Bülowstr. 66, D-10783 Berlin

Fon:   +49 30 235 520-33
Fax:   +49 30 217 520-12
Email: michael.bouschen@akquinet.de
Url:    www.akquinet.de <http://www.akquinet.de>

akquinet tech@spree GmbH, Berlin
Geschäftsführung: Prof. Dr. Christian Roth, Hendrik Saly, Martin Weber
Amtsgericht Berlin-Charlottenburg HRB 86780
USt.-Id. Nr.: DE 225 964 680