Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Java: Insert multiple rows into MySQL with PreparedStatement

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...

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:

INSERT INTO table (col1, col2) VALUES ('val1', 'val2'), ('val1', 'val2')[, ...] 

but with a PreparedStatement I don't know of any way to do this since I don't know beforehand how many elements array will contain. If it's not possible with a PreparedStatement, how else can I do it (and still escape the values in the array)?

like image 437
Tom Marthenal Avatar asked Dec 04 '10 18:12

Tom Marthenal


2 Answers

You can create a batch by PreparedStatement#addBatch() and execute it by 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.

See also:

  • JDBC tutorial - Using PreparedStatement
  • JDBC tutorial - Using Statement Objects for Batch Updates
like image 123
BalusC Avatar answered Oct 13 '22 09:10

BalusC


When MySQL driver is used you have to set connection param rewriteBatchedStatements to 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.

like image 31
MichalSv Avatar answered Oct 13 '22 10:10

MichalSv