Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database.
When can improving performance by send several SQL statements to the database at once and reduce the amount of communication overhead.
Batch Processing Have Three Methods.They are
The
addBatch() method of Statement is used to add individual statements to the batch. The executeBatch() is used to start the execution of all the statements grouped together.
The
executeBatch() returns an array of integers, and each element of the array represents the update count for the respective update statement.
You can remove them with the
clearBatch() method. This method removes all the statements you added with the addBatch() method. However, you cannot selectively choose which statement to remove.
Batching with Statement Object:
Here is a typical sequence of steps to use Batch Processing with Statment Object:
Create a Statement object using either createStatement() methods.
Set auto-commit to false using setAutoCommit().
Add as many as SQL statements you like into batch using addBatch() method on created statement object.
Execute all the SQL statements using executeBatch() method on created statement object.
Finally, commit all the changes using commit() method.
Example :
import java.sql.*;
public class JdbcExecuteStatement {
public static void main(String args[]) {
Connection conn = null;
String SQL="";
String name="fd";
String url = "jdbc:mysql://localhost:3306/test";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, pass);
// Create statement object
Statement stmt = conn.createStatement();
// Set auto-commit to false
conn.setAutoCommit(false);
// Create SQL statement
SQL = "INSERT INTO Abc (no, name) " +
"VALUES(200,'Zia')";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create one more SQL statement
SQL = "INSERT INTO Abc (no, name) " +
"VALUES(201,'Raj')";
stmt.addBatch(SQL);
// Add above SQL statement in the batch.
SQL = "INSERT INTO Abc (no, name) " +
"VALUES(203,'kumar')";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create one more SQL statement
SQL = "UPDATE Abc SET name='"+name+"' " +
"WHERE no = 1";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create an int[] to hold returned values
int[] count= stmt.executeBatch();
//Explicitly commit statements to apply changes
//conn.commit();
} catch (Exception e) {
System.out.println(e);
}
}
}
This above Program is insert multiple rows and updating multiple rows at once .
