Successful practice of consistent training

Question

I perform several SELECT in a row, and I wonder how I should handle PreparedStatement s.

Code example:

 //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 PreparedStatement after each use / use of different instructions, or would it make no difference?

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

Answer 1

You are not using the same PreparedStatement , the factory Connection.prepareStatement method returns you a new instance each time it is called. PreparedStatement.executeQuery does the same with ResultSet . You use the same variables.

This means that you are losing resources - the first PreparedStatement and ResultSet - every time this method is called, which never closes.

My recommendation would be to use Spring JdbcTemplate , which will properly handle these database resources, and you break your code into two methods.