ok so continuing on from the last video
we ended up talking about the fact that
a malicious user can't inject sql into
the statement and that's because
anything substituted for a placeholder
is treated as a single literal value
won't be interpreted as sql but moving
on from that
let's say that we wanted to use or
within a prepared statement how we do
that what I'm talking about is some sql
code on these lines so select....
....
think of it this way for each value we
need a placeholder we can't substitute
more than one value for one place holder
and we can't inject sql and also we can
only substitute values so we can't use
placeholders for things like table and
column names and that's because in order
to pre compile the statements the
database needs to know which table we
are querying and which columns we want so the
only information we can postpone until
we want to perform the query are the
values themselves also we can use
prepared statements with insert update
and delete we use placeholders for the
values of the same way would use them
within select statements and we'll
actually see an example of using a search
shortly so it is good practice to use
prepared statements because of the
potential performance benefit and also
because they protect the database against
sql injection attacks of course that
doesn't mean that our database can't be hacked
but just that it cannot be hacked using
that particular type of attack so to
recap we do the following to use a
prepared statement we declare a constant
for the sql statement that contains
the placeholders we create a prepared
statement instance using connection.
prepared statement and we passed that
the SQL statement in a string when we are
ready to perform the query or the insert
update or delete we call the appropriate
setter methods to set the placeholders
to the values we want to use in the
statement we then run the statement
using prepared statement . executes or
prepared statement . execute query
lastly we process the results the same
way we do when using a regular old
statement
that's all for prepared statements now
final topic for this particular video
is going to be transactions now because
the jdbc connection class autocommit
changes by default every time we call
execute to insert update or delete
records those changes are saved to the
database as soon as the sql statement
completes now sometimes that's what we
want but often it's not
as an example let's just say that our
application is a web portal that people
used to do online banking now one of the
things that they can do is transfer
money from one account to another
when they do this we have to run 2
sql statements the first one we need
to update the source account with the
new balance and the second one is to
update the destination account with the
new balance now what would happen if we
executed the first statement
successfully but the second statement
failed for some reason so let's say the
source account had a balance of one
thousand dollars and the destination
account had a balance of $100 the
customer wants to transfer two hundred
dollars into the destination account
now in step one we update the record for
the source account so that now has a
balance of 800 in step two we update the
destination account so that it has a
balance of 300 now step one completes but
step two fails the two hundred dollars
were transferring will effectively go
missing now hopefully we would notice the
failure but then we have to write
code that would handle any failures by
adding the money back to the source
account but what happens if we can't do
that because the second step failed
because the database is down or it can't
be reached
what we do then and here's another scenario
let's suppose we want to add a new
song to the music database
what do we have to do to achieve this
firstly want to add the artist for the
song to the artists table
secondly we want to add the album the
song is on to the album's table and
third we want to add the song to the
songs table but what happens if we
complete step one but then steps two and
three keep failing now maybe we're
trying to do this over the internet and
we lose an internet connection for some
reason
now this wouldn't be as bad a situation
as the banking example but we would end
up with records in the artists or albums
table that aren't associated with any songs
integrity of the data in the database
could be compromised so it would be nice
if one we wanted to accomplish something
that requires multiple sql
statements we could run all the
statements as a single unit now either
all the sql statement was
successfully complete or none of them
would
and this is where transactions come in
so think of a transaction as a
sequence of sql statements that are
treated as a single logical unit now if
any of the statements fail the results of
any previous statements in the
transaction can be rolled back or just
not saved
it's as if they never happened now in
the banking case if step 2 failed then
the balance in the source account would
be rolled back to its original value in
the music case if steps one and two are
successful but step three failes then step
1 and 2 are rolled back so the artist and album
are never saved in the artists and albums
tables
note that when we're speaking about
databases we usually use the term commit
rather than save when referring to
making any changes permanent so we use
commits from this point forward now
database transactions must be acid
compliant they must meet the following
characteristics so the a in acid stands for
atomicity and this relates to what we've
just discussed if a series of sql
statements change the database then
either all the changes are committed or
none of them are the second letter C
stands for consistency before
transaction begins the databases in a
valid state when it completes the
database is still in a valid state
the I in acid stands for isolation so
until the changes committed by a
transaction are completed that won't be
visible to other connections so
transactions can't depend on each
other and lastly the D in acid stands for
durability once the change is performed
by transaction are committed to the
database their permanent if an
application crashes or the database
server goes down in the case of our
client server database like my sql
for example the changes made by the
transactions are still there when the
application runs again or the database
comes back up
so essentially transactions ensure the
integrity of the data within a database
now we only have to use transactions
when we change the data in a database we
don't need them when we are querying the
database since we're not changing any
data sqlite uses transactions by
default and autocommit by default
in fact no changes can be made to the
database outside of transaction so when
we were working with the contacts
database every time we used update
insert and delete sqlite was
creating a transaction running the
statement and then committing the
changes for us so as we learned earlier
the jdbc connection class also auto commits
changes by default when we turned off
auto commit sqlite stopped
auto committing the changes but there was
still made as part of a transaction
now when working with sqlite the
following commands are used for
transactions
firstly begin transaction now we use
this to manually start transaction the
second one is end transaction so use this
to end the transaction now committing
changes automatically ends a transaction
also ending a transaction also commits
any changes
so in other words end transaction and
commit are aliases we only have to use
one when we want to enter transaction
and commit the changes three commit so
we use this to commit the changes made
by a transaction as mentioned this end
the transaction so we don't need to
also run the end transaction command 4
rollback so this rolls back any
uncommitted changes and ends the
transaction so note that it can only
roll back changes that have occurred
since the last commit or rollback
note that if we close the connection
before we commit any outstanding changes
the changes are rolled back alright so
let's take a look at how to use
transactions within jdbc now when using
jdbc we don't code the transaction
related sql statements and use
statement object to execute them
what we do instead is called methods in
the connection class to execute
transaction related commands so we can
perform the following steps
firstly turn off the default autocommit
behavior by calling connection . set
autocommit with false as an argument 2
we perform the sequel operations that
form the transaction three if there are
no errors call connection . commit
to commit the changes now if there
are errors we call connection . roll
back to rollback any changes made since
the transaction began and 4 we turn
the default auto commit behavior back on by
calling set autocommit this time passing
true as the argument
so to demonstrate a transaction we're
going to add the code to insert a song
into the songs table and that's got
columns for the song title the ID the
id for the album the song was on and
the track number of the song on the
album now what we want to add a song
how do we get the underscore ID for the
album as i pointed out earlier before
adding a song you have to add the album
that's actually on to the album's table
which has columns for the underscore ID
the name of the album and the underscore ID
for the artist so how do we get the
artist ID you guessed it we have to add
the artist to the artists table before
adding the album of course if it is already
a record for the artist or album then we
won't to add them again
so we have to actually check for that
case
now to add a song the user has to
provide us with the song title the album
it's on the artist and the track number
for the song so here's the sequence of
steps we need to follow
firstly we want to get the title album
track number and artists and we'll just
have the main method past as parameters
rather than prompting for them
secondly want to check to see if there's
a record for the artists in the artists
table if yes we're going to go to step 4
if no we're going to proceed to step
three step 3 would be to add the artists
to the artists table
step 4 we want to check to see if the
album is in the album's table if it is
we go to step 6
otherwise we proceed to step 5 step 5 we
add the album to the album's table
step 6 we add the song to the songs
table
now we're going to want all the
insertions to take place within a single
transaction so that we don't end up with
records in the artist table that aren't
associated with any albums or songs
or with albums that aren't associated with any
songs so we should check for the
existence of the song to before we
add it but since more than one song can
have the same title would have to check
that the album was also the same that
the artist is also the same which would
that involve querying all three tables
so we're going to assume the in the
example that we know that the song
doesn't already exist so we can focus on
using a transaction when inserting a
song but in a real world application of
course you wouldn't want to be making that
assumption also we're not going to worry
about updating the artist_list
view in a real world application
would also have to update the view
alright so we're going to need an insert
statement for each table so let's now
go back to intellij and start
by adding constants for them to the
datasource class we're going to use
prepared statements for the following as
you can see here we've got three
different inserts there one for the artist
table one for the albums and one for
the songs
let's go back there now and start creating
some constants and actually given how
long this video has gone for instead
let's just end the video here now and we
actually start working on those
constants in the next video