How to Perform a Bulk Insertion using JDBC?

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.

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 Program to Perform a Bulk Insertion using JDBC
package geeksforgeeks;

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.

After inserting data into the table, there is some data available in 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.