쿼리DSL 연관관계 JOIN하여 DTO로 조회하기

안녕하세요 영한님의 강의를 맹신하는 대학생 개발자입니다!

다름이 아니라 플랫폼을 개발하고 있는데 연관관계가 포함된 People과 PeopleProfile 사이에서 join을 통해 값을 가져오려고 합니다.

PeopleEntity

@Getter
@Entity
@Table(name = "people")
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class People extends BaseTimeEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "people_id")
    private Long peopleId;

    @Column(name = "nickname")
    private String nickname;

    @Column(name = "email")
    private String email;

    @Column(name = "phone_number")
    private String phoneNumber;

    @Enumerated(EnumType.STRING)
    @Column(name = "people_type")
    private PeopleType peopleType;

    @Column(name = "profile_image_uri")
    private String profileImageUri;

    @Column(name = "account_number")
    private String accountNumber;

    @OneToOne(cascade = CascadeType.REMOVE)
    private Member member;

    @OneToOne(mappedBy = "people")
    private PeopleProfile peopleProfile;

...
}

PeopleProfile

@Entity
@Getter
@Table(name = "people_profile")
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class PeopleProfile {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "people_profile_id")
    private Long peopleProfileId;

    @Column(name = "introduction")
    private String introduction;

    @Column(name = "activity_area")
    private String activityArea;

    @Column(name = "education")
    private String education;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "people_id")
    private People people;
    
    @ElementCollection
    @CollectionTable(name = "people_hashtags", joinColumns = @JoinColumn(name = "people_profile_id"))
    @Column(name = "hashtags_id")
    private List<Hashtag> hashtags = new ArrayList<>();

...
}

Hashtag

@Embeddable
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Hashtag {

    @Column(name = "hashtag")
    private String value;

    private Hashtag(String value) {
        this.value = validate(value);
    }

    private String validate(String value) {
        return value;
    }

    public static Hashtag from(String value) {
        return new Hashtag(value);
    }
}

hashtag는 값 타입으로 구현했습니다.

PeopleDTO

@Getter
public class CardPeopleResponse {
    @NotNull 
    private Long peopleId;
    @NotNull 
    private String nickname;
    @NotNull 
    private String peopleType;
    @NotNull 
    private String profileImageUri;
    @Valid
    private CardPeopleProfileResponse profile;
    
    @QueryProjection
    public CardPeopleResponse(final Long peopleId, final String nickname, final People Type peopleType,
        final String profileImageUri, @Valid CardPeopleProfileResponse profile) {
        this.peopleId = peopleId;
        this.nickname = nickname;
        this.peopleType = peopleType.name();
        this.profileImageUri = profileImageUri;
        this.profile = profile;
    }
}

PeopleProfileDTO

@Getter
public class CardPeopleProfileResponse {
    @NotNull
    private final String activityArea;
    @NotNull
    private final List<String> hashtags;
    @NotNull
    private final Integer completedProjectsCount;
    @NotNull
    private final Integer interestsCount;

    @QueryProjection
    public CardPeopleProfileResponse(final String activityArea, final List<Hashtag> hashtags, 
            final Integer completedProjectsCount, final Integer interestsCount) {
        this.activityArea = activityArea;
        this.hashtags = hashtags.stream().map(Hashtag::getValue).toList();
        this.completedProjectsCount = completedProjectsCount;
        this.interestsCount = interestsCount;
    }
}

쿼리DSL

private List<CardPeopleResponse> getCardPeopleContent(Pageable pageable) {
        return queryFactory
            .select(new QCardPeopleResponse(
                people.peopleId,
                people.nickname,
                people.peopleType,
                people.profileImageUri,
                new QCardPeopleProfileResponse(
                    peopleProfile.activityArea,
                    peopleProfile.hashtags,
                    Expressions.constant(0),
                    Expressions.constant(0)
                )
            ))
            .from(people)
            .join(people.peopleProfile, peopleProfile)
            .orderBy(getPeopleOrderSpecifiers(pageable.getSort()))
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .fetch();
    }

completedProjectsCount와 interestsCount는 추후 개발 예정이므로 Expressions.constant(0)으로 대체 해뒀습니다. 위와 같이 서버를 구현하였을때, 서버 구동은 문제 없이 됩니다.

문제점

2024-02-22T16:44:50.163+09:00 DEBUG 3487851 --- [io-52003-exec-1] org.hibernate.SQL                        : 
    select
        p1_0.people_id,
        p1_0.nickname,
        p1_0.people_type,
        p1_0.profile_image_uri,
        p2_0.activity_area,
        h1_0.hashtag 
    from
        people p1_0 
    join
        people_profile p2_0 
            on p1_0.people_id=p2_0.people_id 
    join
        people_hashtags h1_0 
            on p2_0.people_profile_id=h1_0.people_profile_id 
    order by
        p1_0.people_id desc offset ? rows fetch first ? rows only

2024-02-22T16:44:50.274+09:00 ERROR 3487851 --- [io-52003-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessApiUsageException: argument type mismatch] with root cause

java.lang.IllegalArgumentException: argument type mismatch
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:na]
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) ~[na:na]
        at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na]
        at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) ~[na:na]
        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) ~[na:na]
        at com.querydsl.core.types.ConstructorExpression.newInstance(ConstructorExpression.java:144) ~[querydsl-core-5.0.0.jar!/:na]
        at com.querydsl.core.types.FactoryExpressionUtils.compress(FactoryExpressionUtils.java:155) ~[querydsl-core-5.0.0.jar!/:na]
        at com.querydsl.core.types.FactoryExpressionUtils.access$100(FactoryExpressionUtils.java:28) ~[querydsl-core-5.0.0.jar!/:na]
        at com.querydsl.core.types.FactoryExpressionUtils$FactoryExpressionAdapter.newInstance(FactoryExpressionUtils.java:62) ~[querydsl-core-5.0.0.jar!/:na]
        at com.querydsl.core.support.ConstantHidingExpression.newInstance(ConstantHidingExpression.java:87) ~[querydsl-core-5.0.0.jar!/:na]
...

다음과 같은 argument type mismatch에러가 발생합니다. DTO에서 값을 받아와서 생성자에서 데이터 타입을 변경하면 문제가 되는걸까요? 이러한 경우에는 보통 어떻게 조회하는지 궁금합니다!

public CardPeopleProfileResponse(final String activityArea, final List<Hashtag> hashtags,
        final Integer completedProjectsCount, final Integer interestsCount) {
    this.activityArea = activityArea;
    this.hashtags = hashtags.stream().map(Hashtag::getValue).toList();
    this.completedProjectsCount = completedProjectsCount;
    this.interestsCount = interestsCount;
}

.select(new QCardPeopleResponse(
    people.peopleId,
    people.nickname,
    people.peopleType,
    people.profileImageUri,
    new QCardPeopleProfileResponse(
        peopleProfile.activityArea,
        JPAExpressions.select(peopleProfile.hashtags).from(peopleProfile).where(peopleProfile.people.eq(people)),
        Expressions.constant(0),
        Expressions.constant(0)
    )
))

Class-based projections with JPQL is limited to constructor expressions in your JPQL expression, e.g. `SELECT new com.example.NamesOnly(u.firstname, u.lastname) from User u`. (Note the usage of a FQDN for the DTO type!) This JPQL expression can be used in `@Query` annotations as well where you define any named queries.

private List<CardPeopleResponse> getCardPeopleContent(Pageable pageable) {
        return queryFactory
            .select(new QCardPeopleResponse(
                people.peopleId,
                people.nickname,
                people.peopleType,
                people.profileImageUri,
                new QCardPeopleProfileResponse(
                    peopleProfile.activityArea,
                    JPAExpressions.select(peopleProfile.hashtags).from(peopleProfile).where(peopleProfile.people.eq(people))
                )
            ))
            .from(people)
            .join(people.peopleProfile, peopleProfile)
            .orderBy(getPeopleOrderSpecifiers(pageable.getSort()))
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .fetch();
    }

2024-02-25T23:08:06.122+09:00 ERROR 3847208 --- [io-52003-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: more than one row returned by a subquery used as an expression

private List<CardPeopleResponse> getCardPeopleContent(Pageable pageable) {
      List<Tuple> result = queryFactory
          .select(
              people.peopleId,
              people.nickname,
              people.peopleType,
              people.profileImageUri,
              peopleProfile.activityArea
							peopleProfile.hashtags
          )
          .from(people)
          .join(people.peopleProfile, peopleProfile)
          .orderBy(getPeopleOrderSpecifiers(pageable.getSort()))
          .offset(pageable.getOffset())
          .limit(pageable.getPageSize())
          .fetch();

select
        p1_0.people_id,
        p1_0.nickname,
        p1_0.people_type,
        p1_0.profile_image_uri,
        p2_0.activity_area,
        h1_0.hashtag 
    from
        people p1_0 
    join
        people_profile p2_0 
            on p1_0.people_id=p2_0.people_id 
    join
        people_hashtags h1_0 
            on p2_0.people_profile_id=h1_0.people_profile_id 
    order by
        p1_0.people_id desc offset ? rows fetch first ? rows only

private List<Hashtag> getHashtagsForPeople(Long peopleId) {
        String sql = "SELECT h.hashtag FROM people_hashtags h WHERE h.people_profile_id = :peopleId";
        List<String> hashtagValues = em.createNativeQuery(sql)
                                       .setParameter("peopleId", peopleId)
                                       .getResultList();

        return hashtagValues.stream()
                            .map(Hashtag::from)
                            .collect(Collectors.toList());
    }

private List<CardPeopleResponse> getCardPeopleContent(Pageable pageable) {
      List<Tuple> result = queryFactory
          .select(
              people.peopleId,
              people.nickname,
              people.peopleType,
              people.profileImageUri,
              peopleProfile.activityArea
          )
          .from(people)
          .join(people.peopleProfile, peopleProfile)
          .orderBy(getPeopleOrderSpecifiers(pageable.getSort()))
          .offset(pageable.getOffset())
          .limit(pageable.getPageSize())
          .fetch();

      return result.stream().map(tuple -> {
          Long peopleId = tuple.get(people.peopleId);
          String nickname = tuple.get(people.nickname);
          PeopleType peopleType = tuple.get(people.peopleType);
          String profileImageUri = tuple.get(people.profileImageUri);
          String activityArea = tuple.get(peopleProfile.activityArea);
          List<Hashtag> hashtags = getHashtagsForPeople(peopleId);
          CardPeopleProfileResponse profile = new CardPeopleProfileResponse(activityArea, hashtags);
          return new CardPeopleResponse(peopleId, nickname, peopleType, profileImageUri, profile);
      }).toList();
  }