9. Layered Architecture and Data Access

The previous examples stored their data in memory. A real application stores it in a database and is organized into layers (see the diagram in the introduction):

• the web layer (controllers, views): read the request, call the business layer, select the view;
• the business layer: business rules;
• the DAO ([Data Access Object]) layer: database queries.

Each layer is aware only of the layer below it, and as few details about it as possible: the web layer is unaware of SQL, and the business layer is unaware of HTML. This separation makes each layer easier to understand, test, and evolve. Example 28 applies this to managing a list of people: it is, on a smaller scale, the management of doctors and clients from the case study.

In [Java], database access is organized across several layers:

• [JDBC] ([Java Database Connectivity]) is the standard interface for [Java] to send SQL commands to a database. Each SGBD provides a driver [JDBC]: for [MySQL], the library [mysql-connector-j]. Opening a connection is resource-intensive: [Spring Boot] uses a connection pool ([HikariCP]), which keeps a few connections open and lends them to requests;
• [JPA] ([Jakarta Persistence API]) is the standard for [Java] within ORM ([Object-Relational Mapper]): it maps [Java] classes (entities) to database tables and transforms the read rows into objects. [JPA] is merely a standard, consisting of interfaces and annotations; the ORM that implements it is, in this case, [Hibernate];
• [Spring Data JPA] is added on top of this: all you need to do is declare an interface from DAO, and [Spring Data JPA] writes the implementation itself when the application starts, including the queries, which it deduces from the method names.

All of this is provided by the [spring-boot-starter-data-jpa] starter.

9.1. Example 28: A Three-Tier Personnel Management System

28-layers/
├── database/
│   └── dbpersons.sql
├── src/
│   └── main/
│       ├── java/
│       │   └── example28/
│       │       ├── config/
│       │       │   └── WebConfig.java
│       │       ├── controllers/
│       │       │   └── PersonsController.java
│       │       ├── dao/
│       │       │   └── PersonsDao.java
│       │       ├── entities/
│       │       │   └── Person.java
│       │       ├── business/
│       │       │   ├── PersonData.java
│       │       │   ├── BusinessException.java
│       │       │   └── PersonsBusiness.java
│       │       ├── models/
│       │       │   └── PersonForm.java
│       │       └── Example28Application.java
│       └── resources/
│           ├── static/
│           │   ├── css/
│           │   │   └── styles.css
│           │   └── js/
│           │       └── app.js
│           ├── templates/
│           │   ├── fragments/
│           │   │   ├── field.html
│           │   │   └── confirmation.html
│           │   ├── persons/
│           │   │   ├── form.html
│           │   │   └── list.html
│           │   └── layout.html
│           ├── application.properties
│           └── messages.properties
└── pom.xml

9.1.1. The database

The database [dbpersons] is created by a script named SQL. Using [Laragon], you can run it, for example, in the [HeidiSQL] tool (File menu / Run a SQL file), or from the command line, from the example folder:

mysql -u root < database/dbpersons.sql

The [database/dbpersons.sql] file:

-- database of example 28: a single table, [persons]
-- the script is written in UTF-8
SET NAMES utf8mb4;
DROP DATABASE IF EXISTS dbpersons;
CREATE DATABASE dbpersons CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE dbpersons;

CREATE TABLE persons (
  id INT NOT NULL AUTO_INCREMENT,
  -- the version of the row, increased by JPA at each modification
  version INT NOT NULL DEFAULT 0,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY (id),
  -- two persons cannot have the same first and last names
  CONSTRAINT unq_persons_first_name_last_name UNIQUE (first_name, last_name)
) ENGINE = InnoDB;

INSERT INTO persons (first_name, last_name, age) VALUES
  ('Marie', 'Pélissier', 42),
  ('Jacques', 'Bromard', 35),
  ('Philippe', 'Jandot', 58);

• Line 3: The script is written in UTF-8; this is specified to the server so that the accents in the data ([Pélissier]) remain intact;
• line 5: the character set [utf8mb4] accepts all characters (accents, emojis, etc.). The [utf8mb4_unicode_ci] collation compares strings without distinguishing between uppercase and lowercase letters or accents: for [MySQL], [pelissier], and [Pélissier] are equal;
• line 11: a version number, incremented each time the line is modified (see “optimistic lock,” below);
• line 17: a named uniqueness constraint: the database does not allow two people with the same first and last names. The name will be used to identify the error.

9.1.2. The configuration

The file [pom.xml]:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>course.springmvc</groupId>
    <artifactId>examples</artifactId>
    <version>1.0</version>
  </parent>
  <artifactId>example28</artifactId>

  <dependencies>
    <!-- the Thymeleaf views -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
    <!-- validation (Bean Validation, Hibernate Validator) -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>
    <!-- Spring Data JPA (Hibernate) and the MySQL JDBC driver -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <scope>runtime</scope>
    </dependency>
  </dependencies>
</project>

• Lines 20–23: data validation (see Chapter 3);
• lines 25–28: [Spring Data JPA], along with [Hibernate], [HikariCP], and the transaction management for [Spring];
• lines 29–33: the driver [JDBC] for [MySQL]. The scope of [runtime] indicates that the application code does not use it directly (it only recognizes [JPA]): the driver is only required at runtime. It also works with [MariaDB].

The file [application.properties]:

# the listening port of the server (8080 by default)
server.port=5000
# the error responses of Spring Boot do not contain the call stack
spring.web.error.include-stacktrace=never
# the session (which carries the flash attributes) is tracked by a cookie only
server.servlet.session.tracking-modes=cookie

# the connection to the database: JDBC URL, user, password (those of Laragon)
spring.datasource.url=jdbc:mysql://localhost:3306/dbpersons
spring.datasource.username=root
spring.datasource.password=
# Hibernate neither creates nor modifies the tables: that is the role of the SQL script
spring.jpa.hibernate.ddl-auto=none
# the connection to the database is not kept open while the view is being built
spring.jpa.open-in-view=false
# true: the SQL statements sent to the database are displayed in the console
spring.jpa.show-sql=false

• line 6: success messages are sent as Flash attributes (see Example 20), stored in the session: the session cookie is sufficient to retrieve it, without the session ID being added to URL;
• Lines 9–11: the connection to the database for the data source ([DataSource]) that [Spring Boot] creates using [HikariCP]: URL [JDBC] (driver, server, port, database), the username, and the password. These are the default values for [Laragon] (user [root], no password). The database type (and its version) does not need to be specified: [Hibernate] requests this information from the server upon the first connection and adjusts the SQL it generates accordingly;
• line 13: [Hibernate] is capable of creating or modifying tables based on entities; this is prohibited ([none]): the database is created by the script SQL, and it serves as the reference;
• line 15: By default, [Spring Boot] keeps the [JPA] session (and its connection to the database) open until the view ([Open Session In View]) is fully generated: a view could then trigger SQL queries without the user noticing. This behavior is disabled: all reads are performed by the business layer before the view. When this property is not specified, [Spring Boot] reports this at startup with a warning ([spring.jpa.open-in-view is enabled by default...]);
• Line 17: [true] displays in the console each SQL command sent by [Hibernate] (see the execution below).

The file [messages.properties] contains a conversion error message (an age [abc]), as in Example 19. The class [config/WebConfig.java] serves [Bootstrap], as in the previous examples.

9.1.3. The Person Entity

The file [entities/Person.java]:

package example28.entities;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import jakarta.persistence.Version;

// a row of the [persons] table
@Entity
@Table(name = "persons")
public class Person {

    // the primary key, generated by the database (AUTO_INCREMENT); JPA reads its
    // value again after each INSERT
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    // optimistic locking: JPA increases the version at each UPDATE, and adds
    // the condition "AND version = <value read>"
    @Version
    @Column(name = "version")
    private Integer version;

    @Column(name = "first_name", length = 30)
    private String firstName;

    @Column(name = "last_name", length = 30)
    private String lastName;

    @Column(name = "age")
    private int age;

    // a constructor without parameters is required by JPA
    public Person() {
    }

    public Person(String firstName, String lastName, int age) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.age = age;
    }

    public Integer getId() { return id; }

    public Integer getVersion() { return version; }

    public String getFirstName() { return firstName; }
    public void setFirstName(String firstName) { this.firstName = firstName; }

    public String getLastName() { return lastName; }
    public void setLastName(String lastName) { this.lastName = lastName; }

    public int getAge() { return age; }
    public void setAge(int age) { this.age = age; }
}

• Line 12: [@Entity] creates a [JPA] entity from the [Person] class: each row in the table will be a [Person] object. The annotations come from the [jakarta.persistence] package: these are the ones from the [JPA] standard, not those from [Hibernate];
• line 13: the corresponding table (by default, this would be [Person]);
• Lines 18–21: the primary key ([@Id]). [@GeneratedValue(strategy = GenerationType.IDENTITY)] indicates that its value is assigned by the database ([AUTO_INCREMENT]): [Hibernate] reads the assigned value after each [INSERT]. The type [Integer]—not [int]—allows the value [null]: a person who has not yet been registered does not have an ID. [@Column] specifies the column name corresponding to each field;
• lines 25–27: the version column. [@Version] assigns the optimistic lock to [Hibernate]: it sets the version to 0 during [INSERT], increases it by 1 with each [UPDATE], and adds the condition [AND version = <value read>] with each [UPDATE] (and with each [DELETE]). We’ll see later what this is for;
• lines 29–36: the other columns. [length] specifies the column width: it would only be relevant if [Hibernate] were creating the table itself, which is not the case here (see [ddl-auto]);
• lines 39–40: [JPA] requires a parameterless constructor: this is the one that [Hibernate] uses to create an object from a read row, before filling in its fields;
• lines 48–59: the accessors (methods [getX] and [setX]): the entity is a JavaBean. There is no [setId] or [setVersion]: these two values are written only by [Hibernate], which accesses the fields directly.

9.1.4. The DAO layer

The [dao/PersonsDao.java] file:

package example28.dao;

import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;

import example28.entities.Person;

// the DAO layer: queries, no business rule.
// An interface, which Spring Data JPA IMPLEMENTS itself at startup.
// JpaRepository<Person, Integer> already brings findById, save, deleteById...
public interface PersonsDao extends JpaRepository<Person, Integer> {

    // the name of the method describes the query:
    // SELECT ... FROM persons ORDER BY last_name, first_name
    List<Person> findAllByOrderByLastNameAscFirstNameAsc();

    // a person with the same first and last names, other than [exceptId]:
    // SELECT ... WHERE first_name = ? AND last_name = ? AND id <> ? LIMIT 1
    Optional<Person> findFirstByFirstNameAndLastNameAndIdNot(String firstName, String lastName, Integer exceptId);
}

This is the entire DAO layer: an interface, without a single line of executable code.

• Line 13: The interface inherits from [JpaRepository<Person, Integer>] (an entity [Person], whose key is a [Integer]). At startup, [Spring Data JPA] creates a class that implements this interface and turns it into a bean, which is then injected. [JpaRepository] already provides the standard methods: [findById] (read by key, result in a [Optional]), [findAll], [save] ([INSERT] or [UPDATE]), [saveAndFlush], [deleteById], [count]... ;
• Line 17: a derived query: [Spring Data JPA] analyzes the method name and derives the query from it. [findAll]: all people; [By]: without conditions, since nothing follows; [OrderByLastNameAscFirstNameAsc]: sorted by last name, then by first name, in ascending order;
• line 21: [findFirst]: the first person found ([LIMIT 1]), or an empty [Optional]; [ByFirstNameAndLastNameAndIdNot]: whose first and last names match those provided as parameters, and whose ID differs from the third parameter. This is useful when modifying a record, which must not conflict with itself. The comparison is performed by [MySQL], and thus follows the database’s collation rules: it ignores uppercase letters and accents.

An error in a method name ([findAllByOrderByLastNamee]) is detected at startup: the application refuses to start, reporting an unknown property. A query that is too complex for a method name is written in the language [JPQL] ([Jakarta Persistence Query Language], a SQL that refers to entities and their fields rather than tables), in a [@Query] annotation: the case study uses a few of these.

9.1.5. The business layer

The file [business/BusinessException.java]:

package example28.business;

// a business rule rejects the operation: the message is intended for the user
public class BusinessException extends RuntimeException {

    public BusinessException(String message) {
        super(message);
    }
}

A [BusinessException] indicates that a business rule is rejecting an operation. This is not a program error: its message is intended for the user. It inherits from [RuntimeException]: it is an unchecked exception that a method can throw without declaring it ([throws]). We will see that this choice also applies to transactions.

The file [business/PersonData.java]:

package example28.business;

// the data of a person, as the web layer passes them on
public record PersonData(String firstName, String lastName, int age) {
}

A person’s data, as transmitted by the web layer: the business layer does not know the form class.

The [business/PersonsBusiness.java] file:

package example28.business;

import java.util.List;
import java.util.Objects;

import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.orm.ObjectOptimisticLockingFailureException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import example28.dao.PersonsDao;
import example28.entities.Person;

// the BUSINESS layer: the business rules, and only them.
// @Transactional on the class: each public method runs inside a
// transaction, committed (COMMIT) at the end of the method, cancelled (ROLLBACK) if
// it throws an exception
@Service
@Transactional
public class PersonsBusiness {

    private static final String NAMESAKE = "A person already has these first and last names.";
    private static final String MODIFIED = "This record has been modified meanwhile: reload it.";

    private final PersonsDao dao;

    public PersonsBusiness(PersonsDao dao) {
        this.dao = dao;
    }

    // readOnly: a read-only transaction
    @Transactional(readOnly = true)
    public List<Person> list() {
        return dao.findAllByOrderByLastNameAscFirstNameAsc();
    }

    @Transactional(readOnly = true)
    public Person read(int id) {
        return dao.findById(id).orElseThrow(() -> new BusinessException("This person does not exist (anymore)."));
    }

    public Person add(PersonData data) {
        // rule 1: no two persons with the same first and last names
        if (dao.findFirstByFirstNameAndLastNameAndIdNot(data.firstName(), data.lastName(), 0).isPresent()) {
            throw new BusinessException(NAMESAKE);
        }
        return save(new Person(data.firstName(), data.lastName(), data.age()));
    }

    public Person edit(int id, Integer version, PersonData data) {
        Person person = read(id);
        // rule 2: we do not modify a record modified meanwhile by someone else
        if (!Objects.equals(person.getVersion(), version)) {
            throw new BusinessException(MODIFIED);
        }
        if (dao.findFirstByFirstNameAndLastNameAndIdNot(data.firstName(), data.lastName(), id).isPresent()) {
            throw new BusinessException(NAMESAKE);
        }
        // the entity read is "tracked" by JPA: modifying it is enough
        person.setFirstName(data.firstName());
        person.setLastName(data.lastName());
        person.setAge(data.age());
        return save(person);
    }

    public Person delete(int id) {
        Person person = read(id);
        dao.deleteById(id);
        return person;
    }

    // saveAndFlush: INSERT or UPDATE sent RIGHT AWAY to the database (and not at
    // COMMIT), to be able to catch the database errors here
    private Person save(Person person) {
        try {
            return dao.saveAndFlush(person);
        } catch (ObjectOptimisticLockingFailureException e) {
            // the UPDATE "WHERE id = ... AND version = ..." modified no row
            throw new BusinessException(MODIFIED);
        } catch (DataIntegrityViolationException e) {
            // two simultaneous additions: the uniqueness constraint of the database rejected the second
            if (String.valueOf(e.getMostSpecificCause().getMessage()).contains("unq_persons_first_name_last_name")) {
                throw new BusinessException(NAMESAKE);
            }
            throw e;
        }
    }
}

Let’s comment on this code:

• line 18: the business layer is a service bean;
• line 19: [@Transactional], attached to the class, applies to each of its public methods: the method executes within a transaction that is opened ([START TRANSACTION]) before it is called, committed ([COMMIT]) upon its return, and rolled back ([ROLLBACK]) if it throws an unchecked exception. As with [@PreAuthorize] (Example 26), [Spring] wraps the bean in a proxy, which opens and closes the transaction around the call. Everything the method does, including calls to DAO, takes place within the same transaction and on the same connection;
• lines 32–35: [readOnly = true]: a read-only transaction, which exempts [Hibernate] from having to check for modified entities at the end;
• lines 37–40: An absent person is a [BusinessException]. [orElseThrow] returns the contents of [Optional], or throws the exception generated by the lambda if it is empty;
• lines 42–48: the addition. Rule: no two people can have the same first and last names. This is checked before writing to display a clear message. No person has the ID 0: [IdNot] does not remove anyone from the search here;
• lines 50–64: the update. Additional rule: the optimistic lock (lines 53–55). The update form uses the version of the record as it was when the form was opened. If it has changed in the meantime (another user has edited the same record), the update is rejected rather than silently overwriting the user’s work. [Objects.equals] compares two [Integer] instances while accepting [null];
• Line 51: The call to [read] is an internal call within the class: it does not go through the proxy, and its [readOnly] does not apply. [read] simply executes within the transaction of [edit];
• lines 60–63: the entity read in the transaction is followed by [Hibernate] (it is said to be “managed”): simply modify its fields; [Hibernate] detects the changes and generates a [UPDATE] for the table’s columns;
• Lines 66–70: Deletion. The person is read first—to verify that they exist and so that the controller can mention their name;
• Lines 74–87: The record. Normally, [Hibernate] sends [INSERT] and [UPDATE] to the database only at the end of the transaction, at [COMMIT], i.e., after exiting the method: a database error could no longer be intercepted here. [saveAndFlush] saves the entity and immediately sends ([flush]) the pending SQL commands;
• Lines 77–79: The version check mentioned earlier is not always sufficient: two users can save the same record at the same time, and both pass the check. The second user’s [UPDATE ... WHERE id = ? AND version = ?] then does not modify any rows; [Hibernate] detects this, and [Spring] raises a [ObjectOptimisticLockingFailureException], which becomes the same [BusinessException];
• lines 80–85: similarly, two simultaneous additions by the same person pass the homonym check, but the database’s uniqueness constraint does not allow them to be accepted. [Spring] translates the exceptions from the various databases and the various ORM entries into a common hierarchy of exceptions: a constraint violation becomes a [DataIntegrityViolationException]. [getMostSpecificCause()] returns the original exception—that of the [MySQL] driver—whose message specifies the name of the constraint ([Duplicate entry '...' for key 'unq_persons_first_name_last_name']). Another constraint violation continues onward (line 85): this is an anomaly.

In all cases where an exception exits a method of [PersonsBusiness] (including [BusinessException], since it inherits from [RuntimeException]), the proxy rolls back the transaction: none of the method’s actions are retained. Note: By default, a checked exception (which inherits from [Exception] without inheriting from [RuntimeException]) does not cause a rollback.

We verified this latter case by sending 30 requests to add the same person at the same time: only one was accepted; for the others, the database rejected [INSERT] (error [MySQL] No. 1062, which [Hibernate] reports in the console as a warning [HHH000247]), and the user received the "namesake" message, not a 500 error.

9.1.6. The Web Layer

The file [models/PersonForm.java]:

package example28.models;

import jakarta.validation.constraints.Max;
import jakarta.validation.constraints.Min;
import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.NotNull;
import jakarta.validation.constraints.Size;

// the form fields and their constraints (see example 19)
public class PersonForm {

    @NotBlank(message = "The first name is required.")
    @Size(max = 30, message = "30 characters at most.")
    private String firstName;

    @NotBlank(message = "The last name is required.")
    @Size(max = 30, message = "30 characters at most.")
    private String lastName;

    @NotNull(message = "The age is required.")
    @Min(value = 0, message = "The age must be between 0 and 150.")
    @Max(value = 150, message = "The age must be between 0 and 150.")
    private Integer age;

    // the version of the record, carried by a hidden field (edit only)
    private Integer version;

    public String getFirstName() { return firstName; }
    public void setFirstName(String firstName) { this.firstName = firstName; }

    public String getLastName() { return lastName; }
    public void setLastName(String lastName) { this.lastName = lastName; }

    public Integer getAge() { return age; }
    public void setAge(Integer age) { this.age = age; }

    public Integer getVersion() { return version; }
    public void setVersion(Integer version) { this.version = version; }
}

The form and its validation constraints (see Example 19), with the record version (line 26) passed via a hidden field. The age is a [Integer] and not a [int]: an empty field yields [null], which is indicated by [@NotNull].

The file [controllers/PersonsController.java]:

package example28.controllers;

import org.springframework.beans.propertyeditors.StringTrimmerEditor;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.WebDataBinder;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.InitBinder;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import example28.entities.Person;
import example28.business.PersonData;
import example28.business.BusinessException;
import example28.business.PersonsBusiness;
import example28.models.PersonForm;
import jakarta.validation.Valid;

// the WEB layer: it only knows the business layer
@Controller
public class PersonsController {

    private final PersonsBusiness business;

    public PersonsController(PersonsBusiness business) {
        this.business = business;
    }

    // the received strings are stripped of their superfluous spaces (see example 19)
    @InitBinder
    public void initBinder(WebDataBinder binder) {
        binder.registerCustomEditor(String.class, new StringTrimmerEditor(true));
    }

    @GetMapping("/")
    public String home() {
        return "redirect:/persons";
    }

    @GetMapping("/persons")
    public String list(Model model) {
        model.addAttribute("persons", business.list());
        return "persons/list";
    }

    @GetMapping("/persons/add")
    public String addForm(Model model) {
        return form(model, "Add a person", "/persons/add", new PersonForm(), null);
    }

    @PostMapping("/persons/add")
    public String add(@Valid @ModelAttribute("person") PersonForm input, BindingResult result,
            Model model, RedirectAttributes redirect) {
        if (result.hasErrors()) {
            return form(model, "Add a person", "/persons/add", input, null);
        }
        try {
            Person p = business.add(new PersonData(input.getFirstName(), input.getLastName(), input.getAge()));
            redirect.addFlashAttribute("success", p.getFirstName() + " " + p.getLastName() + " has been added.");
            return "redirect:/persons";
        } catch (BusinessException e) {
            // a business rule refuses: we display the form again, the message at the bottom of the page
            return form(model, "Add a person", "/persons/add", input, e.getMessage());
        }
    }

    @GetMapping("/persons/{id:\\d+}/edit")
    public String editForm(@PathVariable int id, Model model, RedirectAttributes redirect) {
        try {
            Person p = business.read(id);
            PersonForm values = new PersonForm();
            values.setFirstName(p.getFirstName());
            values.setLastName(p.getLastName());
            values.setAge(p.getAge());
            values.setVersion(p.getVersion());
            return form(model, "Edit a person", "/persons/" + id + "/edit", values, null);
        } catch (BusinessException e) {
            redirect.addFlashAttribute("error", e.getMessage());
            return "redirect:/persons";
        }
    }

    @PostMapping("/persons/{id:\\d+}/edit")
    public String edit(@PathVariable int id, @Valid @ModelAttribute("person") PersonForm input,
            BindingResult result, Model model, RedirectAttributes redirect) {
        String action = "/persons/" + id + "/edit";
        if (result.hasErrors()) {
            return form(model, "Edit a person", action, input, null);
        }
        try {
            PersonData data = new PersonData(input.getFirstName(), input.getLastName(), input.getAge());
            Person p = business.edit(id, input.getVersion(), data);
            redirect.addFlashAttribute("success", p.getFirstName() + " " + p.getLastName() + " has been edited.");
            return "redirect:/persons";
        } catch (BusinessException e) {
            return form(model, "Edit a person", action, input, e.getMessage());
        }
    }

    @PostMapping("/persons/{id:\\d+}/delete")
    public String delete(@PathVariable int id, RedirectAttributes redirect) {
        try {
            Person p = business.delete(id);
            redirect.addFlashAttribute("success", p.getFirstName() + " " + p.getLastName() + " has been deleted.");
        } catch (BusinessException e) {
            // no form to display again: the error also travels as a flash attribute
            redirect.addFlashAttribute("error", e.getMessage());
        }
        return "redirect:/persons";
    }

    // the model of the form view: title, address to send to, values, global error
    private String form(Model model, String title, String action, PersonForm values, String error) {
        model.addAttribute("title", title);
        model.addAttribute("action", action);
        model.addAttribute("person", values);
        model.addAttribute("error", error);
        return "persons/form";
    }
}

Let’s break down this code:

• lines 26–30: the controller only recognizes the business layer;
• lines 33–36: the received strings are stripped of their leading and trailing spaces, and an empty string becomes [null] (see Example 19);
• lines 38–41: the site root redirects to the list;
• lines 54–68: the addition, in three steps: form validation (errors: reload), call to the business layer (success: flash message and redirection; see Example 20), [BusinessException] (reload with the message at the bottom of the page). Another exception is not caught: this is an anomaly that will result in a 500 error;
• Line 70: [{id:\\d+}]: a path variable constrained by a regular expression (see Example 02): [\d+] refers to a sequence of digits (the backslash is doubled in the string [Java]). [/persons/abc/edit] does not correspond to any action: error 404;
• lines 70–84: the update form, pre-filled with the record read from the database, including its version;
• lines 86–101: same structure as the add operation; the version returned by the form is passed to the business layer;
• lines 103–113: deletion. There is no form to redisplay: any errors are also passed via the flash attribute;
• lines 116–122: the form view template, used for both adding and modifying records: different title and mailing address, form values, and business layer error.

The [templates/persons/form.html] file:

<!doctype html>
<html xmlns:th="http://www.thymeleaf.org" th:replace="~{layout :: page(~{::main})}">
<body>
<main>
  <h1 class="h3" th:text="${title}">Add a person</h1>
  <form method="post" th:action="${action}" th:object="${person}" novalidate>
    <!--/* the version of the record read: it will come back with the form */-->
    <input type="hidden" th:field="*{version}">
    <div th:replace="~{fragments/field :: field(name='firstName', label='First name', type='text')}"></div>
    <div th:replace="~{fragments/field :: field(name='lastName', label='Last name', type='text')}"></div>
    <div th:replace="~{fragments/field :: field(name='age', label='Age', type='number')}"></div>
    <button class="btn btn-primary" type="submit">Save</button>
    <a class="btn btn-link" href="/persons">Cancel</a>
  </form>
</main>
</body>
</html>

• Line 6: [th:object] identifies the form object; [th:action] receives the submission address provided by the controller;
• line 8: the hidden field that carries the record version: [th:field="*{version}"] generates its attributes [name] and [value] (see Example 19);
• Lines 9–11: the three fields generated by the fragment [field] from Example 19.

The file [templates/layout.html]:

<!doctype html>
<html lang="en" xmlns:th="http://www.thymeleaf.org" th:fragment="page(content)">
<head>
  <meta charset="utf-8">
  <title>Persons</title>
  <link rel="stylesheet" href="/bootstrap/css/bootstrap.min.css">
  <link rel="stylesheet" href="/css/styles.css">
</head>
<body>
  <div class="container py-3" style="max-width: 40rem">
    <!--/* the success message (flash attribute, after a redirect): at the top */-->
    <div class="alert alert-success" role="status" th:if="${success != null}" th:text="${success}">success</div>
    <main th:replace="${content}"></main>
    <!--/* the error: from the current request, or from before the redirect (flash attribute) */-->
    <div class="error-area" role="alert" th:if="${error != null}" th:text="${error}">error</div>
  </div>
  <div th:replace="~{fragments/confirmation :: confirmation}"></div>
  <script src="/js/app.js"></script>
</body>
</html>

• line 12: the success message, at the top of the page;
• line 15: the error message at the bottom of the page: either the one from the current request (placed in the template by [form]) or the one stored as a flash attribute before a redirect. In both cases, it is a [error] attribute of the view template;
• lines 17–18: the confirmation window and its script (see Example 21).

The view [persons/list.html] has the same structure as the contact list in Example 21: for each person, a link to the edit form and a POST delete form, the submission of which is confirmed. The fragment [fragments/field.html] is the same as in Example 19, while the window [fragments/confirmation.html] and the script [static/js/app.js] are the same as in Example 21, with no changes.

9.1.7. Execution

If the values in [application.properties] are not suitable (a password for [root], for example), they can be replaced—without modifying the file—with environment variables: the property name is written in uppercase, with periods replaced by underscores. Under [Windows] ([PowerShell]):

$env:SPRING_DATASOURCE_PASSWORD="secret"; ..\mvnw spring-boot:run

Upon startup, with [MariaDB], [Hibernate] displays a warning:

... WARN ... org.hibernate.orm.core : HHH000511: The 5.5.5 version for [org.hibernate.dialect.MySQLDialect] is no longer supported, hence certain features may not work properly.The minimum supported version is 8.0.0. Check the community dialects project for available legacy versions.

This has no consequences: [MariaDB] presents itself to the [MySQL] driver with a version number of “5.5.5,” which [Hibernate] interprets as a very old [MySQL]. With [MySQL] 8, it does not appear.

The list, in URLhttp://localhost:5000/:

An invalid form:

A homonym: the business rule detects it. [MySQL], when compared using the database’s collation, rejects [marie pelissier]—written in lowercase and without accents—as well. Here, the entry [Marie Pélissier]:

A successful addition:

With [spring.jpa.show-sql=true] in [application.properties] (or, without modifying the file, [../mvnw spring-boot:run -Dspring-boot.run.arguments=--spring.jpa.show-sql=true]), the console displays each SQL command sent by [Hibernate]. To add a person, then display the list following the redirection:

Hibernate: select p1_0.id,p1_0.age,p1_0.first_name,p1_0.last_name,p1_0.version from persons p1_0 where p1_0.first_name=? and p1_0.last_name=? and p1_0.id<>? limit ?
Hibernate: insert into persons (age,first_name,last_name,version) values (?,?,?,?)
Hibernate: select p1_0.id,p1_0.age,p1_0.first_name,p1_0.last_name,p1_0.version from persons p1_0 order by p1_0.last_name,p1_0.first_name

Here we can see the search for a namesake (the query derived from the name [findFirstByFirstNameAndLastNameAndIdNot]), the [INSERT], and then the reading of the list ([findAllByOrderByLastNameAscFirstNameAsc]). The values are passed separately as parameters (the [?]): this is what protects against SQL injections. Transaction commands ([START TRANSACTION], [COMMIT]) are not displayed. To modify a master record:

Hibernate: select p1_0.id,p1_0.age,p1_0.first_name,p1_0.last_name,p1_0.version from persons p1_0 where p1_0.id=?
Hibernate: select p1_0.id,p1_0.age,p1_0.first_name,p1_0.last_name,p1_0.version from persons p1_0 where p1_0.first_name=? and p1_0.last_name=? and p1_0.id<>? limit ?
Hibernate: update persons set age=?,first_name=?,last_name=?,version=? where id=? and version=?

[UPDATE] writes the new version and checks the old one ([where id=? and version=?]): this is the optimistic lock. And for a deletion:

Hibernate: select p1_0.id,p1_0.age,p1_0.first_name,p1_0.last_name,p1_0.version from persons p1_0 where p1_0.id=?
Hibernate: delete from persons where id=? and version=?

[deleteById] did not re-read the record: it was already being tracked in the transaction since the call to [read].

The optimistic lock: two forms are open on the same record; the first is saved (the version changes from 0 to 1); the second, which still has version 0, is rejected:

Deletion, with confirmation: