如何使用Spring和JDBCTemplate取消长时间运行的查询? [英] How can I cancel a long-running query using Spring and JDBCTemplate?
问题描述
JDBC java.sql.Statement
类具有 cancel()
方法.可以在另一个线程中调用它来取消当前正在运行的语句.
The JDBC java.sql.Statement
class has a cancel()
method. This can be called in another thread to cancel a currently running statement.
如何使用Spring实现此目标?我无法找到一种在运行查询时获取对语句的引用的方法.我也找不到类似取消的方法.
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.
这是一些示例代码.想象一下,执行此操作最多需要10秒钟,有时应用户的要求,我想取消它:
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");
我将如何修改它,以便获得对 java.sql.Statement
对象的引用?
How would I modify this so I have a reference to a java.sql.Statement
object?
推荐答案
让我简化oxbow_lakes的回答:您可以使用查询方法的 PreparedStatementCreator
变体来访问该语句.
Let me simplify oxbow_lakes's answer: you can use the PreparedStatementCreator
variant of the query method to gain access to the statement.
所以您的代码:
final int i = simpleJdbcTemplate.queryForInt("select max(gameid) from game");
应变成:
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()
您可能希望在实际运行查询之前为其他线程提供对 stmt
的引用,或者只是将其存储为成员变量.否则,您无法真正取消任何东西...
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...
这篇关于如何使用Spring和JDBCTemplate取消长时间运行的查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!