so continuing on from the last video
let's now go ahead and add the constants
for those insert prepared statements
that I talked about going to do firstly is
just like clean up this main . java
file with those comments so
we'll get back to data source java back
up to the top of the file and will add
those constants so we're
going to start with....
....
alright then the third one is for
inserting song so...
....
ok so now that we've got our constants
going to add the code to the open method
to create the prepared statement
instances when we insert an artist or
album we need to know what id the _id
the database is assigned to the
record and if you recall the underscore
IDs the key and the database generates
the value whenever it inserts a record
into the table so we need to use the
artist ID when we insert the album and
the album ID when we insert the song so when we
want to get the generated key after
statement is completed we have to add
another parameter to the prepared
statement calls your about to see let's go
ahead and actually add this now to the
open method though i'm going to add it
below our view that's defined here
actually what we'll do is well defined a
prepared statement up here first because we will
need those as fields....
...
....okay that's our
three prepared statements for inserts
let's get back to the open method again
will do insert...
...
but then we also have this second
parameters so I mentioned that we need the
second parameter
and going to pass a constant
here...
....so once we do that will
then be able to get the key from the
prepared statement object then do
the same for insert into albums and
songs so.....
....
now for this one we don't need the ids
returned so for that reason I'm not
going to actually pass the second
parameter the returns_
generated _keys because we don't
need to pass those to anything else
whereas the artists and the album's
inserts we use the ids we get back
from those to basically passed into
another insert statement
ok alright so now that we've opened
those we need to close them as well in
the close method so let's do that as
well....
....
...
ok so now that we've done we are going to
create three methods one for each table
now the methods that insert an album or
artist will first check to see if the
album or artist already exists and if it
does already exists is going to return
the underscore ID column for the album
or artist but if it doesn't exist it will
then add the album artist and then
return the ID the new ID from that
insert now these two methods will be
private and that's because they should only
ever really be called by the method that
inserts song
which will be responsible for creating
the transaction and committing the
changes now to achieve that the first
thing we need to do is add the constants
for the statements that were going to
use to check whether an artist or an album
already exists in a given table that
will come up to our constants again
...
...you can see what we're
doing their the Select is going to return
the ID but the where clause is doing a
search by name so we get back the id
that we can then use for the insertion
if that already exists and again if it
doesn't exist then the method we're going
to return the new id after the insert so
that way whether the records on file or
not we're going to get the right ID and
then also in the case of both of these
we only need the ID returned from the
Select so that's why we're only
returning the relevant ID column
alright so we now need a couple more of
that prepared statements for these
queries the query artists and the query
album so lets add those....
....
so we're going to create those down here in
our open method so its going to be...
...
...ok and scrolling down
we need to also closed off both of those
in the close method we're going to put
...
alright so next we want to code the
actual methods the insert artist method
etc but let's start work on that in the
next video