java 在循环内创建准备好的语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11971635/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creation of a prepared statement inside a loop
提问by Kumar Vivek Mitra
For clarification:I know that it's the right thing to create the PreparedStatementoutside the loop. I've asked this question just out of curiosity.
澄清一下：我知道PreparedStatement在循环外创建是正确的。我只是出于好奇问了这个问题。
Let's assume that I'm creating a PreparedStatementinside a loop with always the same SQL query.
让我们假设我正在创建一个PreparedStatement始终具有相同 SQL 查询的循环内部。
final String sql = "INSERT INTO ...";
while (condition) {
...
PreparedStatement statement = connection.prepareStatement(sql);
// Fill values of the prepared statement
// Execute statement
...
}
Is this useless since the PreparedStatementobject is always created anew? Or does the underlying database recognise that it's always the same SQL query with which the PreparedStatementis created and reuses it?
这是无用的，因为PreparedStatement对象总是重新创建？或者底层数据库是否认识到它总是与PreparedStatement创建和重用它的SQL 查询相同？
采纳答案by Geoffrey Wiseman
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
一些驱动程序会缓存准备好的语句，是的。例如，浏览此 Oracle 文档：http: //docs.oracle.com/cd/B10501_01/java.920/a96654/stmtcach.htm
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?
我不相信有任何事情要求对所有驱动程序都如此，尽管这似乎是许多 JDBC 驱动程序的一个可能的特性。听起来 MySQL 可能不会这样做： 如何使用 MySQL 准备好的语句缓存？
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.
也就是说，如果您真的想有效地使用准备好的语句，那么在每次循环迭代中使用准备好的语句的实例似乎更有意义。
回答by Kumar Vivek Mitra
1.If you are using the samePreparedStatementthroughout the loop, then its better you keep thePreparedStatementoutside the loop.
1.如果您在整个循环中都使用相同PreparedStatement的内容，那么最好将其保留PreparedStatement在循环之外。
2.If you have sql statment which keeps changing inside the loop, then only its worth using it in the loop.
2.如果你的sql语句在循环内不断变化，那么只有在循环中使用它才值得。
3.Moreover if its keep changing, then just use Statementinstead ofPreparedStatement, else the very purpose of PreparedStatement is lost as you keep changing it.
3.此外，如果它不断变化，那么只需使用Statement而不是PreparedStatement，否则随着您不断变化，PreparedStatement 的目的就失去了。
回答by Karl-Bj?rnar ?ie
Also try disable autocommit with Connection.setAutoCommit(false)and that you use PreparedStatement.executeBatch()
还尝试使用Connection.setAutoCommit(false)禁用自动提交，并使用PreparedStatement.executeBatch()
回答by SHUNMUGA RAJ PRABAKARAN
Two ways so far i know.
目前我知道的两种方式。
1st Way
第一种方式
Its insert record one by one
其插入记录一一
final String sql = "INSERT INTO tablename(columnname) Values(?)";
PreparedStatement statement = connection.prepareStatement(sql);
while (condition) {
statement.setString(1,value);
statement.executeUpdate();
}
(or)
（或者）
2nd way
第二种方式
It inserts all record as bulk insert
它将所有记录插入为批量插入
final String sql = "INSERT INTO tablename(columnname) Values(?)";
PreparedStatement statement = connection.prepareStatement(sql);
while (condition) {
statement.setString(1,value);
statement.addBatch();
}
statement.executeBatch();
回答by user572964
It is useless to create it everytime. See the link http://www.theserverside.com/news/1365244/Why-Prepared-Statements-are-important-and-how-to-use-them-properly
每次都创建它是没有用的。请参阅链接http://www.theserverside.com/news/1365244/Why-Prepared-Statements-are-important-and-how-to-use-them-properly
