Update PostgreSQL Record using Prepared Statement in Java

Introduction

Inquisitive developers are quickly discovering that Java PostgreSQL prepared statements are delivered faster than standard queries. That’s because the method involves database precompiling. Consequently, you save time in making record-updating queries. It’s not hard. Learn how easy it is to update PostgreSQL record using prepared statement Java in only a few steps.

Prerequisite

Download, install and configure the following on your OS:

  • PostgreSQL

  • Eclipse

  • Next, open the Eclipse pom.xml project file and use this code to append the file and add the JDBC driver:


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

About the Prepared Statement PostgreSQL

The PostgreSQL prepared statement is a query method that just before it executes the query, it pre-compiles the database information. This method increases performance as well as helps secure the queried data against SQL injection attacks.

NOTE: The main reason why SQL injection attacks occur is due to non-validation prior to passing the parameters. Using the JDBC driver’s placeholder is key to escaping the drama and having a safer query execution. That’s advantageous. You’ll be using placeholders later in this tutorial.

Creating the PostgreSQL Database

Make a sample PostgreSQL database to use with the examples in this update PostgreSQL record using prepared statement Java tutorial.

  • In the PostgreSQL shell, use this command to start the process of making a database sample:
psql -U postgres
  • Name your sample database updatedb with this command:
postgres=# create database updatedb;
CREATE DATABASE
  • Next, use the code below to make a database connection. Note that your database’s name updatebd follows the backslash “c” in the code:
postgres=# \c updatedb
You are now connected to database "updatedb" as user "postgres".
  • Construct a table and name it updateusers with this command:
create table updateusers
(
    user_id int not null,
    user_name varchar(100),
    user_email varchar(100)
);
  • Now go ahead and add a few records with the updateusers command like this:
INSERT INTO updateusers(user_id, user_name, user_email)
Values
(001,'Richard Dewey','rdewey@example.com'),
(002,'Derick Scotch','dscotch@example.com');
  • Lastly, confirm that your database added the records with the SELECT * FROM updateusers; command:
updatedb=# select * from updateusers;
 user_id |   user_name   |     user_email
---------+---------------+---------------------
       1 | Richard Dewey | rdewey@example.com
       2 | Derick Scotch | dscotch@example.com
(2 rows)

PostgreSQL Prepared Statement Example

Since you completed the sample database successfully, let’s proceed with the update PostgreSQL record using prepared statement Java instructions.

  • Placeholders enable you to do value bootstrapping in the PostgreSQL Prepared Statement like this:
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/updatedb";
            String username = "postgres";
            String password = "1234";

        // hard code value that will replace the old values
            int user_id = 001;
            String user_name = "Richard Downy";
            String user_email = "rdowny@example.com";
           
            String query = "UPDATE updateusers "
                    + "SET user_name =?, user_email = ? "
                    + "WHERE user_id = ?";

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

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

    }

}
 String query = "UPDATE updateusers "
                    + "SET user_name =?, user_email = ? "
                    + "WHERE user_id = ?";

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

Here’s an explanation of the code you just inputted above:

  • The SQL query statement you created will be for your update PostgreSQL record using prepared statement Java. The record with the user_id that matches the user_id variable will be updated later.

  • Make key-value pair assignments for the placeholders in SQL like this:

// bind to the first placeholder user_name
pst.setString(1, user_name);
// bind to the first placeholder user_email
pst.setString(2, user_email);
// bind to the first placeholder user_id
pst.setInt(3, user_id);
  • Now you’re ready to use the executeUpdate() method to execute update PostgreSQL record using prepared statement Java.

  • In the PostgreSQL, confirm the success of the results.

updatedb=# select * from updateusers;
 user_id |   user_name   |     user_email
---------+---------------+---------------------
       2 | Derick Scotch | dscotch@example.com
       1 | Richard Downy | rdowny@example.com
(2 rows)

It worked! You just finished the update PostgreSQL record using prepared statement Java actions victoriously.

Conclusion

In this tutorial, you learned how to complete the update PostgreSQL record using prepared statement Java method. By putting it to work, you benefit from the enhanced speed in executing queries. What’s more, it takes minimal coding to put the prepared statement together. Increased productivity wins every time.

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.