In a moment where JPA, Spring Data and other specifications are the standard to connect to the databases, JDBC is still present and is useful in many use cases. When working at this low level, more code and steps needs to be done, by default JDBC does not implement any timeout for a query, therefore if something happens the operation can take longer and get stuck if it’s not interrupted.
It’s a good practice to add a timeout to the database query, and it’s very easy to do it. Fortunately, the Statement class has a method setQueryTimeout(int seconds) to indicate it. When the query is executed (executeUpdate, executeQuery or execute) if the operation takes longer than the specified timeout it will throw a SQLTimeoutException , and interrupt the operation.
Show me the code
Let’s see some examples as a reference
try {
Statement stmt = connection.prepareStatement("SELECT * FROM MY_TABLE");
stmt.setQueryTimeout(10);//Set a timeout of 10 seconds
ResultSet result = stmt.executeQuery();
} catch (SQLTimeoutException timeoutException){
//executeQuery exceeded 10 seconds, query is interrupted
//handle the exception
}Also doing a DLM (Update) operation.
try {
PreparedStatement stmt = connection.prepareStatement("UPDATE MY_TABLE SET FIELD_1 = ? WHERE ID = ?");
stmt.setString(1, "Some Value");
stmt.setLong(2, 1234L);
stmt.setQueryTimeout(5);//Timeout of 5 seconds
stmt.executeUpdate();
} catch (SQLTimeoutException timeoutException){
//executeQuery exceeded 5 seconds, query is interrupted
//handle the exception
}Easy piece, with just one line stmt.setQueryTimeout(seconds);, the code can avoid getting stuck and become a more resilient application.