Hibernate N+1 problem on OneToOne relationship

Question

I am using spring-data-jpa with hibernate in a spring boot application for ORM. I have an issue with OneToOne relationship generating N+1 queries whereas I don't need the oneToOne object to be fetched.

My OneToOne relationship is EAGERLY fetched, I tried to use lazy with fetch mode to JOIN but still cant get it generate only one query.

Here is the code for both entities and the query:

@Entity
@Table(name = "item")
public class Item implements Serializable {

   @Id
   @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
   @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

   @Column(name = "number")
   private String number;

   @Column(name = "date")
   private LocalDate date;

    @OneToOne(mappedBy = "item", fetch = FetchType.EAGER)
    @JsonIgnore
    private Status status;

}

@Entity
@Table(name = "status")
public class Status implements Serializable {

   @Id
   @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
   @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

   @OneToOne(fetch = FetchType.EAGER)
   @JoinColumn(unique = true)
   private Item item;

}

here is the query in the repository:

@Query(value = "SELECT * FROM ( SELECT  number, MAX(date) AS date FROM item WHERE date < ? GROUP BY number) AS latest_it INNER JOIN item ON item.number = latest_it.number AND item.date = latest_it.date", nativeQuery = true)
List<Item> findLatestByNumberAndDate(LocalDate localDate);

When I run the query, I got tones of queries:

select status0_.id ... from status status0_ left outer join item item1_ on status0_.item_id=item1_.id left outer join status status2_ on item1_.id=status2_.item_id where status0_.item_id=?

Whereas what I expect is seeing only my query defined in repository executed. This issue is a real pain to performance.

Do you have a hint about it ?

Thank you

Not sure but ```@Fetch(FetchMode.JOIN)``` should help you – Shaunak Patel Apr 19 '19 at 11:18 — Shaunak Patel, Apr 19 '19 at 11:18
I tried it on top of status attribute but it doesn't work. – saadoune Apr 19 '19 at 12:40 — saadoune, Apr 19 '19 at 12:40

score 3 · Answer 1 · answered Jan 20 '20 at 08:10

By doing a native query, you don't use the Hibernate Fetch configuration.

You could do a JPQL instead with a JOIN FETCH on the status to fetch the status in the query and avoid the additional queries:

@Query(value = "
    SELECT *, MAX(date)
    FROM Item i
    LEFT JOIN FETCH i.status 
    GROUP BY i.number
    HAVING i.date < ?"
)
List<Item> findLatestByNumberAndDate(LocalDate localDate);

Otherwise, take a look to this post that will show you the different possibilities to make OneToOne Lazy: Making a OneToOne-relation lazy

Hibernate N+1 problem on OneToOne relationship

1 Answers1