How to Timeout JDBC Queries

JDBC queries by default do not have any timeout, which means that a query can block the thread for an unlimited amount time; of course, depending upon the DB load and the cost of the query. It is a good practice to timeout these queries if they can take longer than a certain amount of time.

Timeout on individual Queries


JDBC statements can be configured for timeouts, in seconds. When timeouts are set, the driver would wait for the given number of seconds for the query to execute (i.e. executeQuery and executeUpdate) and throw an SQLTimeoutException if doesn't respond within that time.

Here are a couple of examples.


    Statement stmt = connection.prepareStatement("SELECT * FROM BOOKS");
    stmt.setQueryTimeout(10);//Timeout of 10 seconds
    
    //This would throw an SQLTimeoutException if it exceeds 10 seconds
    ResultSet result = stmt.executeQuery();
    
    


    PreparedStatement stmt = connection.prepareStatement("UPDATE BOOKS SET RETURNED = ? WHERE BID = ?");
    stmt.setBoolean(1, true);
    stmt.setString(2, "B1234");
    stmt.setQueryTimeout(5);//Timeout of 5 seconds
      
    //This would throw an SQLTimeoutException if it exceeds 5 seconds
    stmt.executeUpdate();

Global Timeout (JDBC Driver Level)


If you need to set the same timeout for all query executions, then it can be set directly on the drivers. However, the options would differ from driver to driver.
Here is an example of timeouts set on the Oracle Thin Driver


    Properties properties = new Properties();
    properties.setProperty("user", "scott");
    properties.setProperty("password", "tiger");
    //This timeout is in milliseconds, but can vary for other drivers
    properties.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_READ_TIMEOUT, "2000");
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:1521:SID", properties);

Spring JDBC timeouts


If you are using Spring JDBC, where you do not have direct control over the JDBC statements, JDBCTemplate also provides an option to setQueryTimeOut. If set to -1, it takes the JDBC driver's default setting for timeouts (which is covered above).

    getJdbcTemplate().setQueryTimeout(5);
    getJdbcTemplate().update("UPDATE BOOKS ...", sqlParamSource);