JDBC: Batch Updates


Batch updates in JDBC is a technique to improve database performance by grouping multiple SQL statements together and executing them in a batch instead of executing them one by one. It reduces the number of round trips between the database and the application, thereby improving performance.

To use batch updates in JDBC, you need to follow the following steps:

  1. Create a connection to the database using the DriverManager.getConnection() method.
  2. Create a PreparedStatement object using the Connection.prepareStatement() method. The SQL statement that you want to execute should be passed as an argument to this method.
  3. Set the parameter values for the SQL statement using the appropriate setXXX() method of the PreparedStatement object.
  4. Add the PreparedStatement object to a batch using the addBatch() method of the Statement object.
  5. Repeat steps 2 to 4 for each SQL statement that you want to execute in the batch.
  6. Execute the batch using the executeBatch() method of the Statement object.
  7. Process the results of the batch execution.

Here is an example that demonstrates batch updates in JDBC:

try {
    // Create a connection to the database
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "username", "password");

    // Create a PreparedStatement object
    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO employee (name, age, salary) VALUES (?, ?, ?)");

    // Set parameter values for the first SQL statement
    pstmt.setString(1, "John");
    pstmt.setInt(2, 30);
    pstmt.setDouble(3, 50000.00);

    // Add the first SQL statement to the batch
    pstmt.addBatch();

    // Set parameter values for the second SQL statement
    pstmt.setString(1, "Mary");
    pstmt.setInt(2, 25);
    pstmt.setDouble(3, 40000.00);

    // Add the second SQL statement to the batch
    pstmt.addBatch();

    // Execute the batch
    int[] updateCounts = pstmt.executeBatch();

    // Process the results of the batch execution
    for (int i = 0; i < updateCounts.length; i++) {
        if (updateCounts[i] == Statement.SUCCESS_NO_INFO || updateCounts[i] > 0) {
            System.out.println("SQL statement " + (i+1) + " executed successfully.");
        } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
            System.out.println("SQL statement " + (i+1) + " failed to execute.");
        }
    }

    // Close the PreparedStatement and Connection objects
    pstmt.close();
    conn.close();
} catch (SQLException ex) {
    ex.printStackTrace();
}

In this example, we are creating a PreparedStatement object and setting parameter values for two SQL statements. We are then adding these SQL statements to a batch using the addBatch() method. Finally, we are executing the batch using the executeBatch() method and process the results of the batch execution.

A quick recap to java