alright so in the last video we went
through and figured out the sql
queries we needed to do to test out the
join that we set up for determining the
albums for a given artists we went
through did and three test cases so now that
we've done that we want to go back to
intellij and code up our method and again
doing it this way makes it a lot easier
because we now know this sql we want
to use because we've tested that and we
really just need to convert that to java
code so we're not going to have to worry
that the sql code is correct or not
because we tested that works then a that the sql code is correct or not
potential problem will have as if we
made a typo of something of that nature
ok so back to tell intellij and into data source
java and let's add it at the bottom of
that file this time we're going to need 2
parameters one for the name of the
artist and one for the sort order
alright so let's start of the code so
it's going to be public...
...
what we're doing is we're building up
the code and would be easier if I just
copy this line from the last to select
that we created the last line of sql
code i'm going to paste that in there so
you can actually see what we're trying
to build up here makes it easier so
we've done so far select space albums we
need to put.....
...
....
...
..
...
....
...
so basically putting the artist name in
double quotes alright so far so good now
we need to add the code to work on the
sort order so...
...
..
...
....
...
..
...
ok so we've built up the query string
and then we actually run the query and
as I mentioned in this case we're
returning a list of string objects which
are the album names rather than the full
record since we only asked for the name
column now because of that notice that
we're hard-coding rather this column
index on line 149 when we loop through
the results and remember that I said
when using the result set getter methods
the index corresponds to the index of a
column in the result set not the index
of a column in the table in our query
only returns the album name and so
there's only one column and remember
that these indices the count starts a
1 and not 0 for that reason we can
confidently use one in this case because
it's always going to be the same because
we are only returning just the one column in
the query and again i printed out
the SQL statement here on line 142 in
case we haven't built the statement
correctly or made the typo or something
along those lines let's now test
this out let's go ahead and call
this new method from the main method and
print the result and see if that works i'm
going to add it before the data close
so leave the other code in there
we put list....
...
ok so let's run this and see if we do
get the three albums that we think
we should get for Iron Maiden
and there's our select statement select albums.
name from albums inner join
...
...where....
...
pretty good and they can see below that
we've got those three albums which were
expecting to get for Iron Maiden
alright so you can see now that we did
build up a long query statement and
looking at that most of the statement is
static
it's really only the artists name and
sort order that may be different each
time the method runs so rather than
building the query statement from
scratch each time the method execute we
can define bits of it as constants so
let's go and do that going to
add that to the top of the datasource
just below the other constant set up
and will put...
...
..
...
ok now we could have used string builder
with a bunch of chain append calls
similar to what we did in the method but
since the concatenation will only take
place once when the data source instance
is created
we're going to stick with concatenations
often a data source class like data source is
used as a singleton and that would mean
there would only ever be one instance of
the class and we'll see how this works
when we use our model from within a
graphical user interface later in this
section i'm sure you probably agree by
looking at that these strings look pretty
ugly
so if we ever have to change the name of
a table or column will be glad we didn't
hard code any names we really only
have to type these strings in once and
hopefully now you can start imagining
how you might end up with lots of
constants which is why they often be put
into their own classes
alright so let's now go back and modify
our code to use the constants so i'm
going to go back to our query
method our query
albums for artists and what we're going
to do is just to delete this comment
there and these stringbuilder is
actually gonna be a new stringbuilder
...
......
....so I made this
method as you can see a lot more concise
and I think better and easier to
read
let's just run this to make sure that
still works
ok you can see that we still got
pretty much the same or identical
sql statement and the three
albums have come back okay now that we've
done that I guess we could looking at
coming back to the data source . java we
could do a similar thing for query
artists and make most of the
string that we're building up the string
builder a constant so i'll leave that
as an exercise for you is based on
what we've done here with the query
albums for artists again we made the method
a lot more concise and I think better so
it might be a good challenge for you just
to actually do that do the same thing
with constants for the query artist
method alright I'm going to finish the
video here now in the next video we'll
just discuss how we're going to redefine
the query albums for artists method in a
bit later and also we will start
testing this out or run it with the
the other two queries that we set up the
one for Pink Floyd
and also the one for carole king will do
that in the next video