Grouping a set of INSERT Statements and executing them at once is known as batch insert.
To execute a batch of insert statements using the PreparedStatement object −
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.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();
pstmt.executeBatch();
Using batch inserts, 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 Dispatches in the MySQL database using the CREATE statement as shown below −
CREATE table Dispatches ( Product_Name, varchar(255) Name_Of_Customer, varchar(255) Month_Of_Dispatch, varchar(255) Price, int(11) Location, varchar(255) );
Following JDBC program tries to execute a bunch of INSERT statements at once as a batch, using a Statement object.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Statement; public class BatchInserts_PreparedStatement { 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......"); } }
Connection established...... Records inserted......
If you verify the contents of the table, you can find the newly inserted record 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 | +--------------+------------------+-------------------+-------+----------------+