Spring Boot CRUD REST API with MySQL and Spring Data JPA (Complete Example)

A Spring Boot CRUD REST API with MySQL is five small layers: an entity (the table), a Spring Data JPA repository, a service (the transactions), a controller (the URLs) and an error handler (the failures). This page builds all five on Spring Boot 4.1, creates the table with Flyway, and shows the real curl request and response for every operation, including the five ways a client gets it wrong.

Tested on Spring Boot 4.1.1 (Spring Framework 7.0.9, Hibernate on MySQLDialect) with OpenJDK 21.0.12 against a real MySQL 8.4.6 server and Connector/J 9.7.0. The code is the crud-mysql module. The MySQL server for the captured output was started from the official MySQL tarball, not from Docker: the sandbox had no Docker daemon, so the docker-compose.yml in the module has not been run. The setup script mysql-setup.sql is what the tests used.

The whole API, as a table you can keep next to the code:

Every failure comes back as an RFC 9457 ProblemDetail (application/problem+json), so a client parses one shape. The sections below build it in the order a request travels: database, entity, repository, service, controller, errors.

1. Start MySQL and give the app its own user

The application expects database library and a user library with password library. mysql-setup.sql creates both:

-- Creates the database and the user the application expects. Run as a MySQL administrator:
--   mysql -uroot -p < scripts/mysql-setup.sql
CREATE DATABASE IF NOT EXISTS library;
CREATE USER IF NOT EXISTS 'library'@'%' IDENTIFIED BY 'library';
GRANT ALL ON library.* TO 'library'@'%';

With Docker, docker compose up -d using docker-compose.yml should give you the same thing (a mysql:8.4 image with those variables); I could not run it here, so treat it as untested. Connection settings live in application.properties and can be overridden with environment variables, no code change:

# Connection: override any of these with environment variables, no code change needed.
spring.datasource.url=${DB_URL:jdbc:mysql://localhost:3306/library}
spring.datasource.username=${DB_USER:library}
spring.datasource.password=${DB_PASSWORD:library}

# Flyway owns the schema. Hibernate only checks that the entity matches it and refuses to start if not.
spring.jpa.hibernate.ddl-auto=validate

# Do not keep a database session open while the view is rendered; the REST layer does not need it.
spring.jpa.open-in-view=false

Two lines matter. ddl-auto=validate means Hibernate never changes the schema: it only refuses to start if an entity does not match it. open-in-view=false keeps the database session closed once the service returns. Dependencies in pom.xml: spring-boot-starter-webmvc, -validation, -data-jpa, -flyway, plus flyway-mysql and mysql-connector-j.

Boot 4 note: Flyway has its own starter. In Spring Boot 4 the Flyway auto-configuration is brought in by spring-boot-starter-flyway, and MySQL support is the separate flyway-mysql artifact. This module uses both, and that is the combination the transcripts below were produced with; I did not test the failure modes of leaving either out. More on migrations: Flyway vs Liquibase.

2. Let Flyway create the table

The schema is a SQL file the application runs at startup, V1__create_book.sql:

CREATE TABLE book (
    id      BIGINT        NOT NULL AUTO_INCREMENT,
    title   VARCHAR(200)  NOT NULL,
    author  VARCHAR(120)  NOT NULL,
    isbn    VARCHAR(13)   NOT NULL,
    price   DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT uk_book_isbn UNIQUE (isbn)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

The test then asked the live server what it is connected to and what Flyway built:

--- the server and the driver ---
database : MySQL 8.4.6
driver   : MySQL Connector/J mysql-connector-j-9.7.0 (Revision: 0aade1f13bcc98faf7dda5c02e782481eb291f62)
dialect  : MySQLDialect
ddl-auto : validate

--- flyway_schema_history ---
version=1 description=create book success=true

--- SHOW CREATE TABLE book ---
CREATE TABLE `book` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `author` varchar(120) NOT NULL,
  `isbn` varchar(13) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_book_isbn` (`isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Output: 01-mysql-and-schema.txt. The unique constraint on isbn is a real MySQL UNIQUE KEY, which is what makes the 409 in section 7 possible: the database, not Java code, is the final judge of duplicates.

3. The entity: a Java class that maps to a table

A JPA entity is a class with @Entity whose fields are columns. This is Book.java:

@Entity
@Table(name = "book")
public class Book {

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

    @Column(nullable = false, length = 200)
    private String title;

    @Column(nullable = false, length = 120)
    private String author;

    @Column(nullable = false, length = 13, unique = true)
    private String isbn;

    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;

    protected Book() {                                    // required by JPA
    }

    public Book(String title, String author, String isbn, BigDecimal price) {
        this.title = title;
        this.author = author;
        this.isbn = isbn;
        this.price = price;
    }

    public void change(String title, String author, String isbn, BigDecimal price) {
        this.title = title;
        this.author = author;
        this.isbn = isbn;
        this.price = price;
    }

    public Long getId() { return id; }
    public String getTitle() { return title; }
    public String getAuthor() { return author; }
    public String getIsbn() { return isbn; }
    public BigDecimal getPrice() { return price; }
}

GenerationType.IDENTITY tells Hibernate to use MySQL’s AUTO_INCREMENT. The protected no-argument constructor is required by JPA. There are no setters: change(...) is the one way to modify a book, which keeps invalid half-updates out of the model.

4. The repository: no SQL for the basics

BookRepository.java is an interface. Spring Data writes the implementation:

public interface BookRepository extends JpaRepository<Book, Long> {

    Page<Book> findByAuthorContainingIgnoreCase(String author, Pageable pageable);
}

JpaRepository already gives save, findById, findAll(Pageable) and delete. The one extra method is a derived query: Spring Data reads the method name and builds the query. Here is the SQL Hibernate actually sent for the author filter, and for every other operation (recorded with a Hibernate StatementInspector):

--- list page 0, size 2:  GET /api/books?page=0&size=2  ->  HTTP 200 ---
select b1_0.id,b1_0.author,b1_0.isbn,b1_0.price,b1_0.title from book b1_0 order by b1_0.id limit ?,?
select count(b1_0.id) from book b1_0

--- list filtered by author:  GET /api/books?author=bloch  ->  HTTP 200 ---
select b1_0.id,b1_0.author,b1_0.isbn,b1_0.price,b1_0.title from book b1_0 where upper(b1_0.author) like upper(?) escape '\\' order by b1_0.id limit ?

Output: 04-sql-per-request.txt. A page costs two statements: one limit query and one count so the response can say how many pages exist. The same file shows create (a single insert), update (a select then an update) and delete (a select then a delete). For more on derived queries and when to leave JPA, see Spring Data JPA 4 derived queries and Spring Data JDBC vs JPA.

5. The service: where the transaction lives

BookService.java holds the logic and the @Transactional boundaries:

@Service
public class BookService {
    private static final int MAX_PAGE_SIZE = 50;

    private final BookRepository repository;

    public BookService(BookRepository repository) {
        this.repository = repository;
    }

    @Transactional(readOnly = true)
    public PageResponse<BookResponse> list(String author, int page, int size) {
        PageRequest pageable = PageRequest.of(Math.max(page, 0), Math.min(Math.max(size, 1), MAX_PAGE_SIZE), Sort.by("id"));
        var result = (author == null || author.isBlank())
                ? repository.findAll(pageable)
                : repository.findByAuthorContainingIgnoreCase(author, pageable);
        return PageResponse.of(result.map(BookResponse::from));
    }

    @Transactional(readOnly = true)
    public BookResponse get(Long id) {
        return BookResponse.from(find(id));
    }

    @Transactional
    public BookResponse create(BookRequest r) {
        return BookResponse.from(repository.save(new Book(r.title(), r.author(), r.isbn(), r.price())));
    }

    @Transactional
    public BookResponse update(Long id, BookRequest r) {
        Book book = find(id);
        book.change(r.title(), r.author(), r.isbn(), r.price());   // dirty checking issues the UPDATE at commit
        return BookResponse.from(book);
    }

    @Transactional
    public void delete(Long id) {
        repository.delete(find(id));
    }

    private Book find(Long id) {
        return repository.findById(id).orElseThrow(() -> new BookNotFoundException(id));
    }
}

Three details worth knowing. The page size is capped at 50 so a client cannot ask for a million rows. update loads the book, calls change and returns: there is no save, because Hibernate’s dirty checking notices the change and issues the update when the transaction commits (visible in the SQL above). Reads use readOnly = true. Transaction pitfalls, including calling a @Transactional method from the same class, are in transaction propagation and silent failures.

6. The controller and the request record

A client may send BookRequest.java. The id is never part of it, and the constraints are the validation rules:

/** What a client may send. The id is never part of it: the database assigns it. */
public record BookRequest(
        @NotBlank @Size(max = 200) String title,
        @NotBlank @Size(max = 120) String author,
        @NotBlank @Pattern(regexp = "97[89]\\d{10}", message = "must be 13 digits starting with 978 or 979") String isbn,
        @NotNull @DecimalMin(value = "0.00") BigDecimal price) {
}

BookController.java maps the five operations:

@RestController
@RequestMapping("/api/books")
public class BookController {
    private final BookService service;

    public BookController(BookService service) {
        this.service = service;
    }

    @GetMapping
    public PageResponse<BookResponse> list(@RequestParam(required = false) String author,
                                           @RequestParam(defaultValue = "0") int page,
                                           @RequestParam(defaultValue = "5") int size) {
        return service.list(author, page, size);
    }

    @GetMapping("/{id}")
    public BookResponse get(@PathVariable Long id) {
        return service.get(id);
    }

    @PostMapping
    public ResponseEntity<BookResponse> create(@Valid @RequestBody BookRequest request) {
        BookResponse created = service.create(request);
        URI location = ServletUriComponentsBuilder.fromCurrentRequest().path("/{id}").buildAndExpand(created.id()).toUri();
        return ResponseEntity.created(location).body(created);
    }

    @PutMapping("/{id}")
    public BookResponse update(@PathVariable Long id, @Valid @RequestBody BookRequest request) {
        return service.update(id, request);
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> delete(@PathVariable Long id) {
        service.delete(id);
        return ResponseEntity.noContent().build();
    }
}

@Valid runs the record’s constraints before the method body. POST returns ResponseEntity.created(location), so the client gets 201 and a Location header pointing at the new book. The entity itself is never returned: BookResponse.java is a separate record, which keeps the table layout out of your public API. Now call it. This is the full create, read, list, update and delete sequence, each command followed by its real status line and body (output: 02-crud-walkthrough.txt):

$ curl -i -X POST 'localhost:8080/api/books' -H 'Content-Type: application/json' -d '{"title":"Effective Java","author":"Joshua Bloch","isbn":"9780134685991","price":45.50}'
HTTP 201   Location: http://localhost:8080/api/books/1
{"id":1,"title":"Effective Java","author":"Joshua Bloch","isbn":"9780134685991","price":45.50}

Create returns 201 with the new id, and the Location header on the same line is where the client can fetch it. (The test used a random port and printed it as 8080.) Read one, then list with paging:

$ curl -i -X GET 'localhost:8080/api/books/1'
HTTP 200
{"id":1,"title":"Effective Java","author":"Joshua Bloch","isbn":"9780134685991","price":45.50}

$ curl -i -X GET 'localhost:8080/api/books?page=0&size=2'
HTTP 200
{"content":[{"id":1,"title":"Effective Java","author":"Joshua Bloch","isbn":"9780134685991","price":45.50},{"id":2,"title":"Java Concurrency in Practice","author":"Brian Goetz","isbn":"9780321349606","price":39.99}],"page":0,"size":2,"totalElements":3,"totalPages":2}

$ curl -i -X GET 'localhost:8080/api/books?page=1&size=2'
HTTP 200
{"content":[{"id":3,"title":"Clean Code","author":"Robert Martin","isbn":"9780132350884","price":33.00}],"page":1,"size":2,"totalElements":3,"totalPages":2}

The page body carries page, size, totalElements and totalPages, from PageResponse.java. Filter, update and delete:

$ curl -i -X GET 'localhost:8080/api/books?author=goetz'
HTTP 200
{"content":[{"id":2,"title":"Java Concurrency in Practice","author":"Brian Goetz","isbn":"9780321349606","price":39.99}],"page":0,"size":5,"totalElements":1,"totalPages":1}

$ curl -i -X PUT 'localhost:8080/api/books/1' -H 'Content-Type: application/json' -d '{"title":"Effective Java","author":"Joshua Bloch","isbn":"9780134685991","price":42.00}'
HTTP 200
{"id":1,"title":"Effective Java","author":"Joshua Bloch","isbn":"9780134685991","price":42.00}

$ curl -i -X DELETE 'localhost:8080/api/books/3'
HTTP 204

$ curl -i -X GET 'localhost:8080/api/books/3'
HTTP 404
{"detail":"Book 3 not found","instance":"/api/books/3","status":404,"title":"Not Found"}

Delete returns 204 with no body, and asking for the same id afterwards returns the 404 problem document. The last check reads the table with SQL, not through the API, to prove the API and the database agree:

id=1 title=Effective Java price=42.00
id=2 title=Java Concurrency in Practice price=39.99

For Postman, each command above is one request: set the method and URL, choose Body, raw, JSON for POST and PUT, and add the header Content-Type: application/json. I did not run Postman; the responses are from curl, and they are the same HTTP. The same sequence against the packaged jar (java -jar target/crud-mysql-1.0.0.jar), with raw headers, is in 05-real-curl.txt:

$ curl -i -X POST localhost:8080/api/books -H 'Content-Type: application/json' -d '{"title":"Effective Java","author":"Joshua Bloch","isbn":"9780134685991","price":45.50}'
HTTP/1.1 201 
Location: http://localhost:8080/api/books/1
Content-Type: application/json
Transfer-Encoding: chunked

{"id":1,"title":"Effective Java","author":"Joshua Bloch","isbn":"9780134685991","price":45.50}

Note Transfer-Encoding: chunked on the create response and Content-Length: 94 on the read: both are normal. The 404 in the same file arrives as Content-Type: application/problem+json.

7. Errors: one handler, one response shape

ApiExceptionHandler.java extends ResponseEntityExceptionHandler, which already turns Spring MVC’s own exceptions into ProblemDetail, and adds what belongs to this application:

/**
 * One place that turns exceptions into RFC 9457 problem documents.
 * ResponseEntityExceptionHandler already maps Spring MVC's own exceptions (bad JSON, bad path variable...)
 * to ProblemDetail; we add the ones that belong to this application.
 */
@RestControllerAdvice
public class ApiExceptionHandler extends ResponseEntityExceptionHandler {

    @ExceptionHandler(BookNotFoundException.class)
    ProblemDetail notFound(BookNotFoundException e) {
        return ProblemDetail.forStatusAndDetail(HttpStatus.NOT_FOUND, e.getMessage());
    }

    @ExceptionHandler(DataIntegrityViolationException.class)
    ProblemDetail conflict(DataIntegrityViolationException e) {
        String cause = String.valueOf(e.getMostSpecificCause().getMessage());
        String detail = cause.contains("uk_book_isbn") ? "A book with that ISBN already exists" : "The change conflicts with existing data";
        return ProblemDetail.forStatusAndDetail(HttpStatus.CONFLICT, detail);
    }

    /** The default 400 says "Invalid request content." and nothing else. Add which field failed and why. */
    @Override
    protected ResponseEntity<Object> handleMethodArgumentNotValid(MethodArgumentNotValidException ex, HttpHeaders headers,
                                                                  HttpStatusCode status, WebRequest request) {
        Map<String, String> errors = new LinkedHashMap<>();
        ex.getBindingResult().getFieldErrors().stream()
                .sorted((a, b) -> a.getField().compareTo(b.getField()))
                .forEach(f -> errors.putIfAbsent(f.getField(), f.getDefaultMessage()));
        ProblemDetail body = ex.getBody();
        body.setProperty("errors", errors);
        return handleExceptionInternal(ex, body, headers, status, request);
    }
}

Now break the API on purpose (output: 03-error-responses.txt). Three fields wrong at once:

$ curl -i -X POST 'localhost:8080/api/books' -H 'Content-Type: application/json' -d '{"title":"","author":"Someone","isbn":"123","price":-1}'
HTTP 400
{"detail":"Invalid request content.","instance":"/api/books","status":400,"title":"Bad Request","errors":{"isbn":"must be 13 digits starting with 978 or 979","price":"must be greater than or equal to 0.00","title":"must not be blank"}}

Boot’s default 400 only says Invalid request content., so the handler adds an errors map with every failing field. Next, a duplicate ISBN on create and on update:

$ curl -i -X POST 'localhost:8080/api/books' -H 'Content-Type: application/json' -d '{"title":"Java Concurrency in Practice","author":"Brian Goetz","isbn":"9780321349606","price":39.99}'
HTTP 409
{"detail":"A book with that ISBN already exists","instance":"/api/books","status":409,"title":"Conflict"}

$ curl -i -X PUT 'localhost:8080/api/books/2' -H 'Content-Type: application/json' -d '{"title":"Java Concurrency in Practice","author":"Brian Goetz","isbn":"9780134685991","price":39.99}'
HTTP 409
{"detail":"A book with that ISBN already exists","instance":"/api/books/2","status":409,"title":"Conflict"}

Both are 409 Conflict. The handler recognises the constraint by name, uk_book_isbn, in the cause that MySQL produced:

Duplicate entry '9780134685991' for key 'book.uk_book_isbn'

Never send that message to the client: it names a table, a constraint and the rejected value. The handler replaces it with A book with that ISBN already exists. Finally, an unknown id, an id that is not a number, and a body that is not JSON:

$ curl -i -X GET 'localhost:8080/api/books/99'
HTTP 404
{"detail":"Book 99 not found","instance":"/api/books/99","status":404,"title":"Not Found"}

$ curl -i -X DELETE 'localhost:8080/api/books/99'
HTTP 404
{"detail":"Book 99 not found","instance":"/api/books/99","status":404,"title":"Not Found"}

$ curl -i -X GET 'localhost:8080/api/books/abc'
HTTP 400
{"detail":"Failed to convert 'id' with value: 'abc'","instance":"/api/books/abc","status":400,"title":"Bad Request"}

$ curl -i -X POST 'localhost:8080/api/books' -H 'Content-Type: application/json' -d '{not json'
HTTP 400
{"detail":"Failed to read request","instance":"/api/books","status":400,"title":"Bad Request"}

Each is a client error with a stable shape and no stack trace. The full pattern is in global exception handling with ProblemDetail.

A duplicate-key violation is logged by Hibernate at WARN even though the application handles it as a 409. The log line (HHH000247, ErrorCode 1062, SQLState 23000) is noise for this case, not a failure. Check-then-insert (existsByIsbn before save) is not a substitute for the constraint: two concurrent requests can both pass the check.

Run it yourself

Start MySQL (Docker or any 8.x server), run mysql-setup.sql, then mvn spring-boot:run in the module, or build the jar and run it as in the last transcript. curl-demo.sh replays the requests, and run-all.sh regenerates every file under output/. For the test side, the @DataJpaTest with Testcontainers post shows how to run repository tests against a throwaway MySQL. To containerise the app, see Dockerizing Spring Boot 4; for the older long-form guide, Spring Boot REST API complete guide.

Before you ship this: it has no authentication, no ETag or concurrency control (two clients updating the same book is last-write-wins), and the filter uses LIKE on an unindexed column, which is fine for thousands of rows and not for millions. Those are deliberate omissions to keep the CRUD shape clear.

Common interview questions

Common mistakes

• Using ddl-auto=update in production. Hibernate guesses schema changes. Use Flyway and validate, as here.
• Leaving out spring-boot-starter-flyway or flyway-mysql in Boot 4. This module uses both; check the migration actually ran in flyway_schema_history.
• Returning entities from controllers. Use response records.
• Letting the raw database error reach the client. Map it to a 409 and a safe message (section 7).
• Unbounded page size. Cap it in the service, as MAX_PAGE_SIZE does.
• Checking for duplicates in Java only. Keep the UNIQUE constraint; the check alone is racy.

FAQ

How do I connect Spring Boot to MySQL?

Add mysql-connector-j and spring-boot-starter-data-jpa, then set spring.datasource.url, username and password.

How do I create a CRUD REST API in Spring Boot?

An entity, a JpaRepository, a @Service with @Transactional methods, and a @RestController mapping POST, GET, PUT and DELETE.

How do I return 201 with a Location header?

Return ResponseEntity.created(uri).body(...), building the URI with ServletUriComponentsBuilder.

How do I handle a duplicate key in Spring Data JPA?

Catch DataIntegrityViolationException in a @RestControllerAdvice and return 409 Conflict.

Further reading

• Companion code: crud-mysql module
• Spring Boot tutorial for beginners
• Spring Boot project structure
• Spring Boot annotations list
• Building a REST API with Spring Boot: Complete Beginner’s Guide
• Global Exception Handling with ProblemDetail (RFC 9457) in Spring Boot 4
• Flyway vs Liquibase for Spring Boot 4: Migrations, Rollbacks and Baselines
• @DataJpaTest in Spring Boot 4.1 with Testcontainers @ServiceConnection
• @Transactional in Spring: Propagation, Isolation, and the Six Ways It Silently Does Nothing
• Top 40 Java Collections interview questions
• Top 40 Java concurrency interview questions
• Top 50 Java Stream API coding interview questions
• Spring Data JPA reference