1. Basic JDBC Batch Update Example
2. JDBC Batch Update with Transaction
3. JDBC Batch Update using PreparedStatement
4. Specify Batch Size
5. Performance Test for JDBC Batch Update
String jdbcURL = "jdbc:mysql://localhost:3306/bookshop"; String username = "root"; String password = "password"; Connection connection = null; try { connection = DriverManager.getConnection(jdbcURL, username, password); Statement statement = connection.createStatement(); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email1', 'pass1', 'Name 1')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email2', 'pass2', 'Name 2')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email3', 'pass3', 'Name 3')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email4', 'pass4', 'Name 4')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email5', 'pass5', 'Name 5')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email6', 'pass6', 'Name 6')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email7', 'pass7', 'Name 7')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email8', 'pass8', 'Name 8')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email9', 'pass9', 'Name 9')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email0', 'pass0', 'Name10')"); int[] updateCounts = statement.executeBatch(); for (int count : updateCounts) { System.out.println(count); } connection.close(); } catch (SQLException ex) { ex.printStackTrace(); }As you can see, you can add many SQL statements to a Statement object via its addBatch() method, and call executeBatch() to send the batch to the server for processing. The executeBatch() method returns an array of integer numbers indicating the number of rows affected by each query, in the order of the queries added.Note that with batch update, you can specify only SQL statements that return values like that (Insert, Update, schema update…). That means using SQL Select statements for batch processing makes none sense.
String jdbcURL = "jdbc:mysql://localhost:3306/bookshop"; String username = "root"; String password = "password"; Connection connection = null; try { connection = DriverManager.getConnection(jdbcURL, username, password); connection.setAutoCommit(false); Statement statement = connection.createStatement(); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email1', 'pass1', 'Name 1')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email2', 'pass2', 'Name 2')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email3', 'pass3', 'Name 3')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email4', 'pass4', 'Name 4')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email5', 'pass5', 'Name 5')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email6', 'pass6', 'Name 6')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email7', 'pass7', 'Name 7')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email8', 'pass8', 'Name 8')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email9', 'pass9', 'Name 9')"); statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email0', 'pass0', 'Name 10')"); int[] updateCounts = statement.executeBatch(); for (int count : updateCounts) { System.out.println(count); } connection.commit(); connection.close(); } catch (SQLException ex) { ex.printStackTrace(); try { connection.rollback(); } catch (SQLException e) { e.printStackTrace(); } }
String sql = "INSERT INTO users (email, password, full_name) VALUES (?, ?, ?)"; PreparedStatement statement = connection.prepareStatement(sql); statement.addBatch();And the following code example illustrates how to execute bath update from a List of custom object type (User):
public void insertBatch(List<User> listUsers) { String jdbcURL = "jdbc:mysql://localhost:3306/bookshop"; String username = "root"; String password = "password"; Connection connection = null; try { connection = DriverManager.getConnection(jdbcURL, username, password); connection.setAutoCommit(false); String sql = "INSERT INTO users (email, password, full_name) VALUES (?, ?, ?)"; PreparedStatement statement = connection.prepareStatement(sql); for (User user : listUsers) { statement.setString(1, user.getEmail()); statement.setString(2, user.getPassword()); statement.setString(3, user.getFullName()); statement.addBatch(); } statement.executeBatch(); connection.commit(); connection.close(); } catch (SQLException ex) { ex.printStackTrace(); try { connection.rollback(); } catch (SQLException e) { e.printStackTrace(); } } }Note that the for loop is used to iterate over elements in the collection to set values for the PreparedStatement.
public void insertBatch(List<User> listUsers) { String jdbcURL = "jdbc:mysql://localhost:3306/bookshop"; String username = "root"; String password = "password"; int batchSize = 50; Connection connection = null; try { connection = DriverManager.getConnection(jdbcURL, username, password); connection.setAutoCommit(false); String sql = "INSERT INTO users (email, password, full_name) VALUES (?, ?, ?)"; PreparedStatement statement = connection.prepareStatement(sql); int count = 0; for (User user : listUsers) { statement.setString(1, user.getEmail()); statement.setString(2, user.getPassword()); statement.setString(3, user.getFullName()); statement.addBatch(); count++; if (count % batchSize == 0) { statement.executeBatch(); } } // execute the remaining queries statement.executeBatch(); connection.commit(); connection.close(); } catch (SQLException ex) { ex.printStackTrace(); try { connection.rollback(); } catch (SQLException e) { e.printStackTrace(); } } }As you can see, the number of statements in each batch is 50. You can tweak this number to get best performance depending on your application.
Number of statements | Regular update | Batch update |
100 | 118 ms | 71 ms |
1000 | 723 ms | 403 ms |
10,000 | 4,465 ms | 2,475 ms |
100,000 | 34,890 ms | 18,951 ms |