Editing Records
Editing a record is going to involve the following steps
(although depending on various details of your application,
there might be more steps or fewer steps in some
applications).
- Provide a means for the user to select the record to edit.
- Retrieve that record, if necessary.
- Load the selected record into a form so the user can
edit the writable fields (i.e. they should NEVER be allowed
to edit the primary key field, that's an industry
"best practice")
- Allow the user to "submit" their changes to the record.
- Run an SQL UPDATE query to update the record.
- Present the user with feedback (e.g. update was successful)
You should be able to guess already that these steps are going to
involve moving from an HTML page to the controller, to
an HTML form, to the controller, to the database access class,
back to the controller, and then back to an HTML page again.
During all of this travel, we're going to need to keep
the record's unique ID so we know which record to retrieve
and update!
Let's do a demonstration using our Teams and Players data.
In earlier exercises you had some things we can re-use,
so make sure you've completed those exercises and have
the following:
- Input form for adding a new team, and this should
include a drop-down box of teams from the teams table
in the database.
- A page that lists all the players in the database:
this page is loaded from a handler method that invoked
the database access method to retrieve a list of
all the players in the players table.
- A database access method that retrieved the players
on a specific team (it was actually a set of teams, and
you wrote this method as one of the exercises in
the previous lesson.
If you chose not to complete that exercise, you'll need
to go now and add a database access method that retrieves
a list of players on a specific team (by the team ID).
Let's make some modifications to the player list page:
You should already have an HTML/Thymeleaf page that
lists all the players (mine used a table, but yours can
use something else, that's fine).
Modify this page so that each player in your list has
its own "EDIT" and "DELETE" link. We'll start with the
edit link for now, but you should have no problem doing
the delete link at the same time on your own.
The edit link should map to "/editPlayer" and should include
an extra segment for the unique ID (the player.id field)
for this player. For example /editPlayer/7 for the
player's whose unique id is 7 (this assumes you completed
the work in the previous lessons that added the private int
id field to the Player class).
<a th:href="|@{/editPlayer/}${p.id}|">EDIT</a>
When the user clicks this EDIT link, we want to go to
a controller method that invokes a database access method.
The handler method will be able to grab the id path
segment using a path variable:
@GetMapping("/editPlayer/{id}")
public String editPlayer(Model model, @PathVariable int id) {
...
}
The handler method will need to invoke a database access
method that takes that ID value and uses it to retrieve
that player's record using a select query e.g.
public Player getPlayer(int id) {
String sql = "SELECT * FROM players WHERE id=:id;";
// mapping id param
// retrieve list of players
}
After you retrieve the list of players (how you do this
depends on whether or not your bean data member names
match your column names, so I'll leave that to you),
you need to return the single matching player. There
will only be one match, because the ID values are
unique - id is the primary key for the players table.
If you used the bean property row mapper, you just need
to retrieve the first record:
if (players.size() > 0)
return players.get(0);
else
return null; // later you can say, if getPlayer returns null, not found!
If you had to retrieve your records manually, then you
can just grab the first element 0 of the list and
use the map.get() methods as appropriate:
if (rows.size() > 0) {
Player p = new Player();
Map<String, Object> oneRow = rows.get(0);
p.setId((int)oneRow.get("id"));
... etc ...
} else {
return null;
}
Now you can call your getPlayer() method in the handler
method, and pass it the player ID path variable. Obviously
we can also anticipate that we'll need to add the player
to the model because we're now going to have to present
it somewhere for the user to edit.
@GetMapping("/editPlayer/{id}")
public String editPlayer(Model model, @PathVariable int id) {
Player p = da.getPlayer(id);
model.addAttribute("player", p);
...
}
Now the next step is interesting: what view should we load?
We need a form that allows the user to edit the player's
data. So we'd need label/textfield pairs for the first name,
last name, number, and team (from a drop-down list!).
Don't we already have a form like this? Yes: you have the
form you used to allow a user to add a new player.
We can use this form instead of creating a new one and
having redundant code.
The problem will become apparent as soon as you look at
your add-new-player form from the previous exercises:
<form method="post" action="/addPlayer" th:object="${player}">
...
</form>
Our form action attribute is set to "/addPlayer". Go look
at your handler method for /addPlayer:
@PostMapping("/addPlayer")
public String addPlayer(Model model, @ModelAttribute Player player) {
da.addPlayer(player);
// get a updated list of players
model.addAttribute("players", da.getPlayers());
// map of teams so we can display team name instead of team id
model.addAttribute("teams", da.getTeamMap());
return "playerList.html";
}
Is there any way that we can say "if this is a new player,
do an add, but if this is an existing player, do an update"?
Yes! There are actually a few ways, but thanks to Spring,
I think this is the easiest:
First, we need to make sure that the player's id member
is bound to the form's player object. We don't want
the user to be able to see or edit the id, however.
So we can add it as a hidden field inside the form:
<form method="post" action="/addPlayer" th:object="${player}">
<input type="hidden" name="id" th:field="*{id}">
... etc...
</form>
Since the form is bound to the player object, we now have
a hidden field on the form that will always have the player
ID, and the user won't be able to see or edit it.
But why is this helpful?
Recall that when we load our form to add a new player,
i.e from the URL localhost:8080/newPlayer,
we add a new, blank player to the model. So when we
load the page, the player object's members are all
set to their default values, including the id member.
So when the form is rendered, the hidden field's value
is set to 0, because there is no player ID yet for
a new record.
However, when we load the same from from the
localhost:8080/editPlayer URL handler
method, we've saved an existing player to edit
in the model. This player came from the database
so it has a player.id value. Therefore, its hidden
field value is set to whatever the non-0 id is
for that player record.
This means in our /addPlayer method (which perhaps we
might consider renaming to make it more self-documenting
now that it handles both inserts and updates?) we
can just test the player's id member value: if it's
0, we are adding a new player, but if it's not 0,
we're updating an existing player with new data:
@PostMapping("/addPlayer")
public String addPlayer(Model model, @ModelAttribute Player player) {
// this is a new player
if (player.getId() == 0) {
da.addPlayer(player);
// this is an existing player to update
} else {
//update this player
da.updatePlayer(player);
}
// get a updated list of players
model.addAttribute("players", da.getPlayers());
// map of teams so we can display team name instead of team id
model.addAttribute("teams", da.getTeamMap());
return "playerList.html";
}
Now that this is taken care of, make sure your /editPlayer/{id}
method is loading the newPlayer.html page:
ALSO add the teams to the model
@GetMapping("/editPlayer/{id}")
public String editPlayer(Model model, @PathVariable int id) {
Player p = da.getPlayer(id);
model.addAttribute("player", p);
return "newPlayer.html";
}
Of course, we need an update method in our database
access class! You should be familiar enough with
coding database access methods by now that this is going
to be really easy, but here's the SQL to get you started
in case you haven't done UPDATE queries in your
database class, yet:
public void updatePlayer(Player player) {
String sql = "UPDATE players SET firstName=:firstName, "
+ "lastName=:lastName, number=:number, team=:team"
+ " WHERE id=:id;";
...
}
Test your program: make sure you can edit an existing record
successfully. You should also make sure the add-new
record still works, also!