alright so i ended the last video by
mentioning that we don't want to return
a result set and that's because we don't
want classes that use methods in the
model package to have to understand the
implementation details of the model so
in other words we need an alternative
way to return the information and
we're going to return a list of artists
and to do that what that means is we're
going to need classes for artists album
and song so let's go ahead and create
those with the model package and for
each one we're just going to use
IntelliJ to generate the setter
and getter methods now we may not get to use all
these classes immediately but will
eventually use them all so going to
open the project pane let's create a new
class in the model packaged
and we'll start with artist
so artists we've got..
....
...
alright so now that we've done that we
can actually write the query artists
method we're going to add that to the
datasource class let's go back to their
let's start by creating the class the
method rather....
...
....
...
....
...
.... at the moment we're getting this
error because we haven't thrown it we
havent to put in the code that can
potentially throw-in a sql exception let's
add the code for that will start by
putting statement....
...
....so we're creating a statement and
then we execute the sql statement
select start from artists and that's
going to return all the artists records
with all column values and you can see
it that I'm looping through
on line 64 through the result set and for
each record we create a new artist
object and we use the results set getter
methods to get the values from the
record and send them to the artist
instance and then we add the instance to
the list once were done looping we
return the list to the caller well as we
should do that i haven't written the code
for that yet but lets add that now just before the
catch so that we haven't got any more errors in
this method now ok and this time I made a
bit of a change we are using a finally block
to close the statement and what I should have done
was well to be precise here is I
should also be closing off the result
set at the moment closing of the
statement i'm going to close that first we
need to close the result set before the
statement....
....
...
...again we shouldn't get errors but what
we're doing is we're just covering
ourselves here in a worst-case scenario
let's do the right thing here let's just
print out a message we're gonna put....
...I mean it
doesn't take more than a second or so to
or a few seconds to actually do it at
we have some indication that there
was a problem you can get yourself
stuck sometimes if you don't
put anything in a catch then you're
crushing you may not realize that
because there's no nothing on the screen
to tell you that there's a crash so for
the sake of you know a minute or do this
its worth it
alright so now we're trying
independently to close the result set and
the statement in case you're wondering
why I've got 2 try catch methods in the
finally block that's because either
of those close could actually catch or
could throw the sqlexception if that
happens then the second one would be
executed in other words we had it all in
one to try catch block in the finally
block and say the result set threw an
exception then we wouldn't get to test
these statement closing so it's best to
do them both individually to make sure
that ensuring that checking both
of them and then ultimately closing both
of them whether there's an error or not
or whether there's an error
we're trying to close one thing you have
probably noticed though it does get a
little bit messy here having a lot more
multiple try catches in the final clause
and then another catch above that
for our normal processing can make the code
a little bit messy remember that we
could use the new form of try and declare
the objects as part of this try
statement or if you're not concerned
about closing database resources
immediately after we finish with them we
could move the close method calls into
the try block and not worry about the
case when an exception is thrown and
resources are still open because the
runtime would close them eventually
but generally speaking it's better that
you cope with that intention in mind and
to actually do this yourself either with
a try with resources or you know the call to
close is going to be executed
while as what i've done here is i've
actually put in the closed
method calls myself
alright so now that we've seen the code
to close the object in all its glory
lets actually modify the method to
declare the resources as part of the tri
statements used to try with resources i
really do think generally that's the way
to go because it's more concise and then
we are sure that the close method is
going to be called automatically for us
so how do we do that in the tri finally
we come up here first and what we're
going to do is put the statement in the
first parts let me just type....
....
...
the other thing we need to do is delete
these two references statement and
result set because we are now declaring both
of those objects in the try with
resources block and of course what
that now means is we can get to we can now
do away with the entire finally clause so
we're going to go ahead and delete that
and again the reason we don't need that
finally clause is when we used the try with
resources as we're using up here with
the new statement syntax both the
statement and resultset will be closed
automatically for us will be close method
will be called for both whether there's
an exception generated or not
and obviously in this scenario callers
of the method still have to handle a
null return value however they see fit
so in other words the process that's
calling the query artists method has to
actually check to see whether what we're
getting back is null or not
alright so let's go ahead and call the
method now from the main method and print
results to the console so we're going to
do is add the code here just above the
datasource . close going to put list
....
...
...you can see here that we're
checking the return value from the query
artists if we get a null we're exiting
with a message saying there's no artists
otherwise we're printing at the artists
in the list
let's now just run this to see
that it works and you can see we got
a list back of all our artists
total of 201 and note that the main method
code doesn't make any assumptions about
how or where the data is stored so the
data could be coming from an xml file a
spreadsheet a my sql database or
even a flat file if we change how and
where the data is stored as long as we
don't have to change any of the method
signatures in the datasource class then
we won't have to change any classes that
use it and obviously this case the main
method will remain unchanged in that
scenario a couple more things to note
firstly the statement . close
method closest the
statement and any resultset objects
associated with it so we don't have to
explicitly close the result set that
won't make a difference to the code we
written but if we were explicitly
closing resources closing the statement
would in fact close the results set the
second we can make a quick improvement
to the existing code right now we're
using column names to get the field
values but we can use the column index
instead and that's usually more
efficient but with that said there is a
trade-off if we use column names we
won't have to change any code if the
positions of the columns change within
the table for example we might add a
column to the artists table and position
it between the existing_ID
and name columns now if you use name
columns we won't have to change any code but
if we've used our column indices in our
code and we would have to change them
however this is easily handled if we use
constants for our column indices which
of course we will do now it's
important to note that when working with
result sets the index we pass to the
results set getter method is the index of
the column in the result set not in the
table just so that again so the index
we passed the result set getter methods
is the index of the column in the result
set not in the table now when we
retrieve all the columns result set
indices will be the same as the table
indices but if we only retrieve a
specific column or set of columns then
the results indices may not actually
match the table column indices and we'll
see an example of this later
alright so let's finish the video
now in the next video we'll make those
changes that we talked about going to
start by adding column index constants
for all the tables i'll see you in the
next video