# N+1 query problem with @ManyToOne Association

**URL:** https://discourse.hibernate.org/t/n-1-query-problem-with-manytoone-association/1293
**Category:** Hibernate ORM
**Created:** [August 28, 2018, 9:16pm UTC](https://discourse.hibernate.org/t/n-1-query-problem-with-manytoone-association/1293 "2018-08-28T21:16:00Z")
**Posts on this page:** 5
**Page:** 1

<div class="post-metadata">

### Author: ![RSA](https://avatars.discourse-cdn.com/v4/letter/r/839c29/32.png) [@RSA](https://discourse.hibernate.org/u/RSA)
#### Post date: [August 28, 2018, 9:16pm UTC](https://discourse.hibernate.org/t/n-1-query-problem-with-manytoone-association/1293/1 "2018-08-28T21:16:00Z")

</div>

Hello,

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

Code:

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

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

  \\...
}

```

```auto
@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):

```auto
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

---

<div class="post-metadata">

### Author: ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)
#### Post date: [August 29, 2018, 5:03am UTC](https://discourse.hibernate.org/t/n-1-query-problem-with-manytoone-association/1293/2 "2018-08-29T05:03:46Z")

</div>

> 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](https://vladmihalcea.com/how-to-detect-the-n-plus-one-query-problem-during-testing/), the N+1 query problem comes because you traverse the `@ManyToOne` association after fetching the client entities.

The fix is really simple:

```auto
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](https://vladmihalcea.com/eager-fetching-is-a-code-smell/) 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.

---

<div class="post-metadata">

### Author: ![RSA](https://avatars.discourse-cdn.com/v4/letter/r/839c29/32.png) [@RSA](https://discourse.hibernate.org/u/RSA)
#### Post date: [August 29, 2018, 3:18pm UTC](https://discourse.hibernate.org/t/n-1-query-problem-with-manytoone-association/1293/3 "2018-08-29T15:18:00Z")

</div>

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

---

<div class="post-metadata">

### Author: ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)
#### Post date: [August 29, 2018, 4:19pm UTC](https://discourse.hibernate.org/t/n-1-query-problem-with-manytoone-association/1293/4 "2018-08-29T16:19:10Z")

</div>

> 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](http://docs.jboss.org/hibernate/orm/5.3/userguide/html_single/Hibernate_User_Guide.html#fetching-strategies-dynamic-fetching-entity-graph).

> **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](http://docs.jboss.org/hibernate/orm/5.3/userguide/html_single/Hibernate_User_Guide.html#fetching).

> 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](https://vladmihalcea.com/eager-fetching-is-a-code-smell/).

---

<div class="post-metadata">

### Author: ![RSA](https://avatars.discourse-cdn.com/v4/letter/r/839c29/32.png) [@RSA](https://discourse.hibernate.org/u/RSA)
#### Post date: [August 29, 2018, 9:06pm UTC](https://discourse.hibernate.org/t/n-1-query-problem-with-manytoone-association/1293/5 "2018-08-29T21:06:07Z")

</div>

Vlad,

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

Cheers,  
Ron