Consecutive PreparedStatement good practice

Question

I'm executing a few SELECTs in a row and I'm wondering how I should handle the PreparedStatements.

Example code:

//Connection conn is already declared
PreparedStatement pstmt = null;
ResultSet rset = null;
try {
  String sql = "SELECT ...";
  pstmt = conn.prepareStatement(sql);
  pstmt.setString(1, someVar);

  rset = pstmt.executeQuery();
  // Use ResultSet

  // A different query
  sql = "SELECT ...";
  pstmt = conn.prepareStatement(sql);
  pstmt.setString(1, someVar);

  rset = pstmt.executeQuery();
  // Use ResultSet
} catch (SQLException e) {
  // Handle
} finally {
  if (rset != null)
    rset.close();
  if (pstmt != null)
    pstmt.close();
  if (conn != null)
    conn.close();
}

Now the question is, would it be better to close the PreparedStatements after each usage/use different statements or would it make absolutely no difference?

I've found some information about reusing a PreparedStatement that always has the same query but I'm not sure about using different queries.

Answer 1

You're not using the same PreparedStatement, the factory method Connection.prepareStatement is returning you a new instance each time you call it. PreparedStatement.executeQuery is doing the same with ResultSet. You are just using the same variables.

This means you're leaking resources - the first PreparedStatement and ResultSet - every time this method is called, which are never being closed.

My recommendation would be to use Spring's JdbcTemplate which will handle these database resources correctly for you and you break your code into two methods.