Java - JDBC PostgreSQL Transaction Example

❮ Previous Chapter Next Chapter ❯

---

In this tutorial, you will learn about the JDBC PostgreSQL transaction using JDBC transaction API.

In some cases, you do not want one SQL statement to take effect unless another one completes it.

By definition, a transaction is a set of statements executed as a single unit. In other words, either all statements executed successfully, or none of them executed.

Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

Technologies used

We use below technologies in this tutorial:

• JDK - 1.8 or later
• PostgreSQL- 42.2.9
• IDE - Eclipse Neon
• JDBC - 4.2

Download PostgreSQL JDBC Driver

To connect to the PostgreSQL database server from a Java program, you need to have a PostgreSQL JDBC driver. You can download the latest version of the driver on the postgresql.org website via the download page.

Add the PostgreSQL JDBC driver jar file to the project classpath.

For maven users:

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

For Gradle users:

// https://mvnrepository.com/artifact/org.postgresql/postgresql
compile group: 'org.postgresql', name: 'postgresql', version: '42.2.9'

PostgreSQL Database Setup

In the previous tutorial, we have seen how to create a "users" table in a PostgreSQL database using Java. We are using the same "users" table in this tutorial to insert rows.

Disable auto-commit mode

When you establish a connection to the PostgreSQL database, it is in auto-commit mode. It means that each SQL statement is treated as a transaction and is automatically committed.

If you want to encapsulate one or more statements in a transaction, you must disable the auto-commit mode. To do this, you call the setAutoCommit() method of the Connection object as follows:

conn.setAutoCommit(false);

It is a best practice to disable the auto-commit mode only for the transaction mode. It allows you to avoid holding database locks for multiple statements.

Commit a transaction

To commit a transaction, you call the commit method of the Connection object as follows:

conn.commit();

When you call the commit() method, all the previous statements are committed together as a single unit.

Rollback a transaction

In case the result of one statement is not what you expected, you can use the rollback() method of the Connection object to aborting the current transaction and restore values to the original values.

conn.rollback();

PostgreSQL JDBC Transaction Example

Let’s take an example of using JDBC API to perform a PostgreSQL transaction.

package net.javaguides.postgresql.tutorial;

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

/**
 * JDBC Transaction Management Example
 * @author Ramesh Fadatare
 *
 */
public class JDBCTransactionExample {
    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 = ?;";

    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
            conn.setAutoCommit(false);

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

                // Create insert statement
                insertStmt.setInt(1, 200);
                insertStmt.setString(2, "Tony");
                insertStmt.setString(3, "[email protected]");
                insertStmt.setString(4, "US");
                insertStmt.setString(5, "secret");
                insertStmt.executeUpdate();

                // Create update statement
                updateStmt.setString(1, "Ram");
                updateStmt.setInt(2, 200);
                updateStmt.executeUpdate();

                // STEP 2 - Commit insert and update statement
                conn.commit();
                System.out.println("Transaction is commited successfully.");
            } catch (SQLException e) {
                printSQLException(e);
                if (conn != null) {
                    try {
                        // STEP 3 - Roll back transaction
                        System.out.println("Transaction is being rolled back.");
                        conn.rollback();
                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                }
            }
        } 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();
                }
            }
        }
    }
}

Output:

Transaction is commited successfully.

In this tutorial, you have learned how to perform a transaction to ensure the integrity of data in the PostgreSQL database using the JDBC transaction API. 

Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

---

❮ Previous Chapter Next Chapter ❯