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

You can execute stuff via JdbcTemplate methods which allow you to pass in a PreparedStatementCreator. You could always use this to intercept invocations (perhaps using a Proxy) which caused a cancel to happen on a separate thread by some cond became true.

public Results respondToUseRequest(Request req) {
    final AtomicBoolean cond = new AtomicBoolean(false);
    requestRegister.put(req, cond);
    return jdbcTemplate.query(new PreparedStatementCreator() {
             public PreparedStatement createPreparedStatement(Connection conn) {
               PreparedStatement stmt = conn.prepareStatement();
               return proxyPreparedStatement(stmt, cond);
             }
         }, 
         new ResultSetExtractor() { ... });
}

This canceller could itself be cancelled upon successful completion; for example

private final static ScheduledExecutorService scheduler =
                 Executors.newSingleThreadedScheduledExecutor();  

PreparedStatement proxyPreparedStatement(final PreparedStatement s, AtomicBoolean cond) {
    //InvocationHandler delegates invocations to the underlying statement
    //but intercepts a query 
    InvocationHandler h = new InvocationHandler() {

        public Object invoke(Object proxy, Method m, Object[] args) {
            if (m.getName().equals("executeQuery") {
                Runnable cancel = new Runnable() {
                    public void run() { 
                        try {
                            synchronized (cond) {
                                while (!cond.get()) cond.wait();
                                s.cancel(); 
                            }
                        } catch (InterruptedException e) { }
                    } 
                }
                Future<?> f = scheduler.submit(cancel);
                try {
                    return m.invoke(s, args);
                } finally {
                    //cancel the canceller upon succesful completion
                    if (!f.isDone()) f.cancel(true); //will cause interrupt
                }
            }
            else {
                return m.invoke(s, args);
            }   
        }

    }

    return (PreparedStatement) Proxy.newProxyInstance(
                getClass().getClassLoader(), 
                new Class[]{PreparedStatement.class}, 
                h);

So now the code that is responding to a user's cancellation would look like:

cond.set(true);
synchronized (cond) { cond.notifyAll(); }

Answer 2

I assume by Spring you mean the use of JdbcDaoTemplate and/or JdbcTemplate? If so, this doesn't really help or hinder you in solving your problem.

I'll assume your use case is that you're executing a DAO operation in one thread, and another thread comes in and wants to cancel the first thread's operation.

The first problem you have to solve is, how does the second thread know which one to cancel? Is this a GUI with a fixed number of threads, or a server with several?

Once you've solved that part, you need to figure out how to cancel the statement in the first thread. One simple approach to this would be to store the first thread's PreparedStatement in a field somewhere (perhaps in a simple field, perhaps in a map of thread ID to statements), allowing the second thread to come in, retrieve the statwmentand call cancel() on it.

Bear in mind that it's possible that cancel() may just block, depending on your JDBC driver and database. Also, make sure you think hard about synchronization here, are your threads are going to get into a fight.

Answer 3

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 qurey, or simply store it as a member variable. Otherwise, you can't really cancel anything...

Answer 4

You can register a callback object of type StatementCallback on JdbcTemplate that will get executed with the currently active statement as a parameter. In this callback you can then cancel the statement:

simpleJdbcTemplate.getJdbcOperations().execute(new StatementCallback() {

    @Override
    public Object doInStatement(final Statement statement) throws SQLException, DataAccessException {
        if (!statement.isClosed()) {
            statement.cancel();
        }

        return null;
    }
});

Answer 5

If you need a way to test your cancel query code in Oracle, here is a handy way to do it: http://www.mattoldham.com/2010/08/how-to-make-a-long-running-query/