Java JDBC Transition Management using PreparedStatement Examples

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,

1. Transaction 1: Inserts an entry in books table with book_id and book_name.
2. Transacation 2: Updates the the inserted record with book_vendor and no_of_pages.
3. 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.