Spring-data-jpa eager fetch with join and using pagination not working

Question

I am quite new to hibernate and Spring-data-jpa. All I wanted to do is to fetch an entity and all its related entities using pagination (There are around 40 million records).i.e one query to eager fetch both root entities and all of their mapped entity/collection = 1 query

But I am having (n+1) problem: one query for root entities + one query for related mapped entity/collection of each root entity = (n+1) queries This is really affecting the performance.

I would also like to know if my mappings in the entities are correct.

Can someone please guide me. Thanks

Application stack:

<java-version>1.7</java-version>
<spring.framework.version>4.0.2.RELEASE</spring.framework.version>
<hibernate-core>4.3.4.Final</hibernate-core>
<hibernate-validator>5.1.0.Final</hibernate-validator>
<hibernate-entitymanager>4.3.4.Final</hibernate-entitymanager>
<spring-data-jpa>1.5.1.RELEASE</spring-data-jpa>

I have 2 entity classes(Customer and Order) are as follows:

Customer entity

@Entity
@Table(name = "view_customerOrders_to_process") 
public class Customer implements Serializable {

private Long id;
private List<Order> Orders;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "customer_id", updatable = false)
public Long getId() {
   return id;
}

........

@NotFound(action = NotFoundAction.IGNORE)
@OneToMany(fetch = FetchType.EAGER, mappedBy = "customer") 
//Do not cascade any changes to child entities.
@Where(clause = "order_status = 'PENDING'")
public List<Order> getOrders() {
    return order;
}
}

Tried using batchSize, but for some reason its not working (same n+1 problem).
//@Fetch(FetchMode.SELECT)
//@BatchSize(size=25)

Order entity:

@Table(name = "tbl_orders") 
public class Order implements Serializable {

    private Long id;
    private int customerId;    
    private Customer customer;
    private OrderType orderType;

    ..........

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "order_id", updatable = false)
    public Long getId() {
        return this.id;
    }

    @OneToOne
    @NotFound(action = NotFoundAction.IGNORE)
    @JoinColumn(name = "order_type", nullable = true, unique = false, updatable = false)
    public OrderType getOrderType() {
        return this.orderType;
    }

    @NotFound(action = NotFoundAction.IGNORE)
    @ManyToOne(fetch = FetchType.LAZY, optional = true, targetEntity = Customer.class)
    @Fetch(FetchMode.JOIN)
    @JoinColumns({
        @JoinColumn(updatable = false, insertable = false,
            name="customer_id", referencedColumnName = "customer_id", nullable=false),
      . . . . .
    })
    public Customer getCustomer() {
        return customer;
    }
}

Service Class:

@Service
public class MetsGeneratorService {

    . . .

    @Autowired
    private CustomerRepository customerRepository;

    public List<Customer> run() {
        PageRequest pageRequest = new PageRequest(0, batchSize);        
        List<Customer> customers = customerRepository.findOrdersUnprocessed(pageRequest);
        return customers;
    }

    . . .
}

Repository:

public interface CustomerRepository extends JpaRepository<Customer, Long> {

    @Query("SELECT e FROM Customer e")
    List<Customer> findOrdersUnprocessed(Pageable pageable);

    //Tried using the below one which seems to eliminate paging
    //and understandably getting out of memory.
    //@Query(value = "SELECT e FROM Customer e left join fetch e.orders")       

}

Tried changing to

@Query(value = "SELECT e FROM Customer e left join fetch e.orders",
        countQuery = "select count(e) from Customer e")
List<Customer> findOrdersUnprocessed(Pageable pageable);

if you see the log it is processing doing a "select * " instead of paging.

Log message is WARN org.hibernate.hql.internal.ast.QueryTranslatorImpl - HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

Answer 1

You can use

@Fetch(FetchMode.SUBSELECT)

to remove (n+1) query problem.