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
PreparedStatementobject. - 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | package com.postgresqltutorial; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * * @author postgresqltutorial.com */ 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
DELETEstatement that removes a row specified by its id from theactortable. We used the question mark (?) as the placeholder. - Next, establish a the database connection and create a
PreparedStatementobject 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
UPDATEstatement 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.
1 2 3 4 5 6 7 8 | 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.