title: Cancel SQL Statement with JDBC
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>I already checked if my jdbc driver and the database are compatible for the <code>cancel()</code> method and this is just fine.</p>
  
  <p>Here is my code :</p>
  
  <pre><code>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 &amp;&amp; !stmt.isClosed()) {
              stmt.close();
          }
  
          if(con != null) {
              con.close();
          }
      }
  }
  
  public void cancelQuery() {
      try {
          if(stmt != null &amp;&amp; !stmt.isClosed()) {
              stmt.cancel();
          }
      } catch (SQLException e) {
          e.printStackTrace();
      }
  }
  </code></pre>
  
  <p>So the user clicks the run button => <code>runQuery</code> is executed and stmt is initialized/overriden with the query which needs to execute.</p>
  
  <p>Then the user clicks the cancel button => <code>cancelQuery</code> 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:</p>
  
  <pre><code>    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)
  .....
  </code></pre>
  
  <p>Any idea why this keeps producing an exception ? How can I cancel the statement the right way ?</p>
  
  <p><strong>EDIT:</strong>
  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: </p>
  
  <pre><code>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();
          }
  
         }
     }
  }
  </code></pre>
  
  <p><strong>EDIT2</strong>
  Found my answer the problem was the finally block of the runQuery() method. Because I closed statement &amp; 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 ?</p>