Delete Record in PostgreSQL using Prepared Statement in Java

Introduction

If you’re using Java to build applications, it’s important to know how to perform various database operations in your code. Fortunately, it’s easy to interact with a PostgreSQL database in Java with the help of a prepared statement. In this tutorial, we’ll show you how to delete a PostgreSQL record using a prepared statement in Java.

Prerequisites

Before we proceed with our code examples, let’s review some important prerequisites that are necessary for this tutorial:

  • PostgreSQL must be installed and properly configured on your machine.
  • The Eclipse application must also be properly installed and configured.
  • You’ll need to have a basic understanding of SQL.
  • You’ll need to include the JDBC driver in the Eclipse project. This can be done by adding the following code to the project’s pom.xml file:

     <dependencies>
        <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.9</version>
        </dependency>

    </dependencies>

What is a PostgreSQL Prepared Statement?

A PostgreSQL prepared statement, as the name implies, allows the user to prepare all the necessary queries in advance without executing them. These queries will be prepared and will be sent to the server to be parsed and compiled without executing them. Using a prepared statement can optimize performance and also help prevent SQL injection attacks.

Creating the PostgreSQL Database

We’ll need to create a sample database that we can work with throughout this tutorial, so let’s log in to our PostgreSQL shell and tackle that task:

psql -U postgres

We’ll use the following command to create our database deletedb:

postgres=# create database deletedb;
CREATE DATABASE

We can then connect to our deletedb database using the command \c <database name>:

postgres=# \c deletedb
You are now connected to database "deletedb" as user "postgres".

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

create table deleteusers
(
    user_id int not null,
    user_name varchar(100),
    user_email varchar(100)
);

Once our table has been created, we can insert sample records into it using the following command:

INSERT INTO deleteusers(user_id, user_name, user_email)
Values
(001,'Ronda Dewey','rdewey@example.com'),
(002,'Josh Scotch','jscotch@example.com');

Finally, we can verify that all of our database operations were successful using this command: SELECT * FROM updateusers;. The output should look like the following:

deletedb=# select * from deleteusers;
 user_id |  user_name  |     user_email
---------+-------------+---------------------
       1 | Ronda Dewey | rdewey@example.com
       2 | Josh Scotch | jscotch@example.com
(2 rows)

PostgreSQL Prepared Statement Example

Now that we’ve created a sample database and a table that contains some records, we can try updating a PostgreSQL record using a prepared statement in Java.

In our prepared statement, we’ll be using a placeholder to bootstrap the values instead of indicating the values directly within the statement.

package sampleapplication;

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

public class JavaPreparedPG {

    public static void main(String[] args) {

        // for database connection purposes
             String url_con = "jdbc:postgresql://localhost:5432/deletedb";
            String username = "postgres";
            String password = "1234";
           
            String query = "DELETE FROM deleteusers WHERE id=?";

            try (Connection con = DriverManager.getConnection(url_con, username, password);
                 PreparedStatement pst = con.prepareStatement(query)) {
               
               
                pst.setString(1, 001);
                pst.executeUpdate();

            } catch (SQLException ex) {
                ex.printStackTrace();
            }

    }

}
 String query = "DELETE FROM deleteusers WHERE id=?";

try (Connection con = DriverManager.getConnection(url_con, username, password);
        PreparedStatement pst = con.prepareStatement(query)) {

NOTE: This example shows how to delete a single record; however, we can also perform multiple deletes in the same manner by removing the WHERE condition in our DELETE statement.

The code shown above will delete the records with a user_id that has the value of the placeholder. In this case, that value is 001.

// bind to the first placeholder user_id
pst.setInt(1, 001);

We set the value of the SQL placeholder ‘?’ using a key-value pair system, as shown in the above code.

Then, we execute the statement using the method executeUpdate().

We can verify that our delete operation was successful using the PostgreSQL shell:

deletedb=# select * from deleteusers;
 user_id |  user_name  |     user_email
---------+-------------+---------------------
       2 | Josh Scotch | jscotch@example.com
(1 row)

The results show that we were able to delete a record using a PostgreSQL prepared statement in Java.

Conclusion

If you need to perform PostgreSQL database operations within your Java code, a prepared statement is a smart way to accomplish the task. In this article, we showed how to delete a PostgreSQL record using a prepared statement in Java. Using our code examples as a guide, you’ll be able to incorporate prepared statements into your own Java development efforts.

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.