Java JDBC PostgreSQL Batch Update Example

In this tutorial, we will show you how to perform batch update operations in a PostgreSQL database using Java JDBC. Batch updates allow you to update multiple rows in a single transaction, improving performance by reducing the number of database calls.

What You’ll Learn:

• How to use PreparedStatement for batch updates.
• How to manage transactions in batch operations.
• How to handle exceptions in batch processing.

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

Before proceeding with the batch update, make sure that you have a PostgreSQL database and a users table set up. Here is the basic structure of the users table that we will be using:

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

This table will be used for the batch update operation.

Step 3: Performing Batch Update with JDBC

Batch processing in JDBC allows you to execute multiple SQL statements as a batch, reducing the number of database calls and improving performance.

Steps for Batch Update:

1. Establish a connection to the PostgreSQL database.
2. Disable auto-commit mode to handle transactions manually.
3. Prepare a PreparedStatement object and add multiple update queries to the batch.
4. Execute the batch update.
5. Commit the transaction and handle exceptions.

Java Program: Batch Update Example

Here’s the complete Java program to update multiple rows in the users table using JDBC batch processing:

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 BatchUpdateExample {

    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 update operation
    private static void parameterizedBatchUpdate() {

        String UPDATE_USERS_SQL = "UPDATE users SET name = ? WHERE id = ?;";

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

            // Disable auto-commit mode to manage transactions manually
            connection.setAutoCommit(false);

            // Add multiple update queries to the batch
            preparedStatement.setString(1, "A");
            preparedStatement.setInt(2, 1);
            preparedStatement.addBatch();

            preparedStatement.setString(1, "B");
            preparedStatement.setInt(2, 2);
            preparedStatement.addBatch();

            preparedStatement.setString(1, "C");
            preparedStatement.setInt(2, 3);
            preparedStatement.addBatch();

            preparedStatement.setString(1, "D");
            preparedStatement.setInt(2, 4);
            preparedStatement.addBatch();

            // Execute the batch of update queries
            int[] updateCounts = preparedStatement.executeBatch();
            System.out.println("Batch update 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 update statement to the batch for execution later.
• executeBatch(): This method executes all the update statements added to the batch.
• setAutoCommit(false): Disables auto-commit mode so that all SQL statements in the batch are committed together.
• Handling BatchUpdateException: This handles errors that occur during batch updates, providing detailed information about which rows were affected.

Step 4: Running the Program

To run the program:

1. Compile and run the BatchUpdateExample class.
2. The program will update multiple rows in the users table in a single transaction.

Sample Output:

Batch update results: [1, 1, 1, 1]

This output shows that four rows were successfully updated in the users table.

Conclusion

In this tutorial, we demonstrated how to perform batch updates in a PostgreSQL database using Java JDBC. Batch processing allows you to execute multiple SQL update statements in one go, optimizing performance and reducing the number of database calls.

Key Takeaways:

• Use batch processing to execute multiple SQL statements efficiently.
• Always disable auto-commit for batch operations to manage transactions manually.
• Handle BatchUpdateException to track which statements in the batch succeeded or failed.

By following this guide, you can now confidently perform batch updates in a PostgreSQL database using Java's JDBC API.