After some research I've found a lot of materials on how to write simple and efficient code (using JPQL) that:
But when it comes to combining both of them - it becomes unclear how to do that in an efficient and clean manner.
Either it is eager fetching that works, but pagination is being applied in memory (a.k.a. HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!)
Either it is pagination that works, but eager fetching does not (even when a resultSet
actually contained related entities), resulting in an additional queries to a database to fetch related entities for each row in a batch.
The closest thing, that actually works is https://vladmihalcea.com/fix-hibernate-hhh000104-entity-fetch-pagination-warning-message/
But it left me wondering if there is a more intuitive and clean solution to the problem?
Question: Is there any other best practice on how to use pagination with eager fetching of related entities?
Note: Solution should also provide a way to apply filters to data being retrieved from a database. (e.g. JPQL WHERE clause)
The simplest approach for this problem is using two queries:
FETCH
s on the entities related.As example, the first query:
String jpql = "SELECT user.id FROM User user WHERE user.name = 'John'"
Query q = em.createQuery(jpql);
q.setFirstResult(0);
q.setMaxResults(10);
List<Long> ids = q.getResultList();
The second query:
String jqpl = "SELECT user FROM User user JOIN FETCH user.address WHERE user.id IN (:ids)"
Query q = em.createQuery(jpql);
q.setParameter("ids", ids);
List<User> users = q.getResultList();
Pay attention if you need to order by some column. The order by
clause needs to be present on these two queries, because the database don't respect the id
order that you pass by parameter when the database return the rows.