Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to do multiple left join

 
avihai marchiano
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey,

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
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Mark
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic