alright so as I mentioned at the end of
the last video we're going to redefine
the query albums for artists method
even further will do that a little bit
later on
now it would be nice though if we could
somehow have a placeholder in the sql
statement for the artist's name so that
we could dynamically replace it every
time we performed a query rather than
having to append the artist name to the
statement but hold that thought for
future video in this section of the
course
alright so in the main method we're
going to check the other queries
we came up with
we're obviously tested iron maiden in the
last video that worked okay let's so try
pink floyd we should be getting back if
you recall 12 albums will run this and
you can see we've got the 12 albums a
that's good that was working as well and
just to confirm lets changes this to....
...
obviously we had ascending order so
we'll try that and the sorting is
working as you can see there now so
that's good
and finally let's just try the carole
king song the carole king artist I should say
and then we'll make this one order by
ascending not that it really matters because i
think from memory there was only the one
album anyway
alright so we'll run that we've got just
one album tapestry so you can see
they're so let's now do one
more of the queries that we expect
our application will want to perform so lets add
a method that returns which artist
recorded a particular song now one
wrinkle here is that we want to return
the artist name the album name and the
track number for the song of the album
so to do that we need to create a class
in our model that contains these three
values
let's go ahead and do that first we're
going to call the class song artist
so we're going to create a new model new
java class song artist
alright so we need those three fields as
i mentioned we've got...
...
alright so let's go ahead and test
our query using db browser again I'm just
going to start up my instance alright
we need to open the database so
come up here to file and select the most
recent one we use just music DB and
going to go into the execute sql so
we want to query the artists for three
songs heartless she's on fire and go
your own way we're going to start by
retrieving the record for the song from
the songs table so let's try that out so
in the sql code select....
...
...alright
we can see there that we get the track 9
and the album ID is 308
so now that we've got that we can do a
select...
...
...and execute that and you can see ID
308 weight matches artist number
39 the name dog and butterfly being the
album and let's go do the third step
which is to query the artists table we now
know we need to query it for ID 39
so the create new tab select...
....
...let's execute that and you
see we finally got now down to the name
of the artist which is heart in this
case
alright so let's just repeat these steps
for the other two songs so we go back
here to this first tab this time I'm
going to change the name of the song
which was heartless will change that
id this time this song rather to she's
on fire if i run that let's just check
that I got my typing wrong their just do a she
....
....but we'll just try this
just do a search for any song that's got
the word she in it She so fine
that was the one I was looking for she's on
fire so i'm not too sure why that didn't
come up and try again we'll go back to
....
...
....so let's just try that this time
alright that's better obviously the
capital made all the difference
alright so you can see that's now album
248 track number seven
so look at second part of the query now
change the album to 248 this time
....
....ok let's
just do the final one now which is
go your own way.....
...
the reasons its coming up 4 place is this
is actually on four albums so we're just
going to look at the first album to do
a test here which is album 416
and we know the artist now is 92 we
should get Fleetwood Mac coming back up
the name of the artist Fleetwood mac
alright so we know that sql
right and we're able to get the
information we need to achieve what we
want to do though we need to create an inner
join on those various tables to
get the information so lets actually
create another tab and type top in the sql
code that we want....
....
...
alright so let's actually just try
running that now and see if it works
and I got a typo here so....
...
....
...
....let's run that alright so
just have a look at that again so we
got artist.name...
....
...
....
...
let's try that artists name that should
be artists . name...
...
yeah we got there in the end
ok so our inner join is now
finally working and we tested that were
able to type in particular song and get
all the get the name name of the
artist name of the album and also the
track number that given song
appears on alright finally that works
what I want to do now is start by
swinging back over to the code to our data
source class and we want to add the
constants for the static parts of the
sql statement now the only
varying parts of the song title and sort
order i'm going to do the same as we did
when finding the albums for an artist
will have a constant for the part of the
statement that occurs before the song
title and a constant for the sort
section let's go back to do that go up
to the top here and we'll add it down
here i'm going to start by...
....
...
....
...
anything left to do now is to do the
sort component so....
...
....
...
alright let's finish the video here
because it's getting quite long in the
next one will start writing our query
artists for song method and obviously
we'll be using the constants that we've
defined in this video we test it all and
make sure it's working
I'll see you in the next video