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

Win a copy of The Software Craftsman this week in the Agile forum!
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: 17260

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 - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Consider Paul's rocket mass heater.
subject: How to do multiple left join