How to Perform a Bulk Insertion using JDBC? - Coding

Performing a bulk insert using JDBC involves using the PreparedStatement interface and addBatch() method. This addBatch() method increases the performance while data is inserted into a database.

In this article, we will learn how to perform a bulk insert using JDBC.

Approach:

For this, we need to create a connection with the database by using the Connection class with the required configuration properties.
Then we need to write an insertion SQL Query.
After that create the PreparedStatement method by using the Connection class in Java for this we use java.sql package.
Then take some data in the form of an array of objects.
Set this object data to PreparedStatement by using for loop.
Now, add this PreparedStatement data into the addBatch() method.
Then insert this entire data by using the executeBatch() method which is available in PreparedStatement.

Step-by-step implementation to perform bulk insert using JDBC

Create One Java class.
Now create a connection with the database by using mysql-connector.jar
Then create Insert SQL queries based on the table structure.
Now create one PreparedStatement by using the Connection class.
Then take the required data to insert into the table.
Then add the addBatch() method to PreparedStatement.
Finally, call the executeBatch() to insert data into the table.

Note: We have already created one database in MySQL workbench. The Database name is books. In this Database we have created one table named book.

Below we can refer the description of book table for reference.

Book Table Structure

Here, the book table have four columns i.e. id, author (name of author), name (name of the book), price(book price).

Bulk Insert using JDBC

For performing bulk insertion by using JDBC we need to follow steps we have already mentioned in the above. Now we will take sample data based on the table Structure then will insert those data values by using PreparedStatement with addBatch() method.

Program to Perform a Bulk Insertion using JDBC

Below is the Program to Perform a Bulk Insertion using JDBC:

Java

// Java Program to Perform a Bulk Insertion using JDBC 
package horje; 
  
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.SQLException; 
  
// Driver Class 
public class BulkInsertExample { 
    // Main Function 
    public static void main(String[] args) { 
        try { 
            // Load the MySQL JDBC driver 
            Class.forName("com.mysql.cj.jdbc.Driver"); 
              
            // Establish a connection to the database 
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "password"); 
  
            if (con != null) { 
                // Define the SQL query for bulk insertion 
                String insertQuery = "INSERT INTO book (id, author, name, price) VALUES (?,?, ?, ?)"; 
                PreparedStatement preparedStatement = con.prepareStatement(insertQuery); 
  
                // Sample data for bulk insert 
                Object[][] data = { 
                        {100,"Author1", "Book1", "19.99"}, 
                        {101,"Author2", "Book2", "29.99"}, 
                        {102,"Author3", "Book3", "39.99"} 
                        // Add more rows as needed 
                }; 
  
                // Adding batches of parameters 
                for (Object[] row : data) { 
                    preparedStatement.setInt(1,  (int) row[0]); // id 
                    preparedStatement.setString(2, (String) row[1]); // author 
                    preparedStatement.setString(3, (String) row[2]); // name 
                    preparedStatement.setString(4, (String) row[3]); // price 
                    preparedStatement.addBatch(); 
                } 
  
                // Execute batches 
                int[] batchResults = preparedStatement.executeBatch(); 
  
                // Handle the results if needed 
                for (int result : batchResults) { 
                    // Process results 
                } 
  
                System.out.println("Bulk insert completed successfully."); 
            } else { 
                System.out.println("Not Connected..."); 
            } 
  
        } catch (ClassNotFoundException | SQLException e) { 
            // Print any exceptions that occur during database access 
            System.out.println("Exception is " + e.getMessage()); 
        } 
    } 
} 

Output:

Before inserting data into the table, there is no data available in the table as we can see in the below image.

Empty table

After inserting data into the table, there is some data available in the table :

After inserting data into the table

Here, Bulk insert completed successfully.

Explanation of the above Program:

First, we have created Connection with database by using required configuration properties.
Once connection is successful then write SQL Query for Insert operation.
Then Create preparedStatement by using Connection class object by calling prepareStatement().
This method takes SQL query as an input.
Then we have taken some data based on the table requirement.
Now call the executeBatch() by using preparedStatement object.
While inserting data, if you get any error, that error is handled by using ClassNotFoundException in Java.
If successfully data inserted, then it will print one success message as an output.

Reffered: https://www.geeksforgeeks.org

Java

Related
How to Handle SQL Injection in JDBC using PreparedStatement?
How to Check if a String Contains Only Lowercase Letters in Java?
How to Convert a LinkedHashSet into an Array and List in Java?
How to Convert an ArrayList of Objects to a JSON Array in Java?
How to Read and Write Binary Files in Java?

Type:	Geek
Category:	Coding
Sub Category:	Tutorial
Uploaded by:	Admin
Views:	13