
If you have to execute a series of SQL statements and they are interdependent on each other in such a way that if one of the statements fails, you need to cancel or rollback all statements, in such situation we should use JDBC with Transaction.
What is a Transaction?
A transaction is a set of one or more statements that are executed as a unit, so either all of the statements are executed, or none of the statements is executed.
https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html
Let us see this with the help of the below table books
books table:CREATE TABLE `books` (
`book_id` int NOT NULL AUTO_INCREMENT,
`book_name` varchar(45) NOT NULL,
`book_vendor` varchar(10) DEFAULT NULL,
`no_of_pages` int DEFAULT NULL,
`book_price` decimal(4,2) DEFAULT NULL,
PRIMARY KEY (`book_id`);
books table structure:
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| book_id | int | NO | PRI | NULL | auto_increment |
| book_name | varchar(45) | NO | | NULL | |
| book_vendor | varchar(10) | YES | | NULL | |
| no_of_pages | int | YES | | NULL | |
| book_price | decimal(4,2) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
Now if we have a set of transactions say,
- Transaction 1: Inserts an entry in books table with book_id and book_name.
- Transacation 2: Updates the the inserted record with book_vendor and no_of_pages.
- Transaction 3: Updates the book_price
Example 1: JDBC Example without Transcation Management:
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcExampleWithoutTransactions {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url ="jdbc:mysql://localhost:3306/my_uat";
String userName="root";
String password ="root123";
int bookId = -1;
String bookVendor="Code2care";
String bookName="Dummy Book";
//Transaction 1
String insertRecordBook = "insert into books(book_id,book_name) values(?,?)";
//Transaction 2:
String updateVendorDetails = "update books set book_vendor=?, no_of_pages=? where book_id=?";
//Transaction 3:
String updatePrice = "update books set book_price=? where book_id=?";
String generatedId[] = { "book_id" };
Connection connection = DriverManager.getConnection(url,userName,password);
PreparedStatement transaction1 = connection.prepareStatement(insertRecordBook,generatedId);
PreparedStatement transaction2 = connection.prepareStatement(updateVendorDetails);
PreparedStatement transaction3 = connection.prepareStatement(updatePrice);
transaction1.setObject(1, null);
transaction1.setString(2, bookName);
transaction1.executeUpdate();
ResultSet resultSet = transaction1.getGeneratedKeys();
while(resultSet.next()) {
bookId = resultSet.getInt(1);
System.out.println("Transaction 1 successful: Book_Id:" + bookId);
}
transaction2.setString(1, bookVendor);
transaction2.setInt(2, 250);
transaction2.setInt(3, bookId);
transaction2.executeUpdate();
System.out.println("Transaction 2 successful");
transaction3.setBigDecimal(1, new BigDecimal(25.5));
transaction3.setInt(2, bookId);
transaction3.executeUpdate();
System.out.println("Transaction 2 successful");
}
}
Output:
Transaction 1 successful: Book_Id:1
Transaction 2 successful
Transaction 2 successful
mysql> select * from books;
+---------+------------+-------------+-------------+------------+
| book_id | book_name | book_vendor | no_of_pages | book_price |
+---------+------------+-------------+-------------+------------+
| 1 | Dummy Book | Code2care | 250 | 25.50 |
+---------+------------+-------------+-------------+------------+
1 row in set (0.00 sec)
Scenario:
As you can see we have the filed book_vendor size as 10, what will happen if we try to enter a Vendor name over size 10.
String bookVendor="Dummy Vendor"; //size 12
Output:
Transaction 1 successful: Book_Id:2
Exception in thread "main" com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'book_vendor' at row 1
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
at JdbcExampleWithTransactions.main(JdbcExampleWithTransactions.java:49)
The transaction 1 got completed successfully, transaction 2 threw an exception, and transaction 3 was not executed, yet we ended up with incomplete information into the table!
mysql> select * from books;
+---------+------------+-------------+-------------+------------+
| book_id | book_name | book_vendor | no_of_pages | book_price |
+---------+------------+-------------+-------------+------------+
| 1 | Dummy Book | Code2care | 250 | 25.50 |
| 2 | Dummy Book | NULL | NULL | NULL |
+---------+------------+-------------+-------------+------------+
2 rows in set (0.01 sec)
Example 2: With Transction Management
Rule 1: we need to turn off auto-commit which is off by default.
Rule 2: when an exception accounts we do not commit we rollback.
Example:import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcExampleWithTransactions {
public static void main(String[] args) throws SQLException {
String url ="jdbc:mysql://localhost:3306/my_uat";
String userName="root";
String password ="root123";
int bookId = -1;
String bookVendor="Dummy Vendor";
String bookName="Dummy Book";
//Transaction 1
String insertRecordBook = "insert into books(book_id,book_name) values(?,?)";
//Transaction 2:
String updateVendorDetails = "update books set book_vendor=?, no_of_pages=? where book_id=?";
//Transaction 3:
String updatePrice = "update books set book_price=? where book_id=?";
String generatedId[] = { "book_id" };
Connection connection = null;
try {
connection = DriverManager.getConnection(url,userName,password);
connection.setAutoCommit(false);
PreparedStatement transaction1 = connection.prepareStatement(insertRecordBook,generatedId);
PreparedStatement transaction2 = connection.prepareStatement(updateVendorDetails);
PreparedStatement transaction3 = connection.prepareStatement(updatePrice);
transaction1.setObject(1, null);
transaction1.setString(2, bookName);
transaction1.executeUpdate();
ResultSet resultSet = transaction1.getGeneratedKeys();
while(resultSet.next()) {
bookId = resultSet.getInt(1);
System.out.println("Transaction 1 successful: Book_Id:" + bookId);
}
transaction2.setString(1, bookVendor);
transaction2.setInt(2, 250);
transaction2.setInt(3, bookId);
transaction2.executeUpdate();
System.out.println("Transaction 2 successful");
transaction3.setBigDecimal(1, new BigDecimal(25.5));
transaction3.setInt(2, bookId);
transaction3.executeUpdate();
System.out.println("Transaction 3 successful");
} catch (SQLException e) {
e.printStackTrace();
} finally {
System.out.println("Exception Occurred while doing the transaction, rolling back!");
connection.rollback();
connection.setAutoCommit(true);
}
}
}
Output:
Transaction 1 successful: Book_Id:4
com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'book_vendor' at row 1
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
at JdbcExampleWithTransactions.main(JdbcExampleWithTransactions.java:54)
Exception Occurred while doing the transasction, rolling back
As you can see the transaction was rolledbacked and there was no entry added to the database.

Have Questions? Post them here!
- Drop table using Java JDBC Template
- Java - Check if array contains the value
- YAML Parser using Java Jackson Library Example
- Java Jackson ObjectMapper Class with Examples
- Get Client IP address from HTTP Response in Java
- How to Word-Warp Console logs in IntelliJ
- Exception in thread main java.lang.NoClassDefFoundError: package javaClass
- Setting Java_Home Environment variable on Windows Operating System
- Fix: Maven - Failed to execute goal - Compilation failure - Source/Target option 5 is no longer supported. Use 7 or later
- Java SE JDBC Select Statement Example
- How to extract Java Jar/War/Ear files in Linux
- java: unclosed string literal [Error]
- [Solution] Exception in thread main java.util.EmptyStackException
- Read YAML file Java Jackson Library
- What Java version is used for Minecraft 1.18
- [Java] How to throws Exception using Functional Interface and Lambda code
- [Program] How to read three different values using Scanner in Java
- Java 8 Predicate Functional Interface Examples
- Display Era details (AD BC) in Java Date using SimpleDateFormat
- Convert String Date to Date Object in Java
- Struts 2 Hello World Example in Eclipse
- Read a file using Java 8 Stream
- Java - How to set custom thread name?
- IntelliJ: Error: Could not find or load main class, java.lang.ClassNotFoundException
- java: ']' expected error [fixed]
- Disable Fading Edges Scroll Effect Android Views - Android
- java: unclosed string literal [Error] - Java
- How to add sleep in Powershell Script - Powershell
- ActivityManager Warning: Activity not started, its current task has been brought to the front - Android
- TypeError: must be str, not int [Fix Python] - Python
- Java - How to set custom thread name? - Java
- Skip Test Maven while creating package command - Java
- SharePoint Designer Workflow error - Coercion Failed: Input cannot be null for this coercion - SharePoint