Java JDBC Transition Management using PreparedStatement Examples


Post Banner

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.

JDBC Transcation Management with Example


Have Questions? Post them here!
Advertisements
Try Out Code2care Dev Tools:

Advertisements

Advertisements
Code2care is an initiative to publish and share varied knowledge in programming and technical areas gathered during day-to-day learnings and development activities.

Students and software developers can leverage this portal to find solutions to their various queries without re-inventing the wheel by referring to our easy to understand posts. Technical posts might include learnings, tutorials, trouble-shooting steps, video tutorials, code snippets, how-to, blogs, articles, etc.

🎉 We are celebrating the 10th years of Code2care! Thank you for all your support!

We strongly support Gender Equality & Diversity.