18

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?

Shawn D.
  • 7,895
  • 8
  • 35
  • 47
  • For that kind of scneario I suggest setting it to 1 or 2 seconds which will indicate database congestion. – cherouvim Mar 23 '11 at 08:21

2 Answers2

34

Use setQueryTimeout on the Statement (or PreparedStatement) object.

Tom Micheline
  • 861
  • 1
  • 5
  • 5
11

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

gkamal
  • 20,777
  • 4
  • 60
  • 57