예약 가능한 날짜 SQL => if 와 all 사용

예약기능은 숙박, 항공, 식사, 렌탈 등 다양한 범주에서 사용된다. 

 

진행 중인 프로젝트 ( 렌탈 서비스 ) 에서도 예약기능이 필요하다 생각되었고,

검색 기능에서 

 

(a). 키워드 

(b). 동네 

(c). 렌탈 희망 날짜 

 

세 가지 조건을 만족하는 조회결과를 가져오는 SQL문을 구성하고자 했다. 

 

1.  하나의 SQL 문으로 시도 ( 실패 ) 

 

select * from product left outer join reservation on product.id = reservation.boardid 
where (title like concat('%', #{smartTitle}, '%') 
and boardRegion like concat('%', #{smartRegion}, '%')
and (reservation.startRental is null
or reservation.reservCheck = 0
))
or (
title like concat('%', #{smartTitle}, '%') 
and boardRegion like concat('%', #{smartRegion}, '%')
and reservation.reservCheck = 1
and not (reservation.startRental between #{smartStartDate} and #{smartEndDate}
and reservation.endRental between #{smartStartDate} and #{smartEndDate} 
 )) 
group by product.id order by product.createdAt desc;

 

 

DB에 접근하는 빈도를 적게 하기 위해 

한번의 SQL 문으로 해당 조건들을 모두 만족하는 조회가 가능하게 만든다면 더할나위 없이 좋을 것이다. 

 

먼저, 하나의 상품에 대해 여러개의 예약이 달리는 구조이고 

검색할 때는 예약이 null 인 상품들도 조회해야 하기 때문에 

left outer join 을 사용해 주었다. 

( inner join 을 사용하면 예약이 null 인 상품들은 조회되지 않는다. ) 

 

키워드와 동네에 대한 조건은 기본 통과 조건으로 해두고 

 

검색할 때 입력하는 희망 렌탈날짜 (시작 - 종료) 기간이 

상품에 걸린 수락된 예약(reservCheck=1) 기간에 걸리지 않는 결과를 조회해야 했다. 

 

먼저 (1) 예약의 신청조차 아예 없는 null 인 경우 의 상품들과 

(2) 예약의 신청은 있으나 수락된 예약이 없는 경우 (reservCheck=0) 의 상품들을 조회하고, 

 

(3) 수락된 예약이 있는 경우 날짜가 겹치지 않는 상품을 not 과 between 을 사용해 조회했다. 

 

 

=> 이렇게하면 

상품에 걸린 예약이 '하나' 일 경우에는 원하는 조회값을 가져온다. 

 

하지만 (a)하나의 상품에 수락된 예약과 수락되지 않은 예약이 있거나 

(b)하나의 상품에 수락된 예약이 여러 개 있는 경우, 

 

위의 SQL 문은  

여러개의 예약중에 "하나라도" 위의 조건을 만족하면 => 해당 물품을 조회해 버린다. 

 

예를들어, 

하나의 상품에 수락된 예약이 7~7일 과 9~9일 두 개가 있다. 

 

검색에서 7~8일 까지 렌탈희망날짜를 입력하고 검색하면 

( 7~7일 예약만 있었다면 겹치는 기간이기 때문에 제외가 되지만 ) 

9~9일 예약은 7~8일 검색과 겹치지 않기때문에 => 조건을 만족하는 것으로 보고 해당 물품을 조회해버린다. 

 

즉, 위의 SQL 문은 "모든" 예약들에 대한 조건이 아니라, "하나라도" 예약들에 대한 조건이다.  ( All / Any ) 

 

 

 

2. IF, ALL 로 조회 

 

select product.id from product where product.title like concat('%', #{title}, '%') 
and product.boardRegion like concat('%', #{region}, '%') 

 

select if(
((#{startDate} < all(select reservation.startrental from reservation inner join product on product.id = reservation.boardid where reservation.reservCheck=1 and product.id = #{productId} ))
and
(#{endDate} < all(select reservation.startrental from reservation inner join product on product.id = reservation.boardid where reservation.reservCheck=1 and product.id = #{productId} )))
or
((#{startDate} > all(select reservation.endrental from reservation inner join product on product.id = reservation.boardid where reservation.reservCheck=1 and product.id = #{productId} ))
and
(#{endDate} > all(select reservation.endrental from reservation inner join product on product.id = reservation.boardid where reservation.reservCheck=1 and product.id = #{productId} )))
, #{productId}, 0
);

 

- select if ( 테스트, 1, 0 );   테스트가 true 이면 1 false 이면 0 을 select  

- &lt;  과  &gt; 는  mybatis 를 사용할 때 부등호 < , > 를 대신해서 사용한다. 

[ all ]  

- 시작날짜가 <  해당 상품의 "모든" 예약의 시작날짜보다 작고, 

 시작날짜가 <  해당 상품의 "모든" 예약의 종료날짜보다 작고, 

. . . 

 

 

[ 서버단 ]

(1) 먼저, 단순 조건인 키워드, 지역에 대한 조회를 해서 해당하는 상품의 id 만을 가져온다.

 

// 키워드,지역으로 검색한 상품리스트
List<Integer> titleRegionResult = productDAO.searchByTitle_Region(smartSearchDTO.getSmartTitle(), smartSearchDTO.getSmartRegion());

 

ArrayList<Integer> selectedList = new ArrayList<>();

(2) 키워드,지역 조건을 통과한 상품별로,  해당 상품의 "모든" 예약이 ( all ) 기간에 걸리지 않는 것만 조회한다. 

// 날짜로 검색 : 해당 날짜 조건에 부합하지 않는다 => 예약이 null 값인 것과, 예약수락이 없는 리스트까지 포함됨 
for(int i = 0; i<titleRegionResult.size(); i++) {

if(smartSearchDTO.getSmartStartDate() != "" && smartSearchDTO.getSmartEndDate() != "") {
Integer selected =  productDAO.searchByRentalDate(smartSearchDTO.getSmartStartDate(), smartSearchDTO.getSmartEndDate(), titleRegionResult.get(i));

if(selected>0) {
selectedList.add(selected);  
}

// 렌탈 희망날짜를 적지 않는 경우 => 키워드와 지역의 검색결과만을 담는다. 
}else if(smartSearchDTO.getSmartStartDate() == "" && smartSearchDTO.getSmartEndDate() == "") {
selectedList.add(titleRegion.get(i));
}
} //for 

List<ProductDTO> list = new ArrayList<>();
  
for(int i = selectedList.size()-1; i>=0; i--) {
ProductDTO searchedOne = productDAO.oneProduct(selectedList.get(i));
list.add(searchedOne);
}

 

 => 상품 별로, 모든 예약이 조건을 만족해야 조회가 되기 때문에 

처음에 원했던 조회결과를 얻을 수 있었다.

( 하지만 DB 조회수가 많이 늘어났다.  동적 쿼리를 적용하는 방법을 더 생각해 봐야겠다. )

 

SQL문에 따라 성능이 크게 상향될 수 있다는 걸 다시 한번 생각하게 되었고,

JPA 나 SQL 에 대한 중요성을 새삼 느끼게 되었다. 

계속 더 친해지자!