Java: add multiple rows to MySQL using PreparedStatement

Question

I want to insert multiple rows into a MySQL table at once using Java. The number of rows is dynamic. I used to do ...

for (String element : array) { myStatement.setString(1, element[0]); myStatement.setString(2, element[1]); myStatement.executeUpdate(); } 

I would like to optimize this to use the syntax supported by MySQL:

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

but with PreparedStatement I do not know how to do this, because I do not know in advance how many elements the array will contain. If this is not possible with PreparedStatement , how else can I do it (and still avoid the values ​​in the array)?

Answer 1

You can create the PreparedStatement#addBatch() package and execute it PreparedStatement#executeBatch() .

Here is an example run:

 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 runs every 1000 items because some JDBC and / or DB drivers may have a packet length limit.

See also :

• JDBC Tutorial - Using PreparedStatement
• JDBC Tutorial - Using Statement Objects for Batch Updates

Answer 2

When using the MySQL driver, you must set the connection parameter rewriteBatchedStatements to true ( jdbc:mysql://localhost:3306/TestDB?**rewriteBatchedStatements=true**) .

With this option, the operator is overwritten for bulk insertion, when the table is locked only once, and indexes are updated only once. So it is much faster.

Without this option, the only benefit is cleaner source code.

Answer 3

If you can create your SQL query dynamically, you can make the following workaround:

  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(); 

Answer 4

If you have an automatic increase in the table and you need to access it. you can use the following approach ... Do a test before use, because getGeneratedKeys () in Statement, because it depends on the driver used. The code below is tested on Mary DB 10.0.12 and Mary JDBC driver 1.2

Remember that increasing batch size only improves performance to a certain extent ... for my installation, increasing batch size above 500 actually impaired performance.

 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(); } } 

Answer 5

@Ali Shakiba your code needs some modification. Part of the error:

 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(); 

Answer 6

we can send multiple updates to JDBC to send package updates.

we can use Statement, PreparedStatement and CallableStatement objects to update bacth with autocommit disabled

The addBatch () and executeBatch () functions are available with all operator objects to have BatchUpdate.

here the addBatch () method adds a set of statements or parameters to the current batch.