在循环内创建准备好的语句 [英] Creation of a prepared statement inside a loop

问题描述

为澄清起见:我知道在循环外创建PreparedStatement是正确的做法.出于好奇，我问了这个问题.

For clarification: I know that it's the right thing to create the PreparedStatement outside the loop. I've asked this question just out of curiosity.

让我们假设我正在创建 包含始终相同的SQL查询的循环中.

Let's assume that I'm creating a PreparedStatement inside a loop with always the same SQL query.

final String sql = "INSERT INTO ...";
while (condition) {
   ...
   PreparedStatement statement = connection.prepareStatement(sql);
   // Fill values of the prepared statement
   // Execute statement
   ...
}

这是没有用的，因为总是重新创建PreparedStatement对象吗?还是底层数据库识别出创建PreparedStatement并重复使用它的SQL查询总是相同的?

Is this useless since the PreparedStatement object is always created anew? Or does the underlying database recognise that it's always the same SQL query with which the PreparedStatement is created and reuses it?

推荐答案

某些驱动程序会缓存准备好的语句，是的.例如，浏览以下Oracle文档: http://docs.oracle.com/cd/B10501_01/java.920/a96654/stmtcach.htm

Some drivers do cache prepared statements, yes. For example, skim this Oracle documentation: http://docs.oracle.com/cd/B10501_01/java.920/a96654/stmtcach.htm

我不认为任何要求所有驱动程序都适用的方法，尽管当然，这似乎是许多JDBC驱动程序的可能功能.听起来MySQL可能不会这样做: 如何使用MySQL准备的语句缓存?

I don't believe there's anything that requires this to be true for all drivers, although certainly it seems like a likely feature of many JDBC drivers. It sounds like MySQL might not do this: How to use MySQL prepared statement caching?

也就是说，如果您真的想有效地使用预备语句，那么看起来好像挂在每次循环迭代中使用的预备语句的实例上就更有意义了.

That said, if you really want to use prepared statements efficiently, it seems like hanging on to an instance of a prepared statement that you use on each loop iteration makes a lot more sense.

这篇关于在循环内创建准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！