When should a java PreparedStatement be closed?

Question

In the tutorial "Using Prepared Statements" it states that they should always be closed. Suppose I have a function

getPrice() {
}

that I expect to be called multiple times per second. Should this method be opening and closing the PreparedStatement with every single method call? This seems like a lot of overhead.

Answer 1

First of all, PreparedStatement are never opened. It's just a prepared Statement that is executed. The statement is sent to the RDBMS that executes the SQL statement compiled by the PreparedStatement. The connection to the SQL statement should be opened during the duration of the SQL querying and closed when no other RDMS calls is needed.

You can send many Statement/PreparedStatement as you require provided that you finally close its ResultSet and PreparedStatement once you're completed with them and then close the RDBMS connection.

Answer 2

Should this method be opening and closing the PreparedStatement with every single method call?

If you are creating the PreparedStatement object within the method, then you must close it, once you are done with it. You may reuse the PreparedStatement object for multiple executions, but once you are done with it, you must close it.

This is because, although all Statement objects (including PreparedStatements) are supposed to be closed on invoking Connection.close(), it is rarely the case. In certain JDBC drivers, especially that of Oracle, the driver will be unable to close the connection if the connection has unclosed ResultSet and Statement objects. This would mean that, on these drivers:

• You should never lose a reference to a PreparedStatement object. If you do, then the connection will not be closed, until garbage collection occurs. If you are reusing PreparedStatement instances for different SQL statements, it is easy to forget this.
• You should close the PreparedStatement once you no longer need it. Only then can the Connection.close() actually tear down the physical connection.