JDBC to PostgreSQL - How to Delete a Record in PostgreSQL using the Java JDBC driver

Introduction

When you connect to a PostgreSQL database from a Java application, you can perform a wide variety of database operations. One common task that you can handle in a Java application is deleting a record from a table. With the help of the PostgreSQL JDBC driver, it’s easy to connect to your database and delete whatever records you desire. In this article, we’ll show you how to connect JDBC to PostgreSQL and delete a record from a database table.

Prerequisites

Before we begin to discuss our Java code, let’s make sure a few key prerequisites are in place:

  • First, ensure that PostgreSQL server is properly installed and verify that the service is running in the background. If you’re a Linux and Windows user, you can download PostgreSQL here.

  • Ensure that Eclipse IDE is installed and configured on your system. Linux and Windows users can download Eclipse IDE here.

  • It’s best to have some basic Java syntax knowledge in order to follow along with the examples presented in this tutorial.

Creating a Sample Database

In this section, we’ll create the sample database that will be used throughout this article.

First, we’ll login to our PostgreSQL server and create a database named basketball using the following command:

CREATE DATABASE basketball;

After we create the database, we can connect to it using the PostgreSQL meta-command \c followed by the database name: \c basketball;.

Next, we’ll create a table named tblplayer using the following command:

CREATE TABLE tblplayer (
id SERIAL PRIMARY KEY,
firstname TEXT NOT NULL,
lastname TEXT NOT NULL,
team TEXT NOT NULL,
jersey INT
);

Then we’ll insert some records:

INSERT INTO tblplayer(firstname,lastname,team,jersey) VALUES
('michael', 'jordan', 'bulls', 23),
('scottie','pippen','bulls', 33),
('karl','malone','jazz', 32),
('john','stockton','jazz',12);

The output from our INSERT operation will look like this:

basketball=# SELECT * FROM tblplayer;
id | firstname | lastname | team | jersey
----+-----------+----------+-------+--------
1 | michael | jordan | bulls | 23
2 | scottie | pippen | bulls | 33
3 | karl | malone | jazz | 32
4 | john | stockton | jazz | 12
(4 ROWS)

Deleting a Record in PostgreSQL using the PostgreSQL JDBC Driver

Now that we finished creating our sample database, we’ll proceed with deleting record(s) within our tblplayer table.

Connecting to PostgreSQL server via a Java Application

To perform a DELETE operation against the target table, we first need to connect to the database. To do this, we’ll need to create a function that can access the database and its record(s).

We need the following details to execute a successful connection:

  • a connection string
  • the name of the user
  • the password of the user
// connection string
private final String conectionUrl = "jdbc:postgresql://localhost/basketball";

// the name of the user
private final String user_name = "postgres";

// the password of the user
private final String pwd = "1234";

NOTE: The above connection details will need to be changed based on your PostgreSQL setup.

The code shown below creates a dbcon() function that will use the aforementioned details and connection methods:

public Connection dbcon() {
Connection dbcon = null;

try {
dbcon = DriverManager.getConnection(conUrl, username, password);
System.out.println("You are now connected to the server");
}catch(SQLException e){
System.err.println(e.getMessage());
}

return dbcon;
}

The code shown above uses the getConnection() method of the DriverManager class that uses those string values that we created earlier.

You can try running the program by pressing CTRL + F11 on your keyboard.

public static void main(String[] args) {

JdbcDemo app = new JdbcDemo();
app.dbcon();
}

You should see something like this in your console:

image shows the result of the java application

Java DELETE Operation to PostgreSQL Database using JDBC Driver

Let’s look at some code that will delete a player based on the value of the id specified in the main() function:

public int deletePlayer(int id) {
String SQLdelete = "DELETE FROM tblplayer WHERE id = ?";

int rowsAffected = 0;

try (Connection conn = dbcon();
PreparedStatement prepareStatement = conn.prepareStatement(SQLdelete)){
prepareStatement.setInt(1, id);
rowsAffected = prepareStatement.executeUpdate();
} catch (SQLException ex) {
System.err.println(ex.getMessage());
}
return rowsAffected;
}

There’s a lot going on in this code, so let’s look at it one section at a time:

  • First, we create a SQL statement SQLdelete that will delete a specific record based on the value of its id.

  • Then, we establish our connection using the dbconnect() function after creating the PreparedStatement entity.

  • After that, we use the prepareStatement object to pass the value of the id that we want to delete.

  • We then execute the DELETE statement that we created by calling the executeUpdata() method.

  • Finally, we close the Connection and PreparedStatement objects using the try-catch block.

We can test our application again by modifying our main() function with the following code:

JdbcDemo app = new JdbcDemo();
// the below code will delete 1 record within the table tblplayer
// having an id of 3
app.deletePlayer(3);

After we test the application, we can run a SELECT in our PostgreSQL shell to see if we successfully deleted the target record.

basketball=# SELECT * FROM tblplayer;
id | firstname | lastname | team | jersey
----+-----------+----------+-------+--------
1 | michael | jordan | bulls | 23
2 | scottie | pippen | bulls | 33
4 | john | stockton | jazz | 12
(3 ROWS)

We can see that the record with an id of “3” was deleted.

NOTE: To delete all records within the tblplayer table, just remove the WHERE clause in the SQLdelete statement.

Conclusion

If you’re writing Java code that interacts with PostgreSQL, you may find that you need to perform DELETE operations from your applications. Fortunately, it’s easy to handle this task with the help of the PostgreSQL JDBC driver. In this article, we looked at a simple Java application that connects JDBC to PostgreSQL and then performs a DELETE operation. You can use this sample application as a guide to write your own Java code that deletes records from a PostgreSQL table.

The Code

Shown below is the complete Java application we looked at throughout this tutorial:

public static void main(String[] args) {

JdbcDemo app = new JdbcDemo();
app.deletePlayer(3);
}

public int deletePlayer(int id) {
String SQLdelete = "DELETE FROM tblplayer WHERE id = ?";

int rowsAffected = 0;

try (Connection conn = dbcon();
PreparedStatement prepareStatement = conn.prepareStatement(SQLdelete)){
prepareStatement.setInt(1, id);
rowsAffected = prepareStatement.executeUpdate();
} catch (SQLException ex) {
System.err.println(ex.getMessage());
}
return rowsAffected;
}
}

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.