SqlResultSetMapping native SQL query and One2Many relations

Question

Is it possible with help of SqlResultSetMapping and entityManager.createNativeQuery fetch object with One2Many relations from two different tables ?

For example

@Entity
@Table(name = "posts")
public class Post {
    @OneToMany(mappedBy = "post")
    private List<Comment> comments;
}

@Entity
@Table(name = "comments")
public class Comment {
    @ManyToOne(optional = false)
    @JoinColumn(name = "post_id", referencedColumnName = "post_id")
    private Post post;
}

Query:

select p.*, c.* from posts p left join (
    select * from comments where content like "%test%" order by last_edited limit 0, 3) 
as c on p.post_id = c.post_id

based on native sql query I need to fetch posts objects with a comments.

I mean - as a result I need to receive List of Posts and each post of this list is already populated with an appropriate Comments.

Is it possible with JPA ? If so, could you please show an example ?

score 1 · Answer 1 · answered Jun 29 '22 at 07:12

I know this question is old, but I still had trouble finding an answer, therefore adding one here.

Yes, it is not possible without additional mapping. The result will be a list of Object arrays. The problem is that the list of comments in your case won't be filled automatically. You need to do the mapping yourself.

Let's imagine your return is a resultset like this:

postid	postCommentId	postComment
1001	2001	comment content 1
1001	2002	comment content 2
1999	2999	comment content 1
1001	2003	comment content 3
1001	2004	comment content 4

In the list, you can see two posts. One with 4 comments and one with 1. The SqlResultsetMapping will only map each row as an object array, which means the post.comments list won't be filled. You have to do it manually. Here is a sample of how you could do it.

    List<Object[]> resultList = em.createQuery(query).getResultList();
    // prepare a hashmap for easier mapping
    final Map<Long, Post> mappedResult = new HashMap<>(resultList.size());
    resultList.forEach(o -> {
            Post p = (Post) o[0];
            Comment c = (Comment) o[1];
            var processedPost = mappedResult.get(p.getId()); 
            if(processedPost != null) {
                processedPost.addComment(c);
            } else {
                p.addComment(c);
                mappedResult.put(p.getId(), p);
            }
        });
    // return a sorted list from the created hashmap
    return mappedResult.values().stream().sorted((p1, p2) -> p1.getId().compareTo(p2.getId())).toList();

I am pretty sure there are better and more performant possibilities, but I was not able to find any.

score -1 · Answer 2 · edited May 23 '17 at 12:03

You can do something like this:

SELECT post from Post post 
LEFT JOIN FETCH post.comments -- to fetch all comments in each post
LEFT JOIN FETCH post.comments comment -- to do the WHERE
WHERE comment.content like "%test%"

The problem is the order by last_edited. I think you cannot order the fetched list of comments in JPA, but you can put this annotation in the private List<Comment> comments; to set a default order in the collection:

@OneToMany(mappedBy = "post")
@OrderBy("lastEdited asc")
private List<Comment> comments;

And, finally, to the limit, use the methods firstResult and maxResults from JPA:

return em.createQuery(query)
         .setFirstResult(0) // offset
         .setMaxResults(3) // limit
         .getResultList();

SqlResultSetMapping native SQL query and One2Many relations

2 Answers2