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.
Facing issues? Have Questions? Post them here! I am happy to answer!
Rakesh (He/Him) has over 14+ years of experience in Web and Application development. He is the author of insightful How-To articles for Code2care.
Follow him on: X
You can also reach out to him via e-mail: rakesh@code2care.org
- Get the current timestamp in Java
- Java Stream with Multiple Filters Example
- Java SE JDBC with Prepared Statement Parameterized Select Example
- Fix: UnsupportedClassVersionError: Unsupported major.minor version 63.0
- [Fix] Java Exception with Lambda - Cannot invoke because object is null
- 7 deadly java.lang.OutOfMemoryError in Java Programming
- How to Calculate the SHA Hash Value of a File in Java
- Java JDBC Connection with Database using SSL (https) URL
- How to Add/Subtract Days to the Current Date in Java
- Create Nested Directories using Java Code
- Spring Boot: JDBCTemplate BatchUpdate Update Query Example
- What is CA FE BA BE 00 00 00 3D in Java Class Bytecode
- Save Java Object as JSON file using Jackson Library
- Adding Custom ASCII Text Banner in Spring Boot Application
- [Fix] Java: Type argument cannot be of primitive type generics
- List of New Features in Java 11 (JEPs)
- Java: How to Add two Maps with example
- Java JDBC Transition Management using PreparedStatement Examples
- Understanding and Handling NullPointerException in Java: Tips and Tricks for Effective Debugging
- Steps of working with Stored Procedures using JDBCTemplate Spring Boot
- Java 8 java.util.Function and BiFunction Examples
- The Motivation Behind Generics in Java Programming
- Get Current Local Date and Time using Java 8 DateTime API
- Java: Convert Char to ASCII
- Deep Dive: Why avoid java.util.Date and Calendar Classes
- Ways Compare Dates in Java Programming with Examples - Java
- [Fix] Microsoft AADSTS900561: The endpoint only accepts POST requests. Received a GET request. - Microsoft
- Check help on commands while being on sftp> prompt - FTP
- How to enable Wifi Calling on iPhone - iOS
- Java Interview Questions - Set 2: Core Principles - Java
- How to Generate SHA-512 digest in Notepad++ - NotepadPlusPlus
- How to write hello world different languages syntax - HowTos
- Permanently Set or Change $JAVA_HOME on Mac (macOS) - MacOS