본문 바로가기
Programming/Languages (Java, etc)

[JPA] Querydsl 사용해서 스칼라 서브쿼리 작성하기

by kghworks 2023. 8. 8.

 

 

목차

  • 실행환경
  • 테이블
  • 소스 예제
  • sql 로그

 

JPA에서 스칼라 서브쿼리를 작성하는 방법을 포스팅해봅니다.

 

실행 환경

  • MySQL 8.0.32
  • Java 8 
  • Spring Framework 4.3.6 
  • Hibernate 5.4.4 final
  • Querydsl 4.3.1

테이블

create table jpa_book.member
(
    id      bigint auto_increment
        primary key,
    city    varchar(255) null,
    street  varchar(255) null,
    zipcode varchar(255) null,
    name    varchar(255) null
);


create table jpa_book.item
(
    DTYPE         varchar(31)  not null,
    id            bigint auto_increment
        primary key,
    name          varchar(255) null,
    price         int          not null,
    stockquantity int          not null,
    author        varchar(255) null,
    isbn          varchar(255) null,
    actor         varchar(255) null,
    director      varchar(255) null,
    artist        varchar(255) null,
    etc           varchar(255) null
);


create table jpa_book.orders
(
    id          bigint auto_increment
        primary key,
    orderdate   datetime     null,
    status      varchar(255) null,
    DELIVERY_ID bigint       null,
    MEMBER_ID   bigint       null,
    constraint FK_qiycd4ok215wf6ngu3ririai5
        foreign key (MEMBER_ID) references jpa_book.member (id),
    constraint FK_s4cungkcm3phw177ouoi3bp7c
        foreign key (DELIVERY_ID) references jpa_book.delivery (id)
);

create table jpa_book.orders_item
(
    id         bigint auto_increment
        primary key,
    count      int    not null,
    orderprice int    not null,
    ITEM_ID    bigint null,
    ORDERS_ID  bigint null,
    constraint FK_918997r5x6mq60qmh7dgllge1
        foreign key (ORDERS_ID) references jpa_book.orders (id),
    constraint FK_t5dnipn05lt29bm1ockrc4cmc
        foreign key (ITEM_ID) references jpa_book.item (id)
);

 


소스 예제

Querydsl 

@Repository
public class OrderRepository {

    @PersistenceContext
    private EntityManager em;

    // JPAQueryFactory Bean 설정 후 주입
    @Autowired
    private JPAQueryFactory jpaQueryFactory;


    public List<Tuple> subQueryTest() {
        QMember qMember = new QMember("m"); // 별칭 M 지정
        QOrder subOrders = new QOrder("o"); // 별칭 M 지정

        return jpaQueryFactory.select(qMember.id,
                                      qMember.name,
                                      JPAExpressions.select(subOrders.orderdate.max())
                                                	.from(subOrders)
                                            		.where(subOrders.member.eq(qMember)))
                               .from(qMember)
                               .fetch();

    }
}

 

클라이언트 (테스트 코드)

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:appConfig.xml")
@Transactional
public class QueryDSLTest {

    @Autowired
    private OrderRepository orderRepository;

    @PersistenceContext
    private EntityManager em;


    @Test
    public void queryDSLSubQueryTest() throws Exception {

        // Given
        Address address = new Address();
        address.setCity("서울시");
        address.setStreet("강남구");
        address.setZipcode("123-123");

        Member karina = new Member();
        karina.setName("카리나");
        karina.setAddress(address);
        em.persist(karina);

        Member winter = new Member();
        winter.setName("윈터");
        winter.setAddress(address);
        em.persist(winter);

        Category category = new Category();
        category.setName("카테고리1");

        em.persist(category);

        Order order = new Order();
        order.setMember(karina);
        order.setStatus(OrderStatus.ORDER);
        em.persist(order);

        // When
        List<Tuple> result = orderRepository.subQueryTest();
        
        // Then
        assertTrue(result.size() > 0);
        System.out.println("result = " + result.toString());

    }

}

 

실행결과

result = [[1, 카리나, 2023-08-08 16:22:36.621], [2, 윈터, null]]

sql 로그

Hibernate: 
    /* select
        m.id,
        m.name,
        (select
            max(o.orderdate) 
        from
            
        Order o where
            o.member = m) 
    from
        Member m */ select
            member0_.ID as col_0_0_,
            member0_.name as col_1_0_,
            (select
                max(order1_.orderdate) 
            from
                ORDERS order1_ 
            where
                order1_.MEMBER_ID=member0_.ID) as col_2_0_ 
        from
            MEMBER member0_

 

정제

-- JPQL
select m.id,
       m.name,
       (select max(o.orderdate)
        from Order o
        where o.member = m)
from Member m;

-- SQL
select member0_.ID                             as col_0_0_,
       member0_.name                           as col_1_0_,
       (select max(order1_.orderdate)
        from ORDERS order1_
        where order1_.MEMBER_ID = member0_.ID) as col_2_0_
from MEMBER member0_;

 

댓글