Java - JDBC PostgreSQL Insert Example


In the previous tutorial, we have seen how to create a table in a PostgreSQL database using Java. In this tutorial, you will learn how to insert data into a table in the PostgreSQL database using the JDBC API.
In this example, we will see how to insert a single row as well as multiple rows into the PostgreSQL database.
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.

Inserting one row into a table

To insert a row into a table, you follow these steps:
  • Establish a database connection to get a Connection object.
  • Create a Statement object from the Connection object.
  • Execute the INSERT statement.
  • Close the database connection.
Here is the complete Java program to insert a single row into the "users" table in the PostgreSQL database:
package net.javaguides.postgresql.tutorial;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Insert PrepareStatement JDBC Example
 * 
 * @author Ramesh Fadatare
 *
 */
public class InsertRecordExample {
    private final String url = "jdbc:postgresql://localhost/myDB";
    private final String user = "postgres";
    private final String password = "root";

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

    public static void main(String[] argv) throws SQLException {
        InsertRecordExample createTableExample = new InsertRecordExample();
        createTableExample.insertRecord();
    }

    public void insertRecord() throws SQLException {
        System.out.println(INSERT_USERS_SQL);
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager.getConnection(url, user, password);

            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, "Tony");
            preparedStatement.setString(3, "[email protected]");
            preparedStatement.setString(4, "US");
            preparedStatement.setString(5, "secret");

            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            preparedStatement.executeUpdate();
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

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

Inserting multiple rows into a table

The steps of inserting multiple rows into a table are as follows:
  • Create a database connection.
  • Create a PreparedStatement object.
  • Call the addBatch() method of the PreparedStatement object.
  • Call the executeBatch() method to submit a batch of the INSERT statements to the PostgreSQL database server for execution.
  • Close the database connection.
Here is the complete Java program to insert multiple rows into the "users" table in the PostgreSQL database:
package net.javaguides.postgresql.tutorial;

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

public class InsertMultipleRecordsExample {

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

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

    /**
     * insert multiple users
     */
    public void insertUsers(List < User > list) {
        try (
            Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement statement = conn.prepareStatement(INSERT_USERS_SQL);) {
            int count = 0;

            for (User user: list) {
                statement.setInt(1, user.getId());
                statement.setString(2, user.getName());
                statement.setString(3, user.getEmail());
                statement.setString(4, user.getCountry());
                statement.setString(5, user.getPassword());

                statement.addBatch();
                count++;
                // execute every 100 rows or less
                if (count % 100 == 0 || count == list.size()) {
                    statement.executeBatch();
                }
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public static void main(String[] args) {
        InsertMultipleRecordsExample example = new InsertMultipleRecordsExample();
        example.insertUsers(Arrays.asList(new User(2, "Ramesh", "[email protected]", "India", "password123"),
            new User(3, "John", "[email protected]", "US", "password123")));
    }
}
In this tutorial, we have shown you how to insert one or multiple rows into the PostgreSQL database using the JDBC API.
Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

Comments