CODEWITHSUNDEEP
javaprepared-statement
deltanovember
deltanovember

Reputation: 44061

When should a java PreparedStatement be closed?

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.

Upvotes: 9

Views: 15059

Answers (5)

venkat
venkat

Reputation: 1

Yes..No issues are there if you are creating the prepared statement n number of times, because as you will be using the same statement at all the places. No need to have any observation here regarding performance

Thanks

Upvotes: 0

Vineet Reynolds
Vineet Reynolds

Reputation: 76719

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.

Upvotes: 13

M.J.
M.J.

Reputation: 16666

I think that, after every database interaction, every component like statement, resultset must be closed, except for connection, if u tend to perform more operation.

And there is no need to worry, if you are creting the prepared statement again and again, because as you will be using the same statement again and again, there wont be any performannce issue.

Upvotes: 0

Buhake Sindi
Buhake Sindi

Reputation: 89189

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.

Upvotes: 13

Howard
Howard

Reputation: 39207

As the example in the tutorial shows you should close it after all your queries have been performed.

Once the statement is closed the RDMS may release all resources associated with your statement. Thus to use it further you'd have to re-prepare the very same statement.

Upvotes: 0

Related Questions