N+1 Problem (feat. LAZY loading)

Entity Class

@Entity
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long orderId;

    private Date orderDate;

    private String status;

    @ManyToOne
    @JoinColumn(name = "customer_id")
    private Customer customer;

    @OneToMany(mappedBy = "order")
    private Set<OrderDetail> orderDetails;

    // Getters and setters
}

Problem

public void getOrderList() {

	List<Order> orderList = repository.findAll();
	
	for(Order order : orderList) {
		
        // N+1 problem occurs: getOrderList initiates query another N times
		sout(order.getOrderDetails);  
	
	}

}

Problem in detail (query)

select * from Order where 1 = 1;

select * from Order o join OrderDetails od on o.orderId = od.orderId
where o.orderId = 1;
select * from Order o join OrderDetails od on o.orderId = od.orderId
where o.orderId = 2;
select * from Order o join OrderDetails od on o.orderId = od.orderId
where o.orderId = 3;
select * from Order o join OrderDetails od on o.orderId = od.orderId
where o.orderId = 4;
select * from Order o join OrderDetails od on o.orderId = od.orderId
where o.orderId = 5;

...

Solutions

Solution 1

Change the field fetch type to eager.

@Entity
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long orderId;

    private Date orderDate;

    private String status;

    @ManyToOne
    @JoinColumn(name = "customer_id")
    private Customer customer;

    @OneToMany(mappedBy = "order", fetch = FetchType.EAGER)
    private Set<OrderDetail> orderDetails;

    // Getters and setters
}

Solution 2

Now the information is called in batches of 10, so the problem narrows down to N/10 + 1.

    @OneToMany(mappedBy = "order",  fetch = FetchType.LAZY)
    @BatchSize(size = 10)
    private Set<OrderDetail> orderDetails;

Solution 3

Run a join query using JPQL.

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT o FROM Order o JOIN FETCH o.orderDetails")
    List<Order> findAllOrderWithOrderDetails();
}