How can I cancel a long-running query using Spring and JDBCTemplate?

java, jdbctemplate, spring

Solution

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...

Problem

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?

Original source