Thanks! I’m now trying to achieve the same thing using a JpaRepository
I’m using an EntityGraph to Eager fetch all the ManyToOne/OneToOne fields.
Now, after making a findByJobNumber(jobNumber) call the query works as expected and makes just one call with all the joins in place.
However when I make the exact same call but with findAll() I get the same problem, where the query seems to make a Join but afterwards makes N+1 calls:
Entity Graph: [customer, logicalJob, collectionLocation, collectionLocation.address, deliveryLocation, deliveryLocation.address]
Hibernate:
/* select
generatedAlias0
from
OwningJob as generatedAlias0 */ select
owningjob0_.pkId as pkid1_13_0_,
locationen1_.locationPkId as location1_10_1_,
geographic2_.geographicId as geograph1_1_2_,
locationen3_.locationPkId as location1_10_3_,
geographic4_.geographicId as geograph1_1_4_,
customeren5_.customerPkId as customer1_0_5_,
logicaljob6_.owningJobPkId as owningjo1_12_6_,
owningjob0_.bookingRef as bookingr2_13_0_,
owningjob0_.collectWindowFrom as collectw3_13_0_,
owningjob0_.collectWindowTo as collectw4_13_0_,
owningjob0_.CollectLocation as collect20_13_0_,
owningjob0_.collectionRef as collecti5_13_0_,
owningjob0_.CustomerCode as customer6_13_0_,
owningjob0_.customerOrderNumber as customer7_13_0_,
owningjob0_.customerReference as customer8_13_0_,
owningjob0_.deliverWindowFrom as deliverw9_13_0_,
owningjob0_.deliverWindowTo as deliver10_13_0_,
owningjob0_.deliveryCycle as deliver11_13_0_,
owningjob0_.DeliverLocation as deliver21_13_0_,
owningjob0_.deliveryRef as deliver12_13_0_,
owningjob0_.OwningJobId as owningj13_13_0_,
owningjob0_.jobType as jobtype14_13_0_,
owningjob0_.Revenue as revenue15_13_0_,
owningjob0_.receiver as receive16_13_0_,
owningjob0_.temperatureCategory as tempera17_13_0_,
owningjob0_.tractorType as tractor18_13_0_,
owningjob0_.trailerType as trailer19_13_0_,
locationen1_.GeographicLocation as geograph7_10_1_,
locationen1_.contact as contact2_10_1_,
locationen1_.faxNumber as faxnumbe3_10_1_,
locationen1_.locationId as location4_10_1_,
locationen1_.operationalDescription as operatio5_10_1_,
locationen1_.phoneNumber as phonenum6_10_1_,
geographic2_.country as country2_1_2_,
geographic2_.LineFive as linefive3_1_2_,
geographic2_.latitude as latitude4_1_2_,
geographic2_.lineOne as lineone5_1_2_,
geographic2_.lineThree as linethre6_1_2_,
geographic2_.lineTwo as linetwo7_1_2_,
geographic2_.longitude as longitud8_1_2_,
geographic2_.postCode as postcode9_1_2_,
geographic2_.LineFour as linefou10_1_2_,
geographic2_.trafficArea as traffic11_1_2_,
locationen3_.GeographicLocation as geograph7_10_3_,
locationen3_.contact as contact2_10_3_,
locationen3_.faxNumber as faxnumbe3_10_3_,
locationen3_.locationId as location4_10_3_,
locationen3_.operationalDescription as operatio5_10_3_,
locationen3_.phoneNumber as phonenum6_10_3_,
geographic4_.country as country2_1_4_,
geographic4_.LineFive as linefive3_1_4_,
geographic4_.latitude as latitude4_1_4_,
geographic4_.lineOne as lineone5_1_4_,
geographic4_.lineThree as linethre6_1_4_,
geographic4_.lineTwo as linetwo7_1_4_,
geographic4_.longitude as longitud8_1_4_,
geographic4_.postCode as postcode9_1_4_,
geographic4_.LineFour as linefou10_1_4_,
geographic4_.trafficArea as traffic11_1_4_,
customeren5_.customerId as customer2_0_5_,
customeren5_.customerName as customer3_0_5_,
logicaljob6_.logicalJobId as logicalj2_12_6_
from
dbo.OwningJob owningjob0_
left outer join
dbo.Location locationen1_
on owningjob0_.DeliverLocation=locationen1_.locationId
left outer join
dbo.GeographicIdAddress geographic2_
on locationen1_.GeographicLocation=geographic2_.geographicId
left outer join
dbo.Location locationen3_
on owningjob0_.CollectLocation=locationen3_.locationId
left outer join
dbo.GeographicIdAddress geographic4_
on locationen3_.GeographicLocation=geographic4_.geographicId
left outer join
dbo.Customer customeren5_
on owningjob0_.CustomerCode=customeren5_.customerId
left outer join
dbo.LogicalJob logicaljob6_
on owningjob0_.PkId=logicaljob6_.owningJobPkId
Hibernate:
/* load LocationEntity */ select
locationen0_.locationPkId as location1_10_0_,
locationen0_.GeographicLocation as geograph7_10_0_,
locationen0_.contact as contact2_10_0_,
locationen0_.faxNumber as faxnumbe3_10_0_,
locationen0_.locationId as location4_10_0_,
locationen0_.operationalDescription as operatio5_10_0_,
locationen0_.phoneNumber as phonenum6_10_0_
from
dbo.Location locationen0_
where
locationen0_.locationId=?
I’m not sure why there is different behaviour between findBy and findAll?