Java JDBC PostgreSQL Delete Example

This tutorial will show you how to delete data from a PostgreSQL database using Java and the JDBC API. We'll walk through each step required to remove records from a PostgreSQL table.

What You’ll Learn:

• How to connect to a PostgreSQL database.
• How to delete a record from a PostgreSQL table using a SQL DELETE statement.
• How to handle SQL exceptions in Java.

Technologies Used:

In this tutorial, we will use the following technologies:

• JDK: Version 21 or later
• PostgreSQL JDBC Driver: Version 42.7.4
• IDE: Eclipse, IntelliJ IDEA, or any preferred IDE
• JDBC: Version 4.2

Step 1: Download PostgreSQL JDBC Driver

You need the PostgreSQL JDBC driver to connect your Java program to a PostgreSQL database. You can add it manually or use Maven or Gradle.

For Maven Users:

Add the following dependency to your pom.xml file:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.4</version>
</dependency>

For Gradle Users:

Add the following line to your build.gradle file:

implementation 'org.postgresql:postgresql:42.7.4'

This will automatically download and add the PostgreSQL JDBC driver to your project.

Step 2: PostgreSQL Database Setup

Before proceeding with the deletion operation, ensure that the users table exists and contains some records. The following is an example of the users table:

CREATE TABLE users (
    ID INT PRIMARY KEY,
    NAME TEXT,
    EMAIL VARCHAR(50),
    COUNTRY VARCHAR(50),
    PASSWORD VARCHAR(50)
);

This tutorial will use this table to demonstrate how to delete records using JDBC.

Step 3: Writing Java Code to Delete a Record

To delete a record from the PostgreSQL database using JDBC, you can follow these steps:

1. Establish a database connection.
2. Create a PreparedStatement object to execute the DELETE statement.
3. Execute the SQL DELETE query.
4. Handle exceptions and close the connection.

Java Program: Deleting a Record

package com.example.postgresql;

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

public class DeleteRecordExample {

    private static final String DELETE_USERS_SQL = "DELETE FROM users WHERE id = ?;";

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

    public static void main(String[] args) {
        DeleteRecordExample deleteExample = new DeleteRecordExample();
        try {
            deleteExample.deleteRecord(1); // Delete user with ID = 1
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    // Method to delete a record by ID
    public void deleteRecord(int userId) throws SQLException {
        System.out.println(DELETE_USERS_SQL);

        // Step 1: Establishing a connection
        try (Connection connection = DriverManager.getConnection(url, user, password);

             // Step 2: Create a statement using connection object
             PreparedStatement preparedStatement = connection.prepareStatement(DELETE_USERS_SQL)) {

            preparedStatement.setInt(1, userId);
            System.out.println(preparedStatement);

            // Step 3: Execute the DELETE query
            int result = preparedStatement.executeUpdate();
            System.out.println("Number of records deleted: " + result);

        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    // Method to print SQL exceptions
    public static void printSQLException(SQLException ex) {
        for (Throwable e : ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

Explanation:

• DELETE_USERS_SQL: This SQL query deletes a user based on the id column.
• PreparedStatement: We use PreparedStatement to safely delete a record using a parameterized query.
• executeUpdate(): This method executes the SQL query and returns the number of rows affected (deleted).
• deleteRecord(int userId): This method takes a userId as input and deletes the corresponding record.

Step 4: Running the Program

To run the program:

1. Compile and run the DeleteRecordExample class.
2. The program will attempt to delete the user with ID = 1.

Sample Output:

DELETE FROM users WHERE id = ?;
DELETE FROM users WHERE id = 1;
Number of records deleted: 1

If the deletion is successful, the program will output the number of records deleted.

Conclusion

In this tutorial, we demonstrated how to delete a record from a PostgreSQL database using JDBC in Java. The steps included connecting to the database, preparing a DELETE query, executing it, and handling SQL exceptions. Deleting records is a fundamental operation when interacting with databases.

Key Takeaways:

• Use PreparedStatement for secure deletion of records based on user input.
• Handle SQL exceptions carefully to avoid database issues.
• Always close your database connections to avoid resource leaks.

This knowledge is essential when working with databases in Java applications. You can now confidently delete records from a PostgreSQL database using JDBC.