July 31, 2023
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:
- Establish a database connection.
- Create a
PreparedStatement
object. - Execute a DELETE statement.
- 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 theactor
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 theexecuteUpdate()
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.