alright so we're at the end of the last
video we created we ended up finishing
off the insert song method so lets now
go just go through to what we've
actually done here so we start over
here by starting a transaction and we do
that by turning off the autocommit
behavior of the connection now the
default behavior you may recall is to
commit every change and the way the
database does that is by running every
update delete insert statement as a
transaction
now when we want to write a series of
sql statements as a transaction we
signal that by turning off the default
behavior from that point forward we're
actually in a new transaction so after
calling said auto commit to
turn off the default behavior and
starting a new transaction we perform
all the actions we have to take to
insert the song so we start off by
inserting the artist and then we insert
the album using the artist ID that was
returned from the insert artist method
once we get to that point we can now
insert the song now we do that by
setting the values in the insert into
songs preparedstatement these three
lines here and then we actually call
the execute update to check the only
one row was affected and that's the row
that was added for the song
now if that's the case we're actually
done we inserted the song and all
its associated information so we want to
commit the changes and we're doing that
on this line and once you've done that
also ends the transaction now if something
goes wrong and an exception is thrown
we are calling this connection . roll
back down here and that's going to back
out any changes we've made since
starting the transaction and it will
also end the transaction and down
here in the finally block actually
setting the set auto commit method
or calling it with a value of true to
return to the default autocommit
Behavior now because using transactions
involves getting database locks its best
practice to only turn off auto commit for
the duration of a transaction and to
turn it back on again immediately
afterwards once it's turned back on
unless we turn it off again any inserts
updates or deletes will be auto
committed as soon as they've completed
now resetting the default behavior in
the finally block ensures that it will
be done whether the transactions
succeeds but also if it fails so hopefully now
at this point you can see why we have
the insert artist and insert album
methods both throw the sql exception
rather than handling it if you recall both
of these methods were thrown the sql
exception and the two
cases here where theirs a problem its
inserting or there's a problem with the
generated keys we are throwing exceptions
the sql exception there likewise for
the insert album so that's why these
methods aren't handling the sql
exceptions were just throwing back to
the inserts song method where these are
ultimately called from so what it means
is that all the code that makes up the
transaction is actually now in one block
quite easy to read so its really yeah
this block here that's updating
potentially the three tables if we had
to add checks for negative IDs being
returned the code will look a lot messier
but since we don't have to do that we
can clearly now see that the transaction
involves three insertions alright so
read a lot of code here the big test is
going to try it out and this actually
be void because we're not actually
returning the id i think that
came about because I copy the
previous two methods which returned the
ID of course in this case insert song
that's calling those other methods but
in itself isn't returning anything that
fixes that error
alright again so let's now try this out
and go to the main dot java file and
just before the data source . close
let's now try it out so i'm going to
insert the song touch of grey by the
Grateful Dead and that's on track one on
the album in the dark so to do that
we're going to do...
....and the other thing I need to do
is make this a public method the other
two were private because it only called
by the insert song method but the song
inserts on within itself should be
public so that we can access it
outside of the class so that's going to be
insert song...
....
....let's open up our db browser
and we look at the artists table open
the database first scroll
down to the end okay we got total of
201 theirs no grateful dead
there at the moment and we have a look
also with the album's table scroll down
to the end for that you can see we got 439
entries there and song table lets have a
look at that right down to the end we got
5350 songs
alright let's try running this and see
what happens
we need to just finish this last song
we just put...
and we got that error about could not find
the artist for the song but that's this
previous query up here and actually
that's not going to work of course the
reason for that is we got this return
statement up here on line 62 so
consequently the code below it isn't
gonna be executed
let's just run it again and rather than
commenting out the code I just put in the
valid song....
....
we got a message here
resetting default commit behavior
that's telling us its got now to the code
in data source in the finally clause
we are setting default commit behavior
so hopefully that means that the records
are being added to our database so lets
actually check that will go back to DB
browser will do a refresh here you can see now
we got a new song added 5351
that's a good sign
we can see touch of grey album id is
440 track number one if we
have a look at albums 440 that was the
new entry 439 last time down the end
they're entry 440 is now in the
dark you can see 202 is now the artist
ID we got artist 202 entries and theirs
grateful dead now showings as 202 so that's
awesome so that's actually working and
again we got the message here resetting
default commit behavior which told us
that finally Claus code did get executed
and we didn't see any other exceptions
or any other printouts to indicate
there was a problem and the records
got add ok alright so let's actually try
adding another one just to make sure
that we're on track here going to go back
and run it again
again you could comment out this code if
you wanted to so you won't be asked this every
time you run it
we're trying to add the same record
again so actually go back and have a look at
that
noting this time the artist still 202
so its found the
artist and not try to add it again which is
exactly what we wanted and for albums
to make sure we still got 404 we still
got in the dark and the right entries
there and then also for songs this time
we have got another entry there but the
reason for that was we didn't actually
add a clause to actually query the song
so if we go back and have a look at
data source method we didn't attempt to do
what we did in these other two methods
we actually queried it and look for the
result first before trying to insert it
and insert song method we just went
straight into the process of inserting
the record that would be something that
you would do ordinarily when your inserting a
song you check to see whether the
song was on file first and then we
started work on that go to the top
there
we got our insert in there we only got
the two queries for query artist and
query album so you need to add another
constant here for query song so you need
to execute that additional query if you
want to actually check to see whether
the song existed or not but I didn't do
that and that's why when I ran it the
first two entries the ones for the
artist and album weren't duplicated in the
database but the song was again because
we didn't try to query the insert song
method first let's try running it with
another entry to make sure it still
working and just what I'll do is cancel
out of that will go back to main again
and just changes this going to try try
Bob Dylan song so like a rolling stone
Bob Dylan and Bob Dylans greatest hits
that is actually track 5 let's run this
what i will do rather than typing again just
comment out this bit of code again just to
save us having to type this code each time
run it and I comment out the important
part the part I want to comment is
is that one as well not just the part that
actually checks run it again
ok this time it's finished go back and
check our database again starting at the
album's next got an album there now Bob Dylan's
greatest hits artist new artist being
added you can see there Bob Dylan and
songs we got 5353 this time and you can
see like a Rolling Stones out of there
alright so that's ok when everything is
working ok but let's actually try and
introduce an error into the insert song
method and we can do that by using an
invalid index when we set one of the
values in the prepared statement
let's do that just to see what actually
happens to go back to data source so with
the three set string ints
here
so with these three entries here let's
just change and we'll put for the
album ID instead of 3 should be
let's make another the index 8 which
we know is wrong and if interesting
enough we actually get this error if we
do that cannot resolve query
parameter 3 so its smart enough to
tell us that we haven't got three
entries we could change that to 3 and it
would work
but its got an error with any more than three
so that's pretty neat how that inspection
works for us put that in there knowing
that it's going to fail and we'll just
go back and we'll try and add a
different entry
let's go back and add one for bird dog by the
Everly Brothers all-time greatest hits
and do track seven let's try running
that this time we have got an error
which would expect array index out of
bounds exception
so what's happening is that the values
were replacing is stored in an array when
we try to set the eighth element in the
album ID we're getting that exception so
it's interesting to note that is that
the invalid indexes 7 which means that
under the covers the indices are being
mapped to a zero-based index which
raises the question of why the jdbc API
uses zero-based indices in the first
place but anyway let's go over to check
our db browser and doing a refresh you
can see we've still got 5353 songs
that's ok we haven't got the new song that
we're trying to add we have a look at
the album's note that we haven't gongo into
this file and do a refresh it was
refresh we have actually got a new
entry looking at albums we've got all
time greatest hits and also for artists
the new artist has been added as well
Emily brothers so what's actually going
on here so why were the artist and the
album records added but the song not
didn't we actually have some code to do
rollback so you might be wondering
why has it done this
let's just go back in deconstruct what
happened
so firstly going back to data source .
java we can look at the code and see
that the code crashed on
on line 403 click on that so you can see why
the song didn't get created because it
crashed on that line because of the
wrong indicies so it didn't get to the stage
of actually doing and execute update so
that's why the song didn't get updated
but what about the fact that the album
and the artist got updated why did they got
updated specifically when again what's
supposed to happen is meant to be doing
a rollback if you look really carefully
at the exception down here you can
probably figure out what's going on
so what we've done up here in the try
block so we've caught the sql
exception but what actually happened
here is you've got an array index out of
bounds exception so the sql exception
wasn't thrown so consequently we
didn't get a roll back here but the next
thing you might be asking is hang on
didn't we set auto commit to false
coming up here yes we did do that on line 397
but as a consequence of going
through this try catch and finally the
finally code block was executed we had
set auto commit true on and that's
got a side effect of doing a commit
so what actually happened was the sql
exception was bypassed because a sql
exception wasn't thrown by the code the
code went to the finally block the
finally block then set autocommit to
true which had the side effect of
committing the changes and that's why
the artist and the album were both save
so that's clearly not what we want to
happen in this scenario so how do we
actually get around this what we really
want to do here instead of just a sql
exception we want to catch all
exceptions just going to change the
sql exception to exception and that
will catch the fact that this array
index out of bounds exception or a
sql exception or any other exception
for that matter and we should get a
rollback let's go back to our database
first and what we're going to do is just
delete these two entries we got one for
artist click on this id 204 Everly
Brothers delete and same for the album
and go down and select the last entry
all time greatest hits delete
I'm going to come up to file close
database and we're going to click on yes
to save the changes i'm just going to
open the database again and now that
this code for the exception
lets actually run it again and see
whether we get the proper behavior that
we're looking for a proper rollback
going to click on run this time you can
see what happens we got insert song
exception got performing rollback
resetting default commit behavior so
this time we're actually are login the
fact that we've got an exception will
process the rollback we still go through
during this set auto commit but because
we added the rollback or the roll back
method has been called prior to that
we should find that the changes weren't
added to the database this time lets
going to check them out albums will
refresh this time we've still got 441
records
scroll down and see that the last
successful entry was Bob Dylan's greatest
hits and same for artists refresh to be
sure 203 this time and that's
correct its Bob Dylan again and by the
way this 7 over here the 7 is the invalid
index in case your wonder what that was and
now that has fix our error just to make sure that
things are still working change to 8 back
3 their in the insert songs if we
run this again we got no exception errors
being log there we should be able to
go back to our database do a refresh
Everly Brothers now on the last entry
albums all time greatest hits but most
importantly the songs we got now 5354 so
we've got a new song there bird dog we
know that its from the everly
brothers at this point you now know
how to work with transactions using jdbc
so we use them when we want to perform a
series of sql statements as a unit so
that the changes are only committed to
the database if all the statements ran
successfully now if a statement fails or
something else happens and perhaps lost
connection or the database server goes
down in the middle of performing
transaction and we won't would end up with a
database in an invalid
state will be able to roll back any
changes that have been made or the
connection goes down for some reason the
database will automatically rollback any
changes and end the transaction one final
note here it's also possible to
rollback a transaction to a specific
point rather than running back all the
changes that have been made since the
transaction started so to do this we
need to do is call the connection .
sets save point method which returns a save
point object if we need to roll back the
changes have been made since we created
the save point we passed the save point
to the connection . rollback method in
other words we do a connected . roll back
and we pass that save point object but we
only do this where it makes sense to
rollback any part of a transaction when
something goes wrong
alright so that's transactions in the
next video we're going to start taking a
look at jdbc from within the context of
the graphical user interface type
application see you in the next video