I need to use pagination in conjuntion with a custom query using an Entity Graph. My repository looks like this:
@Repository
public interface MaintenanceRepository extends JpaRepository<Maintenance, Long>, QueryDslPredicateExecutor<Maintenance> {
@EntityGraph(value = "Maintenance.Graph")
@Query("select m from Maintenance m where m.detail.type.company = ?1 ")
Page<Maintenance> findWithCompany(Company company, Pageable pageable);
}
For comparison, I am getting the Maintenances using this method and using findAll method inherited from QueryDslPredicateExecutor:
...
Pageable pageable = new PageRequest(pageNumber, pageSize, Sort.Direction.DESC, "creationTime");
Page<Maintenance> page = repo.findWithCompany(company, pageable);
Page<Maintenance> page2 = repo.findAll(QMaintenance.maintenance.detail.type.company.eq(company), pageable);
LOGGER.debug("Old: {} - {}, New: {} - {}", page.getTotalElements(), page.getContent().size(), page2.getTotalElements(), page2.getContent().size());
...
In the data base there is 3 register of Maintenances and when I call this method using a page with 1, 2 and 50 page size, I get this log.
[28/03/2017 14:14:36] [DEBUG] Old: 3 - 1, New: 3 - 1 //total - content
[28/03/2017 14:15:09] [DEBUG] Old: 3 - 2, New: 3 - 2
[28/03/2017 14:15:27] [DEBUG] Old: 3 - 3, New: 3 - 3
According to the logs the pagination is working fine but the querys are very different when I use my repository method and the inherited findAll method.
//Query for my repository method
Hibernate: select ... where maintenanc0_.detalle=detail1_.id and detail1_.tipo=type2_.id and type2_.compania=? order by maintenanc0_.fecha desc
//Query for inherited `findAll` method
Hibernate: select ... where maintenanc0_.detalle=detail1_.id and detail1_.tipo=type2_.id and type2_.compania=? order by maintenanc0_.fecha desc limit ?
Two logs have been cut for show relevant information, and in the second query the entity graph hints has not been used because I can not provide it with a Predicate object, but I understand and that is not my problem.
Having this results, I understand that with my repository method (first query) I am not getting a real pagination because spring is filling the list with the correct content size, and the query doesn't have limit keyword.
With the second query, I'm getting a real pagination because the data base is doing the work.
According to Spring Data reference I should be able to do pagination with custom query method.
My concern is the performance, because I don't want to have much data loaded in memory and I need get real pagination done at data base level.
Using QueryDslPredicateExecutor is fine but I can't set a hint for entity graph.
