Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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
How to generate multiple insert queries via java?
JDBC provides a mechanism known as batch processing, in which you can group a set of INSERT or, UPDATE or, DELETE commands (those produce update count value) and execute them at once. You can insert multiple records in to a table using this.
Adding statements to the batch
Statement, PreparedStatement and CallableStatement objects hold a list (of commands) to which you can add related statements (those return update count value) using the addBatch() method.
stmt.addBatch(insert1); stmt.addBatch(insert2); stmt.addBatch(insert3);
Executing the batch
After adding the required statements, you can execute a batch using the executeBatch() method of the Statement interface.
stmt.executeBatch();
Using batch updates, we can reduce the communication overhead and increase the performance of our Java application.
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.
Let us create a table with name sales in MySQL database using CREATE statement as shown below −
CREATE TABLE sales( Product_Name varchar(255), Name_Of_Customer varchar(255), Month_Of_Dispatch varchar(255), Price int, Location varchar(255) );
Following JDBC program tries to insert a set of statements into the above mentioned table using batch update.
Example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class BatchUpdates {
public static void main(String args[])throws Exception {
//Getting the connection
String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
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);
//Statements to insert records
String insert1 = "INSERT INTO Dispatches VALUES ('KeyBoard', 'Amith', 'January', 1000, 'Hyderabad')";
String insert2 = "INSERT INTO Dispatches VALUES ('Earphones', 'SUMITH', 'March', 500, 'Vishakhapatnam')";
String insert3 = "INSERT INTO Dispatches VALUES ('Mouse', 'Sudha', 'September', 200, 'Vijayawada')";
//Adding the statements to batch
stmt.addBatch(insert1);
stmt.addBatch(insert2);
stmt.addBatch(insert3);
//Executing the batch
stmt.executeBatch();
//Saving the changes
con.commit();
System.out.println("Records inserted......");
}
}Output
Connection established...... Records inserted......
If you verify the contents of the table, you can find the inserted records in it as −
+--------------+------------------+-------------------+-------+----------------+ | Product_Name | Name_Of_Customer | Month_Of_Dispatch | Price | Location | +--------------+------------------+-------------------+-------+----------------+ | KeyBoard | Amith | January | 1000 | Hyderabad | | Earphones | SUMITH | March | 500 | Vishakhapatnam | | Mouse | Sudha | September | 200 | Vijayawada | +--------------+------------------+-------------------+-------+----------------+
3K+ Views
