title: Correct way of reusing PreparedStatement instance?
body: <p>What's the right way to create a PreparedStatement, reuse it a few times, then clean it up? I'm using the following pattern:</p>
  
  <pre><code>Connection conn = null;
  PreparedStatement stmt = null;
  
  try {
      conn = getConnection(...);
  
      // first use
      stmt = conn.prepareStatement("some statement ?");
      stmt.setString(1, "maybe some param");
      if (stmt.execute()) {
          ...
      }
  
      // second use
      stmt = conn.prepareStatement("some statement ?");
      stmt.setString(1, "maybe some param");
      if (stmt.execute()) {
          ...
      }
  
      // third use.
      stmt = conn.prepareStatement("some statement");
      stmt.execute();
  }
  finally {
      if (stmt != null) {
          try {
              stmt.close();
          } catch (Exception sqlex) {
              sqlex.printStackTrace();
          }
  
          stmt = null;
      }
  
      if (conn != null) {
          try {
              conn.close();
          } catch (Exception sqlex) {
          sqlex.printStackTrace();
          }
  
          conn = null;
      }
  }
  </code></pre>
  
  <p>Can we reuse the "stmt" object like that, or do we have to call stmt.close() between each query?</p>
  
  <p>Thanks</p>
  
  <p>---------- Update ------------------------</p>
  
  <p>Ah ok I see, each of my statements will be different. So is this a more correct pattern?:</p>
  
  <pre><code>Connection conn = null;
  PreparedStatement stmt = null;
  
  try {
      conn = getConnection(...);
  
      // first use
      PreparedStatement stmt1 = null;
      try {
          stmt1 = conn.prepareStatement("some statement ?");
          stmt1.setString(1, "maybe some param");
          if (stmt1.execute()) {
              ...
          }
      }
      finally {
          if (stmt1 != null) {
              try {
                  stmt1.close();
              } catch (Exception ex) {}
          }
      }
  
      // second use
      PreparedStatement stmt2 = null;
      try {
          stmt2 = conn.prepareStatement("some different statement ?");
          stmt2.setString(1, "maybe some param");
          if (stmt2.execute()) {
              ...
          }
      }
      finally {
          if (stmt2 != null) {
              try {
                  stmt2.close();
              } catch (Exception ex) {}
          }
      }
  
      // third use
      PreparedStatement stmt3 = null;
      try {
          stmt3 = conn.prepareStatement("yet another statement ?");
          stmt3.setString(1, "maybe some param");
          if (stmt3.execute()) {
              ...
          }
      }
      finally {
          if (stmt3 != null) {
              try {
                  stmt3.close();
              } catch (Exception ex) {}
          }
      }
  }
  finally {
      if (conn != null) {
          try {
              conn.close();
          } catch (Exception sqlex) {
          sqlex.printStackTrace();
          }
  
          conn = null;
      }
  }
  </code></pre>
  
  <p>So each different statement will be closed individually before the next one executes.</p>