now before we look at prepared
statements and transactions just want to
quickly tie up a few loose ends first one
is functions now as we saw in the previous
video when we want to perform a query we
build up a query string and then we pass
it to statement . execute query now the
same applies when our query contains
functions but how do we get to the
result for example if there's a query
and i'll just type this in here now the
query......
....so how
do we get to the results of that query
so how do we get the count in other words
from the results set
let's have a look at that by adding a
method to our data source class....
....
...
alright so in other words to get the
result as you can see here on line 217 we
treat the function result as a column in
this case since we're only asking for
the count
the result will be at column one let's just
try this out we'll go back to our main
method in the main class and just after
the last query will actually add a call
we'll just type....
...
...just in case
you're wondering the fact that our
constant name has table in it doesn't break
the separation between our model code and
the rest of the application i'm talking
about this line 49 nothing about it
forces me to know that the data is
stored in a database
alright so let's now temporarily
changed to get count method to do a bit
more
let's bring back over to data source and
they get count method again
so what we're going to do is also
retrieve the minimum song ID value so
to do that will change our sql
statement up here on line 213 to be
...
...alright so
we're getting the min result now from our
function call here on line 213 as
the column 2 and adding it or
extracting it into our min integer
variable if we run this we can see we
got count equals 53 to 15 min equals 1 so the
minimum ID value is one which would be
correct as the first entry in the table
this works but it is good practice to
assign names as the resulting columns
using as so let's get back and modify
the code to do that back in data source
again so what we should really be doing up
here on line 213 is put select count
start in parentheses....
...
...and by doing that we can
change this and using another overloaded method here
instead of returning the column
index number we can change that to
return count....
...
....so we're assigning
as you can see up here on line 213 the
value or the return value from count and
using the name count and from min
we are returning that using the min_
ID and we also change the
result set getter methods to use column
names should get the same results i'll
just run this to confirm and same result
counting equals 5 to 50 min equals one
now doing it this way back to show you the
code again up here on line 213 the
means that if we alter the query to
retrieve more results and the
column positions of the resulting values
change we won't have to change the getter
calls
alright so that's all there really is to
working with functions but before we
continue lets remove those changes we
made to query min
we won't be needing those so I'm going
to just put it back to what it was so
delete the min there so we just got
select count star and parentheses that is
count and i'll delete this line for
minimum...
....
...
....
running it again
alright still got the same result
okay now let's briefly take a look at
creating and querying views now I say
briefly because there's really nothing
special about working with views using
jdbc we do exactly what we did from the
command line when we need to use views
from an application its possible that the
views will ship with the database in
addition we don't want to create the view
every time the application runs so let's
create the same view we created when
working from the command line the one
that contains the following columns the
artist album track and title when the
user wants to know the artist for a
song we can query the view instead of a
join so what we can do is go back to
our db browser and execute the SQL to
create the view so let's go ahead and do that
db browser and just gonna open the
database the previous one we worked on
music DB that's now
execute sql and just to save a bit of time
i'm just going to paste the sql code
just size it that one up you can see that
basically it's just creating the view of
we've worked on before take
note that were using the if not exists
clause though at the top so sqlite will
only create the view it doesn't already
exist so that's how we would normally
created but i'm not actually going to
run it in db browser let's go back and
actually created in java code and go
back to data source back up to the top
and what we'll do is we'll actually add
this view
to our public static constants so we added
below the query artists for song sort so
what we need firstly is a....
....
space plus let's just go back to DB
browser briefly and just copy this code
going to paste it in here too so we
can see what we're trying to create it
so that's the sql code and just
comment it out so that's what we're
creating here with this line of code well this
create artist for song views string
alright so moving on we've got to now
at the album's...
...
that's it hope we haven't made any typos
but will check that as well so now
what we're going to do is create the
method that will use to create the view
lets go and do that.....
....
alright so there's our method to create
the view for song artist so we're
returning true to the caller when the
view is successfully created and false
otherwise now what we'll do is
we'll get back to main and just try
running this to make sure that it
does work
so do...
....just try running that we haven't
got any messages here indicating there's
any sort of an error so that's a good sign
that we got the typing correct
now let's swing over before I finish
this video to the Db sqlite browser and
confirm that it's in there so switching
over to that again we're going to browse
data tab and if you're already in the
you might need to do a refresh by clicking on
that button and now if we click on tables you see
the artist_list here note
that the icons a little bit different
to the other ones so that's the icon
for a view as opposed to these other
ones albums artists and songs which is a
table so that's the data but also the database
structure can see down here views
we've got this artist_list we
can see what fields are represented
it is pretty cool so go
back now to browse data select artist_list
there's our data and the view
is obviously working and returning
things in the right order theirs one record
per song effectively you can see down here
1 -11 of 5350 which was the
number that we will be saw returned in
the count earlier in this video so it's
obviously the number of songs in the
database and just to confirm if i go
back and run this again we're still not
getting an error and that's because in
the data source in the view that we
created up at the top the code
we used if not exists so therefore
because it's now existing is not trying
to recreate the view alright let me
just clean this up deleting that now and
that will finish the video at this point
in the next video will continue on and
start using this query see you in the
next video