什么时候应该关闭 java PreparedStatement？

When should a java PreparedStatement be closed?

提问by deltanovember

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.

我希望每秒被调用多次。这个方法应该在每次调用方法时打开和关闭 PreparedStatement 吗？这似乎是很多开销。

回答by Buhake Sindi

First of all, PreparedStatementare never opened. It's just a prepared Statementthat 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.

首先，PreparedStatement从来没有打开过。它只是一个准备好的Statement被执行的。语句被发送到 RDBMS，该 RDBMS 执行由PreparedStatement. 与 SQL 语句的连接应在 SQL 查询期间打开，并在不需要其他 RDMS 调用时关闭。

You can send many Statement/PreparedStatementas you require provided that you finallyclose its ResultSetand PreparedStatementonce you're completed with them and then close the RDBMS connection.

您可以根据需要发送许多Statement/ PreparedStatement，前提是您最终关闭它ResultSet，PreparedStatement一旦完成它们，然后关闭 RDBMS 连接。

回答by Vineet Reynolds

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

这个方法应该在每次调用方法时打开和关闭 PreparedStatement 吗？

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

如果您PreparedStatement在方法中创建对象，则必须在完成后关闭它。您可以重复使用该PreparedStatement对象进行多次执行，但是一旦使用完毕，就必须关闭它。

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:

这是因为，尽管所有 Statement 对象（包括 PreparedStatements）都应该在调用 时关闭Connection.close()，但情况很少如此。在某些 JDBC 驱动程序中，尤其是 Oracle 的驱动程序中，如果连接具有未关闭的 ResultSet 和 Statement 对象，则驱动程序将无法关闭连接。这意味着，在这些驱动程序上：

• 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.

• 您永远不应该丢失对 PreparedStatement 对象的引用。如果这样做，则连接不会关闭，直到垃圾收集发生。如果您为不同的 SQL 语句重用 PreparedStatement 实例，很容易忘记这一点。
• 一旦不再需要 PreparedStatement 就应该关闭它。只有这样 Connection.close() 才能真正拆除物理连接。

回答by Howard

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.

一旦语句关闭，RDMS 可能会释放与您的语句关联的所有资源。因此，要进一步使用它，您必须重新准备完全相同的语句。

回答by M.J.

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.

并且无需担心，如果您一次又一次地创建准备好的语句，因为您将一次又一次地使用相同的语句，因此不会有任何性能问题。

回答by venkat

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

是的..如果您创建准备好的语句 n 次没有问题，因为您将在所有地方使用相同的语句。无需对性能进行任何观察

Thanks

谢谢