JPA eager fetching and pagination best practices

Question

After some research I've found a lot of materials on how to write simple and efficient code (using JPQL) that:

1. Allows eager fetching of related entities (e.g. using JOIN FETCH).
2. Allows pagination on a single entity.

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)

Answer 1

The simplest approach for this problem is using two queries:

1. The first query to apply the where condition and pagination. The query returns only the ids
2. The second query you use the ids returned on the first query and make the FETCHs 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.