Override fetch type in JPQL Queries

Executing a getter — representing a one-to-many or many-to-many association — on an entity causes one or n (with n entries in the collection) database calls, depending on the fetching strategy (see fetch strategies). This is a nasty little detail that may cause performance bottlenecks. Changing the fetch type (lazy/eager) may not be appropriate, since an association is generally used in more than one context, so there may be contradictory concerns. Generally spoken, an eager fetching is only in rare occasions a good idea on one-to-many or many-to-many associations, since this leads to the cartesian product problem (simply put, you have to read [nearly] the hole db in order to get one entity). Fortunately there is another solution that enables overriding a lazy fetch type in a dedicated JPQL-Query. This may entail a hole bunch of JPQL queries you will have to write (not more queries to execute on the db!!!), since you want to eagerly fetch an association in one situation but not in another.

A JPQL query may be enhanced by the keyword fetch in order to override the fetch type (see fetch joins and fetching strategies):

SELECT book FROM Book AS book JOIN FETCH book.chapters WHERE book.id = '1'

This does even affect the fetch strategy:

“Usually, the mapping document is not used to customize fetching. Instead, we keep the default behavior, and override it for a particular transaction, using left join fetch in HQL. This tells Hibernate to fetch the association eagerly in the first select, using an outer join. In the Criteria query API, you would use setFetchMode(FetchMode.JOIN).” (see tuning fetch strategies)

So, if you have the default configuration (select fetching), there won’t be n+1 queries for retrieving the entity and the associated collection. join fetching (as a strategy defined on the entity) is ignored by the persistence provider anyway (this is at least the case for hibernate as the persistence provider).

The fetch keyword can be used for [LEFT [OUTER] | INNER] JOIN joins (that means for all joins known to JPQL).

Leave a Reply

Your email address will not be published. Required fields are marked *

Captcha * Time limit is exhausted. Please reload CAPTCHA.