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.

dot notation '.' in JPQL is an inner join, so a.comments isn't accessing a list property but causing you to return userName/CommentProjection pairs. JPQL will not perform what you want with raw data - you are better off to just fetch the entity directly and use that to build your projection, or use the entity directly. — Chris
– Chris, Commented Jan 23, 2024 at 16:04

mihca · Accepted Answer · 2024-01-26 09:16:35Z

0

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.

answered Jan 26, 2024 at 9:16

mihca

1,2771 gold badge14 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Toni · Accepted Answer · 2024-01-30 16:27:12Z

0

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.

answered Jan 30, 2024 at 16:27

Toni

5,3313 gold badges18 silver badges50 bronze badges

2 Comments

mihca Over a year ago

This has the downside that the involved queries are not transparent. I think JPA / Hibernate might create multiple queries for lazy/eager loading of related objects to get the final result.

Toni Over a year ago

I agree that you have to handle the lazy loading of the related entities. Therefore, you can annotate the query method with EntityGraph to fetch certain related objects eagerly. For instance, using @EntityGraph(attributePaths = {"comments"}) would ensure authors and their comments are fetched with a single query.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related