Introduction

In this article, we are going to see what is the best way to set up the query timeout interval with JPA and Hibernate.

Setting the query timeout allows you to cancel slow-running queries that would, otherwise, put pressure on database resources.

Query timeout with JPA and Hibernate @vlad_mihalcea

Learn more: https://t.co/ijnTgcAKdF pic.twitter.com/D5hqTuvhiJ

— Java (@java) July 17, 2019

The “javax.persistence.query.timeout” JPA query hint

As I explained in this article, the Java Persistence API defines the notion of query hint, which unlike what its name might suggest, it has nothing to do with database query hints. The JPA query hint is a Java Persistence provider customization option.

JPA provides support for setting a timeout interval on a given entity or SQL query via the javax.persistence.query.timeout query hint:

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where lower(p.title) like lower(:titlePattern)", Post.class)
.setParameter("titlePattern", "%Hibernate%")
.setHint("javax.persistence.query.timeout", 50)
.getResultList();

The timeout value is defined in milliseconds, so the JPQL query above will time out after 50 milliseconds unless the result set is being fetched prior to the timeout threshold.

The “org.hibernate.timeout” Hibernate query hint

Hibernate also provides the org.hibernate.timeout query hint, which unlike its JPA counterpart, takes the timeout interval in seconds:

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where lower(p.title) like lower(:titlePattern)", Post.class)
.setParameter("titlePattern", "%Hibernate%")
.setHint("org.hibernate.timeout", 1)
.getResultList();

The Hibernate Query timeout property

If you unwrap the JPA javax.persistence.Query to the Hibernate-specific org.hibernate.query.Query interface that extends the JPA query specification, you can get access to the Hibernate query extension methods which allow you to set a SQL-level comment, hint or provide a timeout threshold.

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where lower(p.title) like lower(:titlePattern)", Post.class)
.setParameter("titlePattern", "%Hibernate%")
.unwrap(org.hibernate.query.Query.class)
.setTimeout(1)
.getResultList();

Just like it was the case with the org.hibernate.timeout query hint, the setTimeout method takes the timeout interval in seconds, so the JPQL query above will time out after one second unless the query finishes faster.

Testing time

To see how the query timeout works, consider the following example:

List<Tuple> result = entityManager
.createNativeQuery(
    "SELECT 1 " +
    "FROM pg_sleep(2) ", Tuple.class)
.setHint(
    "javax.persistence.query.timeout", 
    (int) TimeUnit.SECONDS.toMillis(1)
)
.getResultList();

When running the PostgreSQL query above, the database is going to throw a query_canceled exception:

SELECT 1 
FROM pg_sleep(2)

-- SQL Error: 0, SQLState: 57014
-- ERROR: canceling statement due to user request

Auto-applying the timeout interval to Hibernate queries

If you want to apply the query timeout automatically to all Hibernate queries, then you should pass the JPA javax.persistence.query.timeout query hint as a property:

<property
    name="javax.persistence.query.timeout"
    value="1000"
/>

And, then you execute the following JPQL query:

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where function('1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(2) ) --',) is ''", Post.class)
.getResultList();

Then Hibernate is going to throw the query timeout exception even if we didn’t explicitly specify the timeout interval on the JPQL query:

SELECT p.id AS id1_0_,
       p.title AS title2_0_
FROM post p
WHERE 1 >= ALL (
    SELECT 1
    FROM pg_locks, pg_sleep(2)
) --()=''

-- SQL Error: 0, SQLState: 57014
-- ERROR: canceling statement due to user request

Cool, right?

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Setting the query timeout interval is very useful, as otherwise, slow running queries will keep the database connection acquired over long period of times, therefore, putting pressure on concurrency and scalability.

Insert details about how the information is going to be processed

DOWNLOAD NOW