在方法之间重用PreparedStatement？
我们都知道 我们应该重用 JDBC PreparedStatement 而不是在循环中创建新实例。
但是如何处理不同方法调用之间的PreparedStatement重用呢？ 重用“规则”还算数吗？
我是否应该真正考虑为 PreparedStatement 使用字段，还是应该在每次调用中关闭并重新创建准备好的语句（将其保留在本地）？ （当然，此类的实例将绑定到Connection，这在某些体系结构中可能是一个缺点）
我知道理想的答案可能是“这取决于”。
但我正在为经验不足的开发人员寻找最佳实践，以便他们在大多数情况下都会做出正确的选择。
We all know that we should rather reuse a JDBC PreparedStatement than creating a new instance within a loop.
But how to deal with PreparedStatement reuse between different method invocations?
Does the reuse-"rule" still count?
Should I really consider using a field for the PreparedStatement or should I close and re-create the prepared statement in every invocation (keep it local)?
(Of course an instance of such a class would be bound to a Connection which might be a disadvantage in some architectures)
I am aware that the ideal answer might be "it depends".
But I am looking for a best practice for less experienced developers that they will do the right choice in most of the cases.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
可能是？这将是一个巨大劣势。您要么需要同步对它的访问，这会彻底破坏您的多用户性能，要么创建多个实例并将它们保留在池中。屁股严重疼痛。
语句池是 JDBC 驱动程序的工作，大多数（如果不是全部）当前的驱动程序都会为您执行此操作。当您调用
prepareStatement或prepareCall时，驱动程序将处理现有资源和预编译语句的重用。Statement对象与连接绑定，应尽快使用连接并将其返回到池中。简而言之，最好的标准做法是在方法开始时获取
PreparedStatement，在循环中重复使用它，然后在方法结束时关闭它。实践。Might be? it would be a huge disadvantage. You'd either need to synchronize access to it, which would kill your multi-user performance stone-dead, or create multiple instances and keep them in a pool. Major pain in the ass.
Statement pooling is the job of the JDBC driver, and most, if not all, of the current crop of drivers do this for you. When you call
prepareStatementorprepareCall, the driver will handle re-use of existing resource and pre-compiled statements.Statementobjects are tied to a connection, and connections should be used and returned to the pool as quickly as possible.In short, the standard practice of obtaining a
PreparedStatementat the start of the method, using it repeatedly within a loop, then closing it at the end of the method, is best practice.许多数据库工作负载都是 CPU 密集型的，而不是 IO 密集型的。这意味着数据库最终会花费更多的时间来完成诸如解析 SQL 查询并弄清楚如何处理它们（执行“执行计划”）等工作，而不是访问磁盘。对于“事务性”工作负载比“报告”工作负载更是如此，但在这两种情况下，准备计划所花费的时间可能会比您预期的要多。
因此，如果要频繁执行该语句，并且在“方法调用之间”进行（正确）安排以缓存PreparedStatements 的麻烦值得开发人员花时间，那么这总是一个好主意。与性能一样，测量是关键，但如果您可以足够便宜地做到这一点，请出于习惯缓存您的PreparedStatement。
一些 JDBC 驱动程序和/或连接池提供透明的“准备语句缓存”，因此您不必自己执行此操作。只要您了解特定选择的透明缓存策略的行为，就可以让它跟踪事物……您真正想要避免的是对数据库的命中。
Many database workloads are CPU-bound, not IO-bound. This means that the database ends up spending more time doing work such as parsing SQL queries and figuring out how to handle them (doing the 'execution plan'), than it spends accessing the disk. This is more true of 'transactional' workloads than 'reporting' workloads, but in both cases the time spent preparing the plan may be more than you expect.
Thus it is always a good idea, if the statement is going to be executed frequently and the hassle of making (correct) arrangements to cache PreparedStatements 'between method invocations' is worth your developer time. As always with performance, measurement is key, but if you can do it cheaply enough, cache your PreparedStatement out of habit.
Some JDBC drivers and/or connection pools offer transparent 'prepared statement caching', so that you don't have to do it yourself. So long as you understand the behaviour of your particular chosen transparent caching strategy, it's fine to let it keep track of things ... what you really want to avoid is the hit on the database.
是的，它可以重用，但我相信这仅在使用相同的
Connection对象时才有效，并且如果您使用数据库连接池（例如，在 Web 应用程序中），则>Connection对象每次都可能不同。出于这个原因，我总是在每次在 Web 应用程序中使用之前重新创建
PreparedStatement。如果您不使用连接池，那么您就很成功了！
Yes it can be reused, but I believe this only counts if the same
Connectionobject is being used and if you are using a Database Connection Pool (from within a Web Application, for example) then theConnectionobjects will be potentially different each time.I always recreate the
PreparedStatementbefore each use within a Web Application for this reason.If you aren't using a Connection Pool then you are golden!
我没有看到区别：如果我对同一连接重复执行同一语句，为什么不以任何方式重用
PreparedStatement呢？如果多个方法执行相同的语句，那么也许该语句需要封装在它自己的方法（甚至它自己的类）中。这样您就不需要传递PreparedStatement。I don't see the difference: If I execute the same statement repeatedly against the same connection, why not reuse the
PreparedStatementin any way? If multiple methods execute the same statement, then maybe that statement needs to be encapsulated in its own method (or even its own class). That way you wouldn't need to pass around aPreparedStatement.