Batch updates using PreparedStatement allow multiple parameterized SQL operations to be grouped and executed together. The addBatch() method adds the current set of parameter values to the batch after assigning values using methods such as setInt(), setString(), and setDouble(). The executeBatch() method executes all the parameterized operations in the batch and returns an integer array containing the number of affected rows. Using PreparedStatement for batch processing is useful when the same SQL operation must be performed for multiple records and also helps avoid constructing SQL queries manually with values.
Program: Performing Batch Updates Using PreparedStatement
This program inserts multiple employee records using a parameterized INSERT query. The parameter values are changed for each employee, addBatch() adds each set of values to the batch, and executeBatch() executes all insert operations together.
import java.sql.*;
public class PreparedStatementBatchExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
String sql =
"INSERT INTO employee " +
"(employee_id, employee_name, department, salary) " +
"VALUES (?, ?, ?, ?)";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
PreparedStatement pstmt =
con.prepareStatement(sql);
// First employee
pstmt.setInt(1, 110);
pstmt.setString(2, "Sai");
pstmt.setString(3, "IT");
pstmt.setDouble(4, 45000.00);
pstmt.addBatch();
// Second employee
pstmt.setInt(1, 111);
pstmt.setString(2, "Akhila");
pstmt.setString(3, "HR");
pstmt.setDouble(4, 47000.00);
pstmt.addBatch();
// Third employee
pstmt.setInt(1, 112);
pstmt.setString(2, "Raghu");
pstmt.setString(3, "Finance");
pstmt.setDouble(4, 52000.00);
pstmt.addBatch();
// Execute the batch
int[] results = pstmt.executeBatch();
System.out.println("Batch executed successfully.");
for (int i = 0; i < results.length; i++) {
System.out.println(
"Statement " + (i + 1) +
": " + results[i] +
" row affected."
);
}
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" PreparedStatementBatchExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" PreparedStatementBatchExample
Batch executed successfully.
Statement 1: 1 row affected.
Statement 2: 1 row affected.
Statement 3: 1 row affected.Code language: CSS (css)