PostgreSQL JDBC: Querying Data (original) (raw)

Summary: in this tutorial, you will learn how to query data from a table in the PostgreSQL database using JDBC API.

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

If you use the try-with-resources statement, you don’t need to explicitly call the close() method of the Statement or Connection object. It will automatically close these objects.

Use the getConnection() method of the DriverManager class to establish a connection to the PostgreSQL server.

return DriverManager.getConnection(url, user, password);

We’ll use the DB class created in the connecting to the PostgreSQL server to connect to the PostgreSQL server.

In JDBC, a Statement object represents an SQL statement.

JDBC offers three types of Statement objects:

To execute a query, you use one of the following methods of the Statement object:

Once having a ResultSet object, you use a while loop to iterate over the result in the result set:

while (rs.next()) {
   // ...
}

To close a Statement or Connection object, you call the close() method explicitly in the finally clause of the try...catch...finally statement. This ensures that the resources are closed properly even if any exception occurs.

Starting from JDBC 4.1, you can use a try-with-resources statement to close ResultSet, Statement, and Connection objects automatically.

Let’s explore some examples of querying data from a table using JDBC.

Define a new function findAll() in the ProductDB class to retrieve all rows from the products table:

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ProductDB {

    public static List<Product> findAll() {
        var products = new ArrayList<Product>();

        var sql = "SELECT id, name, price FROM products ORDER BY name";

        try (var conn =  DB.connect();
             var stmt = conn.createStatement()) {

            var rs = stmt.executeQuery(sql);

            while (rs.next()) {
                var product = new Product(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getDouble("price"));
                products.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return products;
    }

    // ...
}

How it works.

First, initialize an ArrayList to store the returned products.

var products = new ArrayList<Product>();

Second, construct a query that retrieves all rows from the products table:

var sql = "SELECT id, name, price FROM products ORDER BY name";

Third, open a database connection and create a Statement object:

try (var conn =  DB.connect();
     var stmt = conn.createStatement()) {
// ..

The try-with-resources will automatically close the Statement and Connection objects.

Fourth, execute the SELECT statement by calling the executeQuery() method:

var rs = stmt.executeQuery(sql);

Fifth, iterate over the result set, initialize the Product object, and add it to the products list:

while (rs.next()) {
    var product = new Product(
        rs.getInt("id"),
        rs.getString("name"),
        rs.getDouble("price")
    );
    products.add(product);
}

Finally, return the products list:

return products;

The following shows how to use the findAll() method of the ProductDB class to retrieve all data from the products table and display each in the standard output:

public class Main {
    public static void main(String[] args) {

        var products = ProductDB.findAll();
        for (var product:  products) {
            System.out.println(product);
        }

    }
}

Output:

Product{id=5, name='Bluetooth Headphones', price=199.0}
Product{id=8, name='Car Mount', price=29.98}
Product{id=1, name='Phone Case', price=19.99}
Product{id=6, name='Phone Stand', price=24.99}
Product{id=2, name='Power Bank', price=19.99}
Product{id=7, name='Ring Holder', price=39.99}
Product{id=3, name='Screen Protector', price=29.99}
Product{id=9, name='Selfie Stick', price=29.99}
Product{id=10, name='Smartwatch', price=399.97}
Product{id=4, name='Wireless Charger', price=35.99}

The following defines a method called findById() to find the product by id:

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ProductDB {
     public static Product findById(int id){
        var sql = "SELECT id, name, price FROM products WHERE id=?";

        try (var conn =  DB.connect();
             var pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            var rs = pstmt.executeQuery();
            if (rs.next()) {
                return new Product(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getDouble("price")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    // ...
}

How it works.

First, construct a SELECT that selects a product by id and use the question mark (?) as the placeholder:

var sql = "SELECT id, name, price FROM products WHERE id=?";

Second, open a connection to the database and create a PreparedStatement object:

try (var conn =  DB.connect();
     var pstmt = conn.prepareStatement(sql)) {
//...

Third, bind the id to the statement:

pstmt.setInt(1, id);

Fourth, execute the statement using the executeQuery() method of the PreparedStatement object:

var rs = pstmt.executeQuery();

Fifth, process the result set if the row with specified id exists and return the Product object:

if (rs.next()) {
    return new Product(
        rs.getInt("id"),
        rs.getString("name"),
        rs.getDouble("price")
    );
}

The following shows how to use the findById() in the main() method of the Main() class to retrieve the product with id 1 from the products table:

public class Main {
    public static void main(String[] args) {

        var p = ProductDB.findById(1);
        if(p != null){
            System.out.println(p);
        }
    }
}

Output:

Product{id=1, name='Phone Case', price=19.99}