PostgreSQL CRUD Tutorials in Java: A Step-by-Step Guide

PostgreSQL is a powerful open-source relational database management system (RDBMS) known for its robustness and extensibility. In this tutorial, we will guide you through the process of performing CRUD (Create, Read, Update, Delete) operations in PostgreSQL using Java. We’ll provide step-by-step instructions and practical examples with detailed explanations.

Prerequisites

Before you begin, make sure you have the following prerequisites in place:

Java Development Kit (JDK): Ensure you have Java installed on your system. You can download it from the official Oracle website or use an open-source alternative like OpenJDK.
Integrated Development Environment (IDE): Choose an IDE like Eclipse, IntelliJ IDEA, or NetBeans to write and manage your Java code.
PostgreSQL: You need to have a PostgreSQL server installed and running. You can download and install PostgreSQL from the official PostgreSQL website or use a package manager if you’re on a Linux system.
PostgreSQL JDBC Driver: You’ll need the PostgreSQL JDBC driver to connect to the PostgreSQL database from your Java application. Download the PostgreSQL JDBC driver JAR file and include it in your project’s classpath.

Step 1: Create a PostgreSQL Database

Let’s start by creating a PostgreSQL database where we will perform CRUD operations. You can use the PostgreSQL command-line client or a graphical tool like pgAdmin. Here’s how to do it using the command-line client:

Install PostgreSQL by following the installation instructions provided on the PostgreSQL website.
During installation, set the PostgreSQL superuser password. Make a note of this password as you will use it to connect to the database.
Open the PostgreSQL command-line client (e.g., psql) and connect using the superuser:
```
psql -U postgres
```
Create a new database. For example, let’s create a database named mydb:
```
CREATE DATABASE mydb;
```

Step 2: Set Up a Java Project

Open your preferred IDE and create a new Java project.

Add the PostgreSQL JDBC driver dependency to your project’s build file (e.g., Maven’s pom.xml or Gradle’s build.gradle).

For Maven, add the following dependency:

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

For Gradle, add the following dependency:

implementation group: 'org.postgresql', name: 'postgresql', version: '42.3.3'

Step 3: Perform CRUD Operations

Create Operation (Insert)

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

public class PostgreSQLCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:postgresql://localhost:5432/mydb";
        String username = "postgres";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String insertQuery = "INSERT INTO employees (first_name, last_name, age) VALUES (?, ?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
            preparedStatement.setString(1, "John");
            preparedStatement.setString(2, "Doe");
            preparedStatement.setInt(3, 30);
            preparedStatement.executeUpdate();

            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code inserts a new employee record into the employees table in the PostgreSQL database.

Read Operation (Select)

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

public class PostgreSQLCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:postgresql://localhost:5432/mydb";
        String username = "postgres";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String selectQuery = "SELECT * FROM employees";
            PreparedStatement preparedStatement = connection.prepareStatement(selectQuery);
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                int age = resultSet.getInt("age");

                System.out.println("ID: " + id + ", Name: " + firstName + " " + lastName + ", Age: " + age);
            }

            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code retrieves and prints all employee records from the employees table

Update Operation (Update)

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

public class PostgreSQLCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:postgresql://localhost:5432/mydb";
        String username = "postgres";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String updateQuery = "UPDATE employees SET age = ? WHERE first_name = ? AND last_name = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(updateQuery);
            preparedStatement.setInt(1, 31);
            preparedStatement.setString(2, "John");
            preparedStatement.setString(3, "Doe");
            preparedStatement.executeUpdate();

            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code updates the age of the employee named “John Doe” in the employees table.

Delete Operation (Delete)

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

public class PostgreSQLCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:postgresql://localhost:5432/mydb";
        String username = "postgres";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String deleteQuery = "DELETE FROM employees WHERE first_name = ? AND last_name = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(deleteQuery);
            preparedStatement.setString(1, "John");
            preparedStatement.setString(2, "Doe");
            preparedStatement.executeUpdate();

            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code deletes the employee named “John Doe” from the employees table.

Conclusion

In this tutorial, you’ve learned how to perform CRUD operations in PostgreSQL using Java. You created a PostgreSQL database, set up a Java project, and wrote code for Create, Read, Update, and Delete operations with detailed explanations. PostgreSQL’s reliability and versatility make it a popular choice for various types of applications, and mastering these fundamental database operations is essential for building robust data-driven applications. As you continue your journey in Java development and PostgreSQL, you can apply these concepts to more complex and feature-rich database applications. Happy coding!

If you want to learn more about MySQL, please use our PostgreSQL tutorials and PostgreSQL Reference.