Native Query에서 Count(*) 사용법

이지우·2021년 10월 10일

[프로젝트] School Visitor

목록 보기

2/2

이전 글에서 고민하던 쿼리를 QueryDSL로 작성하려 하였고,
기존에 JPQLQuery를 사용했던 경험이 있었기에 이번에도 똑같이 작성하려 하였으나 마음대로 잘 되지 않았다. ~~요행을 바라면 안 되는 것 같다 ㅠ~~
그래서 구글링해본 결과 JPQLQuery에서는 서브쿼리를 지원하지 않는다는 정보를 얻었다..
JPQLQuery뿐만 아니라 QueryDSL 자체에서는 from에 대한 서브쿼리를 지원하지 않는다는 정보도 입수했다 ㅜ-ㅠ

결국 native query를 사용하기로 했다!

덧붙여 school visitor 모든 코드는 github에 있다.

JPA Native Query에서 Count를 받고싶은데요..

Native Query를 사용하면서 count를 어떻게 처리해야할지 몰랐다.
리턴받고자 하는 결과물은 School + 해당 학교의 방문횟수 (count) 이다.

이전 글을 작성할 때는 어떤 방법으로 받아오는게 좋을까~가 의문이었고,
native query로 받아오자고 결정한 후에는
'아니 count는 어떻게 받아오는거야!' 가 의문이 되었다.

물론 count를 받아오는 방법에 대한 글은 아주 많았지만, 늘 그렇듯 '내가 원하는 조건까지 달고' count를 받아오는 방법은 한번에 나오지 않는다.

도메인 잠시 설명

School엔티티에는 고유번호, 학교이름, 주소, 지역 ...(생략) 등이 있다.
뽑아오고싶은 결과물은 School + 해당 학교의 방문횟수 (count) 라고 했다.
결과물을 쿼리로 작성했을 때, mysql workbench에서는
select * from school left join (select school_id, count(*) from visit where created_at between '2020-01-01' and '2022-01-01' group by school_id) as sub on sub.school_id = school.school_id where name like '%구미%' or region like '%구미%'; 로 테스트했다.

자질구레한 조건들 다 빼고 봤을때
select * from school left join (select school_id, count(*) from visit group by school_id) as sub on sub.school_id = school.school_id; 요정도 되겠다.

모든 학교의 목록과 해당 학교에 대한 방문횟수를 뽑아낸건데,

school_id	name	address	region	count(*)
1	금오고등학교	경북 구미시 송정동	구미	3
2	경북간호고등학교	경북 포항시 북구 죽장면	포항	1
3	대동고등학교	경북 포항시 북구	포항

*빈값은 null이다

원하는 결과값은 이렇게 나온다. 전부 적기에는 의미가 없을것같아서 일부 컬럼은 제외했다.

아무튼 결과적으로 내가 바꿔야 할 컬럼은 조건들을 다 넣은 위쪽의 긴 쿼리이다.

Native Query로 바꾸기

문제는 native query로 옮길 때였다.
내가 느꼈을 때 native query를 사용하면서 주의해야할 점들은 이거같다.

내가 원하는 값으로 바꿔 옮기려면 결과값을 받아올 DTO가 필요함

native query로 옮길 때는 alias가 중요함. 정말로!

1) DTO

우선 DTO부터 보자. DTO를 왜 이렇게 가져오는지 이해하려면 school테이블 속성에 관련해서도 올려두는 게 좋을 것 같다.

School Table

JPA hibernate를 사용했는데, 테이블을 생성할 때 자동으로 스네이크 표기법으로 변경해준다.

가져올 과정은 이렇다.
JPA에서 @Query(nativeQuery = true) 해서 가져올것이고,
가져올 때 리턴값을 custom dto로 할것인데, 구글링해보니 count쓸때는 dto를 interface로 작성하는것을 봤다.

내가 가져올 내용은 School + count(*)라서, 처음에는 DTO를 작성할 때
이렇게 가져오면 되겠지?! 하면서 School,count 를 속성으로 가져왔는데, 타입이 맞지 않아 안된다고 한다.
결국 가져올 모든 속성들을 하나하나 넣어줘야 하는 것 같다. 물론 본인이 가져올것만 쓰면 된다.
나는 select * 해서 다 가져오고싶었는데 안돼서 모든 속성을 다 적어줬다. class로 받는 방법도 있지 않을까...??
여기서 이상한 점을 느꼈을 수도 있다. 왜 카멜표기법이랑 스네이크표기법을 함께 썼는가🤔?

처음에는 getSchoolId(); 이렇게 카멜표기법만 썼었는데, 이상하게 region, name, address, fax만 가져와졌고 나머지는 null이라고 떴다. 속성명이 잘못된 것 같다는 합리적 의심으로 설마설마 하면서 맨 앞자리만 대문자로 하고 나머지는 스네이크 표기법을 사용했다. 놀랍게도 정말 이렇게 하니 되는 것이었다.

전체 코드를 봐야 이해하기 쉬울 것 같다. 블로그에 올리기보다는 깃허브를 올리는 게 좋을 것 같은데, github으로 들어가면 school 도메인쪽에 관련 코드가 있다.

2) alias

쿼리 자체가 많이 지저분해서 보기 어려울 것 같은데,
@Query(nativeQuery = true, value = "~~~") 에서
value 내의 sql 쿼리만 적어서 최대한 보기 편하게 적어보겠다!

처음에는 native query에 거의 그대로 옮겼었다.

SELECT * 
FROM school 
LEFT JOIN 
	(
    	SELECT school_id, count(*) 
        FROM visit 
        WHERE created_at BETWEEN ?1 AND ?2
        GROUP BY school_id
        ) AS sub 
      ON sub.school_id = school.school_id 
WHERE name LIKE %?3% OR region LIKE %?3%

*참고로 ?1, ?2, ?3은 각각 start, end, keyword라는 조건이 들어갈 자리이다. 이건 마지막에 실제코드쪽에 첨부해두었다.

바로 NonUniqueDiscoveredSqlAliasException 에러를 받았다.

Encountered a duplicated sql alias [school_id] during auto-discovery of a native-sql query ...즉 school_id라는 컬럼이 여러개 쓰여서, native query가 어떤 school_id를 매핑해야 할지 알지 못한다는 말인데,
mysql workbench에서 직접 했을때는 잘 돌아가던데 왜이러는거야! 라고 생각하며 alias를 열심히 해주었다.

SELECT s.* 	
FROM school s	// alias 
LEFT JOIN 
	(
    	SELECT v.school_id, count(*) 	
        FROM visit v			// alias 
        WHERE v.created_at BETWEEN ?1 AND ?2 
        GROUP BY v.school_id
        ) AS sub 
      ON sub.school_id = school.school_id 
WHERE s.name LIKE %?3% OR s.region LIKE %?3%

이번에는 원하는 값인 count가 나오지 않았다. mysql workbench에서와는 달리 count쪽도 추가를 해줘야 하나보다.

SELECT s.* , sub.count	// 서브쿼리에서 alias된 count
FROM school s	
LEFT JOIN 
	(
    	SELECT v.school_id, count(*) as count	// count도 alias 해줌. 이 alias명과 dto interface의 이름이 일치해야함 	
        FROM visit v			 
        WHERE v.created_at BETWEEN ?1 AND ?2 
        GROUP BY v.school_id
        ) AS sub 
      ON sub.school_id = school.school_id 
WHERE s.name LIKE %?3% OR s.region LIKE %?3%