[ACCEPTED]-How can I cancel a long-running query using Spring and JDBCTemplate?-jdbctemplate

Let me simplify oxbow_lakes's answer: you 8 can use the PreparedStatementCreator variant of the query method 7 to gain access to the statement.

So your 6 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 5 can just call

stmt[0].cancel()

You probably want to give a 4 reference to stmt to some other thread before 3 actually running the query, or simply store 2 it as a member variable. Otherwise, you 1 can't really cancel anything...

You can execute stuff via JdbcTemplate methods which 7 allow you to pass in a PreparedStatementCreator. You could always 6 use this to intercept invocations (perhaps 5 using a Proxy) which caused a cancel to happen on a 4 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 3 itself be cancelled upon successful completion; for 2 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 1 to a user's cancellation would look like:

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

You can register a callback object of type 3 StatementCallback on JdbcTemplate that will get executed with the currently 2 active statement as a parameter. In this 1 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;
    }
});

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

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

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

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

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