java 何时关闭 JDBC 中的 Connection、Statement、PreparedStatement 和 ResultSet

When to close Connection, Statement, PreparedStatement and ResultSet in JDBC

提问by 5YrsLaterDBA

Few questions on JDBC coding:

关于JDBC编码的几个问题：

1. For a single client application, do we need a Connection pool?
2. Is it a good idea to create a Connectionat the beginning and keep it alive without close it until application exit? Why?
3. PreparedStatementis associated with Connection, if my connection is not closed after each query, why not keep the PreparedStatementalive and reuse it in other methods?
4. if we create PreparedStatementeach query, does the database knows it is the same PreparedStatementand ignore unnecessary actions after the first time?
5. PreparedStatementis not create once and reuse many times statement? If yes, why need to close it each time?

1. 对于单个客户端应用程序，我们是否需要连接池？
2. Connection在开始时创建一个并使其保持活动状态而不关闭它直到应用程序退出是个好主意吗？为什么？
3. PreparedStatement与 相关联Connection，如果我的连接在每次查询后都没有关闭，为什么不保持PreparedStatement活动状态并在其他方法中重用它？
4. 如果我们创建PreparedStatement每个查询，数据库是否知道它是相同的PreparedStatement并在第一次之后忽略不必要的操作？
5. PreparedStatement是不是创建一次并重复使用多次语句？如果是，为什么每次都需要关闭它？

I know the call to close()will release the resource. But If we know we are going to use it later, why release it and then request it again later?

我知道调用close()将释放资源。但是，如果我们知道以后要使用它，为什么要释放它，然后再请求它呢？

How about a multi-client application? We need a connection pool and so we need to create and close Connection, Statement, and PreparedStatementeach time?

多客户端应用程序怎么样？我们需要一个连接池，所以我们需要创建和关闭连接，Statement以及PreparedStatement每一次？

Thanks.

谢谢。

回答by teabot

Personally I'd use a pool as this will take care of all of the resource management for you. If your connection requirements change then you can easily modify the pool configuration. With a pool in place you can open/close connections and prepared statements according to best-practiceand leave the resource management to the pool.

我个人会使用一个池，因为这将为您处理所有资源管理。如果您的连接要求发生变化，那么您可以轻松修改池配置。使用池后，您可以根据最佳实践打开/关闭连接和准备好的语句，并将资源管理留给池。

Typically, when using a pool:

通常，在使用池时：

• closing a connection will actually just return it to the pool
• the act of preparing a statement will either retrieve a previously prepared statement from the Connection's statement cache, or if one is not available, create a new statement and cache it for later use.
• the act of closing a PreparedStatement will actually just return it to the connection's statement cache.

• 关闭连接实际上只是将它返回到池中
• 准备语句的行为将从连接的语句缓存中检索先前准备好的语句，或者如果一个语句不可用，则创建一个新语句并将其缓存以备后用。
• 关闭 PreparedStatement 的行为实际上只是将它返回到连接的语句缓存。

Furthermore - depending on the pool implementation - it may be able to notify you when there are resource leaks making it easier to identify these sorts of problems in your code.

此外 - 取决于池实现 - 它可能能够在出现资源泄漏时通知您，从而更容易识别代码中的此类问题。

Take a look at the source of an example implementation like DBCP- it's quite interesting to see how they work.

看看像DBCP这样的示例实现的源代码- 看看它们是如何工作的非常有趣。

回答by Serg M Ten

1.Even if you have a single client, a connection pool may still be beneficial. Connecting to the database may take a significant time so doing it very often may slow down your application with slow network requests. Moreover as @teabot explains, a pool may help identifying if any connection is not being closed.

1.即使你只有一个客户端，连接池可能仍然是有益的。连接到数据库可能需要很长时间，因此经常这样做可能会因网络请求缓慢而降低应用程序的速度。此外，正如@teabot 所解释的那样，池可能有助于识别是否有任何连接未被关闭。

2.It is not a good idea to open a connection and leave it open forever for two reasons. First the connection may die if there is a temporary network interruption. The longer it is open the more likely that it is dead when required. Second, a failed transaction may leave the connection in a state not suitable for continuing operation. The best is usually to open a few connections, reuse them for five or ten minutes, then recycle them.

2.出于两个原因，打开一个连接并让它永远打开并不是一个好主意。首先，如果网络暂时中断，连接可能会中断。它打开的时间越长，在需要时它就越有可能失效。其次，失败的事务可能会使连接处于不适合继续操作的状态。最好的通常是打开几个连接，重复使用五到十分钟，然后回收它们。

3.Depending on the database and the driver, the connection may have a prepared statement cache. Even if using a different connection, the RDBMS usually caches statements that are exactly the same including it parameters. Therefore SELECT * FROM table WHERE value=? as a prepared statement will be cached across connections, but if you specify the parameter value like SELECT * FROM table WHERE value='your_data' then probably it won't be cached server side.

3.根据数据库和驱动程序，连接可能有准备好的语句缓存。即使使用不同的连接，RDBMS 通常也会缓存完全相同的语句，包括它的参数。因此 SELECT * FROM table WHERE value=? 作为准备好的语句将跨连接缓存，但如果您指定参数值，如 SELECT * FROM table WHERE value='your_data' 那么它可能不会被缓存服务器端。

4.As explained in 3, depends on the RDBMS implementation, do a benchmark.

4.如3中所述，依赖于RDBMS的实现，做一个benchmark。

5.There is no need to close and prepare again a statement which is going to be reused with different parameters. Just set again the parameters and execute.

5.无需关闭并重新准备将要使用不同参数重复使用的语句。只需再次设置参数并执行即可。

For multiple clients, the database will always have a concurrent connection limit which is not usually any big number. If all the clients go through a webapp then a pool like DBCP is all right. But obviously it's undesirable to create a pool for each client with several connections open permanently.

对于多个客户端，数据库将始终有一个并发连接限制，这通常不是一个很大的数字。如果所有客户端都通过一个 web 应用程序，那么像 DBCP 这样的池就可以了。但显然，为每个客户端创建一个池并永久打开多个连接是不可取的。