Java JDBC PostgreSQL Select Example

In this tutorial, we will show you how to retrieve data from a PostgreSQL database using Java and the JDBC API. You will learn how to fetch both a single record and multiple records from a PostgreSQL table using SQL queries.

What You’ll Learn:

• How to connect to a PostgreSQL database using JDBC.
• How to query single and multiple records from a table.
• Handling SQL exceptions in Java.
• Processing result sets from SQL queries.

Technologies Used:

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

Step 1: PostgreSQL JDBC Driver Dependency

To connect your Java program to the PostgreSQL database, you need the PostgreSQL JDBC driver. Here’s how you can add it:

For Maven Users:

Add the following dependency to your pom.xml file:

<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 file:

implementation 'org.postgresql:postgresql:42.7.4'

This will automatically add the PostgreSQL JDBC driver to your project.

Step 2: PostgreSQL Database Setup

Before querying data, ensure that you have a PostgreSQL database and a table set up.

Here is the basic structure of the users table:

CREATE TABLE users (
    ID INT PRIMARY KEY,
    NAME TEXT,
    EMAIL VARCHAR(50),
    COUNTRY VARCHAR(50),
    PASSWORD VARCHAR(50)
);

Ensure that some records are inserted into this table before querying.

Step 3: Querying Data from PostgreSQL using JDBC

To query data from a PostgreSQL table using JDBC, follow these steps:

1. Establish a connection to the PostgreSQL database.
2. Create a PreparedStatement object.
3. Execute the query to get a ResultSet object.
4. Process the ResultSet to retrieve the data.
5. Close the connection.

Example 1: Select a Single Record from the Table

This example demonstrates how to retrieve a single record from the users table using JDBC.

package com.example.postgresql;

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 static String url = "jdbc:postgresql://localhost/mydb";
    private final static String user = "postgres";
    private final static String password = "root";

    // SQL query to select a single user by ID
    private static final String QUERY = "SELECT id, name, email, country, password FROM users WHERE id = ?";

    public void getUserById(int userId) {
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(QUERY)) {

            // Set the ID parameter for the query
            preparedStatement.setInt(1, userId);
            System.out.println(preparedStatement);

            // Execute the query
            ResultSet rs = preparedStatement.executeQuery();

            // Process the ResultSet to retrieve data
            if (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                String password = rs.getString("password");
                System.out.println(id + ", " + name + ", " + email + ", " + country + ", " + password);
            }

        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    // Method to handle SQL exceptions
    public static void printSQLException(SQLException ex) {
        for (Throwable e : ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
            }
        }
    }

    public static void main(String[] args) {
        RetrieveRecordsExample example = new RetrieveRecordsExample();
        example.getUserById(1); // Fetches the user with ID = 1
    }
}

Example 2: Select All Records from the Table

This example demonstrates how to retrieve all records from the users table.

package com.example.postgresql;

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 static String url = "jdbc:postgresql://localhost/mydb";
    private final static String user = "postgres";
    private final static String password = "root";

    // SQL query to select all users
    private static final String SELECT_ALL_QUERY = "SELECT * FROM users";

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

            System.out.println(preparedStatement);

            // Execute the query
            ResultSet rs = preparedStatement.executeQuery();

            // Process the ResultSet to retrieve all data
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                String password = rs.getString("password");
                System.out.println(id + ", " + name + ", " + email + ", " + country + ", " + password);
            }

        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    // Method to handle SQL exceptions
    public static void printSQLException(SQLException ex) {
        for (Throwable e : ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
            }
        }
    }

    public static void main(String[] args) {
        RetrieveRecordsExample example = new RetrieveRecordsExample();
        example.getAllUsers(); // Fetches all users from the table
    }
}

Conclusion

In this tutorial, we demonstrated how to query data from a PostgreSQL database using JDBC in Java. We showed how to fetch both a single record and multiple records from a table. This knowledge is fundamental when working with databases in any Java application.

Key Takeaways:

• Use PreparedStatement for secure and efficient database queries.
• Handle SQL exceptions properly to avoid crashes and easily debug issues.

This tutorial provides a strong foundation for reading data from PostgreSQL using Java JDBC.