java - 在连接查询 Spring-Boot 中访问多个实体值

问题描述

我有一个应用程序，它基本上具有类似 Post/Comment 的功能。我需要应用程序从 Post 实体呈现 Post 索引页面，并从 Comment 实体显示最新评论和作者。

我的数据库/表结构是（id 是主要的，只是没有这样显示）和示例数据：

create table post
(
    id    int auto_increment,
    title text not null,
    constraint post_id_uindex
        unique (id)
);

id,title
1,This is test post number 1
4,This is test post number 2
5,This is test post number 3

create table comment
(
    id         int auto_increment,
    comment    text     null,
    author     text     null,
    created_at datetime null,
    post_id    int      null,
    constraint comment_id_uindex
        unique (id),
    constraint comment_post_id_fk
        foreign key (post_id) references post (id)
);

id,comment,author,created_at,post_id
1,Nice Post this is,Stephen,2021-08-03 16:49:22,1
2,Another nice post,Billy,2021-08-03 16:49:44,1
3,I didn't like the post at all,Karen,2021-08-04 10:57:05,1

我在两个实体中设置了双向映射：

@Entity
public class Comment {
    private int id;
    private String comment;
    private String author;
    private Timestamp createdAt;
    private Post postByPostId;

    @Id
    @Column(name = "id")
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Basic
    @Column(name = "comment")
    public String getComment() {
        return comment;
    }

    public void setComment(String comment) {
        this.comment = comment;
    }

    @Basic
    @Column(name = "author")
    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    @Basic
    @Column(name = "created_at")
    public Timestamp getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Timestamp createdAt) {
        this.createdAt = createdAt;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Comment comment1 = (Comment) o;
        return id == comment1.id && Objects.equals(comment, comment1.comment) && Objects.equals(author, comment1.author) && Objects.equals(createdAt, comment1.createdAt);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, comment, author, createdAt);
    }

    @ManyToOne
    @JoinColumn(name = "post_id", referencedColumnName = "id")
    public Post getPostByPostId() {
        return postByPostId;
    }

    public void setPostByPostId(Post postByPostId) {
        this.postByPostId = postByPostId;
    }
}

@Entity
public class Post {
    private int id;
    private String title;
    private Collection<Comment> commentsById;

    @Id
    @Column(name = "id")
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Basic
    @Column(name = "title")
    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Post post = (Post) o;
        return id == post.id && Objects.equals(title, post.title);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, title);
    }

    @OneToMany(mappedBy = "postByPostId")
    public Collection<Comment> getCommentsById() {
        return commentsById;
    }

    public void setCommentsById(Collection<Comment> commentsById) {
        this.commentsById = commentsById;
    }
}

我正在使用的 PostRepository @Query 如下。查询本身作为原始 SQL 工作，我收到带有最新评论数据的帖子：

id,title,id,comment,author,created_at,post_id
1,This is test post number 1,3,I didn't like the post at all,Karen,2021-08-04 10:57:05,1
4,This is test post number 2,,,,,
5,This is test post number 3,,,,,

但是当我尝试使用 ${post.commentsById} 访问我的 Thymeleaf 模板中的评论字段时，所有评论都被显示，我希望只显示从 @Query 命令加入的一个评论对象。我还看到，当我删除 @Query 时，它会做同样的事情，在这种情况下使 @Query 毫无意义。

谁能给我指导如何正确设置我们？提前致谢。

@Repository
public interface PostRepository extends JpaRepository<Post, Long> {

    @Query(value = "select p.*, c.* from post p left join (select a.* from comment a join (select max(created_at) created_at from comment group by post_id) b on a.created_at = b.created_at) c on p.id = c.post_id", nativeQuery = true)
    List<Post> findAll();
}

post.html Thymeleaf 模板

<!DOCTYPE html>
<html lang="en" xmlns="http://www.w3.org/1999/html" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Posts</title>
</head>
<body>
<header>
    <div class="text-center m-5">
        <h1 class="display-6">Posts
            <small class="text-muted" th:text="${title}"></small>
        </h1>
    </div>
</header>

<main>
    <table>
        <thead>
        <tr>
            <th>Post ID</th>
            <th>Post Title</th>
            <th>Latest Comment ID</th>
            <th>Latest Comment</th>
            <th>Author</th>
            <th>Created At</th>
        </tr>
        </thead>
        <tbody>
        <tr th:each="post : ${posts}">
            <td th:text="${post.getId} ?: '-'">-</td>
            <td th:text="${post.title} ?: '-'">-</td>
            <td th:text="${post.commentsById} ?: '-'"></td>
        </tr>
        </tbody>
    </table>
</main>

</body>
</html>

使用 Comment 对象数组而不是来自 @Query 的最新评论呈现 HTML

Post ID Post Title  Latest Comment ID   Latest Comment  Author  Created At
1   This is test post number 1  [com.example.demo.entity.Comment@6d578e15, com.example.demo.entity.Comment@e74e80e, com.example.demo.entity.Comment@42b0db5]
4   This is test post number 2  []
5   This is test post number 3  []

标签： javaspring-bootspring-boot-jpa

java - 在连接查询 Spring-Boot 中访问多个实体值

问题描述

解决方案

推荐阅读