July 31, 2023
Summary: in this tutorial, you will learn how to query data from a table in the PostgreSQL database using JDBC API.
Table of Contents
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.
Establishing a database connection
To connect to the PostgreSQL database, you need to provide account information such as username, password, and the connection string. See the connecting to the PostgreSQL database server for more information.
For example, the following method connects to a PostgreSQL database and returns a Connection object:
/**
* Connect to the PostgreSQL database
*
* @return a Connection object
* @throws java.sql.SQLException
*/
public Connection connect() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
The URL, user, and password are as follows:
private final String url = "jdbc:postgresql://localhost/dvdrental";
private final String user = "postgres";
private final String password = "postgres";
Creating a Statement Object
A Statement object represents an SQL statement. First, you create a Statement object from the Connection object. Then, you execute the Statement object to get a ResultSet object that represents a database result set.
JDBC provides you with three kinds of Statement objects:
- Statement: you use the Statement to implement a simple SQL statement that has no parameters.
- PreparedStatement: is the subclass of the Statement class. It gives you the ability to add the parameters to the SQL statements.
- CallableStatement: extends the PreparedStatement class used to execute a stored procedure that may have parameters.
Executing the query
To execute a query, you use one of the following methods of the Statement object:
- execute: returns true if the first object of the query is a ResultSet object. You can get the ResultSet by calling the method getResultSet.
- executeQuery: returns only one ResultSet object.
- executeUpdate: returns the number of rows affected by the statement. You use this method for the INSERT, DELETE, or UPDATE statement.
Processing the ResultSet Object
After having a ResultSet object, you use a cursor to loop through the result set by calling the methods of the ResultSet object.
Note that this cursor is a Java cursor, not the database cursor.
Closing a database connection
In JDBC 4.1, you use a try-with-resources statement to close ResultSet, Statement, and Connection objects automatically.
Querying data examples
In the following section, we will show you various example of querying data from simple to complex.
Querying data with a statement that returns one row
The following method returns the number of actors in the actor table using the COUNT function.
/**
* Get actors count
* @return
*/
public int getActorCount() {
String SQL = "SELECT count(*) FROM actor";
int count = 0;
try (Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL)) {
rs.next();
count = rs.getInt(1);
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
return count;
}
We have done the following in the getActorCount method:
- First, prepared an SQL statement that counts the number of rows in the actor table.
- Second, established a connection to the database, created a Statement object, and executed the query.
- Third, processed the result set by moving the cursor the first row and get its value using the getInt() method.
The following demonstrates the output of the method.
Querying data using a statement that returns multiple rows
The following getActors method queries data from the actor table and displays the actor information.
/**
* Get all rows in the actor table
*/
public void getActors() {
String SQL = "SELECT actor_id,first_name, last_name FROM actor";
try (Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL)) {
// display actor information
displayActor(rs);
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
In the displayActor method, we loop through the result set and print out the information for each row.
/**
* Display actor
*
* @param rs
* @throws SQLException
*/
private void displayActor(ResultSet rs) throws SQLException {
while (rs.next()) {
System.out.println(rs.getString("actor_id") + "\t"
+ rs.getString("first_name") + "\t"
+ rs.getString("last_name"));
}
}
Querying data using a statement that has parameters
To query the database with parameters, you use the PreparedStatement object.
First, you use the question mark (?) as the placeholder in the SQL statement. Then, you use methods of the PreparedStatement object such as setInt, setString,… to pass the value to the placeholders.
The following method allows you to find an actor by his/her id.
/**
* Find actor by his/her ID
*
* @param actorID
*/
public void findActorByID(int actorID) {
String SQL = "SELECT actor_id,first_name,last_name "
+ "FROM actor "
+ "WHERE actor_id = ?";
try (Connection conn = connect();
PreparedStatement pstmt = conn.prepareStatement(SQL)) {
pstmt.setInt(1, actorID);
ResultSet rs = pstmt.executeQuery();
displayActor(rs);
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
You can download the full source code of the tutorial below:
package net.rockdata.tutorial;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
*
* @author rockdata.net
*/
public class Main {
private final String url = "jdbc:postgresql://localhost/dvdrental";
private final String user = "postgres";
private final String password = "postgres";
/**
* Connect to the PostgreSQL database
*
* @return a Connection object
* @throws java.sql.SQLException
*/
public Connection connect() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
/**
* Get all rows in the actor table
*/
public void getActors() {
String SQL = "SELECT actor_id,first_name, last_name FROM actor";
try (Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL)) {
// display actor information
displayActor(rs);
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
/**
* Get actors count
* @return
*/
public int getActorCount() {
String SQL = "SELECT count(*) FROM actor";
int count = 0;
try (Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL)) {
rs.next();
count = rs.getInt(1);
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
return count;
}
/**
* Display actor
*
* @param rs
* @throws SQLException
*/
private void displayActor(ResultSet rs) throws SQLException {
while (rs.next()) {
System.out.println(rs.getString("actor_id") + "\t"
+ rs.getString("first_name") + "\t"
+ rs.getString("last_name"));
}
}
/**
* Find actor by his/her ID
*
* @param actorID
*/
public void findActorByID(int actorID) {
String SQL = "SELECT actor_id,first_name,last_name "
+ "FROM actor "
+ "WHERE actor_id = ?";
try (Connection conn = connect();
PreparedStatement pstmt = conn.prepareStatement(SQL)) {
pstmt.setInt(1, actorID);
ResultSet rs = pstmt.executeQuery();
displayActor(rs);
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
Main main = new Main();
main.findActorByID(200);
}
}
In this tutorial, we have shown you how to query data from the PostgreSQL database using JDBC API.