Java - JDBC PostgreSQL Select Example

❮ Previous Chapter Next Chapter ❯

---

In this previous tutorial, we have seen how to insert one or multiple rows into the PostgreSQL database using the JDBC API. In this tutorial, you will learn how to query data from a table in the PostgreSQL database using the JDBC API.

Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

To query data from a table using JDBC, you use the following steps:

• Establish a database connection to the PostgreSQL server.
• Create an instance of the Statement object
• Execute a statement to get a ResultSet object
• Process the ResultSet object.
• Close the database connection.

Technologies used

We use below technologies in this tutorial:

• JDK - 1.8 or later
• PostgreSQL- 42.2.9
• IDE - Eclipse Neon
• JDBC - 4.2

Download PostgreSQL JDBC Driver

To connect to the PostgreSQL database server from a Java program, you need to have a PostgreSQL JDBC driver. You can download the latest version of the driver on the postgresql.org website via the download page.

Add the PostgreSQL JDBC driver jar file to the project classpath.

For maven users:

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

For Gradle users:

// https://mvnrepository.com/artifact/org.postgresql/postgresql
compile group: 'org.postgresql', name: 'postgresql', version: '42.2.9'

PostgreSQL Database Setup

In this previous tutorial, we have inserted single or multiple rows into the PostgreSQL database using the JDBC API. In this tutorial, we are going to retrieve records from the same PostgreSQL database table.

JDBC PostgreSQL Select Single Record Example

Here is the complete Java program to select single or multiple records from the "users" table:

package net.javaguides.postgresql.tutorial;

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

/**
 * Select PreparedStatement JDBC Example
 * 
 * @author Ramesh Fadatare
 *
 */
public class RetrieveRecordsExample {

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

    private static final String QUERY = "select id,name,email,country,password from Users where id =?";
    private static final String SELECT_ALL_QUERY = "select * from users";

    public void getUserById() {
        // using try-with-resources to avoid closing resources (boiler plate
        // code)

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager.getConnection(url, user, password);
            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(QUERY);) {
            preparedStatement.setInt(1, 1);
            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            ResultSet rs = preparedStatement.executeQuery();

            // Step 4: Process the ResultSet object.
            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);
        }
    }

    public void getAllUsers() {
        // using try-with-resources to avoid closing resources (boiler plate
        // code)

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager.getConnection(url, user, password);
            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_QUERY);) {
            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            ResultSet rs = preparedStatement.executeQuery();

            // Step 4: Process the ResultSet object.
            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);
        }
    }

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

    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());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

Output:

select id,name,email,country,password from Users where id =1
select * from users
2,Ramesh,[email protected],India,password123
3,John,[email protected],US,password123

In this tutorial, we have shown you how to query data from the PostgreSQL database using the JDBC API.

Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

---

❮ Previous Chapter Next Chapter ❯