Hibernate left join fetch by non-primary keys

Question

Following entities:

@Table
class AA1 {
 @Id
 Long id;

 String a_number;

 Category category;

 @ManyToOne(fetch = FetchType.LAZY)
 @JoinColumn(name= 'a_number', referencedColumnName='a_number')
 @JoinColumn(name= 'category', referencedColumnName='category')
 BB1 bb1 

...other fields...
}

@Table
class BB1 {
 @Id
 String a_number;

 Category category;

 String value;
}

JPQL query:

SELECT a FROM AA1 a LEFT JOIN a.bb1 b;

Hibernate produces correct sql query, but when it tries to collect data it makes additional call like:

SELECT b.a_number, b.category, b.value FROM BB1 b WHERE b.a_number = ? AND b.category = ?

I checked that query returns null. How can I avoid such database queries?

My investigation: As far as I see Hibernate creates key by(AA1.a_number and AA1.category) and tries to retrieve entity from context. And for specific row 'left join' query returns null values and Hibernate asks context by key and context returns null, it leads to call to database for it.

I found the similar issue: https://stackoverflow.com/questions/30082281/manytoonefetch-fetchtype-lazy-doesnt-work-on-non-primary-key-referenced-co — Dmitry K, Apr 29 '22 at 12:19

score 1 · Answer 1 · answered Oct 19 '22 at 07:47

You must add FETCH to your JPQL query :

SELECT a FROM AA1 a LEFT JOIN FETCH a.bb1 b;

but keep the LAZY loading, because Hibernante will always try to get ManyToOne or OneToOne relationship, which are EAGER by default, with an additional query.

Look at this article https://thorben-janssen.com/5-common-hibernate-mistakes-that-cause-dozens-of-unexpected-queries/ from Th

score 0 · Answer 2 · answered Oct 19 '22 at 08:55

By default, to make lazy loading work for @OneToOne and @ManyToOne, you need to enable "no proxy lazy associations". Otherwize, despite the FetchType.LAZY annotation, the associated object will be "fetched" and the "fetch" will be done with an extra sql query.

Therefore, one half-way solution to leverage performances without enabling "no proxy lazy associations" is to avoid extra queries by forcing a join fetch on the associated objet. Various technics allow to reach this goal : "LEFT JOIN FETCH" in JPQL queries or EntityGraph.

score -1 · Answer 3 · answered Apr 18 '22 at 19:28

Just looking at the entity definition @ManyToOne(fetch = FetchType.LAZY) is the cause.

You are explicitly telling JPA to fetch BB1 only when it is needed/accessed.Hence when the first call to get the parent entity is made BB1 is not loaded.It is only when you are accessing the child that triggers JPA to fetch it.

If you change it to FetchType.EAGER , both the entities will be queried in a single call.

But be careful there are advantages/pit-falls with either approach. Read more abt it here : https://thorben-janssen.com/entity-mappings-introduction-jpa-fetchtypes/

Changing to EAGER does not work. – Dmitry K Apr 29 '22 at 12:19 — Dmitry K, Apr 29 '22 at 12:19

Hibernate left join fetch by non-primary keys

3 Answers3