Skip to content

Jpa OneToMany N+1 Issue#

Fix N+1 Issue With OneToMany Relationship in Jpa.#

  • In Jpa if we define OneToMany relationship as below.
CustomerEntity.java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
package com.springboot.project.entity;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Index;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.OneToMany;
import jakarta.persistence.OneToOne;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;

import java.util.Date;
import java.util.List;
import java.util.UUID;

@Getter
@Setter
@Entity
@Table(name = "customers", indexes = {
        @Index(name = "uniqueEmailIndex", columnList = "email", unique = true),
        @Index(name = "uniquePhoneIndex", columnList = "phone", unique = true),
        @Index(name = "uniqueMultiIndex", columnList = "email, phone", unique = true)
})
public class CustomerEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;
    private String fullName;
    @Column(unique = true)
    private String email;
    private String address;
    @Column(unique = true)
    private String phone;
    @Enumerated(EnumType.STRING)
    private Gender gender;
    private Date dob;

    @JoinColumn(name = "loyalty_card")
    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private LoyaltyCardEntity loyaltyCard;

    @OneToMany(mappedBy = "customer", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private List<OrderEntity> orders;

}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
package com.springboot.project.entity;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.OneToMany;
import jakarta.persistence.PrePersist;
import jakarta.persistence.PreUpdate;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

@Getter
@Setter
@Entity
@Table(name = "orders")
public class OrderEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    private String orderName;

    private LocalDateTime createdDate;

    private LocalDateTime lastUpdatedDate;

    @Enumerated(value = EnumType.STRING)
    private OrderStatus orderStatus;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id")
    private CustomerEntity customer;

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<ItemEntity> items = new ArrayList<>();

    @PrePersist
    private void setCreatedDate() {
        LocalDateTime localDateTime = LocalDateTime.now();
        this.createdDate = localDateTime;
        this.lastUpdatedDate = localDateTime;
    }

    @PreUpdate
    private void setLastUpdatedDate() {
        this.lastUpdatedDate = LocalDateTime.now();
    }


}
  • Then when we query to get a CustomerEntitty, we will see in the log that every time we get an OrderEntity, a hibernate query will be generated base on how many Order in the list.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
Hibernate: select c1_0.id,c1_0.address,c1_0.dob,c1_0.email,c1_0.full_name,c1_0.gender,l1_0.id,l1_0.issue_date,l1_0.points,c1_0.phone from customers c1_0 left join loyalty_cards l1_0 on l1_0.id=c1_0.loyalty_card where 1=1 and 1=1 and 1=1 offset ? rows fetch first ? rows only
Hibernate: select count(c1_0.id) from customers c1_0 where 1=1 and 1=1 and 1=1
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id=?

...

  • So how do you think if there are 10000 orders or more in a customer. This will make the database execute thousands queries and it will lead to bad performance.

  • To avoid it, we just simply use the annotation @Fetch(FetchMode.SUBSELECT) in the parent as below.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
package com.springboot.project.entity;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Index;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.OneToMany;
import jakarta.persistence.OneToOne;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

import java.util.Date;
import java.util.List;
import java.util.UUID;

@Getter
@Setter
@Entity
@Table(name = "customers", indexes = {
        @Index(name = "uniqueEmailIndex", columnList = "email", unique = true),
        @Index(name = "uniquePhoneIndex", columnList = "phone", unique = true),
        @Index(name = "uniqueMultiIndex", columnList = "email, phone", unique = true)
})
public class CustomerEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;
    private String fullName;
    @Column(unique = true)
    private String email;
    private String address;
    @Column(unique = true)
    private String phone;
    @Enumerated(EnumType.STRING)
    private Gender gender;
    private Date dob;

    @JoinColumn(name = "loyalty_card")
    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private LoyaltyCardEntity loyaltyCard;

    @Fetch(FetchMode.SUBSELECT)
    @OneToMany(mappedBy = "customer", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private List<OrderEntity> orders;

}

  • In Jpa , @Fetch(FetchMode.SUBSELECT) is a Hibernate annotation that controls how related entities are fetched from the database. The FetchMode.SUBSELECT mode is used to load all instances of the related entity or collection at once, at some points after the initial query is executed. This fetching strategy is currently only available for collections and many-valued associations.

  • Now, when we get the customer again, we will not have many duplicated queries generated.

1
2
3
4
5
6
Hibernate: select c1_0.id,c1_0.address,c1_0.dob,c1_0.email,c1_0.full_name,c1_0.gender,l1_0.id,l1_0.issue_date,l1_0.points,c1_0.phone from customers c1_0 left join loyalty_cards l1_0 on l1_0.id=c1_0.loyalty_card where 1=1 and 1=1 and 1=1 offset ? rows fetch first ? rows only
Hibernate: select count(c1_0.id) from customers c1_0 where 1=1 and 1=1 and 1=1
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.created_date,o1_0.last_updated_date,o1_0.order_name,o1_0.order_status from orders o1_0 where o1_0.customer_id in(select c1_0.id from customers c1_0 left join loyalty_cards l1_0 on l1_0.id=c1_0.loyalty_card where 1=1 and 1=1 and 1=1)
Hibernate: select i1_0.order_id,i1_0.id,i1_0.item_name,i1_0.price,i1_0.quantity from items i1_0 where i1_0.order_id in(select o1_0.id from orders o1_0 where o1_0.customer_id in(select c1_0.id from customers c1_0 left join loyalty_cards l1_0 on l1_0.id=c1_0.loyalty_card where 1=1 and 1=1 and 1=1))
Hibernate: select count(c1_0.id) from customers c1_0 where 1=1 and 1=1 and 1=1
Hibernate: select count(*) from customers c1_0