Skip to content

Project — A Secured Library API with a Database

This project combines the whole level: a catalog of authors and books stored with Spring Data JPA, a schema owned by Flyway, reads open to everyone, writes protected by a JWT scope, and tests that pin the persistence behavior and the security rules.

The code on this page (entities, migration, repositories, service, controller, exception handler, security configuration, and the test classes) was built and run on Spring Boot 4.1.1 with Hibernate 7.4 and Flyway 12 against in-memory H2, and all four tests passed. Outputs quoted below are from that run. The PostgreSQL and Testcontainers steps in the stretch goals were not run for this course.

Requirements

Endpoint Access Behavior
GET /api/books public All books with author names, in one query
GET /api/books/{id} public One book or 404 problem detail
POST /api/authors SCOPE_books:write Create an author, optionally with books
POST /api/books/{id}/retitle SCOPE_books:write Change a title; 409 on concurrent modification

Non-functional: schema only via Flyway; ddl-auto: validate; open-in-view off; no entity ever serialized directly.

Project setup

curl -s -o library.zip "https://start.spring.io/starter.zip?type=maven-project&language=java&javaVersion=21&groupId=com.example&artifactId=library&packageName=com.example.library&dependencies=web,validation,data-jpa,h2,flyway,security,oauth2-resource-server"
unzip library.zip -d library && cd library

# src/main/resources/application.yml
spring:
  jpa:
    open-in-view: false
    hibernate:
      ddl-auto: validate
    properties:
      hibernate.generate_statistics: true   # used by the N+1 test; turn off in production
app:
  jwt:
    secret: change-me-change-me-change-me-32b   # dev only

Schema

src/main/resources/db/migration/V1__create_authors_and_books.sql:

create table author (
    id          bigint generated by default as identity primary key,
    name        varchar(200) not null
);

create table book (
    id          bigint generated by default as identity primary key,
    title       varchar(300) not null,
    isbn        varchar(20)  not null unique,
    published   integer,
    author_id   bigint not null references author(id),
    version     bigint not null default 0
);

create index idx_book_author on book(author_id);

Entities and repositories

Author and Book are exactly as in lesson 02: Book.author is a lazy @ManyToOne and the owning side; Author.books is mappedBy = "author" with CascadeType.ALL and orphanRemoval; Book has a @Version field; Author.addBook maintains both sides.

public interface BookRepository extends JpaRepository<Book, Long> {
    Optional<Book> findByIsbn(String isbn);

    List<Book> findByPublishedGreaterThanEqualOrderByTitleAsc(int year);

    @Query("select b from Book b join fetch b.author")
    List<Book> findAllWithAuthor();

    @EntityGraph(attributePaths = "author")
    List<Book> findByTitleContainingIgnoreCase(String fragment);
}

public interface AuthorRepository extends JpaRepository<Author, Long> { }

Service

@Service
@Transactional(readOnly = true)
public class CatalogService {
    private final BookRepository books;
    private final AuthorRepository authors;

    public CatalogService(BookRepository books, AuthorRepository authors) {
        this.books = books;
        this.authors = authors;
    }

    public List<BookView> list() {
        return books.findAllWithAuthor().stream().map(BookView::from).toList();
    }

    public BookView get(long id) {
        return books.findById(id).map(BookView::from)
                .orElseThrow(() -> new BookNotFoundException(id));
    }

    @Transactional
    public AuthorView createAuthor(CreateAuthorRequest request) {
        Author author = new Author(request.name());
        request.books().forEach(b -> author.addBook(new Book(b.title(), b.isbn(), b.published())));
        return AuthorView.from(authors.save(author));
    }

    @Transactional
    public BookView retitle(long id, String newTitle) {
        Book book = books.findById(id).orElseThrow(() -> new BookNotFoundException(id));
        book.setTitle(newTitle);          // dirty checking writes this at commit
        return BookView.from(book);
    }
}

public record BookView(long id, String title, String isbn, String author) {
    static BookView from(Book b) {
        return new BookView(b.getId(), b.getTitle(), b.getIsbn(), b.getAuthor().getName());
    }
}

BookView.from touches getAuthor().getName(). That is safe because it runs inside the transactional service method, where the lazy proxy can still load — and list() avoids N+1 by using the join-fetch query. The get method loads one book and one author: two queries, which is fine for a single item.

CreateAuthorRequest is a record with @NotBlank String name and a @Valid list of nested NewBook(title, isbn, published) records; its compact constructor turns a missing list into List.of(). AuthorView mirrors it for responses.

The controller is thin — each method is one call into CatalogService — and the exception handler maps the persistence failures:

@RestControllerAdvice
public class ApiExceptionHandler extends ResponseEntityExceptionHandler {
    @ExceptionHandler(BookNotFoundException.class)
    ProblemDetail notFound(BookNotFoundException ex) {
        return ProblemDetail.forStatusAndDetail(HttpStatus.NOT_FOUND, ex.getMessage());
    }

    @ExceptionHandler(ObjectOptimisticLockingFailureException.class)
    ProblemDetail conflict(ObjectOptimisticLockingFailureException ex) {
        return ProblemDetail.forStatusAndDetail(HttpStatus.CONFLICT,
                "The book was modified concurrently; reload and retry");
    }

    @ExceptionHandler(DataIntegrityViolationException.class)
    ProblemDetail duplicate(DataIntegrityViolationException ex) {
        return ProblemDetail.forStatusAndDetail(HttpStatus.CONFLICT,
                "A book with that ISBN already exists");
    }
}

The duplicate-ISBN mapping assumes ISBN uniqueness is the only constraint that can fail on these endpoints; in a larger schema, inspect the constraint name before choosing the message.

Security

@Configuration
public class SecurityConfig {
    @Bean
    SecurityFilterChain api(HttpSecurity http) throws Exception {
        http
            .authorizeHttpRequests(auth -> auth
                .requestMatchers(HttpMethod.GET, "/api/books/**").permitAll()
                .requestMatchers(HttpMethod.POST, "/api/books/**", "/api/authors/**")
                    .hasAuthority("SCOPE_books:write")
                .anyRequest().authenticated())
            .oauth2ResourceServer(rs -> rs.jwt(Customizer.withDefaults()))
            .sessionManagement(s -> s.sessionCreationPolicy(SessionCreationPolicy.STATELESS))
            .csrf(csrf -> csrf.disable());
        return http.build();
    }

    @Bean
    JwtDecoder jwtDecoder(@Value("${app.jwt.secret}") String secret) {
        var key = new SecretKeySpec(secret.getBytes(StandardCharsets.UTF_8), "HmacSHA256");
        return NimbusJwtDecoder.withSecretKey(key).build();
    }
}

For production, delete the JwtDecoder bean and set spring.security.oauth2.resourceserver.jwt.issuer-uri to your identity provider.

Tests

Persistence — a @SpringBootTest seeds three authors with two books each, then:

@Test
void nPlusOne() {
    tx.executeWithoutResult(s -> books.findAll().forEach(b -> b.getAuthor().getName()));
    long naive = stats.getPrepareStatementCount();
    stats.clear();
    tx.executeWithoutResult(s -> books.findAllWithAuthor().forEach(b -> b.getAuthor().getName()));
    long fetched = stats.getPrepareStatementCount();
    System.out.println("N+1 statements: naive=" + naive + " joinFetch=" + fetched);
    assertThat(fetched).isEqualTo(1);
}

@Test
void dirtyChecking() {
    catalog.retitle(books.findByIsbn("isbn-11").orElseThrow().getId(), "Renamed");
    assertThat(books.findByIsbn("isbn-11").orElseThrow().getTitle()).isEqualTo("Renamed");
}

Output from the run:

N+1 statements: naive=4 joinFetch=1

The same test class also demonstrates the self-invocation pitfall from lesson 04 using a small separate TxDemoService (innerWithTx via self-invocation: false, bean class TxDemoService$$SpringCGLIB$$0). It is kept separate on purpose: CatalogService is annotated @Transactional(readOnly = true) at class level, so every public method on it already starts a transaction, and the pitfall would be hidden.

Security — SecurityTest walks the policy: anonymous GET /api/books → 200; anonymous POST → 401; POST /api/authors with a nested book and the SCOPE_books:write authority → 201; a JWT without the scope → 403; and a retitle of a missing book with the scope → 404 (proving the request got past security and into the exception handler).

Migrations — every @SpringBootTest context start ran Flyway first:

Migrating schema "PUBLIC" to version "1 - create authors and books"
Successfully applied 1 migration to schema "PUBLIC", now at version v1

and Hibernate's validate then accepted the entities. Deliberately misspelling a column in the entity makes the context fail to start, which is exactly the safety net you want.

Final result of ./mvnw test: Tests run: 4, Failures: 0, Errors: 0, Skipped: 0.

How It Actually Works

Follow one POST /api/books/7/retitle with a valid token:

  1. BearerTokenAuthenticationFilter extracts the token; NimbusJwtDecoder verifies the HMAC signature and expiry; the converter produces authorities including SCOPE_books:write; the result is stored in the thread's SecurityContext.
  2. AuthorizationFilter matches POST /api/books/** and checks the authority.
  3. The controller calls catalog.retitle(7, …) on the proxy. The TransactionInterceptor opens a transaction: gets a Hikari connection, disables auto-commit, binds an EntityManager to the thread.
  4. findById loads the book into the persistence context and snapshots it. setTitle changes the field.
  5. The method returns; the interceptor commits. Before commit, Hibernate flushes: dirty checking compares the title to the snapshot and issues update book set title=?, version=? where id=? and version=?.
  6. If another request changed the same book in between, the where version=? matches zero rows, Hibernate throws, Spring translates it to ObjectOptimisticLockingFailureException, the transaction rolls back, and the handler returns 409.

Exercise (stretch goals)

  1. Add the GET /api/books?q= search using the @EntityGraph query, with a test that asserts exactly one SQL statement.
  2. Add loans: a Loan entity, a V2 migration with a partial unique index (on PostgreSQL: create unique index … on loan(book_id) where returned_at is null), and a lend/return API protected by a SCOPE_loans:write scope.
  3. Run the migrations against PostgreSQL with Testcontainers and fix any H2-only SQL.
  4. Add springdoc with the bearer scheme and commit an OpenAPI snapshot test.
  5. Write a test that simulates two concurrent retitles of the same book (load in two transactions, modify both) and asserts the second returns 409.