Querydsl - 사용기

to_char 사용

-- sql
SELECT TO_CHAR(create_at, 'YYYY-MM-DD')
FROM entity

// Querydsl
StringTemplate datePath = Expressions.stringTemplate("to_char({0},'{1s}')", entity.createAt, ConstantImpl.create("yyyy-MM-dd HH24:MI"));

---

sum(case when) 사용

-- sql
SELECT SUM(CASE WHEN STATUS != CANCEL THEN 1 ELSE 0 END) AS RESERVATION_COUNT
, SUM(CASE WHEN STATUS = CANCEL THEN 1 ELSE 0 END) AS CANCEL_COUNT 
FROM entity

// Querydsl
NumberExpression<Integer> reservationCount = new CaseBuilder().when(entity.status.ne("CANCEL")).then(1).otherwise(0);
NumberExpression<Integer> cancelCount = new CaseBuilder().when(entity.status.eq("CANCEL")).then(1).otherwise(0);

queryFactory.select(Projections.fields(EntityDto.class
	, reservationCount.sum().as("reservationCount")
	, cancelCount.sum().as("cancelCount")
))
.from(entity)
.fetch();

---

from 절에 서브쿼리 사용

참고 : https://programmingnote.tistory.com/85

@Entity
@Subselect(
    "SELECT " +
    "       MAX(CASE WHEN e.STATUS = 'CONFIRM' THEN e.IDX END) AS ID, " +
    "       e.CUST_ID, " +
    "       e.HOSPITAL_ID, " +
    "       SUM(CASE WHEN e.STATUS <> 'CANCEL' THEN 1 ELSE 0 END) AS RESERVATION_COUNT, " +
    "       SUM(CASE WHEN e.STATUS = 'CANCEL' THEN 1 ELSE 0 END) AS CANCEL_COUNT, " +
    "       MAX(CASE WHEN e.STATUS = 'CONFIRM' THEN r.RESERVATION_DATE END) AS LAST_RESERVATION_DATE " +
    "  FROM ENTITY e " +
    " GROUP BY e.CUST_ID, e.HOSPITAL_ID"
)
@Immutable
@Synchronize("ENTITY")
public class EntitySubSelect {

    @Id
    private Long id;

    private String custId;

    private Long hospitalId;

    private Integer reservationCount;

    private Integer cancelCount;

    private LocalDateTime lastReservationDate;

}

@Subselect 로 작성된 쿼리의 결과가 엔티티에 매핑되게 됩니다.
이 엔티티를 Querydsl에서 사용할 수 있습니다.

queryFactory.select(Projections.fields(EntityDto.class
	, entitySubSelect.reservationCount
	, entitySubSelect.cancelCount
))
.from(entitySubSelect)
.fetch();