Steps for querying data

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

• Establish a database connection to the PostgreSQL server.
• Create an instance of theStatement orPreparedStatement object.
• Execute a statement to get aResultSet object.
• Process theResultSet object.
• Close theStatement &Connection object by calling theirclose() method.

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

1) Establishing a database connection

Use thegetConnection() method of theDriverManager class to establish a connection to the PostgreSQL server.

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

We’ll use theDB class created in theconnecting to the PostgreSQL server to connect to the PostgreSQL server.

2) Creating a Statement object

In JDBC, aStatement object represents an SQL statement.

• First, create aStatement object from theConnection object.
• Then, execute theStatement object to get aResultSet object that represents a database result set.

JDBC offers three types ofStatement objects:

• Statement: use the Statement to implement a simple SQL statement that has no parameters.
• PreparedStatement: is the subclass of theStatement class, which allows you to bind parameters to the SQL statement.
• CallableStatement: extends thePreparedStatement class that can execute a stored procedure.

3) Executing a query

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

• execute(): Return true if the first object of the query is aResultSet object. You can get theResultSet by calling the methodgetResultSet().
• executeQuery(): Return only oneResultSet object.
• executeUpdate(): Return the number of rows affected by the statement. Typically, you use this method for executing theINSERT,DELETE, orUPDATE statement.

4) Processing the ResultSet

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

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

5) Closing a database connection

To close aStatement orConnection object, you call theclose() method explicitly in thefinally clause of thetry...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 closeResultSet,Statement, andConnection objects automatically.

Querying data examples

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

1) Querying all rows from the products table

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 anArrayList to store the returned products.

var products= new ArrayList<Product>();

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

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

Third, open a database connection and create aStatement object:

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

The try-with-resources will automatically close theStatement andConnection objects.

Fourth, execute theSELECT statement by calling theexecuteQuery() method:

var rs= stmt.executeQuery(sql);

Fifth, iterate over the result set, initialize theProduct object, and add it to theproducts 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 {    publicstatic 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}

2) Querying data with parameters

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 {     publicstatic Product findById(int id){        varsql = "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:

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

Second, open a connection to the database and create aPreparedStatement 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 theexecuteQuery() 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 thefindById() in themain() method of the Main() class to retrieve the product with id 1 from theproducts table:

public class Main {    publicstatic 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}

Summary

• Use theexecuteQuery() method of theStatement orPreparedStatement object to retrieve data from a table.

Last updated onJanuary 31, 2024

On this page

• Steps for querying data
• Querying data examples
• Summary