DB 함수 호출

DB 함수 호출을 위해서는 방언(Dialect) 설정이 필요하며, 각 DB 마다 제공하는 함수명, 동작이 다르기 때문에 방언 사용시 서비스별 DB에 맞춰 수정이 필요하다.

 

방언 Class 지정 (Application.yml)

방언 Class에 선언된 사용 가능한 함수 확인

• 방언 class에 등록된 함수 외에 함수를 사용하기 위해선 CustomDialect를 구현하면 된다.
• 참고 
  • https://055055.tistory.com/83

JPA Dialect

 

함수 사용

select 사용

jpaQueryFactory
    .select(Projections.fields(OrdersNewDto.class,
                orders.id.as("id")
                , orders.name.as("name")
                , Expressions.numberTemplate(Long.class, "second(timediff({0}, {1}))", batchJobExecution.startTime, batchJobExecution.endTime).as("workingSec")
                , Expressions.stringTemplate("YEAR({0})||MONTH({0})||DAY({0})", orders.orderDate).as("newDate")
            ))
    .from(orders)
    .fetch();

where 사용

return jpaQueryFactory.select(
            Projections.bean(BatchJobResultModel.class
                    , batchJobInstance.jobInstanceId.as("jobInstanceId")
                    , batchJobInstance.jobName.as("jobName")
                    , Expressions.numberTemplate(Long.class, "second(timediff({0}, {1}))", batchJobExecution.startTime, batchJobExecution.endTime).as("workingSec")
                    , batchJobExecution.startTime.as("startTime")
                    , batchJobExecution.endTime.as("endTime")
                    , batchJobExecution.status.as("status")
                    , batchJobExecution.exitMessage.as("exitMessage")
            )
        ).from(batchJobInstance)
        .innerJoin(batchJobExecution)
            .on(batchJobExecution.jobInstanceId.eq(batchJobInstance.jobInstanceId))
        .where(Expressions.stringTemplate("date_format({0}, '%Y%m%d')", batchJobExecution.startTime).eq(Expressions.stringTemplate("date_format(current_date, '%Y%m%d')")))
        .limit(30)
        .orderBy(batchJobInstance.jobInstanceId.desc())
        .fetch()
        ;
 
 
-- 실행 결과
select
    batchjobin0_.JOB_INSTANCE_ID as col_0_0_,
    batchjobin0_.JOB_NAME as col_1_0_,
    second(timediff(batchjobex1_.START_TIME,batchjobex1_.END_TIME)) as col_2_0_,
    batchjobex1_.START_TIME as col_3_0_,
    batchjobex1_.END_TIME as col_4_0_,
    batchjobex1_.STATUS as col_5_0_,
    batchjobex1_.EXIT_MESSAGE as col_6_0_
from
    BATCH_JOB_INSTANCE batchjobin0_
inner join
    BATCH_JOB_EXECUTION batchjobex1_
        on (
            batchjobex1_.JOB_INSTANCE_ID=batchjobin0_.JOB_INSTANCE_ID
        )
where
    date_format(batchjobex1_.START_TIME, '%Y%m%d')=date_format(current_date, '%Y%m%d')
order by
    batchjobin0_.JOB_INSTANCE_ID desc limit ?