从 Java 在 SQL Server 中插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3095464/
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
Multiple row insert in SQL Server from Java
提问by Kaddy
I need to insert multiple rows into SQL Server database (100 at a time) from my Java code. How can I do this? Currently I am inserting one by one and this does not look efficient.
我需要从我的 Java 代码中将多行插入 SQL Server 数据库（一次 100 行）。我怎样才能做到这一点？目前我正在一一插入，这看起来效率不高。
回答by BalusC
You can use PreparedStatement#addBatch()to create a batch and executeBatch()to execute it.
您可以使用PreparedStatement#addBatch()来创建批处理并executeBatch()执行它。
Connection connection = null;
PreparedStatement statement = null;
try {
connection = database.getConnection();
statement = connection.prepareStatement(SQL);
for (int i = 0; i < items.size(); i++) {
Item item = items.get(i);
statement.setString(1, item.getSomeValue());
// ...
statement.addBatch();
if ((i + 1) % 100 == 0) {
statement.executeBatch(); // Execute every 100 items.
}
}
statement.executeBatch();
} finally {
if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}
See also:
另见：
回答by froadie
回答by Tim Coker
You can pass one very long string to SQL with multiple inserts as one statement to SQL Server. This won't work if you're doing parameterized queries, though. And concatenated SQL strings are "Generally a Bad Idea."
您可以将一个很长的字符串传递给 SQL，并将多个插入作为一条语句传递给 SQL Server。但是，如果您正在执行参数化查询，这将不起作用。并且串联的 SQL 字符串“通常是一个坏主意”。
You might be better off looking at the BULK INSERTcommand. It has the problem of being rigid about column orders and such. But its WAY FAST!!
您最好查看BULK INSERT命令。它存在对列顺序等严格的问题。但它的方式很快！
