Java：使用 PreparedStatement 将多行插入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4355046/
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
Java: Insert multiple rows into MySQL with PreparedStatement
提问by Tom Marthenal
I want to insert multiple rows into a MySQL table at once using Java. The number of rows is dynamic. In the past I was doing...
我想使用 Java 一次将多行插入到 MySQL 表中。行数是动态的。过去我在做...
for (String element : array) {
myStatement.setString(1, element[0]);
myStatement.setString(2, element[1]);
myStatement.executeUpdate();
}
I'd like to optimize this to use the MySQL-supported syntax:
我想优化它以使用 MySQL 支持的语法：
INSERT INTO table (col1, col2) VALUES ('val1', 'val2'), ('val1', 'val2')[, ...]
but with a PreparedStatementI don't know of any way to do this since I don't know beforehand how many elements arraywill contain. If it's not possible with a PreparedStatement, how else can I do it (and still escape the values in the array)?
但是PreparedStatement我不知道有什么方法可以做到这一点，因为我事先不知道array将包含多少个元素。如果无法使用 a PreparedStatement，我还能怎么做（并且仍然转义数组中的值）？
采纳答案by BalusC
You can create a batch by PreparedStatement#addBatch()and execute it by PreparedStatement#executeBatch().
您可以通过 来创建批处理PreparedStatement#addBatch()并通过执行它PreparedStatement#executeBatch()。
Here's a kickoff example:
这是一个启动示例：
public void save(List<Entity> entities) throws SQLException {
try (
Connection connection = database.getConnection();
PreparedStatement statement = connection.prepareStatement(SQL_INSERT);
) {
int i = 0;
for (Entity entity : entities) {
statement.setString(1, entity.getSomeProperty());
// ...
statement.addBatch();
i++;
if (i % 1000 == 0 || i == entities.size()) {
statement.executeBatch(); // Execute every 1000 items.
}
}
}
}
It's executed every 1000 items because some JDBC drivers and/or DBs may have a limitation on batch length.
它每 1000 个项目执行一次，因为某些 JDBC 驱动程序和/或 DB 可能对批处理长度有限制。
See also:
另见：
回答by Ali Shakiba
If you can create your sql statement dynamically you can do following workaround:
如果您可以动态创建 sql 语句，则可以执行以下解决方法：
String myArray[][] = { { "1-1", "1-2" }, { "2-1", "2-2" }, { "3-1", "3-2" } };
StringBuffer mySql = new StringBuffer("insert into MyTable (col1, col2) values (?, ?)");
for (int i = 0; i < myArray.length - 1; i++) {
mySql.append(", (?, ?)");
}
myStatement = myConnection.prepareStatement(mySql.toString());
for (int i = 0; i < myArray.length; i++) {
myStatement.setString(i, myArray[i][1]);
myStatement.setString(i, myArray[i][2]);
}
myStatement.executeUpdate();
回答by kapil das
we can be submit multiple updates together in JDBC to submit batch updates.
我们可以在 JDBC 中一起提交多个更新以提交批量更新。
we can use Statement, PreparedStatement, and CallableStatement objects for bacth update with disable autocommit
我们可以使用 Statement、PreparedStatement 和 CallableStatement 对象进行禁用自动提交的 bacth 更新
addBatch()and executeBatch()functions are available with all statement objects to have BatchUpdate
addBatch()和executeBatch()函数可用于所有语句对象以进行 BatchUpdate
here addBatch() method adds a set of statements or parameters to the current batch.
这里的 addBatch() 方法将一组语句或参数添加到当前批处理中。
回答by MichalSv
When MySQL driver is used you have to set connection param rewriteBatchedStatementsto true ( jdbc:mysql://localhost:3306/TestDB?**rewriteBatchedStatements=true**).
使用 MySQL 驱动程序时，您必须将连接参数设置rewriteBatchedStatements为 true ( jdbc:mysql://localhost:3306/TestDB?**rewriteBatchedStatements=true**)。
With this param the statement is rewritten to bulk insert when table is locked only once and indexes are updated only once. So it is much faster.
使用此参数，当表仅锁定一次且索引仅更新一次时，语句将被重写为批量插入。所以它要快得多。
Without this param only advantage is cleaner source code.
没有这个参数，唯一的好处是更干净的源代码。
回答by gladiator
In case you have auto increment in the table and need to access it.. you can use the following approach... Do test before using because getGeneratedKeys() in Statement because it depends on driver used. The below code is tested on Maria DB 10.0.12 and Maria JDBC driver 1.2
如果您在表中有自动增量并需要访问它..您可以使用以下方法...使用前进行测试，因为 getGeneratedKeys() 在 Statement 中，因为它取决于所使用的驱动程序。以下代码在 Maria DB 10.0.12 和 Maria JDBC 驱动程序 1.2 上进行了测试
Remember that increasing batch size improves performance only to a certain extent... for my setup increasing batch size above 500 was actually degrading the performance.
请记住，增加批量大小只会在一定程度上提高性能……对于我的设置，将批量大小增加到 500 以上实际上会降低性能。
public Connection getConnection(boolean autoCommit) throws SQLException {
Connection conn = dataSource.getConnection();
conn.setAutoCommit(autoCommit);
return conn;
}
private void testBatchInsert(int count, int maxBatchSize) {
String querySql = "insert into batch_test(keyword) values(?)";
try {
Connection connection = getConnection(false);
PreparedStatement pstmt = null;
ResultSet rs = null;
boolean success = true;
int[] executeResult = null;
try {
pstmt = connection.prepareStatement(querySql, Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < count; i++) {
pstmt.setString(1, UUID.randomUUID().toString());
pstmt.addBatch();
if ((i + 1) % maxBatchSize == 0 || (i + 1) == count) {
executeResult = pstmt.executeBatch();
}
}
ResultSet ids = pstmt.getGeneratedKeys();
for (int i = 0; i < executeResult.length; i++) {
ids.next();
if (executeResult[i] == 1) {
System.out.println("Execute Result: " + i + ", Update Count: " + executeResult[i] + ", id: "
+ ids.getLong(1));
}
}
} catch (Exception e) {
e.printStackTrace();
success = false;
} finally {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (connection != null) {
if (success) {
connection.commit();
} else {
connection.rollback();
}
connection.close();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
回答by vinay
@Ali Shakiba your code needs some modification. Error part:
@Ali Shakiba 您的代码需要一些修改。错误部分：
for (int i = 0; i < myArray.length; i++) {
myStatement.setString(i, myArray[i][1]);
myStatement.setString(i, myArray[i][2]);
}
Updated code:
更新代码：
String myArray[][] = {
{"1-1", "1-2"},
{"2-1", "2-2"},
{"3-1", "3-2"}
};
StringBuffer mySql = new StringBuffer("insert into MyTable (col1, col2) values (?, ?)");
for (int i = 0; i < myArray.length - 1; i++) {
mySql.append(", (?, ?)");
}
mysql.append(";"); //also add the terminator at the end of sql statement
myStatement = myConnection.prepareStatement(mySql.toString());
for (int i = 0; i < myArray.length; i++) {
myStatement.setString((2 * i) + 1, myArray[i][1]);
myStatement.setString((2 * i) + 2, myArray[i][2]);
}
myStatement.executeUpdate();
