wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes JPA JPQL - How to Order the Joins Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA JPQL - How to Order the Joins" Watch "JPA JPQL - How to Order the Joins" New topic
Author

JPA JPQL - How to Order the Joins

Dave Mark
Greenhorn

Joined: Feb 21, 2004
Posts: 19
Hi all,

I'm having trouble ordering the joins in JPQL (with Hibernate 3). I have a database with about 20 tables and they're arranged in a sort of circle with two parts branching out, eg.

"-->" = "is joined to"

T1 --> T2 --> T3 --> T4 --> T5 --> T6 --> T7 --> T8 --> T1
and
T3 --> T3B --> T3C
and
T7 --> T7B --> T7C

I have to join all of these tables using "left join fetch". Joining T1, T2, ... T8, T1 is no problem...
but then I get an error when attempting to join one of the 'branches' (line 06.)
What is the correct ordering for joins?

Explanation :
The object/table t1 is actually the user and based on the user and based on their group/profile etc. we build menus and other stuff.
Why :
We're not allowed to use multiple requests to the database (we had 52 requests). Joining 'the circle' (T1, T2 ... T8, T1) with 'left join fetch' brings us down to 20 requests, but this is still too much. The application will be used by a huge amount of people all starting at 9 o'clock, so we don't want the log-in/create-menu phase to cripple the systems response time.

Any help much appreciated.
Dave Mark
Greenhorn

Joined: Feb 21, 2004
Posts: 19
I found the right order. In short I followed 'the branches' before continuing round 'the circle' so I did...

T1 --> T2 --> T3 --> T3B --> T3C
then
T3 --> T4 --> T5 --> T6 --> T7 --> T7B --> T7C
then
T7 --> T8 --> T1

My case was actually more complicated than I've described as I actually have a branch which is connected to two parts of 'the circle'. This branch itself having more branches.

So I finally ended up with something like this...

T1 --> T2 --> T3 --> T3B --> T3C
then
T3 --> T4 --> T5 --> 3B
then
T5 --> T6 --> T7 --> T7B --> T7C
then
T7 --> T8 --> T1

...which put all together became...

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JPA JPQL - How to Order the Joins
 
Similar Threads
Problem in viewing records ?
How to extract current month and year's records alone
NullPtrException when reading JTextField
Record selection based on current month and year
The INSERT INTO statement contains the following unknown field name: 'OPANo'.