Eddy Eddy - 2 months ago 8
Java Question

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

I have a

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

The
Hibernate
way to do it is to create one
sql
call to the
User
table, and then another call to the
Address
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
User_Id
) and run an
IN
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

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