Java - JDBC PostgreSQL Batch Insert Example

❮ Previous Chapter Next Chapter ❯

---

In this tutorial, we will discuss the JDBC Batch insert example in the PostgreSQL database. Sometimes we need to run bulk queries of a similar kind for a database, for example, loading data from CSV files to relational database tables.

In this example, we will use the PreparedStatement interface to execute batch inserts statements.

Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

Technologies used

We use below technologies in this tutorial:

• JDK - 1.8 or later
• PostgreSQL- 42.2.9
• IDE - Eclipse Neon
• JDBC - 4.2

Download PostgreSQL JDBC Driver

To connect to the PostgreSQL database server from a Java program, you need to have a PostgreSQL JDBC driver. You can download the latest version of the driver on the postgresql.org website via the download page.

Add the PostgreSQL JDBC driver jar file to the project classpath.

For maven users:

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.9</version>
</dependency>

For Gradle users:

// https://mvnrepository.com/artifact/org.postgresql/postgresql
compile group: 'org.postgresql', name: 'postgresql', version: '42.2.9'

PostgreSQL Database Setup

In the previous tutorial, we have seen how to create a "users" table in a PostgreSQL database using Java. We are using the same "users" table in this tutorial to insert rows.

Java - JDBC PostgreSQL Batch Insert Example

Here is the complete Java program to batch insert into the "users" table in the PostgreSQL database:

package net.javaguides.postgresql.tutorial;

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

/**
 * Insert Batch operation using PreparedStatement Interface
 * @author Ramesh Fadatare
 *
 */
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();
    }

    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();
                }
            }
        }
    }

    private static void parameterizedBatchUpdate() {

        String INSERT_USERS_SQL = "INSERT INTO users" + "  (id, name, email, country, password) VALUES " +
            " (?, ?, ?, ?, ?);";

        try (Connection connection = DriverManager.getConnection(url, user, password);
            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
            connection.setAutoCommit(false);

            preparedStatement.setInt(1, 20);
            preparedStatement.setString(2, "a");
            preparedStatement.setString(3, "[email protected]");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 21);
            preparedStatement.setString(2, "b");
            preparedStatement.setString(3, "[email protected]");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 22);
            preparedStatement.setString(2, "c");
            preparedStatement.setString(3, "[email protected]");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 23);
            preparedStatement.setString(2, "d");
            preparedStatement.setString(3, "[email protected]");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            int[] updateCounts = preparedStatement.executeBatch();
            System.out.println(Arrays.toString(updateCounts));
            connection.commit();
            connection.setAutoCommit(true);
        } catch (BatchUpdateException batchUpdateException) {
            printBatchUpdateException(batchUpdateException);
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    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 i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
    }
}

Output:

[1, 1, 1, 1]

In this tutorial, we have discussed the JDBC Batch insert example in the PostgreSQL database.

Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

---

❮ Previous Chapter Next Chapter ❯