CODEWITHSUNDEEP
javahibernatejpa
user10871691
user10871691

Reputation:

Hibernate JPA fetch joins should not be used in paged queries

In the Hibernate User Guide chapter 15.14 it says:

Fetch joins should not be used in paged queries (e.g. setFirstResult() or setMaxResults()), nor should they be used with the scroll() or iterate() features.

There is no further explanation why JPQL queries like this one:

List<Person> persons = entityManager.createQuery(
    "select distinct pr " +
    "from Person pr " +
    "left join fetch pr.phones ", Person.class )
.setFirstResult(15)
.setMaxResults(10)
.getResultList();

should be avoided.

Can someone explain what are the rationales behind this interdiction?

UPDATE

I found out this post that explains everything.

UPDATE 2

To be more precise:

  1. Why it is forbidden for 1-1 lazy relationships since no row can be lost?

  2. Why it is not implemented with 2 selects behind the scene? ( Something like:

     select pr.id from Person pr where ... pagination
 select distinct pr from Person pr left join fetch pr.phones where pr.id in (:ids_from_previous_select)

)

Upvotes: 1

Views: 1090

Answers (2)

Christian Beikov
Christian Beikov

Reputation: 16400

It's not forbidden for one-to-one associations, that's totally fine. It's just problematic for collections i.e. *-to-many associations.

I think Hibernate could be adapted to do fetch joins more efficiently when doing pagination, but it just isn't implemented this way.

I did implement the kind of pagination you mention for Blaze-Persistence which is a library that works on top of JPA/Hibernate: https://persistence.blazebit.com/documentation/core/manual/en_US/index.html#pagination

There are even a few further optimizations in there:

  • Only the necessary joins are done in what I call the "id-query"
  • The id query does not necessarily have to be a separate query. In fact, by default, Blaze-Persistence inlines this query into the "object-query"
  • Blaze-Persistence also supports the much more efficient keyset pagination through the same API

Upvotes: 0

Antoniossss
Antoniossss

Reputation: 32507

This is because if use JOIN and apply limit offset directly to the SQL, you will end up with possibly truncated list of related entities.

Eg if we will have Person with Friends and will take first 5 records (first page, 5 items of size) you wil end up with 1 person with 5 frends where in fact, you wated to have 5 persons with all friends.

To overcome that, hiberante will execute whole query without limit and will apply limit/offset after aggregation. This will make eg iterable cursor not iterable at all.

HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

Upvotes: 5

Related Questions