JDBC Select

Summary: in this tutorial, you will learn how to retrieve data from a table using JDBC from a Java program.

Here are the steps for retrieving data in a table using JDBC:

• First, open a new database connection.
• Second, create a new PreparedStatement object (or Statement object) that accepts a SELECT statement.
• Third, set the parameters for the statement by calling the set* methods of the PreparedStatement object.
• Fourth, call the executeQuery() to execute the SELECT statement to retrieve data from one or more tables. The executeQuery() method returns a ResultSet object.
• Fifth, iterate through the result sets by calling the next() method of the ResultSet object and process each of them individually.
• Finally, close the ResultSet and database connection.

Querying all rows from a table

The following program illustrates how to retrieve all rows from the products table:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        var sql = "SELECT id, name, price FROM products";

        try (var connection = DBConnection.connect();
             var stmt = connection.createStatement()) {

            var rs = stmt.executeQuery(sql);

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                double price = rs.getDouble("price");
                System.out.printf("%-5s%-25s%-10s%n", id, name, price);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: JavaScript (javascript)

Output:

1    T-Shirt                  11.95     
2    Crew Neck T-shirt        19.99     
3    V-neck T-shirt           24.99     
4    Polo T-shirt             29.99     
5    Graphic T-shirt          22.99     
6    Long Sleeve T-shirt      27.99  Code language: CSS (css)

How it works.

First, construct a SELECT statement that retrieves the id, name, and price from the products table:

var sql = "SELECT id, name, price FROM products";Code language: Java (java)

Second, open a database connection:

var connection = DBConnection.connect()Code language: Java (java)

Third, create a Statement object from the Connection object:

var stmt = connection.createStatement()Code language: Java (java)

Fourth, execute the query by calling the executeQuery() method:

var rs = stmt.executeQuery(sql);Code language: Java (java)

The executeQuery() returns a ResultSet object.

Fifth, iterate through the result set by calling the next() method of the ResultSet object. In each iteration, call the get* method to read data:

 while (rs.next()) {
   int id = rs.getInt("id");
   String name = rs.getString("name");
   double price = rs.getDouble("price");
   System.out.printf("%-5s%-25s%-10s%n", id, name, price);
 }Code language: Java (java)

The try-with-resources will automatically close the ResultSet and database connection.

Querying data with parameters

The following program shows how to retrieve the products whose prices are greater than 20:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        var sql = "SELECT id, name, price FROM products WHERE price > ?";

        try (var connection = DBConnection.connect();
             var pstmt = connection.prepareStatement(sql)) {

            pstmt.setDouble(1, 20);

            var rs = pstmt.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                double price = rs.getDouble("price");
                System.out.printf("%-5s%-25s%-10s%n", id, name, price);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

Output:

3    V-neck T-shirt           24.99     
4    Polo T-shirt             29.99     
5    Graphic T-shirt          22.99     
6    Long Sleeve T-shirt      27.99  Code language: plaintext (plaintext)

How it works.

First, construct a SELECT statement that retrieves the products with prices greater than a specified price:

var sql = "SELECT id, name, price FROM products WHERE price > ?";Code language: Java (java)

In this statement, the question mark (?) is a placeholder that will be replaced with a specified price.

Second, open a database connection:

var connection = DBConnection.connect()Code language: Java (java)

Third, create a PreparedStatement object from the Connection object:

var stmt = connection.createStatement()Code language: Java (java)

This time we use the PreparedStatement object instead of the Statement object because we want to bind the value to the query.

Fourth, bind a value to the query by calling the setDouble() method:

pstmt.setDouble(1, 20);Code language: Java (java)

Fifth, execute the query by calling the executeQuery() method:

var rs = pstmt.executeQuery();Code language: Java (java)

Sixth, iterate through the result set by calling the next() method of the ResultSet object. In each iteration, call the get* method to read data:

 while (rs.next()) {
   int id = rs.getInt("id");
   String name = rs.getString("name");
   double price = rs.getDouble("price");
   System.out.printf("%-5s%-25s%-10s%n", id, name, price);
 }Code language: Java (java)

The try-with-resources will automatically close the ResultSet and database connection.

Summary

• Call the executeQuery() method of the Statement or PreparedStatment object to execute a SELECT statement to retrieve data from a table.
• Use the ResultSet object to store the result set returned by a query.
• Use the next() method inside a loop to iterate over each row in the result set.