Spring Data JPA Pagination HHH000104

Question

I got this repository code:

@Query(value = "select distinct r from Reference r " +
        "inner join fetch r.persons " +
        "left outer join fetch r.categories " +
        "left outer join fetch r.keywords " +
        "left outer join fetch r.parentReferences",
        countQuery = "select count(distinct r.id) from Reference r " +
                "inner join r.persons " +
                "left outer join r.categories " +
                "left outer join r.keywords " +
                "left outer join r.parentReferences")
Page<Reference> findsAllRelevantEntries(Pageable pageable);

When i run tests on that query i got this Hibernate warning:
HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

@Test
void testFindAllRelevantAsync() throws ExecutionException, InterruptedException {
    CompletableFuture<Page<Reference>> all = referenceService.findAllRelevantAsync(PageRequest.of(1, 20));
    CompletableFuture.allOf(all).join();
    assertThat(all.get()).isNotNull();
    assertThat(all.get()).isNotEmpty();
}

The repository code is encapsulated in a service method not shown here. It (the service method) just marshalls the call from the service to the repository and back.

Furthermore the generated sql query does not generate a limit clause. Though it does fire two queries.

One for the count, the other for the fetching of all records.
So it fetches all records and applies the pagination in memory.
This leads to a very slow query execution.

How can i make pagination work with this query?

EDIT

I know that this here is often suggested as solution: How can I avoid the Warning "firstResult/maxResults specified with collection fetch; applying in memory!" when using Hibernate?

Is there a way to achieve the pagination with Spring Data JPA? I don´t want to hardwire either an EntityManager, neither i want to extend code from a BasicTransformerAdapter

Answer 1

You could use a generic / reusable approach based on the two-queries approach.

One SQL query to retrieve the entities' IDs and a second query with an IN predicate including the IDs from the second query.

Implementing a custom Spring Data JPA Executor:

@NoRepositoryBean
public interface AsimioJpaSpecificationExecutor<E, ID extends Serializable> extends JpaSpecificationExecutor<E> {

  Page<ID> findEntityIds(Pageable pageable);
}


public class AsimioSimpleJpaRepository<E, ID extends Serializable> extends SimpleJpaRepository<E, ID>
        implements AsimioJpaSpecificationExecutor<E, ID> {

  private final EntityManager entityManager;
  private final JpaEntityInformation<E, ID> entityInformation;

  public AsimioSimpleJpaRepository(JpaEntityInformation<E, ID> entityInformation, EntityManager entityManager) {
    super(entityInformation, entityManager);
    this.entityManager = entityManager;
    this.entityInformation = entityInformation;
  }

  @Override
  public Page<ID> findEntityIds(Pageable pageable) {
    CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
    CriteriaQuery<ID> criteriaQuery = criteriaBuilder.createQuery(this.entityInformation.getIdType());
    Root<E> root = criteriaQuery.from(this.getDomainClass());

    // Get the entities ID only
    criteriaQuery.select((Path<ID>) root.get(this.entityInformation.getIdAttribute()));

    // Update Sorting
    Sort sort = pageable.isPaged() ? pageable.getSort() : Sort.unsorted();
    if (sort.isSorted()) {
      criteriaQuery.orderBy(toOrders(sort, root, criteriaBuilder));
    }

    TypedQuery<ID> typedQuery = this.entityManager.createQuery(criteriaQuery);

    // Update Pagination attributes
    if (pageable.isPaged()) {
      typedQuery.setFirstResult((int) pageable.getOffset());
      typedQuery.setMaxResults(pageable.getPageSize());
    }

    return PageableExecutionUtils.getPage(typedQuery.getResultList(), pageable,
      () -> executeCountQuery(this.getCountQuery(null, this.getDomainClass())));
  }

  protected static long executeCountQuery(TypedQuery<Long> query) {
    Assert.notNull(query, "TypedQuery must not be null!");

    List<Long> totals = query.getResultList();
    long total = 0L;

    for (Long element : totals) {
      total += element == null ? 0 : element;
    }

    return total;
  }
}

You can read more at https://tech.asimio.net/2021/05/19/Fixing-Hibernate-HHH000104-firstResult-maxResults-warning-using-Spring-Data-JPA.html

Answer 2

The approach to fetch ids first and then do the main query works but is not very efficient. I think this is a perfect use case for Blaze-Persistence.

Blaze-Persistence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. The pagination support it comes with handles all of the issues you might encounter.

It also has a Spring Data integration, so you can use the same code like you do now, you only have to add the dependency and do the setup: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-setup

Blaze-Persistence has many different strategies for pagination which you can configure. The default strategy is to inline the query for ids into the main query. Something like this:

select r 
from Reference r 
inner join r.persons
left join fetch r.categories 
left join fetch r.keywords
left join fetch r.parentReferences 
where r.id IN (
  select r2.id
  from Reference r2
  inner join r2.persons
  order by ...
  limit ...
)
order by ...

Answer 3

I found a workaround myself. Based upon this:
How can I avoid the Warning "firstResult/maxResults specified with collection fetch; applying in memory!" when using Hibernate?

First: Get the Ids by pagination:

@Query(value = "select distinct r.id from Reference r " +
        "inner join r.persons " +
        "left outer join r.categories " +
        "left outer join r.keywords " +
        "left outer join r.parentReferences " +
        "order by r.id",
        countQuery = "select count(distinct r.id) from Reference r " +
                "inner join r.persons " +
                "left outer join r.categories " +
                "left outer join r.keywords " +
                "left outer join r.parentReferences " +
                "order by r.id")
Page<UUID> findsAllRelevantEntriesIds(Pageable pageable);

Second: Use the Ids to do an in query

@Query(value = "select distinct r from Reference r " +
        "inner join fetch r.persons " +
        "left outer join fetch r.categories " +
        "left outer join fetch r.keywords " +
        "left outer join fetch r.parentReferences " +
        "where r.id in ?1 " +
        "order by r.id",
        countQuery = "select count(distinct r.id) from Reference r " +
                "inner join r.persons " +
                "left outer join r.categories " +
                "left outer join r.keywords " +
                "left outer join r.parentReferences ")
@QueryHints(value = {@QueryHint(name = "hibernate.query.passDistinctThrough", value = "false")},
        forCounting = false)
List<Reference> findsAllRelevantEntriesByIds(UUID[] ids);

Note: I get a List<Reference not a Pageable so you have to build your Pageable on your own like so:

private Page<Reference> processResults(Pageable pageable, Page<UUID> result) {
    List<Reference> references = referenceRepository.findsAllRelevantEntriesByIds(result.toList().toArray(new UUID[0]));
    return new PageImpl<>(references, pageable, references.size());
}

This looks not nice and does two statements, but it queries with limit, so only the needed records get fetched.