JPA - Join

• 커뮤니티 게시판 테이블

쿼리: 특정 직업을 가진 사람들이 쓴 글의 정보와 사용자 정보를 조회하는 SQL 는 다음과 같을 것이다

    select
       t.topic_seq '번호',t.title '제목',m.name '작성자',p.title '직업',t.created_at '작성일'
    from `member` m
        join `topic` t
            on m.member_seq = t.member_seq
        join `profile` p
            on  p.profile_seq = m.profile_seq
    where p.profile_seq=1;

query 결과는 아래와 같을 것이다.

JPA 로 해당 부분을 구현 하는 과정은 아래와 같다.

• JPAQueryFactory 설정

    @Configuration
    public class QueryDslConfig {
        @PersistenceContext
        private EntityManager entityManager;
        @Bean
        public JPAQueryFactory jpaQueryFactory() {
            return new JPAQueryFactory(entityManager);
        }
    }

• Projections 정의

    class TopicResult {
        private static final  QMember member = QMember.member;
        private static final QTopic topic = QTopic.topic;
        private static final QProfile profile = QProfile.profile;
  
        public static ConstructorExpression<TopicSearchResultDto> topic(){
            return Projections.constructor(TopicSearchResultDto.class,
                    topic.topicSeq.as("topicNum"),
                    member.name.as("name"),
                    topic.title.as("title"),
                    topic.createdAt.as("createdAt"),
                    profile.title.as("jobTitle")
            );
        }
    }

• Predicate 정의

    class TopicPredicate {
      private final static QMember member = QMember.member;
      private final static  QTopic topic = QTopic.topic;
      private final static  QProfile profile = QProfile.profile;
  
      public static BooleanBuilder byProfile(Long profileSeq){
          BooleanBuilder builder = new BooleanBuilder();
          builder.and(profile.profileSeq.eq(profileSeq));
          return builder;
      }
  }

• 적용

   @Repository
   public class TopicRepositorySupport extends QuerydslRepositorySupport {
       private final JPAQueryFactory queryFactory;
  
       public TopicRepositorySupport(JPAQueryFactory queryFactory) {
           super(Topic.class);
           this.queryFactory = queryFactory;
       }
  
       public List<TopicSearchResultDto> findByProfile(Long profileSeq){
           QMember member = QMember.member;
           QTopic topic = QTopic.topic;
           QProfile profile = QProfile.profile;
           return queryFactory.select(TopicResult.topic())
                   .from(member)
                   .join(topic)
                   .on(member.memberSeq.eq(topic.member.memberSeq))
                   .join(profile)
                   .on(profile.profileSeq.eq(member.profile.profileSeq))
                   .where(TopicPredicate.byProfile(profileSeq))
                   .fetch();
       }
   }
  

• 참고로 그룹핑을 적용 할 경우

  class TopicGroup {
          private static final QMember member = QMember.member;
          private static final QAddress address = QAddress.address;
  
          public static SimpleExpression[] member(){
              return new SimpleExpression[]{
                      member.name
                      //,address.city
              };
          }
          public static SimpleExpression[] city(){
              return new SimpleExpression[]{
                      address.city
              };
          }
      }

• 전체 소스 코드