File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes How to do multiple left join Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "How to do multiple left join" Watch "How to do multiple left join" New topic

How to do multiple left join

avihai marchiano
Ranch Hand

Joined: Jan 10, 2007
Posts: 342

i need to do multiple left outer join, but if i do it more than twice i get an exception.

As i understand if i have a bag mapping i cant do left join more than two times.

I dont want to use set or list with index in the mapping , so how can i solve this problem?

Thank you
Mark Spritzler

Joined: Feb 05, 2001
Posts: 17276

Actually the big issue is that you will be doing a full left outer join, which when you do more than one, you will get a Cartesian Product. Meaning if you have 10 records in each table, and join 4 tables, that will return

10*10*10*10 records or 10,000 results, which isn't what you want, and imagine if there are even more than 10 records in each table.

That is why it is recommended to only left join to one table, and if you need more, set the fetch strategy in the Query object for those other tables to SUBSELECT, then in code after you load the data, get the Collections of the other joins to have Hibernate load them.

Now, here is the bigger reason why you won't want more than one. What use case do you have that would need that many left joins, and know that th emore you have, the more data that comes down, which will affect performance, and most likely there is a design that is cleaner and faster, that only brings down the exact data that you need at that time.

Also remember that users can't read and see and understand lots of data at the same time, so limit the data you get to what a normal person can understand.


Perfect World Programming, LLC - iOS Apps
How to Ask Questions the Smart Way FAQ
I agree. Here's the link:
subject: How to do multiple left join
It's not a secret anymore!