Java JDBC PostgreSQL Batch Insert Example

Author:

In this tutorial, you will learn how to perform batch inserts into a PostgreSQL database using Java and the JDBC API. Batch inserts allow you to insert multiple rows efficiently in a single database transaction, which can significantly improve performance when dealing with large datasets.

What You’ll Learn:

  • How to use PreparedStatement for batch inserts.
  • How to handle batch updates and manage transactions.
  • How to optimize database operations by disabling auto-commit mode.
  • How to handle BatchUpdateException and other SQL exceptions.

Technologies Used:

In this tutorial, we will use the following technologies:

  • JDK: Version 21 or later
  • PostgreSQL JDBC Driver: Version 42.7.4
  • IDE: Eclipse, IntelliJ IDEA, or any preferred IDE
  • JDBC: Version 4.2

Step 1: Download PostgreSQL JDBC Driver

To connect your Java program to the PostgreSQL database, you need the PostgreSQL JDBC driver. You can add it manually or use Maven or Gradle to include it in your project.

For Maven Users:

Add the following dependency to your pom.xml file:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.4</version>
</dependency>

For Gradle Users:

Add the following line to your build.gradle file:

implementation 'org.postgresql:postgresql:42.7.4'

Step 2: PostgreSQL Database Setup

Ensure that you have a PostgreSQL database with a table for batch inserts. For this example, we will use the users table, which has the following structure:

CREATE TABLE users (
    ID INT PRIMARY KEY,
    NAME TEXT,
    EMAIL VARCHAR(50),
    COUNTRY VARCHAR(50),
    PASSWORD VARCHAR(50)
);

Make sure this table is set up before running the Java code for batch inserts.

Step 3: Batch Insert with JDBC

Batch processing in JDBC allows you to execute multiple SQL statements in a batch, reducing the number of database calls and improving performance. Here’s how you can insert multiple rows into a PostgreSQL database in a single transaction using PreparedStatement.

Steps for Batch Insert:

  1. Establish a database connection.
  2. Disable auto-commit mode.
  3. Prepare a PreparedStatement object and add multiple inserts to the batch.
  4. Execute the batch insert.
  5. Commit the transaction and handle any exceptions.

Java Program: Batch Insert Example

package com.example.postgresql;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

public class BatchInsertExample {

    private final static String url = "jdbc:postgresql://localhost/mydb";
    private final static String user = "postgres";
    private final static String password = "root";

    public static void main(String[] args) {
        parameterizedBatchUpdate();
    }

    // Method to handle the batch insert operation
    private static void parameterizedBatchUpdate() {

        // SQL statement for inserting data into the users table
        String INSERT_USERS_SQL = "INSERT INTO users (id, name, email, country, password) VALUES (?, ?, ?, ?, ?);";

        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {

            // Disable auto-commit to manage the transaction manually
            connection.setAutoCommit(false);

            // Adding multiple insert statements to the batch
            preparedStatement.setInt(1, 20);
            preparedStatement.setString(2, "a");
            preparedStatement.setString(3, "a@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 21);
            preparedStatement.setString(2, "b");
            preparedStatement.setString(3, "b@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 22);
            preparedStatement.setString(2, "c");
            preparedStatement.setString(3, "c@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 23);
            preparedStatement.setString(2, "d");
            preparedStatement.setString(3, "d@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            // Execute the batch of insert statements
            int[] updateCounts = preparedStatement.executeBatch();
            System.out.println("Batch insert results: " + Arrays.toString(updateCounts));

            // Commit the transaction
            connection.commit();
            connection.setAutoCommit(true);

        } catch (BatchUpdateException batchUpdateException) {
            printBatchUpdateException(batchUpdateException);
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    // Method to print SQL exceptions
    public static void printSQLException(SQLException ex) {
        for (Throwable e : ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }

    // Method to handle batch update exceptions
    public static void printBatchUpdateException(BatchUpdateException b) {
        System.err.println("----BatchUpdateException----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int updateCount : updateCounts) {
            System.err.print(updateCount + "   ");
        }
        System.err.println();
    }
}

Explanation:

  • PreparedStatement.addBatch(): This method adds the SQL statement to the batch of commands to be executed later.
  • executeBatch(): This method executes all the SQL statements added to the batch in a single transaction.
  • setAutoCommit(false): This disables auto-commit mode, allowing us to commit all statements together manually.
  • Handling BatchUpdateException: This specialized exception handles errors that occur during batch execution.

Step 4: Running the Program

To run the program:

  1. Compile and run the BatchInsertExample class.
  2. The program will insert multiple rows into the users table in a single transaction.

Sample Output:

Batch insert results: [1, 1, 1, 1]

This output shows that four rows were successfully inserted into the users table.

Conclusion

In this tutorial, you learned how to perform batch inserts into a PostgreSQL database using Java JDBC. Batch processing allows you to execute multiple SQL statements in one go, significantly improving performance when dealing with large amounts of data.

Key Takeaways:

  • Use batch processing to optimize database inserts and reduce the number of database calls.
  • Disable auto-commit for batch operations to manage transactions more efficiently.
  • Handle BatchUpdateException to ensure that errors in batch processing are handled correctly.

By following this guide, you can now confidently insert multiple rows into PostgreSQL using Java’s JDBC API in an optimized and efficient manner.

Comments

Post a Comment

Leave Comment