Hibernate 6.2如何用单条HQL查询作者及最新书籍并投影到DTO

需求说明 #

在Hibernate 6.2中，需要查询指定作者实体，并关联其最新出版的书籍（若该作者无书籍则保留作者信息），最终将结果投影到AuthorBookDto中。

实体定义 #

Author实体 #

public class Author {
    @Id
    @GeneratedValue(strategy = SEQUENCE, generator = "author_seq")
    private Long id;

    @JsonBackReference
    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "author")
    private List<Book> listBook = new ArrayList<>();

    // 其他字段
    // getter和setter方法
}

Book实体 #

public class Book {
    @Id
    @GeneratedValue(strategy = SEQUENCE, generator = "book_seq")
    private Long id;

    @JsonManagedReference
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    private Author author;    

    private LocalDateTime publishedDateTime;

    // 其他字段（如title）
    // getter和setter方法
}

AuthorBookDto #

public class AuthorBookDto {
    public Long authorId;
    public Long bookId;
    public Book newestBook;

    // 必须提供对应参数的构造函数，供HQL的NEW语法调用
    public AuthorBookDto(Long authorId, Long bookId, Book newestBook) {
        this.authorId = authorId;
        this.bookId = bookId;
        this.newestBook = newestBook;
    }
}

现有问题 #

当前编写的查询仅能获取书籍的单个字段（如出版日期），无法直接关联完整的Book实体，因此无法正确投影到目标DTO。

期望结果 #

返回的DTO应包含作者信息及最新书籍（无书籍时newestBook为null），示例JSON格式如下：

{
    "authorId": 1,
    "name": "Steven King",
    "bookId": 1,
    "newestBook": {
        "id": 1,
        "title": "The Shining",
        "publishedDateTime": "2023-01-01T13:00:00"
    }
}

解决方案 #

可以通过单条HQL查询实现需求，以下提供两种可行方案：

方案一：子查询关联最新书籍 #

通过子查询获取作者的最新出版日期，再关联对应的书籍实体，左连接保证无书籍的作者也能被返回：

public AuthorBookDto getAuthorWithLatestBook(Long authorId) {
    return entityManager.createQuery(
        "SELECT NEW com.view.AuthorBookDto(a.id, b.id, b) " +
        "FROM Author a " +
        "LEFT JOIN Book b ON b.author = a " +
        "WHERE (b.publishedDateTime = (" +
            "SELECT MAX(b2.publishedDateTime) " +
            "FROM Book b2 " +
            "WHERE b2.author = a" +
        ") OR b IS NULL) " +
        "AND a.id = :authorId",
        AuthorBookDto.class
    )
    .setParameter("authorId", authorId)
    .getSingleResult();
}

注意：若存在多本同一时间出版的书籍，该查询会返回多个结果，此时可在子查询中增加MAX(b2.id)的条件来唯一确定最新书籍。

方案二：使用窗口函数（推荐） #

Hibernate 6支持窗口函数，通过ROW_NUMBER()按作者分组并排序，直接取每组的第一条记录（即最新书籍），能更好处理同日期多书籍的场景：

public AuthorBookDto getAuthorWithLatestBook(Long authorId) {
    return entityManager.createQuery(
        "SELECT NEW com.view.AuthorBookDto(a.id, b.book.id, b.book) " +
        "FROM Author a " +
        "LEFT JOIN (" +
            "SELECT b1 as book, " +
            "ROW_NUMBER() OVER (PARTITION BY b1.author ORDER BY b1.publishedDateTime DESC, b1.id DESC) as rn " +
            "FROM Book b1" +
        ") b ON b.book.author = a AND b.rn = 1 " +
        "WHERE a.id = :authorId",
        AuthorBookDto.class
    )
    .setParameter("authorId", authorId)
    .getSingleResult();
}

该方案中，窗口函数按publishedDateTime降序、id降序排序，确保即使同日期有多本书，也只会返回id最大的那一本。

内容的提问来源于stack exchange，提问作者Al Grant