PostgreSQL Java Tutorial: Delete Data From A PostgreSQL Table

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

To delete data from a Java program, you follow these steps:

1. Establish a database connection.
2. Create a PreparedStatement object.
3. Execute a DELETE statement.
4. Close the database connection.

Check it out how to connect to the PostgreSQL database to learn more about how to establish a database connection.

We will use the actor table in the sample database for the demonstration. The following program demonstrates how to delete a row in the actor table.

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

    /**
     * Delete actors by id
     *
     * @param id
     * @return
     */
    public int deleteActor(int id) {
        String SQL = "DELETE FROM actor WHERE actor_id = ?";

        int affectedrows = 0;

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

            pstmt.setInt(1, 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.deleteActor(214);

    }
}

How it works.

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

In the deleteActor() method:

• First, prepare a DELETE statement that removes a row specified by its id from the actor table. We used the question mark (?) as the placeholder.
• Next, establish a the database connection and create a PreparedStatement object in the try-with-resources statement.
• Then, supply the id value in place of question mark placeholder (?) by calling the setInt() method.
• After that, execute the UPDATE statement by calling the executeUpdate() method.
• Finally, return the number of rows affected.

In the main method, we call the deleteActor() to delete actor with id 214.

Before running the program, let’s check the actor table to verify that the actor with id 214 exists.

SELECT
	actor_id,
	first_name,
	last_name
FROM
	actor
WHERE
	actor_id = 214;

Now, we run the delete program.

If we check the actor table again, the row with id 214 does not exist. It means that we have successfully deleted the actor with id 214 in the actor table.

In this tutorial, we have shown you step by step how to delete data from a table using JDBC.