Spring Data JPA中使用本地查询时，嵌套投影无法正常工作。

Question

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

在参考文献中，他们只提到了如何使用JPQL进行嵌套投影。

假设我有以下投影：

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

public interface UserSummary {
    Integer getId();
}

我该如何使用本地查询来查询线程并进行投影？我尝试了以下代码：

@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);

但是看起来Spring Data只能映射thread实体，而不能映射author实体。

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

我尝试了 author.id as authorId, author.id as author_Id，但它们都不能正常工作。

- cdxf

---

你找到解决方案了吗？ - Jumana Alhaddad

Answer 1

我使用嵌套投影的构造函数来实现此功能。 请注意，要调用构造函数，您需要使用类名和包名。

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

完整示例

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);

}

示例结果

[ {
  "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"
  }
} ]

- Peter Gyschuk