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.
Here are a couple of examples.
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.
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)
Here is an example of timeouts set on the Oracle Thin Driver
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).
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
getJdbcTemplate().setQueryTimeout(5);
getJdbcTemplate().update("UPDATE BOOKS ...", sqlParamSource);
The return type of executeQuery is String
ReplyDeleteResultSet result = stmt.executeQuery(); // getting error