How to handle JDBC Transactions?
We have seen in the previous blogs how we can connect to the MySQL database using JDBC. This blog will check the JDBC transactions management.
By default our JDBC connection is in auto-commit mode, which means sql statement is committed by default to the database after completion. But sometimes, we must turn off the auto-commit and do commit manually from the JDBC to manage the transactions.
By managing the transactions we can control when the changes need to be applied to the database. We can commit a single statement or group of statements at one time. If anything fails we can roll back the whole database changes for that particular transaction.
To disable the auto-commit we can use the setAutoCommit() method of the Connection interface object. This method accepts a boolean value true/false. By default value is true we can set it as false to disable the auto-commit.
commit() and rollback() methods:
commit()
Once we are done with the changes and everything is fine we can use this method from the Connection interface to commit the transaction.
rollback():
If we get any exception or if any condition fails we can roll back the transaction and no changes are applied to the database to do so we can use the rollback() method of the Connection interface.
We can check the following simple code for transaction management. We will work on the students’ table which has the data as follows.
First, we will add the record in the table for roll number, name, class, and gender. Then we will update the student’s marks if not less than zero. If any of this we need to roll back the transaction.
- If marks >= 0 then we can consider that all checks are as expected and we can commit the transaction. In this case, one new entry will be added to the table with the updated marks.
- If marks < 0, then we will fail the condition and will roll back the transaction, in this case, no new entry should be added/marks should be updated.
Let’s see the example below and try to run the code for both marks >= 0 and marks < 0.
You can copy code from TransactionManagement and DatabaseConnector
- For example when we are sending marks > 0
From the output below we can see that the student’s entry is added successfully and marks are also updated.
Result from the students’ table after successful insert and update
- When marks < 0
As we can see from the output, since marks are < 0 we are throwing an exception, due to which even if the insert was successful we are rolling back the transaction in the catch block. Hence no new entry will be added to the table for roll_num = 7.
