Can I set a JDBC timeout for a single query?

sql spring jdbc timeout jdbctemplate
Shawn D. picture Shawn D. · Mar 22, 2011 · Viewed 22.6k times · Source

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

Tom Micheline picture Tom Micheline · Mar 22, 2011

Use setQueryTimeout on the Statement (or PreparedStatement) object.

Related questions

How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?

I was wondering if there is a more elegant way to do IN() queries with Spring's JDBCTemplate. Currently I do something like that: StringBuilder jobTypeInClauseBuilder = new StringBuilder(); for(int i = 0; i < jobTypes.length; i++) { Type jobType = jobTypes[i]; if(…

Read More
Inserting multiple rows using JdbcTemplate

How can I execute the following SQL in a scalable way using JdbcTemplate running on mySQL. In this case, scalable means: Only one SQL statement is executed on the server it works for any number of rows. Here's the statement: …

Read More
How can I print SQL query result log with log4j?

I'm using Spring 3.1.1, MyBatis 3.1.1, MySQL 5.0.67. My Spring configuration is below: <bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="${jdbc.…

Read More