Eddy Eddy - 1 year ago 63
Java Question

Hibernate using sql call for each row to fetch relationship - instead of aggregating to one IN clause

I have a

object with an
. Now let's say I need to fetch 1,000,000 users and display their address in some report.

way to do it is to create one
call to the
table, and then another call to the
table for each user. The result is a grand total of 1,000,001 calls and a long query time.

On the other hand if you aggregate all the foreign keys (for example
) and run an
sql call

FROM Address where User_Id IN (,,,,,,,,)

you reduce the number of calls to 2 - one to the User table and one to the Address table, to bring all the 1,000,000 required address in one call.

But this requires some work on the app side. Not a lot of work, just a
for loop
, but still. Is it possible to ask Hibernate to do it the efficient way?

Please note that
LAZY fetching
has nothing to do with it. For my use case I need an
EAGER fetching

Answer Source

Hibernate will generatesingle query using JOINS. I dont know what sort of configuration you are using whatsoever.SELECT u FROM User u LEFT JOIN FETCH u.address would give u address via joins. Single query

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download