Java JDBC Tutorial – Part 7: JDBC Transactions with MySQL

Hi this is Chad (Shod) with
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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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 to download the Java source code used in this

Leave a Reply

Your email address will not be published. Required fields are marked *