Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 902
Steve McLeod Avatar asked Jun 28 '09 10:06

Steve McLeod


People also ask

How do I cancel a JDBC transaction?

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.

Does spring JdbcTemplate close connection?

In short yes it does close the connection.

What kind of queries can JdbcTemplate execute?

The JdbcTemplate class executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSet s and extraction of returned parameter values.

What is JdbcTemplate query?

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.


1 Answers

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

like image 131
itsadok Avatar answered Sep 24 '22 02:09

itsadok