Java 17 Recipes

12-8. Performing Transactions
 Problem
The way in which your application is structured requires sequential processing of tasks. 
One task depends on another, and each process performs a different database action. If 
one of the tasks along the way fails, the database processing that has already occurred 
needs to be reversed.
 Solution
Set your Connection object autocommit to false and perform the transactions you want 
to complete. Once you’ve successfully performed each of the transactions, manually 
commit the Connection object; otherwise, roll back each of the transactions that have 
taken place. The following code example demonstrates transaction management. If you 
look at the main() method of the TransactionExample class, you see that the Connection 
object’s autoCommit() preference has been set to false so that database statements are 
grouped together to form one transaction. If all the statements within the transaction 
are successful, the Connection object is manually committed by calling the commit() 
method; otherwise, all the statements are rolled back by calling the rollback() method. 
By default, autoCommit is set to true, which automatically treats every statement as a 
single transaction.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.java17recipes.chapter12.recipe12_01.CreateConnection;
public class TransactionExample {
public static Connection conn = null;
public static void main(String[] args) {
boolean successFlag = false;
try {
CreateConnection createConn = new CreateConnection();
conn = createConn.getConnection();
conn.setAutoCommit(false);
Chapter 12 Working With Databases

---

456
queryDbRecipes();
successFlag = insertRecord(
"12-6",
"Simplifying and Adding Security with Prepared 
Statements",
"Working with Prepared Statements");
if (successFlag == true){
successFlag = insertRecord(
null,
"Simplifying and Adding Security with Prepared 
Statements",
"Working with Prepared Statements");
}
// Commit Transactions
if (successFlag == true)
conn.commit();
else
conn.rollback();
conn.setAutoCommit(true);
queryDbRecipes();
} catch (java.sql.SQLException ex) {
System.out.println(ex);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
Chapter 12 Working With Databases

---

457
private static void queryDbRecipes(){
String sql = "SELECT ID, RECIPE_NUMBER, RECIPE_NAME, DESCRIPTION " +
"FROM RECIPES";
try(PreparedStatement pstmt = conn.prepareStatement(sql);) {
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString(2) + ": " +
rs.getString(3) + " - " + rs.getString(4));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
private static boolean insertRecord(String recipeNumber,
String title,
String description){
String sql = "INSERT INTO RECIPES VALUES(" +
"NULL, ?,?,?)";
boolean success = false;
try(PreparedStatement pstmt = conn.prepareStatement(sql);) {
pstmt.setString(1, recipeNumber);
pstmt.setString(2, title);
pstmt.setString(3, description);
pstmt.executeUpdate();
System.out.println("Record successfully inserted.");
success = true;
} catch (SQLException ex){
success = false;
ex.printStackTrace();
}
return success;
}
}
Chapter 12 Working With Databases

---

458
The following is the output.
12-2: Connecting to a Database - DriverManager and DataSource Implementations
12-3: Handling SQL Exceptions - Using SQLException
Record successfully inserted.
java.sql.SQLIntegrityConstraintViolationException: Column 'recipe_number' 
cannot be null
at mysql.connector.java@8.0.27/com.mysql.cj.jdbc.exceptions 
.SQLError.createSQLException(SQLError.java:117)
at mysql.connector.java@8.0.27/com.mysql.cj.jdbc.exceptions 
.SQLExceptionsMapping.translateException(SQLExceptionsMapping 
.java:122)
at mysql.connector.java@8.0.27/com.mysql.cj.jdbc 
.ClientPreparedStatement.executeInternal(ClientPreparedStatement 
.java:953)
at mysql.connector.java@8.0.27/com.mysql.cj.jdbc 
.ClientPreparedStatement.executeUpdateInternal(ClientPrepared 
Statement.java:1098)
at mysql.connector.java@8.0.27/com.mysql.cj.jdbc 
.ClientPreparedStatement.executeUpdateInternal(ClientPrepared 
Statement.java:1046)
at mysql.connector.java@8.0.27/com.mysql.cj.jdbc 
.ClientPreparedStatement.executeLargeUpdate(ClientPrepared 
Statement.java:1371)
at mysql.connector.java@8.0.27/com.mysql.cj.jdbc 
.ClientPreparedStatement.executeUpdate(ClientPreparedStatement 
.java:1031)
at org.java17recipes.chapter12.recipe12_08.Transaction 
Example.insertRecord(TransactionExample.java:83)
at org.java17recipes.chapter12.recipe12_08.TransactionExample 
.main(TransactionExample.java:29)
12-1: Installing MySQL - Downloading and installation of a MySQL Database
12-2: Connecting to a Database - DriverManager and DataSource Implementations
12-3: Handling SQL Exceptions - Using SQLException
In the end, if any of the statements fails, all transactions are rolled back. However, if 
all the statements execute properly, everything is committed.
Chapter 12 Working With Databases

---

459