CODEWITHSUNDEEP
spring-data-jpaspring-data
cdxf
cdxf

Reputation: 5648

Nested projection doesn't work with native query in Spring Data JPA

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections

In the reference, they only mention how to do nested projection with JPQL.

Assume I have these projection:

public interface ThreadWithContent {
    Integer getId();
    String getTitle();
    UserSummary getAuthor();
}

public interface UserSummary {
    Integer getId();
}

How can I query the Thread with projection using native query, I tried this:

@Query(value =
        "select thread.id as id,thread.title as title,author.id as authorId "+
        "from thread inner join users as author " +
        "on thread.author_id = author.id " +
        "where thread.id = ?1",nativeQuery = true)
ThreadWithContent getThreadsById(Integer threadID);

But it looks like Spring Data can only map the thread entity, but can't map the author enitity

{
"title": "Recusandae nihil fugiat deserunt.",
"author": null,
"id": 5
}

I have tried author.id as authorId, author.id as author_Id but none of them works.

Upvotes: 7

Views: 2259

Answers (1)

Peter Gyschuk
Peter Gyschuk

Reputation: 1029

I implemented this feature using constructor for nested projection. Take into account that to call constructor you need to use class name together with package name

@Value("#{new com.mycompany.projections.BadgeProjection(target.badgeId, target.badgeName, target.badgeDescription, target.badgeImageUrl)}")

Complete example

Parent Projection 

import org.springframework.beans.factory.annotation.Value;

import java.time.LocalDate;

public interface ChallengeProjection {
    Long getId();

    String getName();

    String getDescription();

    String getImageUrl();

    LocalDate getStartDate();

    LocalDate getEndDate();

    @Value("#{new com.mycompany.projections.BadgeProjection(target.badgeId, target.badgeName, target.badgeDescription, target.badgeImageUrl)}")
    BadgeProjection getBadge();
}

Nested Projection 

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@AllArgsConstructor
public class BadgeProjection {
    private Long id;

    private String name;

    private String description;

    private String getImageUrl;

}

Spring Repository 

@Repository
public interface WorkoutGroupRepository extends JpaRepository<WorkoutGroup, Long> {

    @Query(value = "select wg.id, wg.name, wg.description, wg.image_url as imageUrl, wg.start_date as startDate, wg.end_date as endDate," +
        "       b.id as badgeId, b.name as badgeName,  b.description as badgeDescription, b.image_url as badgeImageUrl " +
        " from workout_group wg left join badge b on b.id = wg.badge_id where wg.start_date between :weekStartDate and :weekEndDate"
        , nativeQuery = true)
    List<ChallengeProjection> getChallengeProjectionByWeek(@Param("weekStartDate") LocalDate weekStartDate, @Param("weekEndDate") LocalDate weekEndDate);

}

Example result

[ {
  "description" : "Challenge description 1",
  "name" : "Challenge name 1",
  "id" : 1,
  "imageUrl" : "myimage.jpeg",
  "startDate" : "2020-12-15",
  "endDate" : "2020-12-23",
  "badge" : {
    "id" : 1,
    "name" : "Badge 1",
    "description" : "Badge 1",
    "getImageUrl" : "myimage.jpeg"
  }
} ]

Upvotes: 6

Related Questions