Transactions in JDBC

Transactions in JDBC are used to manage a group of SQL operations as a single unit to ensure data consistency and reliability. They help maintain data integrity by applying all changes together or rolling them back in case of failure.

• Groups multiple SQL operations into a single unit of execution
• Ensures reliable and controlled database operations
• Helps manage changes using commit and rollback mechanisms

Understanding ACID Properties

ACID properties define the fundamental principles that ensure reliable and consistent transaction processing in databases.

• Atomicity: Ensures that all operations within a transaction are treated as a single unit—either all succeed or none do. If any part fails, the entire transaction is rolled back.
• Consistency: Guarantees that the database remains in a consistent state before and after the transaction.
• Isolation: Ensures that transactions are executed independently, without interference from other concurrent transactions.
• Durability: Once a transaction is committed, changes are permanent even in case of a system crash.

Auto-Commit in JDBC

By default JDBC Connection is in auto-commit mode, then every SQL statement is committed to the database upon its completion. It is fine for simple applications, but there are three reasons why you may want to turn off the auto-commit and manage your own transactions

• To increase performance.
• To maintain the integrity.
• To use distributed transactions.

Manual Transaction Management

We can manage transactions manually by turning off auto-commit and using commit() and rollback() methods.

Commit and Rollback

In Java, the commit() and rollback() methods play an essential role in database transactions, ensuring atomicity, consistency, isolation, and durability (ACID properties).

Commit: commit() saves all changes made during the transaction to the database.

conn.commit( );

Rollback: rollback() discards all changes made since the last commit, ensuring atomicity.

conn.rollback( );

JDBC Transaction Example Step-by-Step Implementation

This example demonstrates how to execute multiple updates in a single transaction, ensuring either all updates succeed or none do.

Step 1: Create a data base table

Output table:

Step 2: Add MySQL JDBC Driver to Your Project

To interact with a database, you need to add the MySQL JDBC driver to your project. If you are using Maven, add the following dependency to your pom.xml file.

Step 3. Create EmployeeTransactionDemo class

EmployeeTransactionDemo describe how to perform a JDBC transaction using Java. It shows how two related updates to the employee table can be executed as a single unit of work, either both succeed, or neither does.

EmployeeTransactionDemo.java:

Explanation:

• conn.setAutoCommit(false): groups multiple queries into one transaction.
• PreparedStatement safely runs SQL with parameters and avoids SQL injection.
• stmt1.setString(...) and stmt2.setString(...) fill placeholders to update Aman’s address and Rohit’s email.
• conn.commit(): saves both updates together only if both succeed.
• If any error occurs, conn.rollback() undoes all changes.
• finally ensures the connection is always closed.

Step 4: Run Program:

After running the program, the console will display a message confirming whether the transaction was committed or rolled back

Database Table After Transaction:

The changes to the employee table (updates to Aman’s address and Rohit’s email) will be reflected in the database only if the transaction was successfully committed.