N+1 query problem with @ManyToOne Association

Hibernate ORM
1

Hello,

I have the following relationships: A Sponsor has many Events, and an Event belongs to one Sponsor.

Code:

@Entity
@Table(name = "sponsors")
public final class Sponsor {

  @OneToMany(mappedBy = "sponsor", cascade = CascadeType.ALL, orphanRemoval = true)
  private Set<Event> events = new HashSet<>();

  \\...
}

@Entity
@Table(name = "events")
public final class Event {

 @ManyToOne(fetch = FetchType.LAZY)
 @NotNull private Sponsor sponsor;

  \\...
}

Even though I place an explicit Lazy FetchType annotation on Sponsor, I encounter an N+1 problem.
Whenever all events are being fetched, an additional SQL query will be invoked — fetching each Event’s Sponsor. So if I have 5 events, I’ll encounter 5 additional queries

Below is my JPA CriteriaBuilder query (generic):

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> query = criteriaBuilder.createQuery(clazz);
Root<T> root = query.from(clazz);

query.select(root);
return entityManager.createQuery(query).getResultList();

I’ve tried adding optional = false to my @ManytoOne annotation, as well as a @Fetch(FetchMode.JOIN) annotation, but neither work.

Is this a bug? Is there a simple way to solve this issue?

Any help would be greatly appreciated.

Best,
Ron

2

Is this a bug? Is there a simple way to solve this issue?

There’s no bug. You are not JOIN FETCHING the association as you should. As explained in this article, the N+1 query problem comes because you traverse the @ManyToOne association after fetching the client entities.

The fix is really simple:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Event> query = criteriaBuilder.createQuery(Event.class);
Root<Event> root = query.from(Event.class);
root.fetch("sponsor");

query.select(root);
return entityManager.createQuery(query).getResultList();

Notice the root.fetch("sponsor") call which will JOIN FETCH the association and you will no longer get the N+1 query issue.

Anyway, never rely on EAGER fetching for fixing the N+1 query issue because you will still bump into the N+1 problem every time you forget to JOIN FETCH the EAGER association.

3

Hi Vlad, thank you for the quick reply!

I was looking at the join fetch strategy, but I was hoping to avoid it because I’m aiming to share my findAll function between multiple entities.

I’m most likely missing some key Hibernate-specific knowledge, but I was under the impression that fetch = FetchType.LAZY would eliminate the need to the extra queries.

Even if Hibernate makes those additional SQL calls in the presence of a @ManyToOne annotation, wouldn’t fetch = FetchType.LAZY and optional = false be enough of a signal to avoid those calls?

Is that stemming form the fact the JPA specifies @ManyToOne to be eagerly fetched?

Edit:
Just another quirk in behavior I found. When calling entityManager#find , it will honor the @Fetch(FetchMode.JOIN) annotation and issue only one joined query. But, as discussed above, this is not the case when using the CriteriaBuilder to fetch all.

My expectation is that the behavior should be consistent across both finding strategies.
Is there a reason why this wouldn’t be the case?

Thanks for taking the time with this.

Best,
Ron

4

I was looking at the join fetch strategy, but I was hoping to avoid it because I’m aiming to share my findAll function between multiple entities.

Then you can use Entity Graphs.

Even if Hibernate makes those additional SQL calls in the presence of a @ManyToOne annotation, wouldn’t fetch = FetchType.LAZY and optional = false be enough of a signal to avoid those calls?

The additional calls come wither from FetchType.EAGER if you omit the JOIN FETCH or from etch = FetchType.LAZY if you traverse the relationship after the query has run. Optional is not used for fetching.

Just another quirk in behavior I found. When calling entityManager#find , it will honor the @Fetch(FetchMode.JOIN) annotation and issue only one joined query. But, as discussed above, this is not the case when using the CriteriaBuilder to fetch all.

That’s by design. It’s documented in the official User Guide.

My expectation is that the behavior should be consistent across both finding strategies.
Is there a reason why this wouldn’t be the case?

Yes, it is. For more details, check out this article.

5

Vlad,

Thank you for your help. I’ve decided to take your advice and use EntityGraph as the solution for the problem.

Cheers,
Ron