Transaction Management in java is required when we are dealing with relational databases. We use JDBC API for database operations and today we will learn how to use JDBC transaction management. In the JDBC Tutorial we learned how we can use JDBC API for database connectivity and execute SQL queries. We also looked at the different kind of drivers and how we can write loosely couple JDBC programs that helps us in switching from one database server to another easily.

Transaction Management in Java JDBC

This tutorial is aimed to provide details about JDBC Transaction Management and using JDBC Savepoint for partial rollback.

By default when we create a database connection, it runs in auto-commit mode. It means that whenever we execute a query and it’s completed, the commit is fired automatically. So every SQL query we fire is a transaction and if we are running some DML or DDL queries, the changes are getting saved into database after every SQL statement finishes.

Sometimes we want a group of SQL queries to be part of a transaction so that we can commit them when all the queries runs fine. If we get any exception, we have a choice of rollback all the queries executed as part of the transaction.

Let’s understand with a simple example where we want to utilize JDBC transaction management support for data integrity. Let’s say we have UserDB database and Employee information is saved into two tables. For my example, I am using MySQL database but it will run fine on other relational databases as well such as Oracle and PostgreSQL.

The tables store employee information with address details in tables, DDL scripts of these tables are like below.

Our final project looks like below image, we will look into each of the classes one by one.

JDBC-Transaction-Management-Savepoint-Project

As you can see that I have MySQL JDBC jar in the project build path, so that we can connect to the MySQL database.

DBConnection.java

DBConnection is the class where we are creating MySQL database connection to be used by other classes.

EmployeeJDBCInsertExample.java

This is a simple JDBC program where we are inserting user provided values in both Employee and Address tables created above. Now when we will run this program, we will get following output.

As you can see that SQLException is raised when we are trying to insert data into Address table because the value is bigger than the size of the column.

If you will look at the content of the Employee and Address tables, you will notice that data is present in Employee table but not in Address table. This becomes a serious problem because only part of the data is inserted properly and if we run the program again, it will try to insert into Employee table again and throw below exception.

So there is no way we can save the data into Address table now for the Employee. So this program leads to data integrity issues and that’s why we need transaction management to insert into both the tables successfully or rollback everything if any exception arises.

JDBC Transaction Management

JDBC API provide method setAutoCommit() through which we can disable the auto commit feature of the connection. We should disable auto commit only when it’s required because the transaction will not be committed unless we call the commit() method on connection. Database servers uses table locks to achieve transaction management and it’s resource intensive process. So we should commit the transaction as soon as we are done with it. Let’s write another program where we will use JDBC transaction management feature to make sure data integrity is not violated.

EmployeeJDBCTransactionExample.java

Please make sure you remove the earlier inserted data before running this program. When you will run this program, you will get following output.

The output is similar to previous program but if you will look into the database tables, you will notice that data is not inserted into Employee table. Now we can change the city value so that it can fit in the column and rerun the program to insert data into both the tables. Notice that connection is committed only when both the inserts executed fine and if any of them throws exception, we are rolling back complete transaction.

JDBC Savepoint

Sometimes a transaction can be group of multiple statements and we would like to rollback to a particular point in the transaction. JDBC Savepoint helps us in creating checkpoints in a transaction and we can rollback to that particular checkpoint. Any savepoint created for a transaction is automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question.

Let’s say we have a Logs table where we want to log the messages that employee information is saved successfully. But since it’s just for logging, if there are any exceptions while inserting into Logs table, we don’t want to rollback the entire transaction. Let’s see how we can achieve this with JDBC savepoint.

EmployeeJDBCSavePointExample.java

The program is very simple to understand. As you can see that I am creating the savepoint after data is inserted successfully into Employee and Address tables. If SQLException arises and savepoint is null, it means that exception is raised while executing insert queries for either Employee or Address table and hence I am rolling back complete transaction.

If savepoint is not null, it means that SQLException is coming in inserting data into Logs table, so I am rolling back transaction only to the savepoint and committing it.

If you will run above program, you will see below output.

If you will check database tables, you will notice that the data is inserted successfully in Employee and Address tables. Note that we could have achieved this easily by committing the transaction when data is inserted successfully in Employee and Address tables and used another transaction for inserting into logs table. This is just an example to show the usage of JDBC savepoint in java programs.

Download project from above link and play around with it, try to use multiple savepoints and JDBC transactions API to learn more about it.

By admin

Leave a Reply