so we ended the previous video
performing a simple select query and
getting the results to the in the run
window down the bottom but there's
actually an easier way to perform
queries and to get the results so
instead of having to use the execute
method we can use the execute query
method which actually returns results set
so lets just update the code to use
that instead what we can do is here
we've got results with these two lines
here we can actually do is comment those
out and the replacement line for that
would be...
...
if we run that you can see we get the same
results but the advantage here is were
using less code now what we can also do
is retrieve the values from a record
using the column indices rather than the
column names in fact using the column
indices is faster and so it's the
preferred way to do it now we won't
change the code in this application but
we'll use column indices in a future
video in this section of the course we
start working with the music database
but what we do know now is how to do the
basic crud operations create replace
update delete using jdbc but frankly the
code we written can be improved quite a
bit now when we were working from the
command line
it was ok to hard-code everything in
fact we had to each sql statement was
executed in oscillation so we didn't
have to worry about commenting out lines
of code because only the sql
statement we just type was executed
working with the database from within an
application though means that our
statements aren't run in oscillation so we
shouldn't have to type in the table of
column names every single time
in fact it's bad practice to do so just
imagine that with completed the
application that works with the contacts
table in the test javadb and our
manager or client tells us that we've
decided to rename the name column to
first_name
so since we hard-coded everything
would have to find every instance of
name and updated to first underscore
name now that would be trivial in a tiny
application but there's still the
possibility for typos or accidentally
updating the wrong column name in a
statement now using hard-coded strings
for our sql statements also makes our
application vulnerable to what's called
a sql injection attack now we'll come
back to this security problem in a later
video in this section of the course so
what do we usually do when we're using a
hard-coded string in more than one place
in our application code well we create
constants for the strings and use those
constants in if you want to rename
something then we only have to change
the name in one place but we can also
improve the code in another way when
we're adding records to the database we
repeated the same code several times now
repetitive code should always provoke
the thought that maybe we should
write and use a method instead let's go
ahead and update the code to use
constants and a method for insertions now
we could create a separate class for the
string constants we define so that any
class in the application will be able
to access them just to keep things
simple
we're just going to go ahead and define
them within the main class we're going
to use a separate class when we work
with the music database in a future
video in this section of the course
though so what we are going to do is add
constants for the database name
connection string tablename and column
names and also update the code to use
them
we're also going to drop the table when
the application runs and uncomment all
the code so every time the application
runs will recreate the table from
scratch and then perform the insert
update delete and select statements at
the end of it all the goal is to see three
rows printed to the console for Tim
jane and fido note that we're using if
exists when we drop the table or we will
be using that to avoid an exception
being thrown if the table doesn't exist
I'll show you how to do that shortly
let's go ahead and now and actually make
all those changes
going to start right at the top we're
going to start now creating some
constant so just below the class
definition gonna start with....
...
ok so next we want to do create a
constant for our table going to be...
...
...so let's now continue I'm going to
just as I mentioned and remove some of
these comments and remove those as we go
through so firstly we're going to start
now with the....
....
...
....
...
alright so we drop the table if it exists
next you want to go ahead and create the
table so we can change this statement
...
....
...
alright so the lines we want to add here
and we're going to add...
...
..
...
that's the code to create the table
now doesn't
exist
next we want to write some code to
insert an entry so it's going to be....
....
...
....so that's our 4
rows that we are going to be adding to the
database shortly now what we want to do
is do the update for Jane's phone number
that we originally did when we were working in
the previous video so...
...
....
...
....so at this point
we should have removed all the
hard-coding they shouldn't be no
sql code that's got hard code
database names connection string
tablename or column names i think it
looks pretty good so note I've written
sort of spaced out some sql statements here
over multiple lines the intention
is to make it easy-to-read
rather than sort of going from left to
right across the page as you start building
up long lines like these
inserts for example it can become quite
difficult to read so by spacing that out
putting in multiple lines and trying
to line up the columns does I feel make
it a lot easier to read it does get
worse when you're creating sql
statements by appending constants and
strings together so it's really easy to
miss adding a blank between two words
for example or miss a single quotation
mark so this will help but also when we
got to run this the exception message we
get will usually gives a good idea of
where in the statement to look for an error
let's actually try running this now just
to see whether it does work and here's
an example of this that does sql error
or missing database now what we've got
is for a sql statement we've actually
got a message there but we should
probably do a stack trace
as well so we can actually see the exact
line where it crash to help sort of us figure
out where the problem is
so we can actually do a.....
...if we do that now then
run it again we actually got some more
errors more information here now and
we can come down here and eventually it
tells us that the main dot java line 23
is the problem and we've got as trace
of the other code DB dot java so
that code is all part of sql
lites drivers but let's go back to
line 23 by clicking this we can see the
problems like to be here you can see
what I've done there put a left
parenthesis instead of the right
parenthesis let's fix that up will try
running it again this time we've got the
required result we've actually gotten
three records again which is good and if we go
back in just do a refresh
nothing's changed because of course we
still ended up with the same three
entries we did previously
alright so at the moment will
notice that looking at this code
we've actually got some duplication of
code here these statement . execute
these 4 inserts are duplicated so
what I'm going to do first is show you the
obvious way to do it but it won't be the
best way we're going to revisit running
the same sql statement but with
different values in a later video
for the moment though add the
method to main.java so
down the bottom i'm going to add that
will make it a bit of a space here and...
...
....
...
...that should be it
so now were basically put in here the
three values that are called or pass as
parameters to this and we're using the
statement object as pass to this method as
well to update it and now what should we
be able to do is change our insert here to
abbreviate them and it's going to be
much easier to just be...
...
alright so that's going to be the
equivalent of that first line so let's
go ahead and change the other ones and
what I'm just going to do is probably
going to be quicker for me just to paste
it like that and put in....
...
....
...
lets actually try running this is to make
sure it still works
ok you can see we've got the same
results
as i mentioned this isn't the
best way to do it but i'm going to cover
a better way in an upcoming video but at
least for now we don't have all that
repetitive code and if we did have to
make a change to say a column name we
could just come up here and change the
column once and that would then change
and be reflected throughout the rest of
the code without us having to change
anything else
alright so that's it for the
introduction to jdbc next we're going to
switch to working with the music
database and start performing some more
interesting queries and also start
taking a look at more features offered
by the jdbc API see you in the next
video