Java JDBC PostgreSQL Update Example

In this tutorial, we will show you how to update data in a PostgreSQL database using Java and the JDBC API. By following this step-by-step guide, you’ll learn how to update records in a PostgreSQL table through a Java program.

What You’ll Learn:

• How to connect to a PostgreSQL database using JDBC.
• How to update a record in the PostgreSQL database.
• How to handle SQL exceptions in Java.

Technologies Used:

In this tutorial, we will be using the following technologies:

• JDK: Version 21 or later
• PostgreSQL JDBC Driver: Version 42.7.4
• IDE: Eclipse, IntelliJ IDEA, or any preferred IDE
• JDBC: Version 4.2

Step 1: Download PostgreSQL JDBC Driver

To connect your Java program to a PostgreSQL database, you need the PostgreSQL JDBC driver. You can either download it manually or use Maven or Gradle to include it in your project.

For Maven Users:

Add the following dependency to your pom.xml file:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.4</version>
</dependency>

For Gradle Users:

Add the following line to your build.gradle file:

implementation 'org.postgresql:postgresql:42.7.4'

This will automatically add the PostgreSQL JDBC driver to your project.

Step 2: PostgreSQL Database Setup

Ensure that you have the PostgreSQL database set up. We’ll be working with a users table that contains the following fields:

CREATE TABLE users (
    ID INT PRIMARY KEY,
    NAME TEXT,
    EMAIL VARCHAR(50),
    COUNTRY VARCHAR(50),
    PASSWORD VARCHAR(50)
);

If you’ve already inserted some data into the table, you are now ready to update the records.

Step 3: Writing Java Code to Update Data in PostgreSQL

Steps to Update Data:

1. Establish a connection to the PostgreSQL database.
2. Create a PreparedStatement object to execute the SQL update statement.
3. Execute the UPDATE statement using executeUpdate().
4. Close the connection to the database.

Java Program: Update a Record in PostgreSQL

package com.example.postgresql;

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

public class UpdateRecordExample {

    private final String url = "jdbc:postgresql://localhost/mydb";
    private final String user = "postgres";
    private final String password = "root";

    // SQL query to update the user's name by ID
    private static final String UPDATE_USERS_SQL = "UPDATE users SET name = ? WHERE id = ?;";

    public static void main(String[] args) {
        UpdateRecordExample example = new UpdateRecordExample();
        try {
            example.updateRecord();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateRecord() throws SQLException {
        System.out.println(UPDATE_USERS_SQL);
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager.getConnection(url, user, password);

            // Step 2: Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL)) {

            // Set the parameters for the query
            preparedStatement.setString(1, "Ram");
            preparedStatement.setInt(2, 1);

            // Step 3: Execute the update query
            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println("Number of rows updated: " + rowsAffected);
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e : ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

Explanation:

• PreparedStatement: We use PreparedStatement to securely pass parameters to the SQL query.
• setString(1, "Ram"): This sets the first parameter (the new name) in the SQL query to "Ram".
• setInt(2, 1): This sets the second parameter (the ID) in the SQL query to 1.
• executeUpdate(): Executes the update and returns the number of rows affected.

Step 4: Running the Java Program

To Update a Record:

1. Run the main() method in your IDE.
2. The program will update the name of the user with ID 1 to "Ram".

Sample Output:

UPDATE users SET name = ? WHERE id = ?;
Number of rows updated: 1

If the update is successful, it will print the number of rows affected (in this case, 1).

Handling SQL Exceptions

The printSQLException() method helps handle and debug SQL exceptions by printing detailed error information:

public static void printSQLException(SQLException ex) {
    for (Throwable e : ex) {
        if (e instanceof SQLException) {
            e.printStackTrace(System.err);
            System.err.println("SQLState: " + ((SQLException) e).getSQLState());
            System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
            System.err.println("Message: " + e.getMessage());
            Throwable t = ex.getCause();
            while (t != null) {
                System.out.println("Cause: " + t);
                t = t.getCause();
            }
        }
    }
}

Conclusion

In this tutorial, we demonstrated how to update a record in a PostgreSQL database using the JDBC API in Java. You learned how to establish a connection, use a PreparedStatement to execute the UPDATE query, and handle SQL exceptions properly.

Key Takeaways:

• Use PreparedStatement for secure and efficient SQL updates.
• Always handle SQL exceptions to debug issues during the update process.

By following this guide, you can easily update records in your PostgreSQL database using Java.