Retrieve a Record in PostgreSQL using a Prepared Statement in Java

Introduction

A prepared statement is a server-side object linked to database object ID designed to enhance system performance. When executed, the specified prepared statement is parsed and analyzed and then rewritten according to the specified parameters. Prepared statements are used to avoid having the same statement repeatedly parsed, increasing system performance. This tutorial will explain how to retrieve a PostgreSQL record using a prepared statement with a Java application.

Prerequisite

  • PostgreSQL must be properly installed and configured.
  • An understanding of how to execute SQL queries.

  • The ECLIPSE application must be properly installed and configured to retrieve a PostgreSQL record using a prepared statement with a Java application.

  • The JDBC driver must be included in the ECLIPSE project. Confirm the driver is include by appending the following code in the project’s pom.xml file:

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

What is PostgreSQL Prepared Statement

As the name implies, a prepared statement, also known as a parameterized statement, helps users prepare a set of SQL queries to send to the server by an application. The queries are parsed, compiled and then executed once the corresponding function is used against the prepared statement.

Creating the PostgreSQL Database

A sample database must be created for use in the examples demonstrated in this tutorial.

  • Accomplish this by first logging into the PostgreSQL shell with the following command:
psql -U postgres
  • Second, create the database name getdb by executing the following command:

GeSHi Error: GeSHi could not find the language bah (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)
  • Now connect to the database getdb using the command \c -database name- as follows:
postgres=# \c getdb;
You are now connected to database "getdb" as user "postgres".
  • Now create a table named getusers with the following command:
create table getusers
(
    user_id int not null,
    user_name varchar(100),
    user_email varchar(100)
);
  • Sample records can now be insert into the table using this command:
INSERT INTO getusers(user_id, user_name, user_email)
Values
(001,'Raizel Galisanao','rgalisanao@example.com'),
(002,'Abishai Galisanao','agalisanao@example.com');
  • Now the following SELECT * FROM getusers; command can be executed to verify the database has been successfully set up:
getdb=# select * from getusers;
 user_id |     user_name     |       user_email
---------+-------------------+------------------------
       1 | Raizel Galisanao  | rgalisanao@example.com
       2 | Abishai Galisanao | agalisanao@example.com
(2 rows)

PostgreSQL Prepared Statement Example

With a sample database in place, a retrieve operation can now be performed against the new database by executing the following Java code:

package sampleapplication;

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

public class JavaPreparedPG {

    public static void main(String[] args) {

        // for database connection purposes
            String url_con = "jdbc:postgresql://localhost:5432/getdb";
            String username = "postgres";
            String password = "1234";
           
            String query = "SELECT * FROM getusers";

            try (Connection con = DriverManager.getConnection(url_con, username, password);
                PreparedStatement pstatement = con.prepareStatement(query);
                ResultSet rs = pstatement.executeQuery()){
               
               
                while (rs.next()) {
                   
                    System.out.print(rs.getInt(1));
                    System.out.print(": ");
                    System.out.println(rs.getString(2));
                }

            } catch (SQLException ex) {
            ex.printStackTrace();
            }

        }

}
 String query = "SELECT * FROM getusers";

try (Connection con = DriverManager.getConnection(url_con, username, password);
    PreparedStatement pstatement = con.prepareStatement(query);
    ResultSet rs = pstatement.executeQuery()){{

The above code will obtain all of the records in the table getusers.

Now the following code will loop through the data within the rs variable, using the next() method, as it advances the cursor to each piece of data:

while (rs.next()) {
               
        System.out.print(rs.getInt(1));
        System.out.print(": ");
        System.out.println(rs.getString(2));
    }

It will then print the results in the console, using the System.out.print() method, and should resemble the following:

1: Raizel Galisanao
2: Abishai Galisanao

Conclusion

This tutorial explained how to use PostgreSQL to retrieve a PostgreSQL record using a prepared statement with a Java application. The article specifically covered what a PostgreSQL prepared statement is, how to create the PostgreSQL database and how to verify the database using the SELECT * FROM getusers; command. The tutorial also explained how to perform a retrieve operation against the new database with Java code. Remember that PostgreSQL, the ECLIPSE application and the JDBC driver must all be installed and working in order to retrieve a PostgreSQL record using a prepared statement with a Java application.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.