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

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

PostCommentProjection

public interface PostCommentProjection {

    String getTitle();

    String getReview();

}

And then finally in Java

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

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

    List 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?

user_6166627 · Answer

Since you already have the title on hand, you're only lacking the reviews, so search the reviews only and then combine them with the title.

Also, it's a bit strange that you are searching for comments by title. The title is not unique, there may be 10 different posts with the same title, so you will fetch the comments for all 10 posts. I suggest you search for comments by post id.

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

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

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

1 Answers