使用JDBC取消SQL语句 [英] Cancel SQL Statement with JDBC

问题描述

我在这里遇到这个问题.我正在Tomcat应用程序服务器上运行应用程序.作为前端，我使用的是带有javascript的HTML网站，而后端则使用的是Java.

I am stuck with this problem here. I am running an application on my Tomcat Application Server. As a frontend I am using an HTML site with javascript in it, in the backend i am using Java.

当用户单击按钮时，会依次进行几个SQL查询.现在，我想提供一种在用户愿意时取消此查询的功能.

When the user clicks on a button several sql queries are made, one after another. Now I want to provide the ability to cancel this query if the user wants to.

我已经检查了我的jdbc驱动程序和数据库是否与 cancel()方法兼容，这很好.

I already checked if my jdbc driver and the database are compatible for the cancel() method and this is just fine.

这是我的代码:

PreparedStatement stmt = null;

public void runQuery(String query) {
    Connection con = getConnection();       

    try {
        stmt = con.prepareStatement(query);
        stmt.execute();
    } catch(SQLException e) {
        e.printStackTrace();
    } finally {
        if(stmt != null && !stmt.isClosed()) {
            stmt.close();
        }

        if(con != null) {
            con.close();
        }
    }
}

public void cancelQuery() {
    try {
        if(stmt != null && !stmt.isClosed()) {
            stmt.cancel();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

因此，用户单击运行按钮=> runQuery 被执行，并且stmt被需要执行的查询初始化/覆盖.

So the user clicks the run button => runQuery is executed and stmt is initialized/overriden with the query which needs to execute.

然后用户单击取消按钮=>执行 cancelQuery .不幸的是，有时我会收到NullPointerException，因为stmt为null.但是，如果stmt为null，它甚至不应该调用cancelQuery！这是堆栈跟踪:

Then the user clicks the cancel button => cancelQuery is executed. Unfortunately I sometimes get a NullPointerException because stmt is null. But it should not even call cancelQuery if the stmt is null ?! Here is the stacktrace:

    Stacktrace:] with root cause
java.lang.NullPointerException
    at com.sybase.jdbc3.jdbc.SybStatement.doCancel(SybStatement.java:646)
    at com.sybase.jdbc3.jdbc.SybStatement.cancel(SybStatement.java:614)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.cancel(DelegatingStatement.java:269)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.cancel(DelegatingStatement.java:269)
    at de.package.util.DBHelper.cancelQuery(DBHelper.java:82)
.....

有人知道为什么这会不断产生异常吗?我该如何正确取消该语句?

Any idea why this keeps producing an exception ? How can I cancel the statement the right way ?

我已经注意到了注释中的链接，现在从另一个线程运行cancel()方法.但是NullPointer仍然会发生.这就是我现在如何调用cancel()方法的方法:

I had allook at the link in the comments and now running the cancel() method from a different thread. However the NullPointer still happens. This is how i call the cancel() method now:

public void cancelQuery() {
        Thread thread = new Thread(new SQLCancelRunnable(stmt));
        thread.start();
}

    public class SQLCancelRunnable implements Runnable {
    PreparedStatement stmt;

    public SQLCancelRunnable(PreparedStatement stmt) {
        this.stmt = stmt;
    }

    @Override
    public void run() {
        if(stmt != null) {
        try {
            System.out.println(stmt);
            System.out.println(stmt.toString());
                stmt.cancel();
                System.out.println("canceled");

        } catch (SQLException e) {
            e.printStackTrace();
        }

       }
   }
}

EDIT2 找到我的答案，问题是runQuery()方法的finally块.因为我关闭了语句&NullPointer连接被抛出.我现在删除了此块，但这当然会导致大量资源泄漏.任何可以指导我正确方向的人如何正确关闭我的资源?

EDIT2 Found my answer the problem was the finally block of the runQuery() method. Because I closed statement & connection the NullPointer was thrown. I now removed this block but this, of course, leads to huge resource leaking. Anyone who can guide me in the right direction how to close my resources properly ?

推荐答案

    PreparedStatement stmt = null;

    public void runQuery(String query) {
    Connection con = getConnection();       


    try {
    stmt = con.prepareStatement(query);
    stmt.execute();

    }
    catch(SQLException e) {
        e.printStackTrace();
    }
    finally {
        if(stmt != null && !stmt.isClosed()) {
            stmt.close();
        }

        if(con != null) {
        con.close();
        }
    }

}

public void cancelQuery() {
    try {
        if(stmt != null && !stmt.isClosed()) {
            stmt.cancel();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

尝试一下.我在SQLException之后添加了一个通用异常.

Try this. I have added a Generic Exception just after the SQLException.

不能说这是一个非常干净的解决方案，但是它将忽略stmt.close()语句可能引发的空指针异常.

Cannot say this is a very clean solution but it will ignore the null pointer exception which is possiblly raised by stmt.close() statement.

这篇关于使用JDBC取消SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！