alright so let's add column index
constants for all the tables now unlike
everything else in Java column
indices are one based so the first
column is at position 1 the second
position 2 etc and the reason for that
is if you uses 0 you actually get an
exception
alright so let's go to our data source
and we're going to start here under
line 20 after the columns_album
_artist definition let's add those
indices so it's going to be....
...
alright so moving down now to the
artists table and under the column_artist
_name definition will
put our two indices for this table....
....
...
alright so moving on down now to
songs table and below the column_song_album
let's add the
four indices for this and it's probably
good I check this because I realize now
there's no id that we added for
the song's table so let go and add that
....
...
....
...
ok so now that we've done that let's
update the method to use the column
indices gonna go down to our while loop
down here at the moment we're using the
column names as you can see there
let's actually change that now to....
...
so basically just an overloaded method that
also takes an int instead of a string
alright so let's now run this just to
make sure that it's still working
alright so still were able to run it and
we haven't got any problems it's working
fine now it's more efficient to use the
column index because the getter methods
will know exactly where to go to get the
value in the result set when we use the
column name the method has to match the
column name against the columns in the
result set now that doesn't matter when we're
looping through a small set of records
but if we were to loop through thousands
of Records it can will make a difference
now the query artists method retrieves
all the records from the artists table
and while this is useful
we may need a way to retrieve specific
records now having said that
keep in mind that when working with
the real world application we don't
usually have to accommodate every
possible query that can be made against
every table what we usually do is look
at the UI for the application whether
that be web pages or mobile screen or
desktop window and we figure out what
queries we have to perform to populate
each webpage window screen etc with data
and those are the queries we want to
accommodate in our data source class or
classes
alright so let's think about what
queries and application using the music
database might want to perform and also the
sql statements would use to get the
data so let's assume our application
allows users to do the following view a
list of artists view albums by
particular artist view songs on a
particular album find out which artist
recorded a particular album find out
which album the song was on and maybe
even find out which artist recorded a
particular song along with the album the
songs on and the track number that's just a
few you maybe have to think of other
queries user might want to perform we're
going to stick with thats set for our
application we are not going to actually code
them all here but we'll do a number of
them now we've already coded the
method that retrieves a list of artists
so let's now code the method that
retrieves all the albums for a particular
artist in other words viewing all albums by
particular artist now unfortunately
there wasn't an elegant way to build query
strings when using jdbc
now would be nice to be able to create
a query string by chaining methods
together something like you know select
. joined . order by etc and they
may well be third-party libraries that
give us the ability to do just that but
the base API doesn't unfortunately now
of course we could write methods that
behave this way ourselves but that is
getting a little bit outside of the
scope of what we're trying to do in this
section so to perform the query we want
we have to build up a query string and
then pass it to this statement .
execute query or execute but first let's
modify the query artists method to
accept a parameter that indicates if and
how we want the artists to be sorted now
we'll need string constants in the
datasource class that callers can use to
specify the sort order so let's add
those first so we'll add this to the
class and we'll start by doing a....
...
....
so in the query artists with it instead of
passing these select start from
and in the table artists as we're doing
currently we're going to build up a
query string as in the example about to
type up
let's start by adding a an argument to
the query artists method we're going to
put int....
....
...
....
...
..
...now incidentally we could have
change the sort order to use a enum
instead so optional if you wanted to do
that you can convert that in some
instances and will actually make more
sense because we'd be validating the
enum as a parameter so we can only
specify that whereas at the moment the
way we've defined that query artists and
the int that has been passed to it
it's just an integer so it is possible to
pass an invalid value here that wouldn't
be used but will stick with the
integer for now
alright so now that we've constructed
the sql statement we need to then
pass it
so we're going to do a try....
....
...
alright so I've added a sort order
parameters you can see their on line 68
accepts one of the sort constants and
the methods building up the query string
base on what the caller has passed now
we built the same queries before but
then we check the value of the parameter
so the caller doesn't want any
sorting the parameter passing
will be order by none as you can see here
on line 72 so we basically ignore all
the ignore adding the order by
Clause completely to the sql
statement
otherwise we're adding the order by and
adding the collate no case so that we're
doing a case-insensitive order and then
were adding the descending and ascending
depending on the parameter it's
actually passed and just to confirm that
notice that the if condition we're checking
by order by desk here on line 76 and not
order by ascending and we're
doing that in case the caller pass a
value that doesn't match one of the
sort constants so in that case method
will end up returning the artists sorted
in ascending order because the else will
process the else code block and I
think that's a reasonable default we
could have checked the caller had passed a
valid value and thrown exception if they
hadn't as mentioned i used a enum instead
but having a default behavior like this
is nicer and if this was a real
application would make sure to document
that somewhere so that people using
your code wuld realised that's the default
behavior
alright and now you saw that when I
finished the building the query we
passed a result of sb. to string to
execute query method alright so now we
need to go back and modify the main
method to pass one of the sort Constance
so go to main and what we need to do there
is add data....
....
should see that this will give us the
result sorted by artist name you can see
that's clearly z at the bottom go
right up to the numbers and then as
etc that's clearly working well for us
that's just to be consistent i try the
other ones just to make sure they're
working so...
...run that
should give us z through a the a
is in the 10's then the starting off z and
y so that's working well and just to
confirm order by none should also give us
ascending order because that was the
default we defined
I'm sorry order by none does no sorting and
was going to say we passed an
invalid value in that scenario that
should give us descending order so i just passed
a number like number 5 then we know
we haven't set up sort order for that's
what we should get in ascending order
you can see that's working
ok good things are working fine alright
so now that's write the method that
will retrieve all the albums by a
particular artist but before we do let's
try out the sql statement in the DB
browser and it's always good practice to
test the sql we want to use before
coding it now we didn't for the sql
that retrieves all the artists because
it was simple but when we get into using more
complex queries it's always good to run
the queries against the database before
coding them
alright so first let's select an artist
and figure out what the expected results
of the query maybe we take a look at the
table here
so what we'll do is we'll just open the
db browser so....
...come up here to file and do select
the most recent one which was music DB
if you browse the data and
choose artists and lets go with iron maiden Id number
8 there on the screen right so let's
try selecting the albums by iron maiden from
the album's table by running the
following sql and I haven't said that
before but you can actually execute
sql right here at the DB browser
product and click on that and we can
just type select we'll just have really size this font
up so you can see it
because it's quite small....
....
let's run that you can see there that
we've got three entries their and
noting down here also told you three
rows returned and it gives us our sql
that we actually used to create it or to
return those results so again the
number of the beast powerslave and
seventh son of the seventh son are three
albums that are returned by a query for Iron
Maiden
so now that we know what to expect from
the query that will use a join let's run
the query will use against the database
so we can use the same sql tab we can
open another one for pressing the new
tab button which is the left most
button on the toolbar here to open the
tab and then click through to selected
either of them now so now we want to type or
do a join we're going...
....
...
...let's run that and we can see
our sql code on the
bottom here and we can also see the
results so things are working well
and we're got a sql correct
just for good measure let's try
another artist we're going to query the
albums by pink floyd and I looked at
that before and that's got Pink
Floyd's got the id value of a hundred
thirty just to confirm that we go back
to the first tab and will change the id
here where artist = 8 to 130 if my memory serves
and definitely that's a pink floyd and the album
dark side of the moon so we've got the
results and pink floyd and a lot more
albums in this case then the Iron Maiden
as you can see there in fact 12 in
total so again we know we know what to
do there we go back to our second tab
again we can change this....
...
we should get 12 albums return you can
see down here 12 rows returned
there's a 12 albums from Pink Floyd
alright so that's working well and just
to be really sure here so that we can
test the case where an artist is only got
one album let's run our sql on Carole
King which I looked up has only got one
album in this database that by the name
of tapestry and going to leave it up to
you to verify that she has only one album
in the table though so you can write the
code based on what i've done here but
lets actually change it in the
second tab just to her name....
run that we get that one album tapestry
alright so good at this point we done three
queries we done three test cases and we
really ready to go so the next video
we're going to start writing a query
albums for artists method and start
putting this sql back into java and
using jdbc to execute these queries will
see you in the next video