In this tutorial, we want to support the persistence of charity objects into the database. We want to allow the user to change all of their fields of the charity object, except the id.
Up to now, the primary key of the charity domain has been the acronym. We've had a constraint on it to ensure uniqueness, but we've also allowed it to be edited since acronyms might change if a charity changes its name. (There could be a case here for keeping furls and acronyms separate and allowing multiple furls to link to a charity so that bookmarks continue to work).
We had a previous issue whereby a charity couldn't be saved because its acronym would always fail the uniqueness test.
By introducing a separate primary key, we can have logic that avoids this problem. Now, if a charity is submitted for insert or update, we can check if the acronym already exists. If it does, and the ids also match, then the charity can be saved because we're doing an update. If it matches and the ids don't match, then another charity already has that id, and we should reject the change.
We introduce a Long field ‘id' on the Charity class and we ripple that change through the DTO and form classes. We have to do some work on the constructors, but Lombok does most of the work.
We need to make sure that the id gets submitted back with the form. We could do this via the form action or via a hidden field in the form - we choose the latter.
<input th:field="*{id}" type="hidden"/>
We need to ripple the id change through to the SQL (add id to the field list in the query) and add the field to the mapper.
We can identify an insert from an update using the id. If the id is missing, then it's an insert and the id will be provided by the database on insert.
In CharityRepository.java.
public void saveCharity(Charity aCharity) {
if (aCharity.getId() == null) {
System.out.println("Inserting charity" + aCharity);
insertCharity(aCharity);
} else {
System.out.println("Updating charity" + aCharity);
updateCharity(aCharity);
}
}
We then delegate to the insert and update methods.
Spring's JDBC support provides a convenience method for inserts called SimpleJdbcInsert. To add a row, create an object to represent the insert (this is akin to the "Command" pattern) specifying the table and the key column. Notice that we have to pass the JdbcTemplate into the constructor of SimpleJdbcInsert. That method won't accept a JdbcOperations object. For me, this is a bug.
Then, create a map for the parameters.
Finally, execute the insert and capture the new key.
In CharityRepository.java.
private Long insertCharity(Charity aCharity) {
SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbc).withTableName("CHARITY").usingGeneratedKeyColumns("id");
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("id", aCharity.getId());
parameters.put("name", aCharity.getName());
parameters.put("acronym", aCharity.getAcronym());
parameters.put("logo_file_name", aCharity.getLogoFileName());
parameters.put("mission", aCharity.getMissionStatement());
parameters.put("registration_id", aCharity.getRegistration());
Number id = insert.executeAndReturnKey(parameters);
return Long.valueOf(id.longValue());
}
There isn't an equivalent for update, so we need to use an update method.
Write the SQL, and provide the list of parameters to be substituted in. In CharityRepository.java.
private void updateCharity(Charity aCharity) {
jdbc.update(
"UPDATE charity " +
"set name = ?, acronym = ?, mission = ?, registration_id = ?, logo_file_name = ? where id = ? ",
aCharity.getName(),
aCharity.getAcronym(),
aCharity.getMissionStatement(),
aCharity.getRegistration(),
aCharity.getLogoFileName(),
aCharity.getId());
}
Previously, checked the acronym field for uniqueness, but (as described above) that isn't sufficient. What we need to check is that the acronym is unique amongst the set of charities taking into account that we're changing one of them.
Therefore, this is not a validation that needs to work at type level (i.e. charity's are uniquely identifiable by their acronym).
So, we change the annotation slightly.
In UniqueFurl.java, change...
@Target({FIELD, PARAMETER})
...to...
@Target({TYPE})
We then need to move the annotation from the field (acronym) to the class level...
In CharityForm.java.
@Data
@AllArgsConstructor
@NoArgsConstructor
@UniqueFurl
public class CharityForm {
We need to change the implementation of the validator. We will delegate the check to the service and ask it to tell us whether the acronym is unique or not.
If it is not valid, we will configure the validator to add the error to the acronym field (so that the message appears against the correct field.)
In UniqueFurlValidator.java.
@Override
public boolean isValid(CharityForm charityForm, ConstraintValidatorContext constraintValidatorContext) {
Boolean isValid = !charitySearch.duplicatingCharityAcronym(charityForm);
//this part ensures that the validation is shown against the correct field.
if (!isValid) {
constraintValidatorContext.disableDefaultConstraintViolation();
constraintValidatorContext
.buildConstraintViolationWithTemplate("Acronym must be unique.")
.addPropertyNode("acronym").addConstraintViolation();// <- show against the acronym field.
}
return isValid;
}
The method in the service asks the repository to find the charity by acronym. If the charity is present, then we have a clash. We then need to check if the id of the charity being changed is the id of the charity that has that acronym. If they match, then we are doing an update.
If the acronym exists and it's not an update, then the we have a clash and return false.
In CharitySearchImpl.java
public Boolean duplicatingCharityAcronym(CharityForm form) {
System.out.println("Checking Charity Form : " + form);
Optional<Charity> aCharity = charityRepository
.findByAcronym(form.getAcronym());
Boolean exists = aCharity.isPresent();
Boolean update = FALSE;
if (aCharity.isPresent()) {
update = aCharity.get().getId().equals(form.getId());
}
return exists && !update;
}
NOTE: there is a refactoring to make in this method. Can you spot the repeated evaluation that can be removed?
The insert and update of charities should now be supported. You should be able to add and amend charity details and see the data change via the console.
This was a case where a change did ripple through, but each change was relatively minor and each part could be tackled in isolation.
Importantly, we've now seen how to select, insert and update into a table. Deleting data would be done using the "update" method on the JdbcTemplate and using a "delete" query.
In tag 031-introduce-db-interface, we refactor some of the naming to be consistent in our use of interfaces and implementations. The interface is now CharityRepository and the implementation is CharityRepositoryJDBC. We put these into separate packages, which causes a change of imports in CharityServiceImpl.
Note: the service layer depends on the interface not on the implementation.