- Trending Categories
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
Batch Inserts Using JDBC Prepared Statements
Grouping a set of INSERT Statements and executing them at once is known as batch insert.
Batch inserts using PreparedStatement object
To execute a batch of insert statements using the PreparedStatement object −
- Create a PreparedStatement − Create a PreparedStatement object using the prepareStatement() method. Pass the Insert query with place holders “?” instead of values as a parameter to this method.
PreparedStatement pstmt = con.prepareStatement("INSERT INTO Sales VALUES (?, ?, ?, ?, ?)");
- Set the values to the place holders − Using the setXXX() methods (setInt(). SetString(), setFloat() etc…) set the values to the place holders in the PrepareStatement as −
pstmt.setString(1, "KeyBoard"); pstmt.setString(2, "Amith"); pstmt.setString(3, "January"); pstmt.setInt(4, 1000); pstmt.setString(5, "Hyderabad");
- Add statements to the batch − After setting the values of a record to the place holders add it to the batch using the addBatch() method of the PreparedStatement interface as −
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();
- Execute the batch − Finally execute the batch using the executeBatch() method of the PreparedStatement interface.
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.
Example
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.
Example
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......"); } }
Output
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 | +--------------+------------------+-------------------+-------+----------------+
- Related Articles
- Batch Inserts Using JDBC Statements
- Why are Prepared Statements in JDBC faster than Statements? Explain?
- What is batch processing in JDBC?
- What are batch updates in JDBC? Explain?
- How can we run MySQL statements in batch mode?
- How can we use prepared statements in MySQL?
- What is Parameterized Batch Update in JDBC? Explain with an example?
- 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?
- Using MySQL in Batch Mode
- Can we call functions using Callable Statements? Explain with an example in JDBC?
- How can we use prepared statements in a stored procedure?
- What are the types of statements in JDBC?
- What is the similarity between prepared statements and MySQL user variables?
