In this video we will learn how to use JDBC transactions with MySQL.
Please SUBSCRIBE to this channel
—
Transcript
Time – 00:00
Hi this is Chad (Shod) with luv2code.com. Welcome back to another tutorial on Java JDBC, In this video, we’ll learn how to use transactions with JDBC. We will first define what transactions are, then we’ll learn how to develop transactions with JDBC. For this tutorial, we’ll use the following table: employees. The table also has sample data for testing. I have a SQL script that will create the table and add sample data for you. You can download it from the link below.
Time – 00:32
A transaction is basically a unit of work. You can execute multiple statements together. Based on your application if everything is okay, all of those statements are executed together. This is known as a commit. If there’s a problem then none of the statements are executed. This is known as a rollback. In the diagram, if it is okay to save then we commit the statements to the database. If it is not okay to save then we rollback, effectively throwing away the changes.
Time – 00:58
By default auto commit is set to “true”. You have to explicitly turn it off to set auto commit to “false”. At this point the developer controls the commit and rollback. This is very straightforward. If you want to commit, then you call the commit method. Likewise if you want to rollback, then you call the rollback method. That’s it.
Time – 01:17
Let’s look at this with a code snippet. At the beginning, we set auto commit to false. Then we can perform multiple SQL statements like insert, updates and deletes. Next we can ask the user if it’s okay to save. This is simply a call to the helper method that would get input from the user. It returns a Boolean value of true or false. Then if okay we commit, else we rollback.
Time – 01:40
Let’s switch over to Eclipse and see this in action. I have a simple Java program called Transaction Demo. For the transaction in this example we’re going to do the following: the first thing we’re going to do is delete all HR employees and then we’ll update the Engineering salaries to $300,000. Pretty funny LOL. Let’s walk through the code. The first thing we’ll do is we’ll go through and get a connection to the database, we will turn off auto commit by setting it to false. Then just for sanity’s sake, we’re going to have a, make use of a helper routine called shell salaries and it’s going to print up the salaries for the HR and the Engineering departments. It will actually connect to the database and perform a query and get those results accordingly and display them.
Time – 02:24
Then we move into our actual transaction part of it. For our first transaction step we’re going to execute an update. In this case we’re going to actually do a delete from employees where department equals HR. This is where we delete all HR employees. Then we’ll move to the next transaction step and we’ll actually set the salaries to $300,000 for all Engineering employees. Nice feature.
Time – 02:46
Then we’ll tell the user the transaction steps are ready and then we’re going to prompt the user if it’s okay to save. This is just a little simple helper method that’s just going to read the user input. If the user enters “yes” it’s going to return “true”, if the user enters anything else like “no” it will return false. If it’s okay then we will commit the data in the database and we’ll print out “transaction committed”, else we’ll rollback the information. Also, finally as another sanity check we’ll print the salaries after this step of code, so we’ll print out the information for HR and for Engineering.
Time – 03:24
I just ran the application and we’ll just go look at the output here. At the beginning we display the salaries before, so information for the HR department and also the salaries for the Engineering department. It tells us that the transaction steps are ready, is it okay to save, yes or no? At this point I’m going to say “no” and I’ll hit enter. Then it’s going to go through and it’s going to tell us “Hey the transactions were rolled back” and also as a sanity sake, they’re going to display the salaries after.
Time – 03:55
Here’s information for HR so we know we the transaction didn’t execute because we were trying to delete HR employees but they’re still here in our database. A similar thing here for engineering where we’re trying to increase all the salaries to $300,000 but again that’s not their original values so we know that the transaction was rolled back successfully.
Time – 04:14
All right, let’s also verify this in our SQL tool. What I’ll do is I’ll move over to the SQL tool. I have a query here that’s going to select from employees and we’re basically going to select for employees in the HR and Engineering department. I’ll go ahead and execute this query. We can see that we have eight employees that were returned. We have four in the Engineering department, they have their original salaries and also we have people in the HR department that are still there. So we know that there were no changes made to the actual database.
Time – 04:43
Okay let’s go ahead and switch back over to the Eclipse tool and let’s run it and this time let’s apply the transaction. So I’ll just run the application one more time and at this point it’s going to prompt me and here I’ll say “yes” and note the difference. It says transaction committed, the salaries after and show salaries for HR. Hmm, it shows empty because we’ve deleted all of the HR employees. Then also the salaries for engineering, they’re all $300,000. So this transaction was actually applied and committed to the actual database.
Time – 05:15
Again I can confirm this in my MYSQL tool so I’ll just tab over to that window for MYSQL, I’ll run this query one more time. So instead of eight employees I only have four because the HR employees were deleted and note here the salaries for the Engineering department is at $300,000 so we were successful in actually applying all this information to the database. Good job.
Time – 05:38
That wraps up our discussion on transactions with JDBC. We were successful in using transactions for commits and rollbacks. Please subscribe to our channel to view more videos on Java, click the thumbs us to like our video. Also visit our website https://luv2code.com to download the Java source code used in this video.
