@Query(value = "SELECT p FROM ParkBookingInfo p WHERE p.parkInfo.id = :parkId " +
"AND ((p.startTime >= :startTime and p.startTime < :endTime) " +
"OR (p.endTime > :startTime and p.endTime <= :endTime)" +
"OR (p.startTime <= :startTime and p.endTime >= :endTime))")
List<ParkBookingInfo> getSelectedTimeBookingList(@Param("parkId") Long parkId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
private List<LocalDateTime> getNotAllowedTimeList(Long id, BookingInfoRequestDto requestDto, ParkOperInfo parkOperInfo) {
List<LocalDateTime> notAllowedTimeList = new ArrayList<>();
ParkSpaceInfo spaceInfo = mgtService.getParkSpaceInfo(parkOperInfo);
long hours = Duration.between(requestDto.getStartDate(), requestDto.getEndDate()).toHours();
LocalDateTime start = requestDto.getStartDate();
// 선택한 시간만큼 반복
for (int i = 0; i < hours; i++) {
// 선택 시작 시간부터 한시간 단위로 예약 건수를 구한다.
LocalDateTime time = start.plusHours(i);
// 선택시간 사이 예약정보
List<ParkBookingInfo> bookingList = parkBookingInfoRepository.getSelectedTimeBookingList(id, time, time.plusHours(1));
// 예약정보 중 이미 출차한 차량 수는 제외한다.
int bookingCnt = bookingList.size() - parkMgtInfoRepository.countByParkBookingInfoInAndExitTimeNotNull(bookingList);
// 예약 건수가 예약 구역수보다 같거나 크면, 예약불가 시간 리스트에 추가한다.
if (bookingCnt >= spaceInfo.getBookingCarSpace()) {
notAllowedTimeList.add(time);
}
}
return notAllowedTimeList;
}
ParkBookingByHour
) 테이블 추가 설계CREATE TABLE `park_booking_by_hour` (
`id` bigint NOT NULL AUTO_INCREMENT,
`park_info_id` bigint DEFAULT NULL,
`date` date NOT NULL,
`time` int NOT NULL,
`available` int NOT NULL,
PRIMARY KEY (`id`),
KEY `FK_park_info_id` (`park_info_id`),
CONSTRAINT `FK_park_info_id` FOREIGN KEY (`park_info_id`) REFERENCES `park_info` (`id`)
)
ParkBookingByHour
테이블에서 예약시간에 해당하는 주차가능대수(available
) 컬럼 값을 확인하도록 QueryDSL 사용하여 로직을 구현했다. → 테이블 조회 속도는 빠르나, 예약정보 생성 후 ParkBookingByHour
에 데이터 생성 및 업데이트 추가 작업이 필요하다.private List<LocalDateTime> getNotAllowedTimeList(Long parkId, BookingInfoRequestDto requestDto) {
List<ParkBookingByHour> hourList = parkBookingByHourRepositoryCustom.findByParkInfoIdAndFromStartDateToEndDate(parkId, requestDto.getStartDate(), requestDto.getEndDate());
return hourList.stream()
.filter(hour -> hour.getAvailable() <= 0)
.map(hour -> LocalDateTime.of(hour.getDate(), LocalTime.of(hour.getTime(), 0, 0)))
.collect(Collectors.toList());
}
*findByParkInfoIdAndFromsStartDateToEndDate
QueryDSL 쿼리 코드*@Slf4j
@Repository
@RequiredArgsConstructor
public class ParkBookingByHourRepositoryImpl implements ParkBookingByHourRepositoryCustom {
private final JPAQueryFactory jpaQueryFactory;
private final QParkBookingByHour qParkBookingByHour = QParkBookingByHour.parkBookingByHour;
private final QParkInfo qParkInfo = QParkInfo.parkInfo;
@Override
public List<ParkBookingByHour> findByParkInfoIdAndFromStartDateToEndDate(Long parkInfoId, LocalDateTime startDate, LocalDateTime endDate) {
LocalDate startDay = startDate.toLocalDate();
LocalDate endDay = endDate.toLocalDate();
long days = Period.between(startDay, endDay).getDays();
int startTime = startDate.getHour();
int endTime = endDate.getHour();
if (endDate.toLocalTime().getMinute() == 0 && endDate.toLocalTime().getSecond() == 0) {
endTime = endDate.minusHours(1).getHour();
}
List<ParkBookingByHour> result = new ArrayList<>();
if (days == 0) {
result = jpaQueryFactory.selectFrom(qParkBookingByHour)
.innerJoin(qParkBookingByHour.parkInfo, qParkInfo)
.fetchJoin()
.where(qParkBookingByHour.parkInfo.id.eq(parkInfoId)
.and(qParkBookingByHour.date.eq(startDay))
.and(qParkBookingByHour.time.between(startTime, endTime)))
.fetch();
} else {
List<ParkBookingByHour> resultFirstDay = jpaQueryFactory.selectFrom(qParkBookingByHour)
.innerJoin(qParkBookingByHour.parkInfo, qParkInfo)
.fetchJoin()
.where(qParkBookingByHour.parkInfo.id.eq(parkInfoId)
.and(qParkBookingByHour.date.eq(startDay))
.and(qParkBookingByHour.time.between(startTime, 23)))
.fetch();
List<ParkBookingByHour> resultLastDay = jpaQueryFactory.selectFrom(qParkBookingByHour)
.innerJoin(qParkBookingByHour.parkInfo, qParkInfo)
.fetchJoin()
.where(qParkBookingByHour.parkInfo.id.eq(parkInfoId)
.and(qParkBookingByHour.date.eq(endDay))
.and(qParkBookingByHour.time.between(0, endTime)))
.fetch();
result.addAll(resultFirstDay);
result.addAll(resultLastDay);
if (days > 1) {
List<ParkBookingByHour> resultMiddleDays = jpaQueryFactory.selectFrom(qParkBookingByHour)
.innerJoin(qParkBookingByHour.parkInfo, qParkInfo)
.fetchJoin()
.where(qParkBookingByHour.parkInfo.id.eq(parkInfoId)
.and(qParkBookingByHour.date.between(startDay.plusDays(1), endDay.minusDays(1))))
.fetch();
result.addAll(resultMiddleDays);
}
}
return result;
}
}
아래 쿼리문을 예약시간만큼 반복하여 실행
Hibernate:
select
parkbookin0_.id as id1_2_,
parkbookin0_.car_num as car_num2_2_,
parkbookin0_.end_time as end_time3_2_,
parkbookin0_.park_info_id as park_inf5_2_,
parkbookin0_.start_time as start_ti4_2_,
parkbookin0_.users_id as users_id6_2_
from
park_booking_info parkbookin0_
where
parkbookin0_.park_info_id=?
and (
parkbookin0_.start_time>=?
and parkbookin0_.start_time<?
or parkbookin0_.end_time>?
and parkbookin0_.end_time<=?
or parkbookin0_.start_time<=?
and parkbookin0_.end_time>=?
)
Hibernate:
select
count(parkmgtinf0_.id) as col_0_0_
from
park_mgt_info parkmgtinf0_
where
(
parkmgtinf0_.park_booking_info_id in (
?
)
)
and (
parkmgtinf0_.exit_time is not null
)
ParkBookingByHour
에 데이터를 생성 및 업데이트하는 시간이 추가로 소요됨예약 시간이 길어도 아래 쿼리 3개만 실행됨
Hibernate:
select
parkbookin0_.id as id1_2_0_,
parkinfo1_.id as id1_4_1_,
parkbookin0_.available as availabl2_2_0_,
parkbookin0_.date as date3_2_0_,
parkbookin0_.park_info_id as park_inf5_2_0_,
parkbookin0_.time as time4_2_0_,
parkinfo1_.address1 as address2_4_1_,
parkinfo1_.address2 as address3_4_1_,
parkinfo1_.la as la4_4_1_,
parkinfo1_.lo as lo5_4_1_,
parkinfo1_.name as name6_4_1_
from
park_booking_by_hour parkbookin0_
inner join
park_info parkinfo1_
on parkbookin0_.park_info_id=parkinfo1_.id
where
parkbookin0_.park_info_id=?
and parkbookin0_.date=?
and (
parkbookin0_.time between ? and ?
)
Hibernate:
select
parkbookin0_.id as id1_2_0_,
parkinfo1_.id as id1_4_1_,
parkbookin0_.available as availabl2_2_0_,
parkbookin0_.date as date3_2_0_,
parkbookin0_.park_info_id as park_inf5_2_0_,
parkbookin0_.time as time4_2_0_,
parkinfo1_.address1 as address2_4_1_,
parkinfo1_.address2 as address3_4_1_,
parkinfo1_.la as la4_4_1_,
parkinfo1_.lo as lo5_4_1_,
parkinfo1_.name as name6_4_1_
from
park_booking_by_hour parkbookin0_
inner join
park_info parkinfo1_
on parkbookin0_.park_info_id=parkinfo1_.id
where
parkbookin0_.park_info_id=?
and parkbookin0_.date=?
and (
parkbookin0_.time between ? and ?
)
Hibernate:
select
parkbookin0_.id as id1_2_0_,
parkinfo1_.id as id1_4_1_,
parkbookin0_.available as availabl2_2_0_,
parkbookin0_.date as date3_2_0_,
parkbookin0_.park_info_id as park_inf5_2_0_,
parkbookin0_.time as time4_2_0_,
parkinfo1_.address1 as address2_4_1_,
parkinfo1_.address2 as address3_4_1_,
parkinfo1_.la as la4_4_1_,
parkinfo1_.lo as lo5_4_1_,
parkinfo1_.name as name6_4_1_
from
park_booking_by_hour parkbookin0_
inner join
park_info parkinfo1_
on parkbookin0_.park_info_id=parkinfo1_.id
where
parkbookin0_.park_info_id=?
and (
parkbookin0_.date between ? and ?
)