Spring 学习笔记 03：JPA 查询——派生方法、JPQL 与分页排序

public interface BookRepository extends JpaRepository<Book, Long> {

    List<Book> findByAuthor(String author);

    List<Book> findByAuthorIgnoreCase(String author);

    List<Book> findByPublishedYearGreaterThanEqual(int year);

    List<Book> findByTitleContainingIgnoreCaseOrderByTitleAsc(String keyword);

    long countByStatus(BookStatus status);

    boolean existsByIsbn(String isbn);

    List<Book> findByAuthorIn(Collection<String> authors);

    List<Book> findByPublishedYearBetween(int from, int to);

    Optional<Book> findFirstByOrderByPublishedYearDesc();
}

public interface BookRepository extends JpaRepository<Book, Long> {

    @Query("select b from Book b where lower(b.title) like lower(concat('%', :keyword, '%'))")
    List<Book> searchByTitle(@Param("keyword") String keyword);

    @Query("select b from Book b where b.publishedYear >= :year and b.status = :status order by b.title asc")
    List<Book> findByYearAndStatus(@Param("year") int year, @Param("status") BookStatus status);

    @Query("select count(b) from Book b where b.author = ?1")
    long countByAuthor(String author);
}

package com.example.library.repository;

public interface BookSummary {

    String getTitle();

    String getAuthor();
}

@Query("select b.title as title, b.author as author from Book b where b.status = :status")
List<BookSummary> findSummaries(@Param("status") BookStatus status);

package com.example.library.dto;

public record BookTitleDto(String title, String author) {
}

@Query("""
       select new com.example.library.dto.BookTitleDto(b.title, b.author)
       from Book b
       where b.status = :status
       """)
List<BookTitleDto> findDtoByStatus(@Param("status") BookStatus status);

// bookRepository 是注入进来的 BookRepository（第 02 篇那个接口）
List<Book> books = bookRepository.findAll(Sort.by("publishedYear").descending().and(Sort.by("title")));

Pageable pageable = PageRequest.of(0, 10, Sort.by("title").ascending());
Page<Book> page = bookRepository.findByStatus(BookStatus.AVAILABLE, pageable);

public interface BookRepository extends JpaRepository<Book, Long> {

    Page<Book> findByStatus(BookStatus status, Pageable pageable);
}

Page<BookTitleDto> dtoPage = page.map(b -> new BookTitleDto(b.getTitle(), b.getAuthor()));

public interface BookRepository extends JpaRepository<Book, Long> {

    // 派生查询 + 分页：关键词模糊匹配，排序交给 Pageable
    Page<Book> findByTitleContainingIgnoreCase(String keyword, Pageable pageable);

    // JPQL + 分页：返回 DTO 投影
    @Query("""
           select new com.example.library.dto.BookTitleDto(b.title, b.author)
           from Book b
           where (:keyword is null or lower(b.title) like lower(concat('%', :keyword, '%')))
           """)
    Page<BookTitleDto> searchTitlesAsDto(@Param("keyword") String keyword, Pageable pageable);
}

@Service
@Transactional(readOnly = true)
public class BookServiceImpl implements BookService {

    private final BookRepository bookRepository;

    public BookServiceImpl(BookRepository bookRepository) {
        this.bookRepository = bookRepository;
    }

    @Override
    public Page<Book> search(String keyword, Pageable pageable) {
        if (keyword == null || keyword.isBlank()) {
            return bookRepository.findAll(pageable);          // 自带方法同样接受 Pageable
        }
        return bookRepository.findByTitleContainingIgnoreCase(keyword, pageable);
    }

    @Override
    public Page<BookTitleDto> searchTitles(String keyword, Pageable pageable) {
        // 走 JPQL 的 DTO 投影，REST 接口用这一版，避免把实体交给前端
        return bookRepository.searchTitlesAsDto(keyword, pageable);
    }

    // 其余方法见第 02、06 篇
}

public interface BookService {

    Page<Book> search(String keyword, Pageable pageable);

    Page<BookTitleDto> searchTitles(String keyword, Pageable pageable);
}

@Controller
@RequestMapping("/books")
public class BookViewController {

    private final BookService bookService;

    public BookViewController(BookService bookService) {
        this.bookService = bookService;
    }

    @GetMapping
    public String list(@RequestParam(name = "keyword", required = false) String keyword,
                       @RequestParam(defaultValue = "0") int page,
                       Model model) {
        Pageable pageable = PageRequest.of(Math.max(page, 0), 10, Sort.by("title").ascending());
        model.addAttribute("page", bookService.search(keyword, pageable));
        model.addAttribute("keyword", keyword);
        return "books/list";
    }
}

@RestController
@RequestMapping("/api/books")
public class BookController {

    @GetMapping
    public Page<BookTitleDto> list(@RequestParam(name = "keyword", required = false) String keyword,
                                   Pageable pageable) {
        return bookService.searchTitles(keyword, pageable);
    }
}

<tr th:each="book, stat : ${page.content}">
    <td th:text="${stat.count}">1</td>

<div th:if="${page.totalPages > 1}">
    <a th:if="${page.hasPrevious()}"
       th:href="@{/books(page=${page.number - 1}, keyword=${keyword})}">上一页</a>
    <span th:text="|${page.number + 1} / ${page.totalPages}|">1 / 3</span>
    <a th:if="${page.hasNext()}"
       th:href="@{/books(page=${page.number + 1}, keyword=${keyword})}">下一页</a>
</div>

@Modifying
@Query("update Book b set b.status = :status where b.id = :id")
int markStatus(@Param("id") Long id, @Param("status") BookStatus status);

@Modifying(clearAutomatically = true, flushAutomatically = true)

@Query(value = "select * from books where author = :author", nativeQuery = true)
List<Book> findByAuthorNative(@Param("author") String author);