현재 진행 중인 스프링 팀 프로젝트에서 나는 숙소 검색 및 조회 부분을 맡고 있고,
어제 오늘 다중조건 검색 기능을 구현했다.
검색 결과를 조회하는 화면을 요청하는 두 가지 방식이 있는데
구현 내용
(1) 홈 화면에서 각 숙소유형(모텔,호텔,게스트하우스,...)을 클릭했을 때는
다중 조건으로 공용시설, 객실시설, 기타태그를 선택할 수 있고
(2) 홈 화면에서 키워드를 검색했을 때는
다중 조건으로 숙소유형을 선택하게 하도록 했다.
두 방식은 요청URI는 같지만 요청파라미터에 따라 구분되고 JSTL을 통해 다르게 출력된다. (type= ~ / keyword=~)
지역, 날짜(기간), 가격 조건은 두 가지 방식 모두 공통적으로 선택할 수 있다.
그리고 평점, 거리, 가격 순 정렬을 함께 제공한다.
데이터 조회 방식의 일원화
상세조건에서 체크해야 할 옵션, 지역 정보 같은 데이터는
(1), (2)번 어떤 방식으로 요청했던지간에, 최초의 화면 출력 시에 필요한 데이터이다.
또 숙소 검색 정보 자체는 화면 출력 이후에도 검색 조건을 변경하면서, 계속 새 데이터를 받아와야 하는데
굳이 화면 재요청을 하고 싶지 않기 때문에 어차피 ajax 요청을 구현할 생각이었다.
따라서
컨트롤러에서 해당 페이지를 출력하는 jsp를 요청할 때에는 조건 관련된 데이터만 전달하고,
최종적으로 '숙소 정보'를 조회하는 일은 jsp에서 ajax방식으로 요청하는 방식으로 구현했다.
다중조건의 내용에 따라 SQL문의 내용이 달라지는 동적 SQL문을 mybatis를 통해 매핑해서,
해당 SQL문의 조회 결과로 받은 리스트 객체를, 클라이언트에게 json 응답 데이터로 주는 것이다.
서브쿼리의 서브쿼리 ...
다른 부분은 수업 때 계속 해보았던 내용이라 어렵지 않았다.
하지만 SQL문을 짜는 그 자체가 어려웠다.
- 숙소 테이블의 행이 숙소 관련 조건을 만족할 것
- 그 숙소를 참조하는 객실 테이블의 행이 객실 관련 조건을 만족할 것
- 그 숙소를 참조하는 객실이 특정 기간에 예약 가능한 상태일 것
현재 프로젝트에서 설계한 객실 테이블은 실제 실 하나 하나 고유한 정보가 아니라 객실 유형에 대한 것으로, 해당 객실의 총 개수를 컬럼으로 가지고 있다. 즉, 예약 가능 여부는(해당 객실의 총 개수 - 특정 기간에 해당 객실번호로 예약된 객실 수) > 0인지 확인해야 한다.
=> 처음에는 실제 객실별 정보 테이블을 설계했으나 조인을 해야 할 일이 많아질 것 같아 그 대신 위와 같이 설계했다.
실제 객실별 정보 테이블이 있었다면 조인을 하더라도 서브쿼리를 쓸 필요가 적어서 쿼리가 더 간단해졌을까? 생각해봐야겠다. - 1~3번을 만족하는 숙소 별로, 그 숙소가 가지는 2~3을 만족하는 객실 중 최저금액을 조회할 것 (가격 정렬 기준으로 삼기 위한 금액)
- 1~3번을 만족하는 숙소의 좌표와 현재 좌표 간의 거리를 구할 것 (거리 정렬)
클라이언트에서 데이터 정렬을 하지 않고, 서버에서 정렬까지 해서 주므로 DB에서 거리 정렬까지 해결해야 하는 상황이라고 봤다.
그래서 구글링을 통해 알게 된 두 좌표 간의 거리 계산 함수를 오라클에서 CREATE해둔 뒤 사용했다.
아래 두 링크를 참조함
https://aljjabaegi.tistory.com/336
https://gist.github.com/rockoil/cbf5eba9491daf66c0c3f4d83ccd83c1
여러 개의 mapper 메소드로 나눈 뒤, 서비스에서 리스트 객체에 대해 조건을 다시 검사하고 조작하는 것도 생각해보았지만
오히려 비효율적이라고 생각이 들었다.
아직 데이터 수집 전이라, 테스트를 위해 가짜데이터를 몇개 DB에 넣고, 부분 부분 select문을 실행시켜보면서
쿼리를 작성했다.
그렇게 오라클에서 내가 원하는 대로 결과를 조회시키는 SQL문을 먼저 작성한 뒤,
mapper 파일에서 #{}으로 값들을 바인딩되도록 하고 <where>태그를 활용해서 동적 SQL문으로 만들었다.
mybatis 동적 SQL
몇 번씩 수정이 있었지만, 최종적으로 논리는 이렇다.
처음에는 숙소에서 확인할 조건과 객실에서 확인할 조건을 동등한 위계로 놓고 생각해서 어려움이 있었다.
이를 테면 최종 조회 결과에서는 distinct로 숙소아이디를 조회해야 하는데, 그러고 나니 숙소가 가지는 - 검색조건을 만족하는 객실의 - 최저금액을 정확히 계산할 수 없었다.
하지만 두 가지를 분리하고, 몇 번의 단계로 나눠보니 서브쿼리는 여러 번 중첩되게 되었지만
논리 자체는 명확해졌다.
nr: room별로 특정 기간에 예약 불가능한 객실 수를 조회
r: 객실 관련 모든 조건을 만족하는 객실이 참조하는 숙소 아이디를 조회. 이 때 조건 중 특정 기간 예약 가능 여부를 확인하는 조건도 있다. 이를 nr과의 조인을 통해 확인할 수 있다. 그리고 group by 숙소아이디를 하여 숙소 별 최저금액을 계산
=> 최종적으로 a와 r을 조인. r에서 얻은 숙소아이디에 대한 필요한 정보를 조회
<!-- List<Accommodation> getAccommodationsByCriteria(AccoCriteria criteria); -->
<!--
1. 서브쿼리에서 조건을 만족하는 객실을 가지는 숙소아이디와, 그 숙소의 조건을 만족하는 객실 중 가장 저렴한 금액을 조회한다.
2. 서브쿼리에서 획득한 조회결과(r)와 숙소 테이블을 조인하여 객실, 숙소에 대한 모든 조건을 만족하는 숙소정보를 조회한다.-->
<select id="getAccommodationsByCriteria" parameterType="kr.co.nc.criteria.AccoCriteria" resultMap="AccoResultMap">
select distinct a.acco_id, a.acco_name, a.acco_district, a.acco_address, a.acco_latitude, a.acco_longitude,
a.acco_introduce_comment, a.acco_thumbnail_image, a.acco_review_rate, a.acco_review_count, a.city_id,
DISTANCE_WGS84(#{currentLat}, #{currentLong}, a.acco_latitude, a.acco_longitude) as distance,
r.minPrice
from accommodations a,
(select fr.acco_id, min(fr.room_day_price) minPrice
from acco_rooms fr, (select room_no, count(*) as cnt
from reservation
where reservation_check_in = #{startDate}
or (reservation_check_in < #{startDate} and reservation_check_out > #{startDate})
or (reservation_check_in > #{startDate} and reservation_check_in < #{endDate})
group by room_no) nr
<where>
fr.room_no = nr.room_no (+)
and (fr.room_numbers - NVL(nr.cnt,0)) > 0
<if test="capacity >= 0">
and fr.room_capacity >= #{capacity}
</if>
<if test="minPrice >= 0 and maxPrice > 0">
and fr.room_day_price between #{minPrice} and #{maxPrice}
</if>
<if test="roomFacilities != null">
and fr.room_no in (select room_no from acco_room_facilities
where ro_facility_id in
<foreach item="rofa" collection="roomFacilities" open="(" close=")" separator=",">
#{rofa}
</foreach>
)
</if>
</where>
group by fr.acco_id) r
<where>
a.acco_deleted = 'N'
and a.acco_id = r.acco_id
<if test="keyword != null">
and (a.acco_name like '%' || #{keyword} || '%'
or a.acco_district like '%' || #{keyword} || '%'
or a.acco_address like '%' || #{keyword} || '%')
</if>
<if test='city != null and city != ""'>
and a.city_id = #{city}
</if>
<if test="types != null">
and a.acco_id in (select acco_id from acco_types
where acco_type_id in
<foreach item="type" collection="types" open="(" close=")" separator=",">
#{type}
</foreach>
)
</if>
<if test="commonFacilities != null">
and a.acco_id in (select acco_id from acco_common_facilities
where co_facility_id in
<foreach item="cofa" collection="commonFacilities" open="(" close=")" separator=",">
#{cofa}
</foreach>
)
</if>
</where>
<choose>
<when test="sort == 'rate'">
order by a.acco_review_rate desc
</when>
<when test="sort == 'dist'">
order by distance asc
</when>
<when test="sort == 'highprice'">
order by minPrice desc
</when>
<when test="sort == 'lowprice'">
order by minPrice asc
</when>
</choose>
</select>특정 기간으로 예약 가능 여부 확인하기
이 중 가장 어려움을 겪었던 건 예약 가능한 객실을 조회하는 부분이었다.
서브 쿼리 중 아래 내용은 객실 종류별로 해당 기간에 예약 불가한 수를 조회하는 내용이다.
(select room_no, count(*) as cnt
from reservation
where reservation_check_in = #{startDate}
or (reservation_check_in < #{startDate} and reservation_check_out > #{startDate})
or (reservation_check_in > #{startDate} and reservation_check_in < #{endDate})
group by room_no) nr체크인, 체크아웃 날짜와 조건날짜 시작일, 종료일이 어떤 범위에 있으면 예약을 할 수 없는지 알아야 한다.
조건날짜 시작일이 체크인 전인 경우, 후인 경우로 나누어보니 위와 같이 정리됐다.
그런데 시작일 = 체크인날짜 이면 무조건 예약 불가 상태인 것이므로
- 시작일 = 체크인 날짜
- 체크인 < 시작일 < 체크아웃
- 시작일 < 체크인 && 체크인 < 종료일
최종적으로는 이렇게 정리할 수 있었다.
이 쿼리를 객실 테이블과 조회하여 (객실 총 개수 - cnt)를 하면 그 기간에 해당 객실유형을 예약할 수 있는지 알 수 있다.
지금은 아주 적은 데이터로 해보고 있지만, 실무에서는 쿼리를 무겁게 짜서 부하가 걸리는 일이 많다고 들었는데..
그래서 이렇게 서브쿼리가 엮여있고, 조인도 하고, 함수도 쓰는.. 이 쿼리가 적절한지는 잘 모르겠다. ㅜ
하지만 지금 내가 아는 범위 내에서, 또 나의 개발 환경에서는 충분하게 작동하고 있는 방법이었기 때문에 기록해둔다.
나중에 미래의 내가 보면 야~~ 이거 이렇게 하는 게 아닌데 ㅋ 하고 있겠지..
