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.
Is 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.
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:
BasicLoader. The thing is, I don't see a doQuery in that API: hibernate.org/hib_docs/v3/api/org/hibernate/loader/Loader.html So, I would have to override which methods? I'm guessing doList, getResultSet, prepareQueryStatement and scroll. Am I right? –
Partner 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>
© 2022 - 2024 — McMap. All rights reserved.