How to set a default query timeout with JPA and Hibernate?

I am doing some big queries on my database with Hibernate and I sometimes hit timeouts. I would like to avoid setting the timeout manually on every Query or Criteria.

I开发者_Python百科s there any property I can give to my Hibernate configuration that would set an acceptable default for all queries I run?

If not, how can I set a default timeout value on Hibernate queries?

---

JPA 2 defines the javax.persistence.query.timeout hint to specify default timeout in milliseconds. Hibernate 3.5 (currently still in beta) will support this hint.

See also https://hibernate.atlassian.net/browse/HHH-4662

---

JDBC has this mechanism named Query Timeout, you can invoke setQueryTime method of java.sql.Statement object to enable this setting.

Hibernate cannot do this in unified way.

If your application retrive JDBC connection vi java.sql.DataSource, the question can be resolved easily.

we can create a DateSourceWrapper to proxy Connnection which do setQueryTimeout for every Statement it created.

The example code is easy to read, I use some spring util classes to help this.

public class QueryTimeoutConfiguredDataSource extends DelegatingDataSource {

private int queryTimeout;

public QueryTimeoutConfiguredDataSource(DataSource dataSource) {
    super(dataSource);
}

// override this method to proxy created connection
@Override
public Connection getConnection() throws SQLException {
    return proxyWithQueryTimeout(super.getConnection());
}

// override this method to proxy created connection
@Override
public Connection getConnection(String username, String password) throws SQLException {
    return proxyWithQueryTimeout(super.getConnection(username, password));
}

private Connection proxyWithQueryTimeout(final Connection connection) {
    return proxy(connection, new InvocationHandler() {
        //All the Statement instances are created here, we can do something
        //If the return is instance of Statement object, we set query timeout to it
        @Override
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            Object object = method.invoke(connection, args);
            if (object instanceof Statement) {
                ((Statement) object).setQueryTimeout(queryTimeout);
            }
            return object;
        });
}

private Connection proxy(Connection connection, InvocationHandler invocationHandler) {
    return (Connection) Proxy.newProxyInstance(
            connection.getClass().getClassLoader(), 
            ClassUtils.getAllInterfaces(connection), 
            invocationHandler);
}

public void setQueryTimeout(int queryTimeout) {
    this.queryTimeout = queryTimeout;
}

}

Now we can use this QueryTimeoutConfiguredDataSource to wrapper your exists DataSource to set Query Timeout for every Statement transparently!

Spring config file:

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource">
        <bean class="com.stackoverflow.QueryTimeoutConfiguredDataSource">
            <constructor-arg ref="dataSource"/>
            <property name="queryTimeout" value="1" />
        </bean>
    </property>
</bean>

---

Here are a few ways:

• Use a factory or base class method to create all queries and set the timeout before returning the Query object
• Create your own version of org.hibernate.loader.Loader and set the timeout in doQuery
• Use AOP, e.g. Spring, to return a proxy for Session; add advice to it that wraps the createQuery method and sets the timeout on the Query object before returning it

---

Yes, you can do that.

As I explained in this article, all you need to do is to pass the JPA query hint as a global property:

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

Now, when executing a JPQL query that will timeout after 1 second:

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();

Hibernate will throw a query timeout exception:

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

For more details about setting a timeout interval for Hibernate queries, check out this article.

---

For setting global timeout values at query level - Add the below to config file.

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"></property>
    <property name="queryTimeout" value="60"></property>
</bean>

For setting global timeout values at transaction(INSERT/UPDATE) level - Add the below to config file.

<bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="myEmf" />
    <property name="dataSource" ref="dataSource" />
    <property name="defaultTimeout" value="60" />
    <property name="jpaDialect">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
    </property>
</bean>