JPA return selected OneToMany list in query as single Java object in projection result

Question

I have a similar setup as in this question, i.e., one Author can have multiple Comments. In my case, it is modeled as OneToMany in the Author class.

@Entity
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String userName;

    @OneToMany()
    @JoinTable(name = "AUTHOR_COMMENTS")
    private List<Comment> comments;
}

JPA created a reference table to implement this relation, hence the @JoinTable annotation.

Now, I want to query the author with all its comments in a single query.

The answer from here suggests that this should be possible in JPQL by selecting the list of comments in the query.

@Query(value= "SELECT a.userName as userName, a.comments as comments from Author a")
List<AuthorProjection> authorsWithComments();

Note that in my case the result is projected to relevant fields.

public interface AuthorProjection {
    String getUserName();
    List<CommentProjection> getComments();
}

The problem is that JPA returns only a single comment in the list.

I get:

John Doe, [First comment]
John Doe, [Second comment]

But I want:

John Doe, [First comment, Second comment]

So, I want all comments returned in the list of the corresponding author, all using just one single query.

• Is this even possible?
• Do I need to configure anything (I use an H2 database)?
• Are there requirements on the domain model to make it work (e.g. backreference from Comment to Author needed)?

I found other answers mentioning JOIN FETCH and SELECT NEW. But I was not able to make it work with JOIN FETCH. And SELECT NEW does not seem applicable because I already use a projection interface.

Answer 1

As pointed out in the comments, selecting a whole list of objects like this is not possible.

But depending on requirements, it might be possible to use database specific aggregate function to select a single additional column that aggregates the values of a subquery.

For above example, a string concatenating aggregate function such as STRING_AGG from PostgreSQL might be a viable solution:

SELECT
    a.userName as userName,
    (SELECT STRING_AGG(c.text, ",") FROM Comment c WHERE c MEMBER OF a.comments) as commentTextCsv
FROM Author a

Note that above example uses MEMBER OF operator of JPA to check whether the current comment is in the list of comments of the author. An alternative would be to use the IN operator and a subquery to check whether the current comment's ID is in the list of comment IDs of the author.

Answer 2

Instead of writing a query manually, use JPA query methods where queries are derived from the method name directly.

To do so, define a method in the repository, without the @Query annotation, as follows.

List<AuthorProjection> findAuthorsWithCommentsBy();

Note: find and By keywords are mandatory.