alright so it's now time to write our
method which will be very similar to the
method that queries the albums for an
artist
except in this case we're going to be
returning a list of songs artists
objects now also we can't use our column
index constants and that's because
they're returning columns from different
tables now we're ok to hard code indices
because we can be sure that the order of
the columns return for this query will
always be the same even if the
column position has change within the tables
now we specified the columns we want and
they are all in query alright so let's
go ahead and now add the query artist for
song method
down here at the bottom so we're going
to do...
....
...
....
...
....
...
...so this is a new feature of
intelliJ a relatively new feature
which telling us its found duplicated code
in this file so what is looking at is that
code and also the code is in the
previous method query artists actually
got the query albums for artists got
that same code i'll leave that for now
but that would be a good exercise to put
this in a separate method somehow
we won't do that now because we are focusing on
the sql so next we're going to print
out the sql statement so let's do
that so....
....
ok alright so that's our query artist for
song method
let's now go over and add our code to
the main method to get the artist
for one of these songs so we're just
gonna get into the bottom actually what
i do is i just put it down here on line 33
and make it more space and we want to do
a list....
....
...
...so if we get down to here we're going to go
through our for each to print out the
results so for....
....
...
....
ok let's just run this and see what
happens
you can see artist name equals heart album
name equals dog and butterfly track equals
nine and we can see the sql code
that was generated when we print
that out from the actual method in the
data source class that's working nicely
just to confirm that let's
actually try one of those other songs
will try the other one
and remembering it was she's on fire....
....
....will run that artist name equals
aerosmith album name equals done with
mirrors track equals seven so that's good
lastly we'll check out the third one
things are looking good
go your own way
run that this time you can see we've got
four results there because it's four
albums with that particular song exists
for fleetwood Mac and you can see there
thats also sorted now in ascending order
so there are all the queries we're
going to code for now doing the others
would just be a matter of defining the
static constants and then building up
the string in a new method depending on
the query we may have to create a new
class to hold the results now we didn't
do any queries that contain wild cards
but as we've seen when we wanted do a
query is just a matter of writing the
sql statement testing it against the
database and then building the query
string using a stringbuilder and
Constance and we didn't have to do
anything special when we use wildcards
or any other valid SQL we haven't
touched on we just build a query string
then we pass it to the statement
execute query or statement . executes and
if you've been coding along with me and
I hope you have hang on to these classes
that's because starting with the
next video we're going to be using them
again but also where we use the music
database from the GUI application later
in this section because we've been
diligent about hiding the implementation
details from the callers of the
datasource class and because we've done
that we're gonna be able to just drop
the classes in a model package into our
GUI applications project and use the
as is now if we had mixed databases code
with the code in the main method we
wouldn't have been able to do that at
least not as easy when we get to the the
GUI application will code a few more
of the queries at that point just before
i finish this video let's touch on one
more topic when we're working
from the command line we were able to
query the schema from the tables in the
database but when using jdbc this is one
area where the support for this and
sometimes the way we do it can be
different depending on the database now
unfortunately sqlite doesn't have
good support for querying the database
schema now the connection class has a
get metadata method that is supposed to
return information about the database
tables but when using this method from
with a sqlite database
unfortunately the returned object
doesn't have any
it's fieldset they're all null what we
can do though is we can query meta data
using the result set from a query now
often this is sufficient because we
usually interested in the definition of
a specific table for example we may want
to get the column index for value will
find out what the column names are for a
table just to see how this works lets
just add a quick method to the datasource
class to do a general query of the songs
table and then we'll look at the meta
data that comes back so we click on data
source
let's write that method that's going to
be....
...
....
...
....
alright so that's our method so we're
performing the query and then we call
the results set . get metadata
method to get the schema information
from the table now we get the column
count and then use the loop to print
each column name you notice at that we
initialized I to 1 the loop because the
first column is at position one not
position 0 now using the results set meta
data we can get information such as the
column names and types and the
attributes in other words you know
whether the nullable etc let's just run
this first so we can just do...
....we
just run that you can see there columns in
the song table we've got ID track titled
and album so relation to the other types of
information that we can look at let's
just go to oracles documentation
results set metadata
you can see there's quite a few
methods that do various things so its
worthwhile checking that out and for
example if you're in the situation where
we didn't know the names of the columns
in the table or how many they were we
can do a query that doesn't require that
information and then get it from the
results set
of course we need to at least know the
name of the table which we've done in
this scenario that we just created up as
i mentioned that this is one area that
getting schema info where the support
and the way of getting it can differ
across databases
alright so that's it for this video I'm
going to finish it here now in the next
video we're going to start looking at
creating views using functions in our
statements prepared statements and
transactions so see you in the next
video