Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly.
Hypersistence Optimizer is that tool!
Introduction
Recently, I noticed the hibernate.query.fail_on_pagination_over_collection_fetch configuration property that was introduced in Hibernate 5.2, and I had absolutely no idea it can be used to prevent the HHH000104 Hibernate issues.
Even if the HHH000104 issue is logged as a warning message, it might pass unnoticed when the amount of data is fairly low, only to become a significant performance issue when data starts accumulating.
In this article, I’m going to explain why you should always activate the hibernate.query.fail_on_pagination_over_collection_fetch configuration property and, instead of a warning message, you will get an exception that’s unlikely to go unnoticed.
Fetching an entity collection with pagination
Considering we have the following Post and PostComment entities which form a bidirectional one-to-many association:
We want to fetch 5 Post entities along with all their associated PostComment child entities while filtering the title property of the parent Post entities, so we write the following JPQL query:
List<Post> posts = entityManager
.createQuery(
"select p " +
"from Post p " +
"left join fetch p.comments " +
"where p.title like :titlePattern " +
"order by p.createdOn", Post.class)
.setParameter(
"titlePattern",
"High-Performance Java Persistence %"
)
.setMaxResults(5)
.getResultList();
assertEquals(5, posts.size());
assertArrayEquals(
LongStream.rangeClosed(1, 5).toArray(),
posts.stream().mapToLong(Post::getId).toArray()
);
If we run the JPQL query above, you will see that only the first 5 Post entities are going to be returned.
However, if you take a look at the log, you will see the following output:
WARN [main]: o.h.h.i.a.QueryTranslatorImpl -
HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!
DEBUG [main]: n.t.d.l.SLF4JQueryLoggingListener -
Query:["
SELECT
p.id AS id1_0_0_,
pc.id AS id1_1_1_,
p.created_on AS created_2_0_0_,
p.title AS title3_0_0_,
pc.created_on AS created_2_1_1_,
pc.post_id AS post_id4_1_1_,
pc.review AS review3_1_1_,
pc.post_id AS post_id4_1_0__,
pc.id AS id1_1_0__
FROM
post p
LEFT OUTER JOIN
post_comment pc
ON
p.id=pc.post_id
WHERE
p.title LIKE ?
ORDER BY
p.created_on
"],
Params:[(
'High-Performance Java Persistence %'
)]
The first thing to notice is the HHH000104 warning message which indicates that the pagination was done in-memory. The SQL statement confirms this as there is no LIMIT clause used to restrict the result set size.
If there are 100 post records matching the provided title pattern and each post has around 50 comments, then this result set will contain 5000 records that will be fetched by Hibernate, only to be discarded after reaching the provided threshold of 5 Post entities.
Enabling the hibernate.query.fail_on_pagination_over_collection_fetch configuration
Since Hibernate ORM 5.2.13, you can now enable the hibernate.query.fail_on_pagination_over_collection_fetch configuration property as follows:
try {
entityManager.createQuery(
"select p " +
"from Post p " +
"left join fetch p.comments " +
"where p.title like :titlePattern " +
"order by p.createdOn", Post.class)
.setParameter(
"titlePattern",
"High-Performance Java Persistence %"
)
.setMaxResults(5)
.getResultList();
fail("Should have thrown Exception");
} catch (Exception e) {
assertTrue(
e.getMessage().contains(
"In memory pagination was about to be applied"
)
);
}
A HibernateException is thrown instead of just logging a warning message. This way, you are going to get instant feedback of these in-memory pagination issues and address them long before they hit production systems.
This setting is not just for JPQL queries, being applied to Criteria API queries as well.
Writing JPA Criteria API queries is not very easy. The Codota IDE plugin can guide you on how to write such queries, therefore increasing your productivity.
For more details about how you can use Codota to speed up the process of writing Criteria API queries, check out this article.
try {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Post> criteria = builder.createQuery(Post.class);
Root<Post> post = criteria.from(Post.class);
post.fetch(Post_.comments);
ParameterExpression<String> parameterExpression = builder.parameter(String.class);
criteria.where(
builder.like(
post.get(Post_.title),
parameterExpression
)
)
.orderBy(
builder.asc(
post.get(Post_.createdOn)
)
);
entityManager
.createQuery(criteria)
.setParameter(
parameterExpression,
"High-Performance Java Persistence %"
)
.setMaxResults(5)
.getResultList();
fail("Should have thrown Exception");
} catch (Exception e) {
assertTrue(
e.getMessage().contains(
"In memory pagination was about to be applied"
)
);
}
Cool, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
The hibernate.query.fail_on_pagination_over_collection_fetch configuration property is very useful and you should definitely have it enabled if you are using a Hibernate ORM version that’s greater than 5.2.13.
2 Comments on “How to detect HHH000104 issues with hibernate.query.fail_on_pagination_over_collection_fetch”
very good tip, vlad!
Enabling this feature may be still even better if your project has enough integration tests so that all of exceptions (or most of them) will be caught in development time.
Based on my book, High-Performance Java Persistence, this workshop teaches you various data access performance optimizations from JDBC, to JPA, Hibernate and jOOQ for the major rational database systems (e.g. Oracle, SQL Server, MySQL and PostgreSQL).
The SQL Master Class for Java Developers training is aimed to level up your SQL skills with techniques such as Window Functions, recursive queries, Pivoting, JSON processing, and many other database querying features supported by Oracle, SQL Server, MySQL, or PostgreSQL.
very good tip, vlad!
Enabling this feature may be still even better if your project has enough integration tests so that all of exceptions (or most of them) will be caught in development time.
Thanks. Well, not having integration tests causes even more serious issues than this one I described here.