리팩토링: DB접근 줄이기

기존 로직 :

@Service
@RequiredArgsConstructor
public class ProductService {

    private final ProductRepository productRepository;
    private final ProductInfoPerNightRepository productInfoPerNightRepository;
    private final AccommodationRepository accommodationRepository;

    @Transactional(readOnly = true)
    public AccommodationDetailListResponse getAccommodationDetail(
        Long accommodationId, LocalDate checkInDate,
        LocalDate checkOutDate, int personNumber) {
        validateInputs(checkInDate, checkOutDate, personNumber);

        Accommodation accommodationEntity = accommodationRepository.findById(accommodationId)
            .orElseThrow(() -> new AccommodationException(ErrorType.NOT_FOUND));

        List<Product> productEntityList = productRepository.findAllByAccommodationId(
            accommodationId);

       List<Product> validProductList = new ArrayList<>();
        for (Product product : productEntityList) {
            boolean allDatesExist = true;

            // select * from productInfoPerNight p where p.productId = id and between checkInDate and date and checkOutDate
            // 객실 1개당 6월~8월까지 데이터, -> 얘네들을 하나씩 비교검사를 하고 있어요
            for (LocalDate date = checkInDate; date.isBefore(checkOutDate);
                date = date.plusDays(1)) {
                if (!productInfoPerNightRepository
                    .existsByProductIdAndDate(product.getId(),
                        date)) {
                    allDatesExist = false;
                    break;
                }
            }

            if (allDatesExist && personNumber <= product.getMaximumNumber()) {
                validProductList.add(product);
            }
        }

        if (validProductList.isEmpty()) {
            throw new ProductException(ErrorType.NOT_FOUND);
        }

        List<ProductResponse> productResponses = validProductList.stream()
            .map(product -> {
                ProductImageResponse productImageResponse = ProductImageResponse.from(
                    product.getProductImage());
                int minCount = productInfoPerNightRepository
                    .findMinCountByProductIdAndDateRange(
                        product.getId(), checkInDate, checkOutDate);
                return ProductResponse.from(product, minCount, productImageResponse);
            })
            .collect(Collectors.toList());

        AccommodationImageResponse accommodationImageResponse = AccommodationImageResponse.from(
            accommodationEntity.getImages());
        AccommodationOptionResponse accommodationOptionResponse = AccommodationOptionResponse.from(
            accommodationEntity.getOptions());

        return AccommodationDetailListResponse.from(accommodationEntity, checkInDate,
            checkOutDate, accommodationImageResponse, accommodationOptionResponse,
            productResponses);
        
    }

@Query("SELECT CASE WHEN COUNT(p) > 0 THEN TRUE ELSE FALSE END " +
    "FROM ProductInfoPerNight p " +
    "WHERE p.product.id = :productId " +
    "AND p.date = :date " +
    "AND p.count > 0")
boolean existsByProductIdAndDate(
    @Param("productId") Long productId,
    @Param("date") LocalDate date
);

 

 

변경 로직 :

@Transactional(readOnly = true)
public AccommodationDetailListResponse getAccommodationDetail(
    Long accommodationId, LocalDate checkInDate,
    LocalDate checkOutDate, int personNumber) {
    validateInputs(checkInDate, checkOutDate, personNumber);

    Accommodation accommodationEntity = accommodationRepository.findById(accommodationId)
        .orElseThrow(() -> new AccommodationException(ErrorType.NOT_FOUND));

    List<Product> productEntityList = productRepository.findAllByAccommodationId(
        accommodationId);

    // 날짜 범위 내의 모든 ProductInfoPerNight 데이터를 한 번에 가져와 메모리에서 처리한다.
    // 전 버전: 각 제품에 대해 날짜별로 개별 쿼리를 실행하여 데이터베이스에서 확인
    //뭐지
    List<ProductInfoPerNight> productInfoPerNightList = productInfoPerNightRepository
        .findByAccommodationIdAndDateRange(accommodationId, checkInDate, checkOutDate);

    //유효 정보 필터링
    //요청 인원수 personNumber가 product.getMaximumNumber 보다 크면 false
    Map<Long, List<ProductInfoPerNight>> productInfoPerNightsMap = productInfoPerNightList.stream()
        .collect(Collectors.groupingBy(p -> p.getProduct().getId()));

    List<Product> validProductList = productEntityList.stream()
        .filter(product -> {
            List<ProductInfoPerNight> infoPerNights = productInfoPerNightsMap.get(product.getId());
            if (infoPerNights == null || infoPerNights.size() < ChronoUnit.DAYS.between(checkInDate, checkOutDate)) {
                return false;
            }
            return personNumber <= product.getMaximumNumber();
        })
        .collect(Collectors.toList());

    if (validProductList.isEmpty()) {
        throw new ProductException(ErrorType.NOT_FOUND);
    }

    //유효 정보 응답 생성
    List<ProductResponse> productResponses = validProductList.stream()
        .map(product -> {
            ProductImageResponse productImageResponse = ProductImageResponse.from(product.getProductImage());
            int minCount = productInfoPerNightsMap.get(product.getId()).stream()
                .mapToInt(ProductInfoPerNight::getCount)
                .min()
                .orElseThrow(() -> new ProductException(ErrorType.NOT_FOUND));
            return ProductResponse.from(product, minCount, productImageResponse);
        })
        .collect(Collectors.toList());

    AccommodationImageResponse accommodationImageResponse = AccommodationImageResponse
        .from(accommodationEntity.getImages());

    AccommodationOptionResponse accommodationOptionResponse = AccommodationOptionResponse
        .from(accommodationEntity.getOptions());

    return AccommodationDetailListResponse.from(accommodationEntity, checkInDate,
        checkOutDate, accommodationImageResponse,
        accommodationOptionResponse, productResponses);

@Query("SELECT p FROM ProductInfoPerNight p " +
    "WHERE p.product.accommodation.id = :accommodationId " +
    "AND p.date BETWEEN :startDate AND :endDate " +
    "AND p.count > 0")
List<ProductInfoPerNight> findByAccommodationIdAndDateRange(
    @Param("accommodationId") Long accommodationId,
    @Param("startDate") LocalDate checkInDate,
    @Param("endDate") LocalDate checkOutDate);

Execution time 실행 시간이 줄어든 것을 볼 수 있다.