Accessing Multiple Entity Values in a Join Query Spring-Boot

Question

I have an application that essentially has Post/Comment-like functionality. I need the application to render a Post index page from the Post entity with the latest comment and author being displayed from the Comment entity.

My database/table structure is (ids are primary just not shown as such) and sample data:

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

I have bi-directional mapping set up in my two entities:

@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;
    }
}

The PostRepository @Query I am using is below. The query itself works as raw SQL and I get the post with those latest comment data:

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,,,,,

But when I try accessing the comment fields in my Thymeleaf template using ${post.commentsById} all comments are being displayed which I was hoping would only display the one comment object joined from the @Query command. I also see that when I remove the @Query it does the same thing making the @Query pointless in this case.

Can anyone give me direction on how to correctly set this us? Thanks in advance.

@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 template

<!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>

Rendered HTML with Comment object array instead of latest comment from @Query

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  []

Answer 1

JpaRepository already defines findAll as the method that will find all entities. I'm not sure what the effect of declaring/over-riding it in your repository annotated with @Query will be. You could turn on the SQL debugging in JPA to find out exactly what is executing. It might be worth renaming your findAll method and calling the new method name to see if that works.

However I think there is probably a better alternative, like adding a getter method to return the latest comment in your entity and calling that in your template.

Answer 2

I took Hopey One's advice and renamed my query which is probably best practice to leave default query names alone. Projections suggested by Ashutosh did work.

I created an Interface for the index page:

package com.example.demo.entity;

import java.sql.Timestamp;

public interface Index {

    int getPostId();

    String getPostTitle();

    String getLatestComment();

    String getLatestCommentAuthor();

    Timestamp getLatestCommentCreatedAt();
}

In the PostRepository I created the @Query giving aliases to the fields in the Index interface:

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

    @Query(value = "select p.id as postId, p.title as postTitle, c.comment as latestComment, c.author as latestCommentAuthor, c.created_at as latestCommentCreatedAt 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<Index> findAllWithLatestComment();
}

My Thymeleaf code snippet

<table>
    <thead>
    <tr>
        <th>Post ID</th>
        <th>Post Title</th>
        <th>Latest Comment</th>
        <th>Author</th>
        <th>Created At</th>
    </tr>
    </thead>
    <tbody>
    <tr th:each="post : ${posts}">
        <td th:text="${post.postId} ?: '-'">-</td>
        <td th:text="${post.postTitle} ?: '-'">-</td>
        <td th:text="${post.latestComment} ?: '-'">-</td>
        <td th:text="${post.latestCommentAuthor} ?: '-'">-</td>
        <td th:text="${post.latestCommentCreatedAt} ?: '-'">-</td>
    </tr>
    </tbody>
</table>

HTML Output

Posts All Posts
Post ID Post Title  Latest Comment  Author  Created At
1   This is test post #1    I didn't like the post at all   Karen   2021-08-04 10:57:05.0
6   This is test post #2    -   -   -

Thank you for all the help!