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