Steps for deleting data from a table in JDBC

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

• Establish a database connection to PostgreSQL.
• Create aPreparedStatement object.
• Execute aDELETE statement.
• Close the database connection.

Deleting data example

The following example adds a delete() function to theProductDB class to delete a product by id:

import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;public class ProductDB {    public static int delete(int id){        var sql  = "DELETE FROM products WHERE id=?";        try (varconn  = DB.connect();             var pstmt = conn.prepareStatement(sql)) {            pstmt.setInt(1, id);            return pstmt.executeUpdate();        } catch (SQLException e) {            e.printStackTrace();        }        return 0;    }    // ...}

How it works.

First, construct a DELETE statement to delete a product from the products table specified by id:

var sql= "DELETE FROM products WHERE id=?";

Second, open a database connection and create a PreparedStatement object:

try (var conn= DB.connect();     var pstmt= conn.prepareStatement(sql)) {// ...

The try-with-resources statement will automatically close the statement and connection.

Third, bind the id to the statement:

pstmt.setInt(1, id);

Fourth, execute the prepared statement and return the number of deleted rows:

return pstmt.executeUpdate();

The following shows how to use the delete() method of theProductDB class to delete a product with the id 1 from the database table:

public class Main {    public static void main(String[] args) {        int deletedRows= ProductDB.delete(1);        System.out.println("Deleted Rows: " + deletedRows);    }}

Output:

Deleted Rows: 1

Verify the deletion

First, open the Command Prompt on Windows or Terminal on Linux and connect to the PostgreSQL server:

psql -U postgres -d sales

Second, retrieve the product with id 1 to verify the deletion:

SELECT * FROM productsWHERE id = 1;

Output:

id | name | price----+------+-------(0 rows)

The result set is empty, meaning that the program deleted the product with id 1 successfully.

Summary

• Use thePreparedStatement to delete data from a table using JDBC.

Last updated onFebruary 2, 2024

On this page

• Steps for deleting data from a table in JDBC
• Deleting data example
• Verify the deletion
• Summary