Updated March 29, 2023
Definition of JDBC Batch Update
JDBC batch update is nothing but a group of update statements, after grouping, all the statements will be sent to the database for update operations. It will not send the update statement one by one it will send all the update statements in one batch file, it will send all update statements to the database server in one go. Sending all the update statements to the database server is faster than send them one by one, it will be less traffic involved while sending the update statement in a batch file. It is important to update the batch file.
Syntax
Below is the syntax which is as follows.
1) JDBC transaction using simple statement –
public static void main /* main method */ (string args [])
class.forName ("database driver name")
connection con_object DriverManager.getConnection ("connection string")
Try
{
Statement stmt_object = null;
Statement stmt_object = con_object.createStatement ();
Stmt_object.addBatch ("update query");
Stmt_object.addBatch ("update query");
Stmt_object.addBatch ("update query");
}
finally
{
If (stmt_object != null)
stmt_object.close ();
}2) JDBC transaction using Prepared statement –
public static void main /* main method */ (string args [])
class.forName ("database driver name")
Connection con_object DriverManager.getConnection ("connection string")
Con_object.setAutoCommit (value)
PreparedStatement Prp_stmt_object = null;
Try
{
PreparedStatement = con_object.prepareStatement (update query);
PreparedStatement.setString (value, value);
PreparedStatement.setString (value, value);
PreparedStatement.addBatch ();
PreparedStatement.setString (value, value);
PreparedStatement.setString (value, value);
PreparedStatement.addBatch ();
}
Finally
{
If (Prp_stmt_object != null)
Prp_stmt_object.close ();Parameters:
1) Connection String – It contains the information of username, password, hostname & port of the database server.
2) Add batch – This method is used to execute the statement in the batch. It will add the data in the batch file after adding in the batch it will process to the database server for execution.
3) Statement – This method is used to execute the batch update operation in java. To perform the batch update operations we need to add SQL statement in the batch.
4) Execute batch – This method is used to execute the batch update. We can execute multiple update statements using this method.
5) Prepared Statement – This statement is used to execute the statement. We can execute batch update operations using PreparedStatement in JDBC.
How to Perform batch update in JDBC?
- Executing using two ways.
1) Statement
2) PreparedStatement
- Using batch database is executing the update operations in parallel, the execution speed of batch update as compared to one by one updation is high.
Batch update will take less time to update the statement as compared to one-by-one statement update.
- Using statement we can use the statement object for the execution of batch update on the database server.
- Using PreparedStatemente we can use the PreparedStatement object for the execution of batch update on the database server.
- There are two methods used to update multiple statements in the batch.
1) addBatch()
2) executeBatch()
- Basically, add batch method is used to add a single statement into the batch. This method will add multiple single update statements into the batch.
- Execute batch method is used to execute all the statement groups together. This method is used to start the execution of all update statements in the JDBC batch update method.
- Execute batch method is returning the integer of arrays, also it will returning each element of the array.
- Below steps shows how to execute the batch using statement object are as follows.
1) Create the object of statement using createStatement() method.
2) Set the autocommit method false.
3) Add multiple update statement using addBatch() method.
4) Execute all update statements using executeBatch() method.
5) After all execution commits all the changes which was we have done using batch update.
- We can remove the update statement from the batch using clearBatch() method. This method is used to remove all the statement which was added in batch.
- We cannot remove specific statements using clearBatch method it will delete all the statements from the batch.
Examples
Below are the examples which are as follows.
1. JDBC batch update using statement
The below example shows batch update using the statement.
Code:
public static void main /* main method */ (String[] args) throws Exception {
final String DB_CON = "jdbc:postgresql://localhost:5432/jdbc_tran";
final String USER_NAME = "postgres";
final String PASSWORD = "postgres";
try {
Connection con = DriverManager.getConnection (DB_CON, USER_NAME, PASSWORD);
Statement stmt_tran = con.createStatement();
stmt_tran.addBatch ("update jdbc_tran set id=1 where stud_id=102");
stmt_tran.addBatch ("update jdbc_tran set id=101 where stud_id=103");
stmt_tran.addBatch ("update jdbc_tran set id=101 where stud_id=104");
int[] count = stmt_tran.executeBatch();
System.out.println ("Updated all the statement");
}
catch (SQLException sqlex)
{
sqlex.printStackTrace ();
}
2. JDBC batch update using commit and setAutoCommit method
The below example shows commit and setAutoCommit method.
Code:
public static void main /* main method */ (String[] args) throws Exception {
final String DB_CON = "jdbc:postgresql://localhost:5432/jdbc_tran";
final String USER_NAME = "postgres";
final String PASSWORD = "postgres";
try {
Connection con = DriverManager.getConnection (DB_CON, USER_NAME, PASSWORD);
con.setAutoCommit (false);
Statement stmt_tran = con.createStatement ();
stmt_tran.addBatch ("update jdbc_tran set id=102 where stud_id=102");
stmt_tran.addBatch ("update jdbc_tran set id=103 where stud_id=103");
stmt_tran.addBatch ("update jdbc_tran set id=104 where stud_id=104");
int[] count = stmt_tran.executeBatch();
con.commit ();
System.out.println ("Updated and committed all the transaction.....");
} catch (SQLException sqlex) {
sqlex.printStackTrace ();
}
3. JDBC batch update using commit, rollback, and setAutoCommit method
The below example shows commit, rollback, and setAutoCommit method.
Code:
public static void main /* main method */ (String[] args) throws Exception {
final String DB_CON = "jdbc:postgresql://localhost:5432/jdbc_tran";
final String USER_NAME = "postgres";
final String PASSWORD = "postgres";
try {
Connection con = DriverManager.getConnection (DB_CON, USER_NAME, PASSWORD);
con.setAutoCommit (false);
Statement stmt_tran = con.createStatement ();
stmt_tran.addBatch ("update jdbc_tran set id=102 where stud_id=102");
stmt_tran.addBatch ("update jdbc_tran set id=103 where stud_id=103");
stmt_tran.addBatch ("update jdbc_tran set id=104 where stud_id=104");
int[] count = stmt_tran.executeBatch();
con.commit ();
con.rollback ();
System.out.println ("Updated and rollback all the transaction.....");
} catch (SQLException sqlex) {
sqlex.printStackTrace ();
}
Conclusion
JDBC batch update is used to update multiple statements in the group. In JDBC we can update the batch using statement and using the PreparedStatement. There are two methods used in the JDBC batch i.e. addBatch and executeBatch. For deleting the batch statement we have using cleanBatch method.
Recommended Articles
This is a guide to JDBC Batch Update. Here we discuss the definition, syntax, How to perform batch update in JDBC? and examples for better understanding. You may also have a look at the following articles to learn more –
