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

Question

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?

Answer 1

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