July 31, 2023
Summary: in this tutorial, you will learn about the JDBC PostgreSQL transaction using JDBC transaction API.
Table of Contents
In some cases, you do not want one SQL statement to take effect unless another one is completed. For example, when you want to insert a new actor, you also want to assign the film that actor participates.
To make sure that both actions take effect nor neither actions occur, you use a transaction.
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.
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.
We will insert a new actor into the actor table and assign the actor a film specified by a film id.
First, create a class that represents an actor as follows:
package net.rockdata.tutorial;
/**
*
* @author rockdata.net
*/
public class Actor {
/**
* actor's first name
*/
private String firstName;
/**
* actor's last name
*/
private String lastName;
/**
* initialize an actor with the first name and last name
*
* @param firstName
* @param lastName
*/
public Actor(String firstName, String lastName) {
this.firstName = firstName;
this.lastName = lastName;
}
/**
* initialize an actor
*/
public Actor() {
}
/**
* @return the firstName
*/
public String getFirstName() {
return firstName;
}
/**
* @param firstName the firstName to set
*/
public void setFirstName(String firstName) {
this.firstName = firstName;
}
/**
* @return the lastName
*/
public String getLastName() {
return lastName;
}
/**
* @param lastName the lastName to set
*/
public void setLastName(String lastName) {
this.lastName = lastName;
}
}
Then, create an App class for the demonstration.
/**
* need to fix the db ALTER TABLE film_actor ALTER COLUMN actor_id TYPE INT;
* ALTER TABLE film_actor ALTER COLUMN film_id TYPE INT;
*/
package net.rockdata.tutorial;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
*
* @author rockdata.net
*/
public class App {
private final String url = "jdbc:postgresql://localhost/dvdrental";
private final String user = "postgres";
private final String password = "postgres";
/**
* Connect to the PostgreSQL database
*
* @return a Connection object
* @throws java.sql.SQLException
*/
public Connection connect() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
/**
* Close a AutoCloseable object
*
* @param closable
*/
private App close(AutoCloseable closeable) {
try {
if (closeable != null) {
closeable.close();
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
return this;
}
/**
* insert an actor and assign him to a specific film
*
* @param actor
* @param filmId
*/
public void addActorAndAssignFilm(Actor actor, int filmId) {
Connection conn = null;
PreparedStatement pstmt = null;
PreparedStatement pstmt2 = null;
ResultSet rs = null;
// insert an actor into the actor table
String SQLInsertActor = "INSERT INTO actor(first_name,last_name) "
+ "VALUES(?,?)";
// assign actor to a film
String SQLAssignActor = "INSERT INTO film_actor(actor_id,film_id) "
+ "VALUES(?,?)";
int actorId = 0;
try {
// connect to the database
conn = connect();
conn.setAutoCommit(false);
// add actor
pstmt = conn.prepareStatement(SQLInsertActor,
Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, actor.getFirstName());
pstmt.setString(2, actor.getLastName());
int affectedRows = pstmt.executeUpdate();
if (affectedRows > 0) {
// get actor id
rs = pstmt.getGeneratedKeys();
if (rs.next()) {
actorId = rs.getInt(1);
if (actorId > 0) {
pstmt2 = conn.prepareStatement(SQLAssignActor);
pstmt2.setInt(1, actorId);
pstmt2.setInt(2, filmId);
pstmt2.executeUpdate();
}
}
} else {
// rollback the transaction if the insert failed
conn.rollback();
}
// commit the transaction if everything is fine
conn.commit();
System.out.println(
String.format("The actor was inserted with id %d and "
+ "assigned to the film %d", actorId, filmId));
} catch (SQLException ex) {
System.out.println(ex.getMessage());
// roll back the transaction
System.out.println("Rolling back the transaction...");
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
} finally {
this.close(rs)
.close(pstmt)
.close(pstmt2)
.close(conn);
}
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
App app = new App();
// OK transaction
app.addActorAndAssignFilm(new Actor("Bruce", "Lee"), 1);
// Failed transaction
// app.addActorAndAssignFilm(new Actor("Lily", "Lee"), 9999);
}
}
How the App class works.
The connect() method establishes a connection to the dvdrental database and returns a Connection object.
The close() method closes a closable object such as Resultset, Statement, and Connection.
The addActorAndAssignFilm() method inserts a new actor and assigns a film to the actor within a transaction.
- First, insert a new actor into the actor table.
- Next, get the id of the newly inserted actor
- Then, assign the actor to a film by inserting a new row into the film_actor table.
- After that, if both step 2 and 3 succeeded, commit the transaction. Otherwise, rollback the transaction
- Finally, close the ResultSet, PreparedStatement, and Connection objects.
If we execute the program with the first scenario, we get the following result:
run:
The actor was inserted with id 217 and assigned to the film 1
BUILD SUCCESSFUL (total time: 2 seconds)
We can verify it by querying the actor table:
SELECT
actor_id,
first_name,
last_name
FROM
actor
ORDER BY
actor_id DESC;
and also the film_actor table:
SELECT
actor_id,
film_id
FROM
film_actor
WHERE
actor_id = 217;
Now if we insert a new actor and assign her to a film that does not exist, it issues the following error messages:
run:
ERROR: insert or update on table "film_actor" violates foreign key constraint "film_actor_film_id_fkey"
Detail: Key (film_id)=(9999) is not present in table "film".
Rolling back the transaction...
BUILD SUCCESSFUL (total time: 0 seconds)
The transaction is rolled back and nothing is inserted into the actor and film_actor tables.
In this tutorial, you have learned how to perform a transaction to ensure the integrity of data in the PostgreSQL database using JDBC transaction API.