JPA QueryDSL 에서 MAX 엔티티 가져오기

QueryDSL을 사용하면서 max 앤터티를 가져오는 방법은 2가지가 있다.

첫번째는 JPAExpressions를 이용하여 키값과 max값을 조회조건으로 하여 가져오는것.

두번째는 orderby를 이용하여 첫번째 앤터티를 가져오는것.

 

1. JPAExpressions를 이용한 max값과 비교하여 가져오기

JPAQueryFactory query = new JPAQueryFactory(em); // JPA 쿼리
		
QBuilding qBuilding = QBuilding.building;
QBuilding b = new QBuilding("b");

Building building
	= query.selectFrom(qBuilding)
	    .where(qBuilding.buildingID.eq(
	            JPAExpressions.select(b.buildingID.max()).from(b)))
	    .fetchFirst();

이런 경우 실제 실행되는 쿼리는 아래와 같다. (MS-SQL 기준)

select
    TOP(1) building0_.BuildingID as Building1_3_,
    building0_.Address as Address2_3_,
    building0_.InsertID as InsertID3_3_,
    building0_.InsertDT as InsertDT4_3_,
    building0_.UpdateID as UpdateID5_3_,
    building0_.UpdateDT as UpdateDT6_3_,
    building0_.BranchID as BranchI10_3_,
    building0_.BuildingNm as Building7_3_,
    building0_.OwnType as OwnType8_3_,
    building0_.Version as Version9_3_ 
from
    dbo.Building building0_ 
where
    building0_.BuildingID=(
        select
            max(building1_.BuildingID) 
        from
            dbo.Building building1_
    )

실행계획을 보면

 

 

2. orderby 를 이용한 첫번째 엔터티 가져오기

JPAQueryFactory query = new JPAQueryFactory(em); // JPA 쿼리
		
QBuilding qBuilding = QBuilding.building;

Building building
	= query.selectFrom(qBuilding)
		.orderBy(qBuilding.buildingID.desc())
	    .fetchFirst();

실제 실행되는 쿼리는 다음과 같다.

select
    TOP(1) building0_.BuildingID as Building1_3_,
    building0_.Address as Address2_3_,
    building0_.InsertID as InsertID3_3_,
    building0_.InsertDT as InsertDT4_3_,
    building0_.UpdateID as UpdateID5_3_,
    building0_.UpdateDT as UpdateDT6_3_,
    building0_.BranchID as BranchI10_3_,
    building0_.BuildingNm as Building7_3_,
    building0_.OwnType as OwnType8_3_,
    building0_.Version as Version9_3_ 
from
    dbo.Building building0_ 
order by
    building0_.BuildingID desc

실행계획은 다음과 같다.

둘다 큰차이는 없지만

코딩의 단순화와 실행계획이 한단계 준다는 사소한 문제로

orderby를 이용한 max entity 조회를 선호하고 있다.