Skip to content
Tutorials Center
My learning

Topics

Lesson 112 of 119 ยท Projects

Project: Spring MVC CRUD with MySQL

Build a server-rendered inventory app with Spring MVC, Thymeleaf, Spring Data JPA, Flyway and MySQL - list with search and paging, add, edit, delete, validation and tests.

Most business software is a set of forms over a database: list the records, add one, change one, delete one. This project builds exactly that, an inventory manager for products, with the stack that a large share of Java shops use for internal tools: Spring MVC controllers, Thymeleaf pages, Spring Data JPA repositories and MySQL. There is no JavaScript framework and no JSON; the browser submits forms and the server answers with HTML.

You will see how the layers fit together (controller, service, repository), how a form is bound to an object and validated, why every successful POST ends in a redirect, how paging and search work, and how to test it. Spring, Thymeleaf and MySQL can't run in this page's editor, so the application is a guided walkthrough: every file is shown with its path, in the order you would create it. Two runnable examples show what happens underneath: the SQL that the repository produces (run against H2 in MySQL mode), and a plain-Java simulation of the request flow with real validation rules, flash messages and the post/redirect/get cycle. The background lessons are Spring MVC, Thymeleaf, Spring Data JPA and Validation and Error Handling.

What we are building

Page Request What happens
Product list GET /products?q=lamp&page=0 Search by name, 5 products per page, links to other pages
New product form GET /products/new Empty form
Create POST /products Validate; on errors show the form again; otherwise save and redirect to the list with a message
Edit form GET /products/{id}/edit Form filled from the database; 404 for an unknown id
Update POST /products/{id} Same validation, then save and redirect
Delete POST /products/{id}/delete Delete and redirect (a POST, because HTML forms can't send DELETE)

A product has a name, a SKU (stock-keeping unit, unique), a price and a quantity in stock.

flowchart LR
  B["Browser"] --> C["ProductController<br/>@Controller"]
  C --> S["ProductService<br/>@Transactional"]
  S --> R["ProductRepository<br/>Spring Data JPA"]
  R --> D[("MySQL 8.4")]
  C --> V["Thymeleaf templates<br/>products/list, products/form"]
  V --> B

The controller handles HTTP and chooses a view; the service holds the rules and transactions; the repository talks to MySQL.

Project layout

TEXT
inventory/
  compose.yaml
  pom.xml
  src/main/java/com/example/inventory/
    InventoryApplication.java
    product/
      Product.java
      ProductRepository.java
      ProductForm.java
      ProductService.java
      ProductController.java
      ProductNotFoundException.java
      DuplicateSkuException.java
  src/main/resources/
    application.properties
    db/migration/V1__create_product.sql
    templates/products/list.html
    templates/products/form.html
  src/test/java/com/example/inventory/product/
    ProductControllerTest.java

Packages are organized by feature (product), not by layer, so everything you change for "products" is in one folder.

MySQL with Docker Compose

Run the database in a container so that nobody has to install it (Docker for Java Developers):

YAML
# compose.yaml
services:
  mysql:
    image: mysql:8.4
    environment:
      MYSQL_DATABASE: inventory
      MYSQL_USER: inventory
      MYSQL_PASSWORD: ${DB_PASSWORD:-inventory-dev}
      MYSQL_ROOT_PASSWORD: ${DB_ROOT_PASSWORD:-root-dev}
    ports:
      - "127.0.0.1:3306:3306"          # reachable from this machine only
    volumes:
      - mysql-data:/var/lib/mysql
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-uroot", "-p${DB_ROOT_PASSWORD:-root-dev}"]
      interval: 5s
      retries: 20

volumes:
  mysql-data:

docker compose up -d starts it. The defaults are for development only; production values come from the environment, never from source control.

The build and the configuration

The pom.xml follows the one in Project: Task API with Spring Boot (Spring Boot 4 parent, Java 25). Only the dependencies differ:

XML
<!-- pom.xml (dependencies) -->
<dependencies>
  <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-webmvc</artifactId></dependency>
  <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId></dependency>
  <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId></dependency>
  <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-validation</artifactId></dependency>
  <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-flyway</artifactId></dependency>
  <dependency><groupId>org.flywaydb</groupId><artifactId>flyway-mysql</artifactId></dependency>
  <dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><scope>runtime</scope></dependency>

  <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-webmvc-test</artifactId><scope>test</scope></dependency>
</dependencies>

In Spring Boot 3 the web starter is called spring-boot-starter-web and Flyway needs only flyway-core plus flyway-mysql. The configuration points at the container, with environment variables overriding the defaults:

PROPERTIES
# src/main/resources/application.properties
spring.application.name=inventory

spring.datasource.url=jdbc:mysql://localhost:3306/inventory
spring.datasource.username=${DB_USER:inventory}
spring.datasource.password=${DB_PASSWORD:inventory-dev}

# Flyway owns the schema; Hibernate only checks that the entity matches it
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.open-in-view=false
spring.jpa.show-sql=true

spring.thymeleaf.cache=false

ddl-auto=validate is the safe choice for MySQL: if the entity and the table disagree, the application refuses to start, instead of silently creating or dropping columns. open-in-view=false keeps database sessions out of the view rendering, so a lazy association touched in a template fails loudly instead of firing hidden queries. The schema lives in a migration (Flyway Database Migrations):

SQL
-- src/main/resources/db/migration/V1__create_product.sql
CREATE TABLE product (
    id          BIGINT       NOT NULL AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL,
    sku         VARCHAR(20)  NOT NULL,
    price_cents INT          NOT NULL,
    quantity    INT          NOT NULL,
    created_at  DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    CONSTRAINT uk_product_sku UNIQUE (sku),
    CONSTRAINT ck_product_price CHECK (price_cents >= 0),
    CONSTRAINT ck_product_quantity CHECK (quantity >= 0)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

Money is stored as integer cents, never double, so there is no rounding drift. The database also enforces the rules (unique SKU, no negative numbers) because the application is not the only thing that could ever write to this table.

The entity and the repository

Java
// src/main/java/com/example/inventory/product/Product.java
package com.example.inventory.product;

import jakarta.persistence.*;
import java.time.Instant;

@Entity
@Table(name = "product")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)       // MySQL AUTO_INCREMENT
    private Long id;

    @Column(nullable = false, length = 100)
    private String name;

    @Column(nullable = false, length = 20, unique = true)
    private String sku;

    @Column(name = "price_cents", nullable = false)
    private int priceCents;

    @Column(nullable = false)
    private int quantity;

    @Column(name = "created_at", nullable = false, insertable = false, updatable = false)
    private Instant createdAt;                                 // filled by the database default

    protected Product() { }                                    // required by JPA

    public Product(String name, String sku, int priceCents, int quantity) {
        this.name = name;
        this.sku = sku;
        this.priceCents = priceCents;
        this.quantity = quantity;
    }

    public void change(String name, String sku, int priceCents, int quantity) {
        this.name = name;
        this.sku = sku;
        this.priceCents = priceCents;
        this.quantity = quantity;
    }

    public Long getId() { return id; }
    public String getName() { return name; }
    public String getSku() { return sku; }
    public int getPriceCents() { return priceCents; }
    public int getQuantity() { return quantity; }
    public Instant getCreatedAt() { return createdAt; }
}
Java
// src/main/java/com/example/inventory/product/ProductRepository.java
package com.example.inventory.product;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Long> {

    Page<Product> findByNameContainingIgnoreCase(String name, Pageable pageable);

    boolean existsBySku(String sku);

    boolean existsBySkuAndIdNot(String sku, Long id);
}

There is no implementation. Spring Data builds one at startup, and the method names are the query: findByNameContainingIgnoreCase becomes a select ... where upper(name) like upper(?) with limit/offset plus a select count(*) for the page's total. save, findById, deleteById and findAll(Pageable) come from JpaRepository. The first runnable example shows the SQL for each operation.

The SQL behind the repository

The program creates the same table in H2 (an in-memory database with a MySQL compatibility mode) and runs, with plain JDBC, the statements that Spring Data and Hibernate produce for this application. Compare each one with a repository method.

Java The SQL behind ProductRepository

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Note the real limit/offset paging and the separate count(*): that second query is what lets the page say "page 2 of 3". Hibernate with MySQL produces essentially the same statements, with like upper(?) escape '\\' and column aliases. Paging by offset gets slow on millions of rows; for an inventory screen it is fine.

The form object and validation

The form is not the entity. Entities describe tables; a form describes what a person may type, as text, and carries the validation rules. Keeping them apart means a user can never set fields you didn't put on the form (such as id) just by adding parameters to the request, which is the mass assignment problem. A plain class with getters and setters is the most compatible choice for Thymeleaf's th:field:

Java
// src/main/java/com/example/inventory/product/ProductForm.java
package com.example.inventory.product;

import jakarta.validation.constraints.*;
import java.math.BigDecimal;

public class ProductForm {

    @NotBlank(message = "Please enter a name")
    @Size(max = 100, message = "At most 100 characters")
    private String name;

    @NotBlank(message = "Please enter a SKU")
    @Pattern(regexp = "[A-Z0-9-]{3,20}", message = "Use 3 to 20 capital letters, digits or hyphens")
    private String sku;

    @NotNull(message = "Please enter a price")
    @DecimalMin(value = "0.00", message = "The price cannot be negative")
    @Digits(integer = 6, fraction = 2, message = "Use at most 2 decimals")
    private BigDecimal price;

    @NotNull(message = "Please enter a quantity")
    @Min(value = 0, message = "The quantity cannot be negative")
    private Integer quantity;

    public static ProductForm from(Product product) {
        ProductForm form = new ProductForm();
        form.name = product.getName();
        form.sku = product.getSku();
        form.price = BigDecimal.valueOf(product.getPriceCents(), 2);     // 3499 -> 34.99
        form.quantity = product.getQuantity();
        return form;
    }

    public int priceInCents() { return price.movePointRight(2).intValueExact(); }

    public String getName() { return name; }
    public void setName(String name) { this.name = name == null ? null : name.trim(); }
    public String getSku() { return sku; }
    public void setSku(String sku) { this.sku = sku == null ? null : sku.trim().toUpperCase(java.util.Locale.ROOT); }
    public BigDecimal getPrice() { return price; }
    public void setPrice(BigDecimal price) { this.price = price; }
    public Integer getQuantity() { return quantity; }
    public void setQuantity(Integer quantity) { this.quantity = quantity; }
}

The setters normalize input (trim, upper-case the SKU), so the rules see clean values. When a user types abc into the price field, Spring can't convert it to a BigDecimal; it records a type mismatch error on the field instead of throwing, and you show it next to the input (you can customize the text in messages.properties with the key typeMismatch.price). The only BigDecimal conversion left is priceInCents(), and @Digits guarantees it can't overflow or lose digits, so intValueExact() never throws for validated input.

The service

Java
// src/main/java/com/example/inventory/product/ProductService.java
package com.example.inventory.product;

import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional(readOnly = true)
public class ProductService {

    private final ProductRepository products;

    public ProductService(ProductRepository products) {
        this.products = products;
    }

    public Page<Product> search(String query, int page, int size) {
        PageRequest request = PageRequest.of(Math.max(page, 0), size, Sort.by("name").ascending());
        return products.findByNameContainingIgnoreCase(query.trim(), request);
    }

    public Product get(long id) {
        return products.findById(id).orElseThrow(() -> new ProductNotFoundException(id));
    }

    @Transactional
    public Product create(ProductForm form) {
        if (products.existsBySku(form.getSku())) {
            throw new DuplicateSkuException(form.getSku());
        }
        return save(new Product(form.getName(), form.getSku(), form.priceInCents(), form.getQuantity()));
    }

    @Transactional
    public Product update(long id, ProductForm form) {
        Product product = get(id);
        if (products.existsBySkuAndIdNot(form.getSku(), id)) {
            throw new DuplicateSkuException(form.getSku());
        }
        product.change(form.getName(), form.getSku(), form.priceInCents(), form.getQuantity());
        return product;                          // no save() needed: dirty checking issues the UPDATE at commit
    }

    @Transactional
    public void delete(long id) {
        if (!products.existsById(id)) {
            throw new ProductNotFoundException(id);
        }
        products.deleteById(id);
    }

    private Product save(Product product) {
        try {
            return products.saveAndFlush(product);       // flush now, so a unique violation surfaces here
        } catch (DataIntegrityViolationException e) {
            throw new DuplicateSkuException(product.getSku());     // lost the race to another request
        }
    }
}
Java
// ProductNotFoundException.java
package com.example.inventory.product;

import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.ResponseStatus;

@ResponseStatus(HttpStatus.NOT_FOUND)               // an unknown id gives a 404 page, not a 500
public class ProductNotFoundException extends RuntimeException {
    public ProductNotFoundException(long id) { super("No product with id " + id); }
}

// DuplicateSkuException.java
package com.example.inventory.product;

public class DuplicateSkuException extends RuntimeException {
    private final String sku;
    public DuplicateSkuException(String sku) { super("SKU already in use: " + sku); this.sku = sku; }
    public String getSku() { return sku; }
}

Look at the two lines of defense for the duplicate SKU. The existsBySku check gives a friendly message in the normal case. It can't be the only protection, though, because two requests can both pass it before either inserts. The UNIQUE constraint in the database is the real guarantee, and the catch turns its violation into the same domain exception. The same pattern applies to every uniqueness rule you ever write.

The controller

Java
// src/main/java/com/example/inventory/product/ProductController.java
package com.example.inventory.product;

import jakarta.validation.Valid;
import org.springframework.data.domain.Page;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

@Controller
@RequestMapping("/products")
class ProductController {

    private static final int PAGE_SIZE = 5;
    private final ProductService products;

    ProductController(ProductService products) {
        this.products = products;
    }

    @GetMapping
    String list(@RequestParam(defaultValue = "") String q,
                @RequestParam(defaultValue = "0") int page, Model model) {
        Page<Product> result = products.search(q, page, PAGE_SIZE);
        model.addAttribute("page", result);
        model.addAttribute("q", q);
        return "products/list";
    }

    @GetMapping("/new")
    String newForm(Model model) {
        model.addAttribute("form", new ProductForm());
        return "products/form";
    }

    @PostMapping
    String create(@Valid @ModelAttribute("form") ProductForm form, BindingResult errors,
                  RedirectAttributes redirect) {
        if (errors.hasErrors()) {
            return "products/form";                                    // 200 with the errors shown
        }
        try {
            products.create(form);
        } catch (DuplicateSkuException e) {
            errors.rejectValue("sku", "duplicate", "This SKU is already in use");
            return "products/form";
        }
        redirect.addFlashAttribute("message", "Product created.");
        return "redirect:/products";                                   // Post/Redirect/Get
    }

    @GetMapping("/{id}/edit")
    String editForm(@PathVariable long id, Model model) {
        model.addAttribute("form", ProductForm.from(products.get(id)));
        model.addAttribute("id", id);
        return "products/form";
    }

    @PostMapping("/{id}")
    String update(@PathVariable long id, @Valid @ModelAttribute("form") ProductForm form,
                  BindingResult errors, Model model, RedirectAttributes redirect) {
        model.addAttribute("id", id);
        if (errors.hasErrors()) {
            return "products/form";
        }
        try {
            products.update(id, form);
        } catch (DuplicateSkuException e) {
            errors.rejectValue("sku", "duplicate", "This SKU is already in use");
            return "products/form";
        }
        redirect.addFlashAttribute("message", "Product updated.");
        return "redirect:/products";
    }

    @PostMapping("/{id}/delete")
    String delete(@PathVariable long id, RedirectAttributes redirect) {
        products.delete(id);
        redirect.addFlashAttribute("message", "Product deleted.");
        return "redirect:/products";
    }
}

Four details deserve attention:

  • Post/Redirect/Get. After a successful POST the controller returns redirect:/products instead of a page. If it rendered the list directly, pressing Reload would resubmit the form and create the product twice; after a redirect, reload just repeats a harmless GET.
  • Flash attributes carry the "Product created." message across that redirect. Spring stores them in the session for exactly one following request, so the message shows once and disappears.
  • BindingResult must come right after the @Valid parameter. Without it, a validation failure throws an exception and the user gets an error page rather than the form with messages.
  • A failed POST returns the view with status 200. The form object is still in the model, and th:field refills each input from it, so the user keeps what they typed.

Add spring.mvc.hiddenmethod.filter.enabled=true and a _method=delete field only if you want REST-style DELETE from forms; plain POST /products/{id}/delete is simpler and works everywhere.

The templates

HTML
<!-- src/main/resources/templates/products/list.html -->
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
  <meta charset="UTF-8">
  <title>Products</title>
</head>
<body>
  <h1>Products</h1>

  <p th:if="${message}" class="flash" th:text="${message}">Saved.</p>

  <form th:action="@{/products}" method="get">
    <input type="search" name="q" th:value="${q}" placeholder="Search by name">
    <button type="submit">Search</button>
    <a th:href="@{/products/new}">Add product</a>
  </form>

  <table>
    <thead>
      <tr><th>Name</th><th>SKU</th><th>Price</th><th>In stock</th><th></th></tr>
    </thead>
    <tbody>
      <tr th:each="product : ${page.content}">
        <td th:text="${product.name}">Desk lamp</td>
        <td th:text="${product.sku}">LMP-001</td>
        <td th:text="${#numbers.formatDecimal(product.priceCents / 100.0, 1, 'COMMA', 2, 'POINT')}">34.99</td>
        <td th:text="${product.quantity}">12</td>
        <td>
          <a th:href="@{/products/{id}/edit(id=${product.id})}">Edit</a>
          <form th:action="@{/products/{id}/delete(id=${product.id})}" method="post"
                onsubmit="return confirm('Delete this product?')" style="display:inline">
            <button type="submit">Delete</button>
          </form>
        </td>
      </tr>
      <tr th:if="${page.empty}"><td colspan="5">No products found.</td></tr>
    </tbody>
  </table>

  <nav th:if="${page.totalPages > 1}">
    <a th:if="${page.hasPrevious()}" th:href="@{/products(q=${q}, page=${page.number - 1})}">Previous</a>
    <span th:text="${'Page ' + (page.number + 1) + ' of ' + page.totalPages}">Page 1 of 3</span>
    <a th:if="${page.hasNext()}" th:href="@{/products(q=${q}, page=${page.number + 1})}">Next</a>
  </nav>
</body>
</html>
HTML
<!-- src/main/resources/templates/products/form.html -->
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
  <meta charset="UTF-8">
  <title th:text="${id == null} ? 'New product' : 'Edit product'">Product</title>
</head>
<body>
  <h1 th:text="${id == null} ? 'New product' : 'Edit product'">Product</h1>

  <form th:action="${id == null} ? @{/products} : @{/products/{id}(id=${id})}"
        th:object="${form}" method="post">
    <label>Name
      <input type="text" th:field="*{name}">
      <span class="error" th:if="${#fields.hasErrors('name')}" th:errors="*{name}">Name error</span>
    </label>
    <label>SKU
      <input type="text" th:field="*{sku}">
      <span class="error" th:if="${#fields.hasErrors('sku')}" th:errors="*{sku}">SKU error</span>
    </label>
    <label>Price
      <input type="text" th:field="*{price}" inputmode="decimal">
      <span class="error" th:if="${#fields.hasErrors('price')}" th:errors="*{price}">Price error</span>
    </label>
    <label>Quantity
      <input type="number" th:field="*{quantity}" min="0">
      <span class="error" th:if="${#fields.hasErrors('quantity')}" th:errors="*{quantity}">Quantity error</span>
    </label>
    <button type="submit">Save</button>
    <a th:href="@{/products}">Cancel</a>
  </form>
</body>
</html>

Thymeleaf escapes every th:text and th:field value, so a product named <script>alert(1)</script> appears as text rather than running (Web Security Basics). The Delete button is a one-button form with method="post": a link would use GET, and a GET that deletes data gets triggered by browser prefetching and crawlers. If you add Spring Security later, th:action inserts the CSRF token into these forms automatically.

Seeing the request flow

The next program is a plain-Java simulation of this controller and service. It is not Spring: there is no DispatcherServlet and no binding. But it uses the same rules as ProductForm, the same decisions as ProductController (a 200 with errors versus 302 to the list), a flash message that is shown once, paging (three rows per page here) and a 404 for unknown ids. Read the printed requests like an HTTP log.

Java Post/Redirect/Get, validation and paging, simulated

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The shapes to notice in the log: every failed form is a 200 with errors and no change in the data; every successful change is a 302 and the next request shows the flash message exactly once; unknown ids are 404. These are the behaviors you will pin down with tests.

Tests

@WebMvcTest starts only the web layer, with the service replaced by a mock, so the tests check routing, binding, validation, redirects and the view names, quickly and without MySQL (Testing Spring Boot Applications, Test Doubles and Mockito):

Java
// src/test/java/com/example/inventory/product/ProductControllerTest.java
package com.example.inventory.product;

import static org.mockito.ArgumentMatchers.any;
import static org.mockito.Mockito.*;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.*;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.*;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.webmvc.test.autoconfigure.WebMvcTest;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.test.context.bean.override.mockito.MockitoBean;
import org.springframework.test.web.servlet.MockMvc;
import java.util.List;

@WebMvcTest(ProductController.class)
class ProductControllerTest {

    @Autowired MockMvc mvc;
    @MockitoBean ProductService service;

    @Test
    void listShowsProducts() throws Exception {
        var lamp = new Product("Desk lamp", "LMP-001", 3499, 12);
        when(service.search("", 0, 5)).thenReturn(new PageImpl<>(List.of(lamp), PageRequest.of(0, 5), 1));

        mvc.perform(get("/products"))
           .andExpect(status().isOk())
           .andExpect(view().name("products/list"))
           .andExpect(content().string(org.hamcrest.Matchers.containsString("Desk lamp")));
    }

    @Test
    void invalidFormIsShownAgainAndNothingIsSaved() throws Exception {
        mvc.perform(post("/products")
                .param("name", "")
                .param("sku", "x")
                .param("price", "abc")
                .param("quantity", "-1"))
           .andExpect(status().isOk())
           .andExpect(view().name("products/form"))
           .andExpect(model().attributeHasFieldErrors("form", "name", "sku", "price", "quantity"));

        verifyNoInteractions(service);
    }

    @Test
    void validFormRedirectsToTheList() throws Exception {
        mvc.perform(post("/products")
                .param("name", "Desk lamp")
                .param("sku", "lmp-001")
                .param("price", "34.99")
                .param("quantity", "12"))
           .andExpect(status().is3xxRedirection())
           .andExpect(redirectedUrl("/products"))
           .andExpect(flash().attribute("message", "Product created."));

        verify(service).create(any(ProductForm.class));
    }

    @Test
    void duplicateSkuComesBackAsAFieldError() throws Exception {
        when(service.create(any())).thenThrow(new DuplicateSkuException("LMP-001"));

        mvc.perform(post("/products")
                .param("name", "Desk lamp").param("sku", "LMP-001")
                .param("price", "34.99").param("quantity", "12"))
           .andExpect(status().isOk())
           .andExpect(model().attributeHasFieldErrorCode("form", "sku", "duplicate"));
    }

    @Test
    void unknownProductIsA404() throws Exception {
        when(service.get(99)).thenThrow(new ProductNotFoundException(99));

        mvc.perform(get("/products/99/edit")).andExpect(status().isNotFound());
    }
}

(These imports are Spring Boot 4's; in Boot 3 @WebMvcTest lives in org.springframework.boot.test.autoconfigure.web.servlet and @MockitoBean replaces the older @MockBean.) The repository deserves a test against the real database engine, because H2 doesn't behave exactly like MySQL (collations and case-sensitivity of LIKE, for example). Testcontainers starts a throwaway MySQL (Testcontainers):

Java
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)   // use the container, not H2
@Testcontainers
class ProductRepositoryTest {

    @Container
    @ServiceConnection
    static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.4");

    @Autowired ProductRepository products;

    @Test
    void searchIgnoresCaseAndPages() {
        products.save(new Product("Desk lamp", "LMP-001", 3499, 12));
        products.save(new Product("Floor lamp", "LMP-003", 7999, 5));
        products.save(new Product("Notebook", "NTB-007", 450, 200));

        var page = products.findByNameContainingIgnoreCase("LAMP", PageRequest.of(0, 1, Sort.by("name")));

        assertThat(page.getTotalElements()).isEqualTo(2);
        assertThat(page.getContent()).extracting(Product::getName).containsExactly("Desk lamp");
    }

    @Test
    void duplicateSkuViolatesTheUniqueConstraint() {
        products.saveAndFlush(new Product("Desk lamp", "LMP-001", 3499, 12));

        assertThatThrownBy(() -> products.saveAndFlush(new Product("Other", "LMP-001", 100, 1)))
            .isInstanceOf(DataIntegrityViolationException.class);
    }
}

The Flyway migration runs automatically against the container, so this test also proves that the migration and the entity agree (ddl-auto=validate).

Running it

Terminal
docker compose up -d            # starts MySQL
./mvnw spring-boot:run          # starts the app on http://localhost:8080
./mvnw test                     # runs the tests (Docker must be running for the Testcontainers test)

Open http://localhost:8080/products, add a few products, search, and try the mistakes the tests cover: an empty name, the price abc, a duplicate SKU, an unknown address such as /products/999/edit. With show-sql=true the console prints the SQL from the first runnable example as you click.

Ideas for extending the project

  • Sorting. Pass sort (name, price, quantity) as a request parameter, validate it against a whitelist, and build the Sort from it. Never put the parameter into SQL directly (SQL Injection).
  • Categories. Add a Category entity with @ManyToOne, a <select> in the form, and a fetch join in the list query to avoid N+1 queries (Entity Relationships, Fetching and Performance).
  • Optimistic locking. Add a @Version field and a hidden version input, and show "someone else changed this product" when two people edit the same row.
  • Login. Protect the pages with Spring Security form login and restrict delete to admins (Spring Security).
  • Error pages. Add templates/error/404.html and templates/error/500.html; Spring Boot picks them up automatically.
  • CSV export and file upload. Download the list as CSV, and upload a product image (Spring MVC: File Upload and Pagination).
  • Package it. Build the jar, run it in a container beside MySQL with Compose (Docker for Java Developers).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does the controller return "redirect:/products" after a successful POST instead of rendering the list?

    This is Post/Redirect/Get. Without it, the browser's Reload button resubmits the POST and creates a duplicate.
  2. 2. What must directly follow a @Valid @ModelAttribute parameter in the handler method's signature if you want to show the form again on errors?

    With a BindingResult right after the validated object, validation errors are collected into it and the handler can return the form view. Without it, Spring throws an exception.
  3. 3. The service checks existsBySku before saving, and the table also has a UNIQUE constraint on sku. Why both?

    Two concurrent requests can both pass the exists check. Only the database can enforce uniqueness atomically, so the service also translates the violation into the same domain exception.
  4. 4. Why is the form a separate class (ProductForm) rather than the Product entity?

    Binding request parameters straight onto an entity allows mass assignment and mixes UI rules with persistence. A form object lists exactly what a user may submit.
  5. 5. The product list uses a repository method that returns Page<Product>. What extra query does Spring Data run for it?

    A Page needs the total element count, so besides the select with limit and offset there is a count query. A Slice would skip it.

Summary

  • A classic server-rendered CRUD app is four layers: Thymeleaf templates, a @Controller, a @Transactional service and a Spring Data JPA repository over MySQL. Packages by feature keep them together.
  • Flyway owns the schema (ddl-auto=validate); the database also enforces rules (UNIQUE, CHECK), because the app is not the only writer. Money is stored as integer cents.
  • A form class with Bean Validation annotations is bound with @Valid @ModelAttribute plus a BindingResult; failures return the form with 200, and th:field/th:errors show the input and messages.
  • Every successful change ends in redirect: (Post/Redirect/Get) with a flash attribute for the message. Delete is a POST form, never a link.
  • Check uniqueness in the service for a friendly message and let the constraint decide races. Paging is Pageable + Page, which runs a limit/offset query and a count(*).
  • Test the controller with @WebMvcTest and a mocked service, and the repository against a real MySQL with Testcontainers.

Next: Project: Employee Management with Spring Data JPA builds a REST API with related entities, specifications and DTOs on the same Spring Data JPA foundation.