PostgreSQL Prepared Statement in Java

Introduction

There are multiple statement types understood by PostgreSQL. While PostgreSQL statements can easily be parsed, multiple user performing multiple queries at the same time against the database can sometimes result a bottleneck. To help prevalent this phenomenon, a “prepared” statement can be constructed to avoid the repeated parsing of the same statements. This tutorial will explain how to set up and run a PostgreSQL prepared statement in Java.

Prerequisite

  • PostgreSQL must be properly installed and configured in order to set up and run a PostgreSQL prepared statement with Java.

  • A solid comprehension of SQL for executing queries of Postgre.

  • The ECLIPSE application must be properly installed and configured.
  • The JDBC driver must be included the ECLIPSE project.

What is PostgreSQL Prepared Statement

The PostgreSQL Prepared statement, also known as a parameterized statement, helps the system run more efficiently and is typically utilized when performing frequent, similar queries during a session.

A prepared statement creates a SQL template that is sent to the database for parsing and compilation, with the results being stored without actually executing the queries. This is designed to reduce drain on system resources.

Creating the PostgreSQL Database

To create a sample database for use with the examples in this tutorial:

  • First, login to the PostgreSQL server, via the command line, with the following command:
psql -U postgres
  • Now create a new database by executing the following command:
postgres=# create database userinfo;
CREATE DATABASE
  • Now connect to the database userinfo using the command \c -database name-as shown here:
postgres=# \c userinfo
You are now connected to database "userinfo" as user "postgres".
  • Next, create a table named users with the following command:
create table users
(
    user_id int not null,
    user_name varchar(100),
    user_email varchar(100)
);
  • Now sample records can be inserted into the table with the following command:
INSERT INTO users(user_id, user_name, user_email)
Values
(001,'Richard Dewey','rdewey@example.com'),
(002,'Derick Scotch','dscotch@example.com'),
(003,'John Stacey','jstacey@example.com'),
(004,'Yuen Park','ypark@example.com'),
(005,'Renz Toledo','rtoledo@example.com');
  • Finally, confirm the database has been properly set up by executing the following command: SELECT * FROM users;

The results should resemble the following:

 user_id |   user_name   |     user_email
---------+---------------+---------------------
       1 | Richard Dewey | rdewey@example.com
       2 | Derick Scotch | dscotch@example.com
       3 | John Stacey   | jstacey@example.com
       4 | Yuen Park     | ypark@example.com
       5 | Renz Toledo   | rtoledo@example.com
(5 rows)

PostgreSQL Prepared Statement Example

With the sample database properly set up and working, it is worthwhile to note that placeholders should be used when writing a prepared statement to avoid exposing the values directly to the statement. This adds a level of security to the application and helps to optimize system performance.

Execute the following code to create a sample application package:

package sampleapplication;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class JavaPreparedPG {

    public static void main(String[] args) {

            // PostgreSQL connection details
            String url_con = "jdbc:postgresql://localhost:5432/userinfo";
            String userame = "postgres";
            String password = "1234";
           
            // hardcoded values that we will be using for the prepared statement
            int user_id = 006;
            String user_name = "Anthony Baker";
            String user_email = "abaker@example.com";

            // The query that we will be using for our prepared statement, which uses the placeholders '?'.
            String query = "INSERT INTO users(user_id, user_name, user_email) VALUES(?, ?, ?)";

            try (Connection db_con = DriverManager.getConnection(url_con, username, password);
                 PreparedStatement pst = db_con.prepareStatement(query)) {
               
                pst.setInt(1, user_id);
                pst.setString(2, user_name);
                pst.setString(3, user_email);
                pst.executeUpdate();

            } catch (SQLException ex) {

                ex.printStackTrace();
            }

    }

}
String query = "INSERT INTO users(user_id, user_name, user_email) VALUES(?, ?, ?)";

            try (Connection db_con = DriverManager.getConnection(url_con, username, password);
                 PreparedStatement pst = con.prepareStatement(query)) {

The above Java code makes up the prepared statement. As mentioned above, placeholders were used as an alternative to directly indicating values within statement.

Note there are three (3) ‘?’ placeholders that will have values assigned to them with the following code:

pst.setInt(1, user_id);
pst.setString(2, user_name);
pst.setString(3, user_email);

The above code uses a key-value pair structure designed to maintain the distribution order of the placeholders.

Now execute the statement using the method executeUpdate().

Since the above Java code will not return any data, the INSERT operation can be verified using the PostgreSQL shell as shown here:

userinfo=# select * from users;
 user_id |   user_name   |     user_email
---------+---------------+---------------------
       1 | Richard Dewey | rdewey@example.com
       2 | Derick Scotch | dscotch@example.com
       3 | John Stacey   | jstacey@example.com
       4 | Yuen Park     | ypark@example.com
       5 | Renz Toledo   | rtoledo@example.com
       6 | Anthony Baker | abaker@example.com
(6 rows)

The above image confirms records were successfully inserted using a PostgreSQL prepared statement in Java.

Conclusion

This tutorial explained the basics of how to set up and run a PostgreSQL prepared statement in Java. The article explained what a PostgreSQL prepared statement is, how to create the PostgreSQL database and insert sample records into the need table. The tutorial also covered how to verify the INSERT operation was successful using the PostgreSQL shell. Remember, to add a level of security, be sure to use placeholders when writing a prepared statement to avoid exposing the values directly to the statement.

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.