Batch Processing

If you need to insert 1,000 rows, calling executeUpdate() 1,000 times means 1,000 separate round-trips to the database, each with its own network latency and overhead. Batch processing lets you queue up many statements and send them to the database together, which can be dramatically faster.

Why Batching Matters

Every call to executeUpdate() involves sending a request to the database server and waiting for a response. When you have a loop running that once per row, most of the time is spent on network round-trips rather than actual work. Batching groups multiple statements into a single request, so the round-trip cost is paid once instead of once per row.

Batching with Statement

Statement supports addBatch(sql) to queue up a SQL string, and executeBatch() to run everything that's been queued in one go. executeBatch() returns an int[] where each element is the row count affected by the corresponding queued statement.

Statement stmt = conn.createStatement();

stmt.addBatch("INSERT INTO students (name, course) VALUES ('Amit', 'JDBC')");
stmt.addBatch("INSERT INTO students (name, course) VALUES ('Priya', 'JDBC')");
stmt.addBatch("INSERT INTO students (name, course) VALUES ('Rahul', 'JDBC')");

int[] results = stmt.executeBatch(); // runs all 3 in one go
System.out.println(results.length + " statements executed.");

Batching with PreparedStatement (Preferred)

PreparedStatement supports batching too, and combines both benefits at once: the SQL is parameterized (safe from SQL injection) and batched (fewer round-trips). The pattern is to bind values with setX(), then call addBatch() to queue that set of values, and repeat for each row before calling executeBatch() once at the end.

import java.sql.*;

public class BatchInsert {
  public static void main(String[] args) throws Exception {
    Connection conn = DriverManager.getConnection(
      "jdbc:mysql://localhost:3306/coding_with_sonu", "root", "");
    conn.setAutoCommit(false); // see Transactions - batches pair well with manual commit

    String sql = "INSERT INTO students (name, course) VALUES (?, ?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);

    String[][] rows = {
      {"Amit", "JDBC"},
      {"Priya", "JDBC"},
      {"Rahul", "JDBC"}
    };

    for (String[] row : rows) {
      pstmt.setString(1, row[0]);
      pstmt.setString(2, row[1]);
      pstmt.addBatch(); // queue this row, don't run yet
    }

    int[] results = pstmt.executeBatch(); // send everything in one round-trip
    conn.commit();

    System.out.println(results.length + " rows inserted in one batch.");

    pstmt.close(); conn.close();
  }
}

Because the SQL text (INSERT INTO students (name, course) VALUES (?, ?)) never changes between iterations, only the bound values do, this is generally the preferred way to batch — it's both parameterized and batched at the same time.

Batching + Manual Commit for Bulk Inserts

For large bulk-insert jobs, batching is commonly combined with turning off auto-commit (covered on the Transactions page). Auto-commit off means the database isn't committing after every single queued statement either — you commit once, after the whole batch finishes, for maximum performance.