Java JDBC PostgreSQL Transaction Example

In this tutorial, you will learn how to use JDBC transactions to manage multiple SQL statements in PostgreSQL. A transaction ensures that either all the SQL operations in a set are executed successfully or none of them are applied, maintaining the integrity of your data.

What You’ll Learn:

• How to disable auto-commit mode in JDBC.
• How to commit or roll back a transaction.
• How to handle exceptions in a transaction.
• Best practices for managing transactions in Java.

Technologies Used:

• 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 the PostgreSQL database, you need the PostgreSQL JDBC driver. You can add 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'

Step 2: PostgreSQL Database Setup

Before we start working with transactions, ensure you have a PostgreSQL database set up with a users table. Here is an example structure of the users table:

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

Step 3: JDBC Transactions in PostgreSQL

A transaction is a sequence of one or more SQL statements executed as a single unit. Either all of them succeed, or none of them are applied.

Steps for a JDBC Transaction:

1. Disable auto-commit mode.
2. Execute multiple SQL statements.
3. Commit the transaction if all statements are successful.
4. Roll back the transaction in case of any failure.

Key Methods:

• setAutoCommit(false): Disables auto-commit mode so multiple SQL statements can be executed as a single unit.
• commit(): Commits the transaction, applying all changes.
• rollback(): Rolls back the transaction, reverting changes if any issue occurs.

Java Program: Performing a Transaction with JDBC

Here’s a complete Java program that demonstrates inserting and updating data in a PostgreSQL database as part of a transaction.

package com.example.postgresql;

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

public class JDBCTransactionExample {

    // SQL statements for inserting and updating a user
    private static final String INSERT_USERS_SQL = "INSERT INTO users " +
            "  (id, name, email, country, password) VALUES (?, ?, ?, ?, ?);";
    private static final String UPDATE_USERS_SQL = "UPDATE users SET name = ? WHERE id = ?;";

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

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            // Step 1: Disable auto-commit mode for the transaction
            conn.setAutoCommit(false);

            try (PreparedStatement insertStmt = conn.prepareStatement(INSERT_USERS_SQL);
                 PreparedStatement updateStmt = conn.prepareStatement(UPDATE_USERS_SQL)) {

                // Insert a new user
                insertStmt.setInt(1, 200);
                insertStmt.setString(2, "Tony");
                insertStmt.setString(3, "tony123@gmail.com");
                insertStmt.setString(4, "US");
                insertStmt.setString(5, "secret");
                insertStmt.executeUpdate();

                // Update the user
                updateStmt.setString(1, "Ram");
                updateStmt.setInt(2, 200);
                updateStmt.executeUpdate();

                // Step 2: Commit the transaction
                conn.commit();
                System.out.println("Transaction committed successfully.");

            } catch (SQLException e) {
                printSQLException(e);
                if (conn != null) {
                    try {
                        // Step 3: Roll back the transaction in case of an error
                        System.out.println("Transaction is being rolled back.");
                        conn.rollback();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            }

        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    // Method to print detailed SQL exceptions
    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:

• setAutoCommit(false): Disables auto-commit mode, so the two SQL statements (insert and update) are executed as a single transaction.
• commit(): Commits the transaction, applying all changes to the database.
• rollback(): Reverts the changes if an error occurs during the transaction.
• printSQLException(): Prints detailed error information in case of any SQL exception.

Step 4: Running the Program

To run the program:

1. Compile and run the JDBCTransactionExample class.
2. If both the INSERT and UPDATE statements succeed, you will see the following output:

Transaction committed successfully.

If any error occurs, the transaction will be rolled back, and the output will indicate a rollback.

Conclusion

In this tutorial, we demonstrated how to use JDBC transactions to ensure data integrity when performing multiple SQL operations in a PostgreSQL database. You learned how to disable auto-commit, commit a transaction, and roll back if necessary.

Key Takeaways:

• Always disable auto-commit for transactions that require multiple SQL operations.
• Use commit() to apply changes and rollback() to revert them in case of failure.
• Handle SQL exceptions carefully to ensure that your transactions are managed correctly.

By following this guide, you can confidently use JDBC transactions to manage complex operations in PostgreSQL databases.