JDBC Delete

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

Deleting data in a table using JDBC

Here are the steps for deleting data from a table using JDBC:

• First, open a new database connection.
• Second, create a new PreparedStatement object that accepts an DELETE statement.
• Third, set the parameters for the statement by calling the set* methods of the PreparedStatement object.
• Call the executeUpdate() to execute the DELETE statement to delete data from a table.
• Finally, close the database connection.

The following program shows how to delete a product with id 1 from the products table:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        var id = 1;
        var sql = "DELETE FROM products WHERE id = ?";

        try (var connection = DBConnection.connect();
             var pstmt = connection.prepareStatement(sql)) {

            pstmt.setInt(1, id);

            int rowsDeleted  = pstmt.executeUpdate();

            var message = rowsDeleted > 0
                    ? "Row deleted successfully."
                    : "Failed to delete row.";

            System.out.println(message);

        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

How it works.

First, declare and initialize a variable for holding the product id you want to delete:

var id = 1;Code language: SQL (Structured Query Language) (sql)

Second, construct an DELETE statement:

var sql = "DELETE FROM products WHERE id = ?";Code language: SQL (Structured Query Language) (sql)

In this syntax, the question mark (?) is a placeholder for the id. When executing the DELETE statement, you need to provide the id value.

Third, open a new connection to the database:

var connection = DBConnection.connect()Code language: SQL (Structured Query Language) (sql)

Fourth, create a PreparedStatement object with the DELETE statement:

var pstmt = connection.prepareStatement(sql)Code language: SQL (Structured Query Language) (sql)

Fifth, set the value for id by calling the setInt() method:

pstmt.setInt(1, id);Code language: SQL (Structured Query Language) (sql)

Sixth, execute the DELETE statement and assign the number of deleted rows to the rowsDeleted variable:

int rowsDeleted  = pstmt.executeUpdate();Code language: SQL (Structured Query Language) (sql)

Seventh, set a message based on the number of deleted rows and display it:

var message = rowsUpdated > 0
                    ? "Row deleted successfully."
                    : "Failed to delete row.";Code language: SQL (Structured Query Language) (sql)

Finally, show the error message if an exception occurs in the catch block:

System.err.println(e.getMessage());Code language: SQL (Structured Query Language) (sql)

If you execute the program and no exception occurs, you’ll see the following message:

Row deleted successfully.Code language: SQL (Structured Query Language) (sql)

Verify the deletion

First, connect to the MariaDB server using the bob account:

mysql -u bob -pCode language: plaintext (plaintext)

It’ll prompt you for a password for the bob user account. Please enter a valid password and press the Enter key to connect.

Second, change the current database to sales:

use sales;Code language: SQL (Structured Query Language) (sql)

Third, retrieve the product with id 1 from the products table:

select * from products 
where id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

Empty set (0.01 sec)Code language: plaintext (plaintext)

It returns an empty set, meaning the product with id 1 has been deleted successfully.

Finally, quit the mysql program:

quitCode language: SQL (Structured Query Language) (sql)

Summary

• Use the PreparedStatement to execute an DELETE statement to update data in a table.