- [Tim] Alright, so the last video we finished off queries,
and we've got our application working okay,
but how about inserts, updates, and deletes of data.
How do we go about doing that
given that we only really worked on queries today?
So, we do handle them the same way, but the question is,
how and when do we update the user interface?
Usually we'll already be displaying data,
and the user will do something
that will add a new record, update a record,
or delete a record, there they would think of it that way.
In our case, they'd want to update an artist name,
perhaps to correct a spelling error
or delete an artist
because they gave away the only album
they had by that artist
or they'll want to add a new album,
which could result in also adding a new record
to the artist's table.
So, we wanna keep the database and the UR in sync.
There are a couple of ways to do this.
The first is to re-querie the database
after every successful insert, update and delete.
And when working with a small data set
like the artist's table, that's okay,
but for larger data sets, it may not make sense.
Now this is one area where it will depend on the databases
and application you're working on.
So another alternative
would be to perform the database operation
and then manually update the user interface.
We wouldn't re-querie the database, in our case,
when doing an insert, we get the list
the table view is displaying
and add the new artist record.
And because we're using data binding,
when we add the new record to the list,
it will then automatically be displayed in the table.
So that was for an insert.
For an update,
we kept the list the table view is displaying,
find the record,
and update the field.
Once again, because of data binding,
the change will be reflected in the table.
And lastly, for a delete, same deal as insert,
except I would actually delete the record.
So, we'll update the user interface
after the database operation has successfully completed.
Now if something is going to go wrong,
it's more likely that the database operation will fail.
So we don't want to update the user interface first,
because if the database operation fails,
the user interface will be out of sync with the database.
But if the database operation succeeds,
but for some reason the user interface update fails,
that's not really as bad.
The user won't see the change,
but most likely there will be a refresh button,
either a new application,
or the browser they're using,
if they're using a web application,
in which case though,
they'd have to manually refresh the data to see the change,
just as we sometimes have to do that
when using the sequel lite browser.
And they don't even have to do this
if the user interface update failed,
and that would probably be pretty rare.
So how does that all translate to code?
So it would run the insert,
the update would delete using a task,
and then would run the UI update using the task.set
on succeeded method.
So lets go ahead and do that method of updating,
and we're going to start by coding an update example.
Now, to avoid having to code a dialogue box,
we're gonna assume that the user wants
to change the name of AC DC, AC space DC
to AC slash DC.
Now we're gonna assume that the user's
pressed the button called update,
we've displayed a dialogue box
showing the current name of the selected list,
and then typed in AC slash DC and pressed the okay button.
Now, because we have the selected artist,
we know what the artist ID is,
and we'd use it to update the appropriate record
in the database,
and we'd then update the selected artist record
and the table would update.
Alright, so we need to open our data source,
and we need a new prepared statement
and associated constants.
And we also have to create the prepared statements
in the open method, and close it in the closed method.
Lets go ahead and do that.
So to start off with,
we want a string containing our prepared statement,
in fact through the sequel code we're gonna be using.
That's going to be PSF,
and my course would update.
Artist name
equals this will be update
space plus table and it's graphs.
There would be a name here.
Her top product, so string that will be.
A table and its artists.
Plus, space and set in double quotes,
plus, there's gonna be column
underscore artist underscore name.
I'm sorry, plus in double quotes,
space equals question mark space,
where space, plus column artist ID.
Plus equals question mark.
Alright we want our private prepared statement,
that's gonna be private prepared statement,
and we're going to call this one update artist name.
Alright they are now open.
Our method, we want to put update artist name
equals conn dot prepare statement.
And there's going to be our update underscore artist name
and then for the close, the close method,
just before we close the connexion,
we'll test to see whether the
update artist name prepared statement is open.
If the update artist name is not equal to null,
update artist name dot close.
So basically, the update state we're gonna be running here
is gonna be update artists
set name equals question mark space where,
ID equals question mark.
That's effectively what we've done here.
Alright so next what we need to do is add the method
to update an artist name,
which will be very similar to methods you have already seen.
So its going to accept the artist ID,
and the new name as parameters,
and return true if the update succeeded,
and false otherwise.
So were gonna write that down at the end of the class.
Actually what I'll do, we got some,
I'll actually put it in here after the insert album.
Because insert,
so technically we can get rid of that method all together,
but I won't do that now.
That was relating to a previous app.
But we're working on the non-gooey part
of this music database,
and of course we deleted the song classes
earlier in the section.
Alright so, the update artist name
is going to be public boolean update artist name,
and we need an ID and the new name.
Wanna try there,
and we'll add the catch already,
so the catch is going to be SQL exception E,
then we'll do
bring out a message with query,
and we'll put update failed rather than query failed.
Take failed, plus E dot get message.
Then the actual code is going to be
updateartistname.setstring.
First one's going to be the IDs
so its going to be one comma new name
'cause the first parameter was new name,
or rather the first part of that query
if you go back to the top and have a look,
we've got the first one here is
where column artist name equals and a question mark.
So that's our first one we need to use
and then the second one is gonna be the ID,
so that's right, we're in the right place,
doing the right thing.
So one's going to be the new name,
and then update artist name dot set int
for the second one for the key,
for what the ID is to come up ID.
Then int affected records
is equal to update artist name dot execute update
'cause we're updating it obviously.
And we gotta return, affected records is equal to one.
So returning true if we only updated one record
for the artist, otherwise false.
Then right down here, if we get down to here,
well actually what we'll do is
we'll put it in there, so return false.
So we get an exception we gotta return false
because there was some sort of problem updating it.
Alright so that keeps intelliger happy.
Alright so next we need to update the vent handler
for the update artist button to the controller.
And that's also gonna look similar
to what we've done before,
So let's go back to our controller,
and we'll add the code.
Like so, and it's gonna be public void
update artist.
Alright so the code for this is gonna be
final artist artist, is equal to
that's going to be casting as an artist,
then it's artist table dot get items dot get two.
That's gonna be task, boolean,
equals new task,
and we're just going to press enter there,
to get the code written for us.
How I've written in method call,
and we got a return here, data source
dot get instance dot update artist name
and we're gonna do artist dot get ID.
And then we're going to have the new name be AC slash DC.
Like so.
Finish that off.
And once I wanna set up that task dot set on succeeded
so, task dot set on succeeded.
So set up our lambda,
lambda I should say.
And its going to be if task dot value property
dot get, oops dot get that should be.
Then in that case, we're gonna do artist
dot set name
ACDC with a slash.
And artist table dot refresh.
And of course, right down the bottom,
all importantly, we need to invoke the threads.
So new thread,
task dot start.
Alright, so that's our update artist method.
Alright, so let's just go through this briefly
but we're actually done here.
So first thing we're getting the actual record
that we wanna update.
Now normally we'd get this
by calling the table view selection model,
and that would be the code
that will actually look like this.
And I'll actually leave this in here,
so normally we'd do final artist, artist
and you've seen this code before, equals artist.
Gonna be artist table dot get selection model
dot get selected item.
So that would be what we would normally do,
but I'll comment that out.
And again the reason that we're doing it this way,
with code on line 57,
is we're simulating this
to avoid having to create a dialogue
that accepts a new name.
But, as an optional exercise you can go ahead and do that.
Then we can see from the data being displayed,
or we will be able to see that shortly,
that AC DC is in position three,
so we're gonna get that record from the table views items,
which will actually be index two.
So let's just run that to confirm that that is the case.
Okay, so there's AC DC, it's the third element,
the third entry in the table view,
and it's going to be in index position two,
so that's where the dot get two comes from,
and notice that it doesn't got a slash
in the name at the moment.
Alright so we'll close that down.
So when we run the task that we've defined,
it's going to be calling the update artist name,
passing the ID.
So it's passing the ID which from this entry alone 57,
so we know that's the right one.
And we're passing the new name
which is gonna have a slash between the AC and DC,
and notice that the actual task actually returns a boolean,
and that's because the update artist name method
returns boolean.
Now, when the task succeeds,
we gotta trick the value return mother method
from the tasks value property,
that's here on line 67.
If it's true, meaning that the update succeeded,
then we go ahead and actually update the UI.
So we change the artist name to AC slash DC,
as you can see there
and we then refresh the table.
Now, we should not have to manually refresh the table,
I just want to point that out,
but there is a bug in the current version of Java Effects,
when it comes to table views and observable lists.
Now the table view is not always noticing
when an element in the list changes.
So this is actually a workaround to force a refresh,
and in fact the refresh method was only added in Java 8U60,
perhaps in response to this bug.
So maybe fixing this bug will be complex we don't know,
the documentation of interest suggests or states
that its useful in cases,
this is the refresh method,
where the underlying data source will change in a way
that's not observed by the table view itself.
So, the way that I read that leads me to suspect
that yes, the method was added, because the developers know
that data binding is broken in some cases.
So we're adding the refresh share to force that update.
So the refresh method forces the table view
to redraw its visible area.
And, it won't redraw rows that are off the screen,
but since those will redraw,
if and when the user scrolls to them,
it doesn't matter in our case.
Alright, so let's actually run this and see if it works.
So it should be up, now we have to click on update artist
and we should see AC DC change to AC slash DC.
And when you run this, you wanna make sure
this is visible on the screen,
otherwise the refresh method is not going to redraw the row.
Alright, so update artist.
And of course, what we haven't done,
you might be wondering, "Well Tim, why isn't that working?"
we haven't actually bound the actual control
to our new method, have we?
So we need to do that,
we need to add the event handler in the fxml.
So lets go ahead and do that,
so the third button update artist,
we need to add our on action.
On action is equal to hash,
and that's gonna be update artist.
Alright, so let's try that again.
Okay, AC DC on the screen, we click on update artist,
and there you can see that it's been updated, AC slash DC,
and if we actually close this down and run it again,
in reloading the data,
we should find the AC slash DC is showing,
which tells us the database was updated correctly.
Alright so that's it for using a database
from a gooey application.
You now know the basics
and I think you've got enough knowledge
to research anything that we haven't covered here.
Alright so, as always, I'll see you in the next video.