Can I set a JDBC timeout for a single query?

Question

I have a web app on Tomcat, which handles DB connection pooling, and using Spring JDBCTemplate for executing queries. It's been requested that I implement a status page which will be monitored by a heartbeat process to determine if everything is healthy with the server.

As part of this, I want to do a DB query to determine if the connection to the database is ok. Ideally, since it'd just be a 'select 1 from ', I'd want it to come back fast, within 10 seconds, to indicate a failure if the DB didn't respond in that time.

However, I don't want to change my connection to time out that quickly for normal requests.

Is there a way to set a per-query timeout using either raw JDBC or Spring JDBC wrappers?

Answer 1

If you are using spring to manage transactions a time out can be specified at the transaction level as well. @Transactional(timeout=10)

Answer 2

Use setQueryTimeout on the Statement (or PreparedStatement) object.