Spring Data - Projection and @Query nested property

Question

Assume I have those DTO:

public interface ForumDTO extends ForumBaseDTO{
    Integer getId();
    ThreadDTO getLastThread();
}
public interface ThreadDTO {
    Integer getId();
    Integer getCommentCount()
}

In my Repository I have this query using those DTO as projection:

   @Query("select forum.id as id, " +
   "forum.name as name, " +
   "lastThread.id as lastThread_id " +
   "from Forum forum " +
   "inner join forum.lastThread as lastThread " +
   "where forum.parent.id = ?:"
   )
   Iterable<ForumDTO> findAllByParentId(Integer id);

I can access id,name in ForumDTO using this repo just fine, but with lastThread it just return null. I have tried as lastThread.Id,as lastThread_id, as lastThreadId but none of them work.

Stephan · Accepted Answer · 2018-04-16 22:21:27Z

2

You're almost there.

You need to access it from forum to follow out the foreign key:

@Query("select forum.id as id, " +
   "forum.name as name, " +
   "**forum.lastThread.id** as lastThread_id " +
   "from Forum forum " +
   "inner join forum.lastThread as lastThread " +
   "where forum.parent.id = ?:"
   )
   Iterable<ForumDTO> findAllByParentId(Integer id);

That said, you're killing yourself with extra work. The same Query can be written as:

@Query("select forum from Forum where forum.parent.id = :forumId")
Iterable<ForumDTO> findAllByParentId(@Param("forumId")Integer id);

You just need to make sure that the foreign key to Parent is present on the entity.

Also notice the @Param annotation. It makes your parameters easier to track, and also does some basic type checking against the db. It's very useful to prevent SQL injection attacks, and UncheckedTypeExceptions.

edited Apr 16, 2018 at 22:21

answered Apr 16, 2018 at 21:52

Stephan

6868 silver badges23 bronze badges

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

2 Comments

user2477 Over a year ago

But when ThreadDTO has a field like getCommentCount that is computated by using count(comments.id) your way doesn't work. Initially, I use the field name so I can use count() function

Stephan Over a year ago

That's a different issue. Aggregates are a little tricky in JPA, but the reason your'e getting the issue you are with this one is that you aren't properly following the foreign key path.

Collectives™ on Stack Overflow

Spring Data - Projection and @Query nested property

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related