java - How to create nested projection of one to many mapping in spring data jpa

Question

I have a one to many mapping with post and post_comments table,our requirement is to retrieve only few values in both the tables and send back to caller as one to Many Mapping like postDTO. Below is our code.

Post Entity

@Entity(name = "Post")
@Getter
@Setter
public class Post {

    @Id
    private Long id;

    private String title;

    private LocalDateTime createdOn;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "post", orphanRemoval = true)
    private List<PostComment> comments = new ArrayList<>();

    public void addComment(PostComment comment) {
       this.comments.add(comment);
       comment.setPost(this);
    }

}

PostCommentEntity

@Getter
@Setter
public class PostComment {

    @Id
    private Long id;

    private String review;

    private LocalDateTime createdOn;

    public PostComment(String review) {
        this.review = review;
        this.createdOn = LocalDateTime.now();
    }

    @ManyToOne
    private Post post;

}

postDTO --> Desired response format which we need.

@Getter
@Setter
@Builder
@ToString
public class PostDTO {

    String title;

    @Builder.Default
    List<PostCommentsDTO> comments;
}

PostCommentsDTO --> One to many nested projection value.

@Data
@Builder
public class PostCommentsDTO {

    String review;

}

As we couldn't achieve this directly using spring data jpa. Achieved using alternative mapping.

PostRepository We need to get only title from post table and reviews from postcomment table desired as postDTO class, as We cannot perform mapping in a single instance I'm delegating the mapping in Java as below by creating intermediary projection.

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

    @Query("SELECT p.title as title, c.review as review FROM Post p JOIN p.comments c where p.title = :title")
    List<PostCommentProjection> findByTitle(@Param("title") String title);
}

PostCommentProjection

public interface PostCommentProjection {

    String getTitle();

    String getReview();

}

And then finally in Java

    List<PostCommentProjection> postCommentProjections = this.postRepository.findByTitle("Post Title");

    final Function<Entry<String, List<PostComments>>, PostDTO> mapToPostDTO = entry -> PostDTO.builder()
            .title(entry.getKey()).comments(entry.getValue()).build();
    final Function<PostCommentProjection, String> titleClassifier = PostCommentProjection::getTitle;
    final Function<PostCommentProjection, PostComments> mapToPostComments = postCommentProjection -> PostComments
            .builder().review(postCommentProjection.getReview()).build();
    final Collector<PostCommentProjection, ?, List<PostComments>> downStreamCollector = Collectors
            .mapping(mapToPostComments, Collectors.toList());

    List<PostDTO> postDTOS = postCommentProjections.stream()
            .collect(groupingBy(titleClassifier, downStreamCollector)).entrySet().stream().map(mapToPostDTO)
            .collect(toUnmodifiableList());

Is there an effective or automatic way to fetch the POSTDTO project directly from repository?

标签： javahibernatejpaspring-data-jpa

Answer 1

由于您手头已有标题，因此您只缺少评论，因此仅搜索评论，然后将它们与标题结合起来。

此外，您按标题搜索评论有点奇怪。标题不是唯一的，可能有 10 个不同的帖子具有相同的标题，因此您将获取所有 10 个帖子的评论。我建议您通过帖子 ID 搜索评论。

@Repository
public interface PostCommentRepository extends JpaRepository<PostComment, Long> {
    
    @Query("select review from PostComment where post.id = :postId")
    List<String> findAllReviewsByPostId(Long postId);
}

... main() {
   ...
   List<String> reviews = commentRepo.findAllReviewsByPostId(post.getId());
   PostDTO dto = new PostDTO(post.getTitle(), reviews);
}