
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!
- Create a Zip file using Java Code programmatically
- Eclipse : A java Runtime Environment (JRE) or Java Development kit (JDK) must be available
- How to Sort a LinkedList in Java
- Loading class com.mysql.jdbc.Driver. This is deprecated. The new driver class is com.mysql.cj.jdbc.Driver
- How to declare and initialize Array in Java Programming
- [Fix] java: integer number too large compilation error
- Java JDBC Connection with MySQL Driver in VS Code + Troubleshooting
- Reading .xls and .xlsx Excel file using Apache POI Java Library
- IntelliJ: Error: Could not find or load main class, java.lang.ClassNotFoundException
- How to get Client IP address using Java Code Example
- Truncate table using Java JDBC Example
- Struts 2 : There is no Action mapped for namespace [/] and action name [form] associated with context path [/proj]
- How to get file path in Idea IntelliJ IDE
- Java Generics explained with simple definition and examples
- Java SE 8 Update 301 available with various bug fixes and security improvements
- Java: Collect Stream as ArrayList or LinkedList
- Java JDBC Connection with PostgreSQL Driver Example
- How to check if Java main thread is alive
- How to fix Java nio NoSuchFileException wile reading a file
- Java 8+ get Day of the Week Examples with LocalDateTime, DateTime, ZonalDateTime and Instant classes
- Ways to Convert Integer or int to Long in Java
- [Java] How to throws Exception using Functional Interface and Lambda code
- [Fix] Spring Boot: mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
- Java: The value of the local variable string is not used
- Java JDBC: Insert Java 8 LocalDate and Time using PreparedStatement
- Fail to connect to camera service Android java RuntimeException - Android
- Run DynamoDB Local on Docker Container - Docker
- [Fix] Microsoft teams error code 503 - Failed to reach https - Teams
- [Fix] Microsoft Remote Desktop Error Code: 0x204 - Microsoft
- Error : Invalid key hash.The key hash does not match any stored key hashes - Android
- View in File Explorer option missing in SharePoint Online Edge browser - SharePoint
- Sublime Text 3 spell check shortcut - Sublime
- How to Kill service running on port using terminal command - HowTos