[spring-jpa] native query 삽질로그

조건: 두 list object가 같은 결과로 리턴, 후에 합쳐서 데이터 추가 세팅 후, 날짜를 기준으로 정렬한 값을 반환해야 한다.

환경: springboot2.6.2

 

1. nativeQuery=true, projection interface 사용

@Query(value = "select hur.gid, hur.ci, hur.nick, hur.last_logout as lastLogout, " +
        "huld.begin_chip as beginChip, huld.last_chip as lastChip, huld.last_chip - huld.begin_chip as changeChip, huld.win_chip as winChip, timestamp(huld.base_date) as baseDateTime, hll.expired_Date as expiredDate, " +
        "case when hll.expired_date > now() and huld.base_date = DATE_FORMAT(now(), '%Y-%m-%d') then 'true' else 'false' end as canRelease " +
        "from hd_user_loss_daily huld " +
        "join hd_user_rat hur on hur.gid = huld.gid " +
        "join hd_loss_limit hll on hll.ci = hur.ci " +
        "where hur.ci = :#{#req.ci} " +
        "and huld.base_date between :#{#req.startDate} and :#{#req.endDate} ; "
        , nativeQuery = true)
<S extends LossChipDailyProjection> List<S> getListUserLossDailyWithUserRat(@Param("req") LossChipDailyReq req);



@Query(value = "select log.ci, " +
        "case when log.gid = 'ADMIN' then log.sval1 else log.gid end as releaser, " +
        "case when log.gid = 'ADMIN' then log.log_date else null end as adminReleaseDate, " +
        "case when log.gid <> 'ADMIN' then log.log_date else null end as elseReleaseDate, " +
//            "log.ival2 as lastChip, " +
        "log.log_date as baseDateTime " +
        "from hd_loss_limit_log log " +
        "where log.log_type = 'RELEASED' " +
        "and log.ci = :#{#req.ci} " +
        "and log.log_date between :#{#req.startDateTime} and :#{#req.endDateTime} ; "
        , nativeQuery = true)
<S extends LossChipDailyProjection> List<S> getListReleaseInfoByCi(@Param("req") LossChipDailyReq req);

request param을 object로 넘기고 싶으면, SpEL을 사용하면 된다.

public interface LossChipDailyProjection {
    String getCi();

    String getGid();

    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = DateFormatConst.DEFAULT)
    LocalDateTime getAdminReleaseDate();

    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = DateFormatConst.DEFAULT)
    LocalDateTime getElseReleaseDate();

    String getReleaser();

    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = DateFormatConst.DEFAULT)
    LocalDateTime getExpiredDate();

    Boolean getCanRelease();

    String getNick();

    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = DateFormatConst.DEFAULT)
    LocalDateTime getLastLogout();

    Long getBeginChip();

    Long getLastChip();

    Long getChangeChip();

    Long getWinChip();

    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = DateFormatConst.DEFAULT)
    LocalDateTime getBaseDateTime();
}

서비스 로직은 아래와 같다.

//public class LossChipDailyRes implements LossChipDailyProjection
//LossChipDailyRes -> LossChipDailyProjection로 바꿔도 동일

public List<LossChipDailyRes> getList(LossChipDailyReq req){
	//같은 response list로 가져와서
    List<LossChipDailyRes> listUser = lossDailyRepository.getListUserLossDailyWithUserRat(req);
    List<LossChipDailyRes> listRelease = logRepository.getListReleaseInfoByCi(req);

	//검사 후 추가 값 세팅
    for(LossChipDailyRes eachR : listRelease){
        LocalDateTime dailyBaseDateTime = eachR.getBaseDateTime().withHour(0).withMinute(0).withSecond(0);
        for(LossChipDailyRes eachD : listUser){
            if(eachD.getBaseDateTime().equals(dailyBaseDateTime)){
                eachR.setBeginChip(eachD.getBeginChip());  //에러발생
            }
        }
    }
	
    //두 리스트 합치고 정렬해서 반환
    List<LossChipDailyRes> unionContent = Stream.concat(listUser.stream(), listRelease.stream())
                                        .sorted(Comparator.comparing(LossChipDailyProjection::getBaseDateTime).reversed())
                                        .collect(Collectors.toList());

    return unionContent;
}

repository에서 반환되는 값을 class로 받아도 실제로 그리 안 들어가고 interface에 머무는 듯하다. 

왜냐면

1. @JsonFormat부분을 class에 넣으면 날짜 변환이 안된다.
2. setter 쪽 로직에서 아래와 같은 에러가 난다.

java.lang.UnsupportedOperationException: A TupleBackedMap cannot be modified.

인터페이스 -> 클래스로 매핑하는 로직 따위를 넣으면서까지 이 방식을 고수하고 싶지 않아서 다른 방법을 찾아본다.

(조금만 더 찾아보면 될 것 같기도 한데.... 잘 모르겠다) 

---

2.  @NamedNativeQuery를 사용

위와 같이 native query를 사용하되 return 값을 class로 받을 수 있는 방법을 고민하다가 @NatedNativeQuery를 사용하면 result mapping class와 매핑 룰을 지정하여 class로 리턴할 수 있을 것 같아서 시도해보았다. entity클래스 상단에 아래 부분을 추가해보았다.

@NamedNativeQuery(name = "UserLossDaily.getListUserLossDailyWithUserRat2",
query = "select hur.gid, hur.ci, hur.nick, hur.last_logout as lastLogout, " +
        "huld.begin_chip as beginChip, huld.last_chip as lastChip, huld.last_chip - huld.begin_chip as changeChip, huld.win_chip as winChip, timestamp(huld.base_date) as baseDateTime, hll.expired_Date as expiredDate, " +
        "case when hll.expired_date > now() and huld.base_date = DATE_FORMAT(now(), '%Y-%m-%d') then 'true' else 'false' end as canRelease " +
        "from hd_user_loss_daily huld " +
        "join hd_user_rat hur on hur.gid = huld.gid " +
        "join hd_loss_limit hll on hll.ci = hur.ci " +
        "where hur.ci = :#{#req.ci} " +  //에러발생
        "and huld.base_date between :#{#req.startDate} and :#{#req.endDate} ;"
        , resultSetMapping = "Mapping.LossChipDailyRes")
@SqlResultSetMapping(name =  "Mapping.LossChipDailyRes",
classes = @ConstructorResult(targetClass = LossChipDailyRes.class, columns = {
        @ColumnResult(name = "gid"),
        @ColumnResult(name = "ci"),
        @ColumnResult(name = "nick"),
        @ColumnResult(name = "lastLogout"),
        @ColumnResult(name = "beginChip"),
        @ColumnResult(name = "lastChip"),
        @ColumnResult(name = "changeChip"),
        @ColumnResult(name = "winChip"),
        @ColumnResult(name = "baseDateTime"),
        @ColumnResult(name = "expiredDate"),
        @ColumnResult(name = "canRelease"),
}))
@Entity
...

그런데 신박한 에러가 난다. 바로 SpEl을 사용할 수 없는 것이었다.

Space is not allowed after parameter prefix ':'

구글링 해보니 : 앞에 \\를 줘서 escape 하라는 글이 있었는데, 그건 select절 내에서 named parameter를 가져올 때나 가능하고 저렇게 SpEl문법을 escape 할 때 사용할 수 있는 것 같아 보이지는 않았다.

개인적으로

1. request parameter를 method req변수로 쫙 늘어뜨리고 싶지 않아서(object로 전달하고 그 안에서 꺼내서 매핑하고 싶어서)
2. entitiy 위에 매핑 룰/쿼리를 쫙 쓰면서 관리하고 싶지 않아서(쿼리는 repository에서 관리하는 게 보기에도 찾기에도 좋다고 생각) 빠르게 포기했다.

---

3. 결국 돌고 돌아 JQPL로..

사실 jpql로 쓰면 바로 될 것이라는 것을 알고 있었지만

1. entity에 (단순히 select를 위한) relation표기를 별로 안 좋아해서
2. response dto에 변수가 많은, 순서에 예민한 constructor생성을 피해보려고

최대한 다른 방식으로 해보고 싶었는데.. 짧은 시간 안에 해답을 찾기가 어려웠다.

@Query(value = "select new com.model.restriction.LossChipDailyRes( " +
        "hur.gid, hur.ci, hur.nick, hur.lastLogout, " +
        "huld.beginChip, huld.lastChip, huld.winChip, huld.pk.baseDate, hll.expiredDate, " +
        "case when hll.expiredDate > current_timestamp and huld.pk.baseDate = function('date_format', current_date, '%Y-%m-%d') then true else false end ) " +
        "from UserLossDaily huld " +
        "join huld.userRat hur " +
        "join hur.restrictionLawCi hll " +
        "where hur.ci = :#{#req.ci} " +
        "and huld.pk.baseDate between :#{#req.startDate} and :#{#req.endDate} "
        )
List<LossChipDailyRes> getListUserLossDailyWithUserRat3(@Param("req") LossChipDailyReq req);


 @Query(value = "select new com.model.restriction.LossChipDailyRes(" +
        "log.ci, " +
        "case when log.gid = 'ADMIN' then log.sval1 else log.gid end, " +
        "case when log.gid = 'ADMIN' then log.pk.logDate else null end, " +
        "case when log.gid <> 'ADMIN' then log.pk.logDate else null end, " +
//            "log.ival2, " +
        "log.pk.logDate) " +
        "from UserRestrictionLawCiLog log " +
        "where log.logType = 'RELEASED' " +
        "and log.ci = :#{#req.ci} " +
        "and log.pk.logDate between :#{#req.startDateTime} and :#{#req.endDateTime} "
        )
List<LossChipDailyRes> getListReleaseInfoByCi3(@Param("req") LossChipDailyReq req);

//조인이 필요한 엔티티마다 릴레이션 추가
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "gid", updatable = false, insertable = false)
UserRat userRat;

//변수 순서가 개복치..
public LossChipDailyRes(String gid, String ci, String nick, LocalDateTime lastLogout, Long beginChip, Long lastChip, Long winChip, LocalDate baseDate, LocalDateTime expiredDate, Boolean canRelease) {
    this.ci = ci;
    this.gid = gid;
    this.expiredDate = expiredDate;
    this.canRelease = canRelease;
    this.nick = nick;
    this.lastLogout = lastLogout;
    this.beginChip = beginChip;
    this.lastChip = lastChip;
    this.changeChip = lastChip - beginChip;
    this.winChip = winChip;
    this.baseDate = baseDate;
    this.baseDateTime = baseDate.atTime(0, 0, 0);
}

native query -> JPQL로 변환 시 주의사항

• native query에서는 dto이름 그대로 매핑하기 때문에 as로 이름을 다시 지정해줬어야 핬지만, jpql query로 바꿀 때는 as를 쓰면 안된다(쿼리 파싱이 안되서 서버가 안 뜸). 순서만 dto constructor랑 맞추줘야 함.
• sql function을 사용할 때 function으로 다 바꿔야하고 now()(mysql전용)도 current_date(jpa용) 등으로 바꿔야한다.
• true/false도 quotation 처리 확인해야한다. boolean으로 못 가져오고 string으로 인식할 수 있음..

//native query
case when hll.expired_date > now() and huld.base_date = DATE_FORMAT(now(), '%Y-%m-%d') then 'true' else 'false' end as canRelease 

//jpql
case when hll.expiredDate > current_timestamp and huld.pk.baseDate = function('date_format', current_date, '%Y-%m-%d') then true else false end

 

위와 같이 jpql로 바꾸니.. 다행히 서비스 로직을 그대로 사용할 수 있었다.

native query를 자주 사용하지 않아 + 복합적인 조건으로 인해 시간이 걸린 삽질이었지만, native query의 특징을 배울 수 있었던 시간이었다..

 

---

SpEl

https://spring.io/blog/2014/07/15/spel-support-in-spring-data-jpa-query-definitions

SpEL support in Spring Data JPA @Query definitions

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.named-parameters

Spring Data JPA - Reference Documentation

 

@NamedNativeQuery

https://thorben-janssen.com/spring-data-jpa-dto-native-queries/

Spring Data JPA - How to Return DTOs from Native Queries

 

jpa projection

https://www.baeldung.com/spring-data-jpa-projections

Spring Data JPA Projections | Baeldung

https://moonsiri.tistory.com/95

[SpringBoot] JPA Projection