PostgreSQL Java Tutorial: Update Data in PostgreSQL Database

July 31, 2023

Summary: in this tutorial, you will learn how to update data in a PostgreSQL database using JDBC API.

To update data in a table in a PostgreSQL database, you use these steps:

  1. Create a database connection.
  2. Create a PreparedStatement object.
  3. Execute the UPDATE statement by calling the executeUpdate() method of the PreparedStatement object.
  4. Close the database connection.

Creating a database connection

To create a PostgreSQL database connection from a Java program, you need to have the PostgreSQL JDBC driver. Check it out how to connect to a PostgreSQL database for the detailed information.

The following connect() method establishes a connection to the dvdrental sample database and returns a Connection object.

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

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

The following updateLastName() method updates the last name of an actor in the actor table.

    /**
     * Update actor's last name based on actor's id
     *
     * @param id
     * @param lastName
     * @return the number of affected rows
     */
    public int updateLastName(int id, String lastName) {
        String SQL = "UPDATE actor "
                + "SET last_name = ? "
                + "WHERE actor_id = ?";

        int affectedrows = 0;

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

            pstmt.setString(1, lastName);
            pstmt.setInt(2, id);

            affectedrows = pstmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return affectedrows;
    }

How it works.

  • First, prepare an UPDATE statement to update the last name of an actor in the actor table.
  • Next, establish a database connection and create a PreparedStatement object.
  • Then, pass the new last name of the actor and the id of the actor that is being updated by calling the setString() and setInt() methods of the PreparedStatement object.
  • After that, call the executeUpdate() method to execute the UPDATE statement. The method returns the number of row affected.
  • Finally, because we use the try-with-resources statement, therefore the PreparedStatement and Connection objects are automatically closed.

The following is the complete update program.

package net.rockdata.tutorial;

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

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

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

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

    /**
     * Update actor's last name based on actor's id
     *
     * @param id
     * @param lastName
     * @return the number of affected rows
     */
    public int updateLastName(int id, String lastName) {
        String SQL = "UPDATE actor "
                + "SET last_name = ? "
                + "WHERE actor_id = ?";

        int affectedrows = 0;

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

            pstmt.setString(1, lastName);
            pstmt.setInt(2, id);

            affectedrows = pstmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return affectedrows;
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        Main main = new Main();
        main.updateLastName(200, "Climo");

    }
}

Before running the program, we check the actor with id 200.

SELECT
	*
FROM
	actor
WHERE
	actor_id = 200;

PostgreSQL JDBC Update Example

Now, we run the Java program to update he last name of Thora from Temple to Climo.

postgresql jdbc update

And we check the actor with id again.

PostgreSQL JDBC Update data

As you see the values in the last_name column changed to Climo as we expected.

Because the last_update column is automatically updated to the time when the row changes so that its value changes as well.

In this tutorial, we have shown you step by step how to update data in a table using JDBC API.

Search

Categories

Tags