Java CRUD Operations with PostgreSQL

Author:

In this tutorial, you will learn how to connect a Java program to a PostgreSQL database and perform CRUD operations using JDBC (Java Database Connectivity) API. These CRUD operations correspond to the basic SQL operations: Create (INSERT), Retrieve (SELECT), Update, and Delete.

What You’ll Learn:

  • How to set up a PostgreSQL database for CRUD operations.
  • How to create a users table.
  • How to perform Create, Read, Update, and Delete operations using JDBC in Java.

Technologies Used:

In this tutorial, we will use the following technologies:

  • JDK: Version 21 (latest)
  • PostgreSQL JDBC Driver: Version 42.7.4
  • IDE: Eclipse, IntelliJ IDEA, or any preferred IDE
  • JDBC: Version 4.2

What is JDBC?

JDBC (Java Database Connectivity) is a standard API that allows Java applications to interact with relational databases. It supports all major databases like PostgreSQL, MySQL, Oracle, and SQL Server.

JDBC Steps:

  1. Import JDBC packages.
  2. Establish a connection to the database.
  3. Create a statement to perform queries.
  4. Execute the statement.
  5. Process the result.
  6. Close the database connection.

Step 1: Download PostgreSQL JDBC Driver

To connect Java to PostgreSQL, you need the PostgreSQL JDBC driver. You can manually download the JAR file or use Maven/Gradle for automatic dependency management.

For Maven Users:

Add the following dependency to your pom.xml:

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

For Gradle Users:

Add the following line to your build.gradle:

implementation 'org.postgresql:postgresql:42.7.4'

Step 2: Setting up PostgreSQL Database

Before performing any CRUD operations, ensure you have PostgreSQL installed on your system.

Create a Database:

Run the following command to create a new database in PostgreSQL:

CREATE DATABASE mydb;

Create a Users Table:

We will create a users table to perform our CRUD operations:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name TEXT,
    email VARCHAR(50),
    country VARCHAR(50),
    password VARCHAR(50)
);

Step 3: JDBC CRUD Operations in Java

3.1 Create Table Example

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

public class CreateTableExample {

    private final String url = "jdbc:postgresql://localhost/mydb";
    private final String user = "postgres";
    private final String password = "root";

    public static void main(String[] args) {
        CreateTableExample example = new CreateTableExample();
        try {
            example.createTable();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void createTable() throws SQLException {
        String createTableSQL = "CREATE TABLE users (" +
                "ID INT PRIMARY KEY, " +
                "NAME TEXT, " +
                "EMAIL VARCHAR(50), " +
                "COUNTRY VARCHAR(50), " +
                "PASSWORD VARCHAR(50))";

        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement statement = connection.createStatement()) {
            statement.execute(createTableSQL);
            System.out.println("Table 'users' created successfully.");
        }
    }
}

3.2 Insert Record Example

Single Row Insert:

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

public class InsertRecordExample {

    private final String url = "jdbc:postgresql://localhost/mydb";
    private final String user = "postgres";
    private final String password = "root";

    private static final String INSERT_USERS_SQL = "INSERT INTO users" +
            "  (id, name, email, country, password) VALUES " +
            " (?, ?, ?, ?, ?);";

    public static void main(String[] argv) throws SQLException {
        InsertRecordExample example = new InsertRecordExample();
        example.insertRecord();
    }

    public void insertRecord() throws SQLException {
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {

            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, "Tony");
            preparedStatement.setString(3, "tony@gmail.com");
            preparedStatement.setString(4, "US");
            preparedStatement.setString(5, "secret");

            int rows = preparedStatement.executeUpdate();
            System.out.println(rows + " row(s) inserted.");
        }
    }
}

Multiple Row Insert:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

public class InsertMultipleRecordsExample {

    private final String url = "jdbc:postgresql://localhost/mydb";
    private final String user = "postgres";
    private final String password = "root";

    private static final String INSERT_USERS_SQL = "INSERT INTO users" +
            "  (id, name, email, country, password) VALUES " +
            " (?, ?, ?, ?, ?);";

    public void insertUsers(List<User> users) throws SQLException {
        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement statement = conn.prepareStatement(INSERT_USERS_SQL)) {

            for (User user : users) {
                statement.setInt(1, user.getId());
                statement.setString(2, user.getName());
                statement.setString(3, user.getEmail());
                statement.setString(4, user.getCountry());
                statement.setString(5, user.getPassword());
                statement.addBatch();
            }
            int[] result = statement.executeBatch();
            System.out.println("Inserted " + result.length + " rows.");
        }
    }

    public static void main(String[] args) throws SQLException {
        List<User> users = Arrays.asList(
                new User(2, "Ramesh", "ramesh@gmail.com", "India", "password123"),
                new User(3, "John", "john@gmail.com", "US", "password123")
        );
        new InsertMultipleRecordsExample().insertUsers(users);
    }
}

3.3 Select Record Example

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

public class RetrieveRecordsExample {

    private final String url = "jdbc:postgresql://localhost/mydb";
    private final String user = "postgres";
    private final String password = "root";

    private static final String SELECT_ALL_QUERY = "SELECT * FROM users";

    public void getAllUsers() throws SQLException {
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_QUERY);
             ResultSet rs = preparedStatement.executeQuery()) {

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                System.out.println(id + ", " + name + ", " + email + ", " + country);
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        RetrieveRecordsExample example = new RetrieveRecordsExample();
        example.getAllUsers();
    }
}

3.4 Update Record Example

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

public class UpdateRecordExample {

    private final String url = "jdbc:postgresql://localhost/mydb";
    private final String user = "postgres";
    private final String password = "root";

    private static final String UPDATE_USERS_SQL = "UPDATE users SET name = ? WHERE id = ?;";

    public static void main(String[] argv) throws SQLException {
        UpdateRecordExample example = new UpdateRecordExample();
        example.updateRecord();
    }

    public void updateRecord() throws SQLException {
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL)) {

            preparedStatement.setString(1, "Ram");
            preparedStatement.setInt(2, 1);
            int rows = preparedStatement.executeUpdate();
            System.out.println(rows + " row(s) updated.");
        }
    }
}

3.5 Delete Record Example

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

public class DeleteRecordExample {

    private static final String DELETE_USERS_SQL = "DELETE FROM users WHERE id = ?;";
    private final String url = "jdbc:postgresql://localhost/mydb";
    private final String user = "postgres";
    private final String password = "root";

    public static void main(String[] argv) throws SQLException {
        DeleteRecordExample example = new DeleteRecordExample();
        example.deleteRecord(1);
    }

    public void deleteRecord(int id) throws SQLException {
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(DELETE_USERS_SQL)) {

            preparedStatement.setInt(1, id);
            int rows = preparedStatement.executeUpdate();
            System.out.println("Deleted " + rows + " row(s).");
        }
    }
}

Conclusion

In this tutorial, we demonstrated how to perform basic CRUD operations (Create, Read, Update, and Delete) on a PostgreSQL database using JDBC in Java. This guide serves as a foundation for building Java applications that interact with relational databases like PostgreSQL. By following these steps, you can now create, query, update, and delete data in your PostgreSQL database from your Java applications.

Comments

Post a Comment

Leave Comment