- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is Parameterized Batch Update in JDBC? Explain with an example?
Grouping a set of INSERT or, UPDATE or, DELETE commands (those produce update count value) and execute them at once this mechanism is known as a batch update.
If you pass quires with parameters using batch update it is known as a parameterized batch update.
Generally, to perform batch updates you need to add all the required statements using the addBatch() method and execute them using the executeBatch() method as:
//Creating a Statement object Statement stmt = con.createStatement(); //Setting auto-commit false con.setAutoCommit(false); //Adding the statements to batch stmt.addBatch("INSERT INTO Sales VALUES ('KeyBoard', 'Amith', 'January', 1000, 'Hyderabad')"); stmt.addBatch("INSERT INTO Sales VALUES ('Earphones', 'SUMITH', 'March', 500, 'Vishakhapatnam')"); stmt.addBatch("INSERT INTO Sales VALUES ('Mouse', 'Sudha', 'September', 200, 'Vijayawada')"); //Executing the batch stmt.executeBatch();
If you observe the above code here, we are adding the certain part of the statement to the batch again and again instead of this, you can perform a parameterized batch update where you use a prepared statement and create a query with place holders and pass values to these place holders using the setter methods.
While adding values you need to add each set of values of the query to the batch and then execute them at once.
Creating a parameterized Batch update
To create a parameterized batch update, you need to create a prepared statement to insert, update, or, delete values with place holders.
Set values to the placeholders, parameters using the setXXX() statements. Add each set of values to the batch using the addbatch() method, Finally execute the batch using the executeBatch method.
Note: Before adding statements to the batch you need to turn the auto commit off using the con.setAutoCommit(false) and, after executing the batch you need to save the changes using the con.commit() method.
Example
Assume we have created a table named Sales in the database with the following description:
+-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | Product_Name | varchar(255) | YES | | NULL | | | Name_Of_Customer | varchar(255) | YES | | NULL | | | Month_Of_Dispatch | varchar(255) | YES | | NULL | | | Price | int(11) | YES | | NULL | | | Location | varchar(255) | YES | | NULL | | +-------------------+--------------+------+-----+---------+-------+
This example tries to insert a set of statements into the above mentioned table using parameterized batch update.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Statement; public class ParameterizedBatchUpdate { public static void main(String args[])throws Exception { //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/testDB"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established......"); //Creating a Statement object Statement stmt = con.createStatement(); //Setting auto-commit false con.setAutoCommit(false); PreparedStatement pstmt = con.prepareStatement("INSERT INTO Sales VALUES (?, ?, ?, ?, ?)"); pstmt.setString(1, "KeyBoard"); pstmt.setString(2, "Amith"); pstmt.setString(3, "January"); pstmt.setInt(4, 1000); pstmt.setString(5, "Hyderabad"); pstmt.addBatch(); pstmt.setString(1, "Earphones"); pstmt.setString(2, "Sumith"); pstmt.setString(3, "March"); pstmt.setInt(4, 500); pstmt.setString(5, "Vishakhapatnam"); pstmt.addBatch(); pstmt.setString(1, "Mouse"); pstmt.setString(2, "Sudha"); pstmt.setString(3, "September"); pstmt.setInt(4, 500); pstmt.setString(5, "Vishakhapatnam"); pstmt.addBatch(); //Executing the batch stmt.executeBatch(); //Saving the changes con.commit(); System.out.println("Records inserted......"); } }
Output
Connection established...... Records inserted......
- Related Articles
- What are batch updates in JDBC? Explain?
- Write an example JDBC program demonstrating the batch processing with statement object?
- Write an example JDBC program demonstrating the batch processing with PreparedStatement object?
- Write an example JDBC program demonstrating the batch processing with CallableStatement object?
- What is batch processing in JDBC?
- How to process SQL statements with JDBC explain with an example?
- What is heat? Explain with an example.
- What is Symbiosis? Explain with an example.
- What is Inheritance in Java? Explain with an example
- What is CheckBoxTreeItem in JavaFX explain with an example?
- Can we call functions using Callable Statements? Explain with an example in JDBC?
- Explain SHARED, UPDATE and EXCLUSIVE locks with the help of an example
- What is shallow copy? Explain with an example in Java.
- What is deep copy? Explain with an example in Java.
- What is Image Array? Explain with an example in C++
