The JDBC java.sql.Statement
class has a cancel()
method. This can be called in another thread to cancel a currently running statement.
How can I achieve this using Spring? I can't find a way to get a reference to a statement when running a query. Nor can I find a cancel-like method.
Here's some sample code. Imagine this takes up to 10 seconds to execute, and sometimes on the user's request, I want to cancel it:
final int i = simpleJdbcTemplate.queryForInt("select max(gameid) from game");
How would I modify this so I have a reference to a java.sql.Statement
object?
cancel() will cancel the execution of a running Statement (select, update, etc). The JDBC docs specifically say that Statement. cancel() is safe to run from another thread and even suggests the usage of calling it in a timeout thread.
In short yes it does close the connection.
The JdbcTemplate class executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSet s and extraction of returned parameter values.
JdbcTemplate class is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving the application code to provide SQL and extract results.
Let me simplify oxbow_lakes's answer: you can use the PreparedStatementCreator
variant of the query method to gain access to the statement.
So your code:
final int i = simpleJdbcTemplate.queryForInt("select max(gameid) from game");
Should turn into:
final PreparedStatement[] stmt = new PreparedStatement[1];
final int i = (Integer)getJdbcTemplate().query(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
stmt[0] = connection.prepareStatement("select max(gameid) from game");
return stmt[0];
}
}, new ResultSetExtractor() {
public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
return resultSet.getString(1);
}
});
Now to cancel you can just call
stmt[0].cancel()
You probably want to give a reference to stmt
to some other thread before actually running the query, or simply store it as a member variable. Otherwise, you can't really cancel anything...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With