How to Timeout a JDBC query
source link: https://blog.adamgamboa.dev/how-to-timeout-a-jdbc-query/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
How to Timeout a JDBC query – Adam Gamboa G – DeveloperSkip to content
Full Stack notes and tips for developers… Java, React, Kubernetes, Scala, and more…
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
}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
}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.
References
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK