PostgreSQL Java Tutorial: Insert Data Into a Table

July 31, 2023

Summary: in this tutorial, you will learn how to insert data into a table in the PostgreSQL database using JDBC API.

We will use the actor table in the sample database for the demonstration.

Inserting one row into a table

To insert a row into a table, you follow these steps:

  1. Establish a database connection to get a Connection object.
  2. Create a Statement object from the Connection object.
  3. Execute the INSERT statement.
  4. Close the database connection.

To connect to a PostgreSQL database server, you have to provide a connection string that specifies the location of the database server as well as the database name. In addition, you need to provide the username and password to authenticate with the database server.

The following connect() method creates a database connection and returns a Connection object.

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

    /**
     * Connect to the PostgreSQL database
     *
     * @return a Connection object
     */
    public Connection connect() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

Check it out how to connect a PostgreSQL database server for the detailed information.

When we insert a row into a table that has auto generated id, we often want to get the id value back for further processing.

To get the auto generated id, you have to:

  • Pass the Statement.RETURN_GENERATED_KEYS to the preparedStatement() object when you create the Statement object.
  • Call the getGeneratedKeys() method of the Statement object to get the id value.

The following insertActor() method inserts a row into the actor table.

public long insertActor(Actor actor) {
        String SQL = "INSERT INTO actor(first_name,last_name) "
                + "VALUES(?,?)";

        long id = 0;

        try (Connection conn = connect();
                PreparedStatement pstmt = conn.prepareStatement(SQL,
                Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, actor.getFirstName());
            pstmt.setString(2, actor.getLastName());

            int affectedRows = pstmt.executeUpdate();
            // check the affected rows 
            if (affectedRows > 0) {
                // get the ID back
                try (ResultSet rs = pstmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        id = rs.getLong(1);
                    }
                } catch (SQLException ex) {
                    System.out.println(ex.getMessage());
                }
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return id;
    }

The Actor class is as follows:

package net.rockdata.tutorial;

/**
 *
 * @author rockdata.net
 */
public class Actor {

    private String firstName;
    private String lastName;

    public Actor(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;

    }

    public Actor() {
    }

    /**
     * @return the firstName
     */
    public String getFirstName() {
        return firstName;
    }

    /**
     * @param firstName the firstName to set
     */
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    /**
     * @return the lastName
     */
    public String getLastName() {
        return lastName;
    }

    /**
     * @param lastName the lastName to set
     */
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
}

postgresql jdbc insert

Inserting multiple rows into a table

The steps of inserting multiple rows into a table is as follows:

  1. Create a database connection.
  2. Create a PreparedStatement object.
  3. Call the addBatch() method of the PreparedStatement object.
  4. Call the executeBatch() method to submit a batch of the INSERT statements to the PostgreSQL database server for execution.
  5. Close the database connection.

Because the length of an SQL statement that you send to PostgreSQL is limited, therefore, you should call the executeBatch() for a certain number of rows or less e.g., for every 100 rows.

The following insertActors() method inserts a list of actors into the actor table.

    /**
     * insert multiple actors
     */
    public void insertActors(List<Actor> list) {
        String SQL = "INSERT INTO actor(first_name,last_name) "
                + "VALUES(?,?)";
        try (
                Connection conn = connect();
                PreparedStatement statement = conn.prepareStatement(SQL);) {
            int count = 0;

            for (Actor actor : list) {
                statement.setString(1, actor.getFirstName());
                statement.setString(2, actor.getLastName());

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

postgresql jdbc insert batch

In this tutorial, you have learned how to insert one or multiple rows into the PostgreSQL database using the JDBC API.

Search

Categories

Tags