Multiple Queries made for Join

Hibernate ORM
1

Hi, I’m currently using Spring Boot 2.4.1 with Hibernate 5.4.25.Final.

I’m running into an issue where after making a Join using the Criteria builder or even just using a JpaRepository multiple Queries are being made.

Here is the CriteriaQuery:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object> criteria =  builder.createQuery(Object.class);
    Root<OwningJob> root = criteria.from(OwningJob.class);

    Path<String> customerReference = root.get("customerReference");
    Path<String> jobNumber_ = root.get("jobNumber");
    Path<Object> customerPath = root.join("customer");

    criteria = criteria
        .multiselect(customerReference, jobNumber_, customerPath)
        .where(builder.equal(root.get("jobNumber"), jobNumber));

    return entityManager
        .createQuery(criteria)
        .getSingleResult();

which results in these two queries, note the second one seems completely unneeded since the first join should be getting that data?

Hibernate: 
    select
        owningjob0_.customerReference as col_0_0_,
        owningjob0_.OwningJobId as col_1_0_,
        customer1_.customerPkId as col_2_0_,
        customer1_.customerPkId as customer1_0_,
        customer1_.customerId as customer2_0_,
        customer1_.customerName as customer3_0_ 
    from
        dbo.OwningJob owningjob0_ 
    inner join
        dbo.Customer customer1_ 
            on owningjob0_.CustomerCode=customer1_.customerId 
    where
        owningjob0_.OwningJobId=?
Hibernate: 
    select
        customer0_.customerPkId as customer1_0_0_,
        customer0_.customerId as customer2_0_0_,
        customer0_.customerName as customer3_0_0_ 
    from
        dbo.Customer customer0_ 
    where
        customer0_.customerId=?

The Customer Entity is annotated in the OwningJob class as follows:

  @ManyToOne
  @JoinColumn(name = "CustomerCode", referencedColumnName = "CustomerId")
  public Customer customer;

What am I doing wrong here?

2

The join you are doing in your Criteria Query is just a random join to Hibernate i.e. it doesn’t know that you want to use this state for initializing entity associations. So if you want to initialize the customer association in OwningJob, you will have to use root.fetch("customer") instead.

The additional query is triggered because the default fetch type for @ManyToOne and @OneToOne is EAGER. Switch to LAZY and the additional query should be gone.

1 Like
3

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?

4

I don’t know how your entity model looks like or your repository, so it’s hard to say. One possible issue could be, that your persistence context already contains a proxy for the entity for which you are seeing lazy loading happening. If this is the case, you will unfortunately see lazy loading being triggered like you describe, on first access. Hibernate is not yet able to merge the state into existing proxies.