何时关闭 JDBC 中的 Connection、Statement、PreparedStatement 和 ResultSet
关于 JDBC 编码的几个问题：
- 对于单个客户端应用程序，我们需要连接池吗？
- 在开始时创建一个连接并保持其活动状态而不关闭它直到应用程序退出是一个好主意吗？ 为什么？
PreparedStatement与Connection关联，如果我的连接在每次查询后都没有关闭，为什么不保持PreparedStatement活动并在其他方法中重用它呢？- 如果我们为每个查询创建
PreparedStatement，数据库是否知道它是相同的PreparedStatement并在第一次之后忽略不必要的操作？ PreparedStatement不是创建一次并重用多次的语句吗？ 如果是的话为什么每次都需要关闭呢？
我知道调用 close() 将释放资源。 但是如果我们知道稍后会使用它，为什么要释放它并稍后再次请求呢？
多客户端应用程序怎么样？ 我们需要一个连接池，所以每次都需要创建和关闭Connection、Statement和PreparedStatement？
Few questions on JDBC coding:
- For a single client application, do we need a Connection pool?
- Is it a good idea to create a
Connectionat the beginning and keep it alive without close it until application exit? Why? PreparedStatementis associated withConnection, if my connection is not closed after each query, why not keep thePreparedStatementalive and reuse it in other methods?- if we create
PreparedStatementeach query, does the database knows it is the samePreparedStatementand ignore unnecessary actions after the first time? PreparedStatementis not create once and reuse many times statement? If yes, why need to close it each time?
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?
How about a multi-client application? We need a connection pool and so we need to create and close Connection, Statement, and PreparedStatement each time?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
就我个人而言，我会使用池，因为这将为您处理所有资源管理。 如果您的连接要求发生变化，您可以轻松修改池配置。 有了池，您就可以根据最佳实践打开/关闭连接和准备好的语句，并将资源管理留给池。
通常，当使用池时：
此外，根据池的实现，它可能能够在出现资源泄漏时通知您，从而更容易识别代码中的此类问题。
看一下 DBCP 等示例实现的源代码 - 了解它们的工作原理非常有趣。
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-practice and leave the resource management to the pool.
Typically, when using a pool:
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.
1.即使您只有一个客户端，连接池可能仍然有益。 连接到数据库可能需要很长时间，因此经常这样做可能会因网络请求缓慢而减慢应用程序的速度。 此外，正如 @teabot 所解释的，池可能有助于识别是否有任何连接未关闭。
2. 由于两个原因，打开连接并使其永远保持打开状态并不是一个好主意。 首先，如果网络暂时中断，连接可能会中断。 打开的时间越长，在需要时死亡的可能性就越大。 其次，失败的事务可能会使连接处于不适合继续操作的状态。 最好的方法通常是打开几个连接，重复使用它们五到十分钟，然后回收它们。
3. 根据数据库和驱动程序，连接可能具有准备好的语句缓存。 即使使用不同的连接，RDBMS 通常也会缓存完全相同的语句（包括其参数）。 因此 SELECT * FROM table WHERE value=? 因为准备好的语句将跨连接缓存，但如果您指定像 SELECT * FROM table WHERE value='your_data' 这样的参数值，那么它可能不会在服务器端缓存。
4. 正如3中所解释的，取决于RDBMS的实现，做一个基准测试。
5. 无需关闭并再次准备将使用不同参数重用的语句。 只需重新设置参数并执行即可。
对于多个客户端，数据库始终具有并发连接限制，该限制通常不是很大的数字。 如果所有客户端都通过 Web 应用程序，那么像 DBCP 这样的池就可以了。 但显然，为每个客户端创建一个永久打开多个连接的池是不可取的。
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.
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.
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.
4. As explained in 3, depends on the RDBMS implementation, do a 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.
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.