[jpa] mysql로 동적 쿼리 작업 시 시행착오

환경: springboot2.7.6, mysql, spring jpa

 

mybatis 사용 시 where절에 대한 동적 쿼리를 작성하는 일이 다분했는데,

jpa를 사용하면서 어떻게 하면 효율적 일지 고민하게 된다.

변수 한두개면 그냥 일반적인 jpa문을 여러 개 만들어두고 서비스단에서 분기 쳐서 서로 다른 repository의 함수(정적인 쿼리)를 가져오게 했는데,

옵션이 되는 변수가 많아지면 가독성에도, 관리에도 어려움이 있는 코드가 된다.

그리하여.. where절을 동적으로 변환할 수 있는 방법이 있나 찾아본다.

 

 

1. Specification & Criteria

Specification을 사용하면 자바 코드를 작성하 듯 쿼리를 작성할 수 있다.

허나 직관적으로 SQL문이라는 생각이 안 들고..

사용법도 복잡해서 개인적으로 선호하지 않는다..

https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html 

 

2. query dsl 활용

BooleanBuilder를 사용하여, 여러 조건을 빌더에 담을 수 있다.

 1번 방법보다는 가독성이 좋긴 하지만,, 이 역시 자바코드로 작성하기에 if문이 많아질 수 있다는 부담이 있다..

또한 query dsl 특성상 컴파일을 따로 하여 Q클래스를 만들고 이를 기반으로 작성되는데,,

컴파일 시 에러가 나면 이후에 모든 컴파일에서 에러가 난 것으로 보여 컴파일 에러 원인을 찾기가 어려웠던 경험이 있고

잡다하게 만들어야 하는 클래스들이 많아서 개인적으로 안 좋아한다.

https://samuel-mumo.medium.com/dynamic-queries-and-querydsl-jpa-support-in-spring-a1b4e233084b

 

3. JPQL에서 작성하기

개인적으로 좋아하는 쿼리 작성법이다.

아래와 같이 작성하면 dto의 값이 있는지 없는지에 따라서 다음 쿼리 적용 유무를 판단한다.

(:#{#request.arenaId} IS NULL OR t.arenaId = :#{#request.arenaId})

주의해야 할 점은 OR절이 있다 보니 관련 조건에 꼭 괄호처리를 하여 한다. 그렇지 않으면 전체가 조회될 수도..

허나 IN 절 작성 시.. 몇몇 에러를 만났는데. 아래에 기술한다.

---

참고로 request.sequences는 리스트 타입이다.

에러

 Antlr.NoViableAltException: unexpected AST node: {vector}

해결

is null 앞절에(List에) 괄호 추가

+ "AND ((:#{#request.sequences}) IS NULL OR t.seq IN :#{#request.sequences}) "

 

에러

Caused by: java.sql.SQLException: Operand should contain 1 column(s)

해결

coalesce 사용

https://www.w3schools.com/sql/func_mysql_coalesce.asp

+ "AND (COALESCE(:#{#request.sequences}) IS NULL OR t.seq IN :#{#request.sequences}) "

 

에러

h2를 이용한 repository 테스트를 진행할 경우, 아래와 같은 에러가 발생한다.

Caused by: org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "?"; SQL statement:

coalesce의 쓰임새가 h2와 mysql이 다르다는 소리다.

실제로 h2에서는 coalesce가 사용되지 않은 쿼리도 문제없이 작동했다.

하지만 나는 h2와 mysql 모두를 만족시켜야 하므로.. 좀 더 연구해 보니 아래와 같은 쿼리가 나오게 된다..

+ "AND ((COALESCE(:#{#request.sequences}, t.seq) = t.seq) OR t.seq IN :#{#request.sequences}) "

---

그래서 최종 쿼리의 모양새는

 @Query(value =
      "SELECT new com.model.Response(t.seq, t.startDate, t.dismissedDate, t.seatCnt, t.prizeTotal) "
          + "FROM Table t " + "WHERE (:#{#request.arenaId} IS NULL OR t.arenaId = :#{#request.arenaId}) "
          + "AND ((COALESCE(:#{#request.sequences}, t.seq) = t.seq) OR t.seq IN :#{#request.sequences}) "
          + "AND t.startDate >= :#{#request.startDate} AND t.dismissedDate <= :#{#request.endDate} ")
  Page<Response> getRecords(Request request, Pageable pageable);

---

의미론적인 에러

위와 같이 작성하여 프로그래밍적인 에러는 피했는데, 막상 데이터를 조회해보니 잘못 조회되는 부분이 있었다.

in절에 대한 문제였는데, 위 쿼리를 보면서 설명한다.

1. sequences는 List<Long>이고 화면에서 받아오는 값이 아닌, id의 유무에 따라 서버에서 받아서 채워 넣는 값이다. 최초에 null이다.
2. id가 있으면 디비에서 조회해서 세팅을 해주고 없으면 emptyList가 들어간다.
3. id가 있지만 참여를 하지 않아 디비에 데이터가 없으면 최종적으로는 getRecord함수가 emptyList로 반환되기를 원했다.
4. 하지만 위 쿼리는 sequences 값의 emptyList와 null을 구분하지 않는다. 
   1. COALESCE(:#{#request.sequences}, t.seq) = t.seq 이 부분이 true가 되어서 다음 AND 절로 넘어간다.
5. 그래서 null의 전체조회 의미와 emptyList의 참여하지 않음이 같은 결과를 내게 된다..
   1. 즉, 참여하지 않은 사람이 전체에 참여한 것처럼 나온다.

따라서 사용에 주의해야한다!!