목차
- 실행환경
- 테이블
- 소스 예제
- 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_;
'Programming > Languages (Java, etc)' 카테고리의 다른 글
[Java] Java 8 사용하기 : Optional로 Null 지혜롭게 다루기 (1) | 2023.09.03 |
---|---|
[Java] Java 8 사용하기 : 가독성과 유연성, 람다와 Streams API (0) | 2023.08.16 |
[JAVA, SPRING] 버전 선택 가이드 2023 (1) | 2023.07.05 |
[SPRING] 테스트 코드 - 테스트 소개 (0) | 2023.03.21 |
[SPRING] Bean의 정의와 IoC 컨테이너 (부제 : IoC는 DI가 아니에요) (0) | 2023.02.14 |
댓글