Right way to reuse an instance of PreparedStatement?

Question

What is the right way to create a PreparedStatement, reuse it several times, and then clear it? I am using the following pattern:

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; } } 

Is it possible to reuse the stmt object, for example, or do we need to call stmt.close () between each request?

thanks

---------- Update ------------------------

And, ok, I see, each of my statements will be different. So is this a more correct template ?:

 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; } } 

Thus, each individual statement will be closed individually until the next execution.

Answer 1

It's the other way around - you only need to prepare it once, and then reuse it.

those. It:

 // second use stmt = conn.prepareStatement("some statement ?"); stmt.setString(1, "maybe some param"); if (stmt.execute()) { ... } 

should be as follows:

 // second use stmt.setString(1, "maybe some param"); if (stmt.execute()) { ... } 

Your third use, which is another statement, should either be a new variable or close the prepared statement first. (Although usually with PreparedStatements you save them and reuse them).