1. JPA 이용해서 바꾸기

package com.group.libraryapp.domain.fruit;

import javax.persistence.*;
import java.time.LocalDate;

@Entity
@Table(name = "fruits")
public class Fruit {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(name = "warehousing_date", nullable = false)
    private LocalDate warehousingDate;

    @Column(nullable = false)
    private double price;

    @Column(nullable = false)
    private String status;

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public LocalDate getWarehousingDate() {
        return warehousingDate;
    }

    public double getPrice() {
        return price;
    }

    public String getStatus() {
        return status;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setWarehousingDate(LocalDate warehousingDate) {
        this.warehousingDate = warehousingDate;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public void setStatus(String status) {
        this.status = status;
    }
}

이렇게 fruit 객체를 만들었다.

ckage com.group.libraryapp.domain.fruit;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface FruitRepository extends JpaRepository<Fruit, Long> {
    @Query("SELECT SUM(f.price) FROM Fruit f WHERE f.name = :name AND f.status = :status")
    Double getFruitStat(@Param("name") String name, @Param("status") String status);
    Fruit findByName(String name);
}

그리고 JPA 레포지토리를 확장하는 FruitRepository를 새로 만들어줬다.

package com.group.libraryapp.service.fruit;

import com.group.libraryapp.domain.fruit.Fruit;
import com.group.libraryapp.domain.fruit.FruitRepository;
import com.group.libraryapp.dto.HW.FruitRequest;
import com.group.libraryapp.dto.HW.FruitSoldRequest;
import com.group.libraryapp.dto.HW.FruitStatResponse;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class FruitService {

    private final FruitRepository fruitRepository;

    public FruitService(FruitRepository fruitRepository) {
        this.fruitRepository = fruitRepository;
    }

    @Transactional
    public FruitRequest addFruit(FruitRequest fruitRequest) {
        Fruit fruit = new Fruit();
        fruit.setName(fruitRequest.getName());
        fruit.setWarehousingDate(fruitRequest.getWarehousingDate());
        fruit.setPrice(fruitRequest.getPrice());
        fruit.setStatus("NOT_SOLD");

        fruitRepository.save(fruit);

        return fruitRequest;
    }

    @Transactional
    public void markFruitAsSold(FruitSoldRequest request) {
        Fruit fruit = fruitRepository.findById(request.getId()).orElseThrow(IllegalArgumentException::new);
        fruit.setStatus("SOLD");
        fruitRepository.save(fruit);
    }

    @Transactional(readOnly = true)
    public FruitStatResponse getFruitStat(String name) {
        Double soldAmount = fruitRepository.getFruitStat(name, "SOLD");
        Double notSoldAmount = fruitRepository.getFruitStat(name, "NOT_SOLD");
        long sold = soldAmount != null ? soldAmount.longValue() : 0;
        long notSold = notSoldAmount != null ? notSoldAmount.longValue() : 0;
        return new FruitStatResponse(sold, notSold);
    }
}

그리고 이렇게 서비스를 만들어 기존의 코드를 SQL 문이 아닌 JPA를 이용하게 했다.

2. 과일 개수 세기

FruitRepository에서 과일의 개수를 세는 메소드를 추가해야 한다. 그런 다음에는 FruitService에서 해당 메소드를 호출하여 원하는 기능을 구현할 수 있다.

package com.group.libraryapp.domain.fruit;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface FruitRepository extends JpaRepository<Fruit, Long> {
    Fruit findByName(String name);

    @Query("SELECT COUNT(f) FROM Fruit f WHERE f.name = :name")
    long countByName(@Param("name") String name);
}

package com.group.libraryapp.service.fruit;

import com.group.libraryapp.domain.fruit.Fruit;
import com.group.libraryapp.dto.HW.FruitStatResponse;
import com.group.libraryapp.repository.fruit.FruitRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class FruitService {

    private final FruitRepository fruitRepository;

    @Autowired
    public FruitService(FruitRepository fruitRepository) {
        this.fruitRepository = fruitRepository;
    }

    @Transactional(readOnly = true)
    public long countFruitsByName(String name) {
        return fruitRepository.countByName(name);
    }
}

Copy code
@GetMapping("/count")
public ResponseEntity<Map<String, Long>> countFruitsByName(@RequestParam("name") String name) {
    long count = fruitService.countFruitsByName(name);
    Map<String, Long> responseBody = new HashMap<>();
    responseBody.put("count", count);
    return ResponseEntity.ok(responseBody);
}

이렇게 만들고 PostMan을 실행해봤다.

이렇게 잘 된다!

3. 판매되지 않은 특정 금액 이상 혹은 특정 금액 이하의 과일 목록

package com.group.libraryapp.domain.fruit;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface FruitRepository extends JpaRepository<Fruit, Long> {
    @Query("SELECT SUM(f.price) FROM Fruit f WHERE f.name = :name AND f.status = :status")
    Double getFruitStat(@Param("name") String name, @Param("status") String status);

    @Query("SELECT COUNT(f) FROM Fruit f WHERE f.name = :name")
    long countByName(@Param("name") String name);

    List<Fruit> findByPriceGreaterThanEqualAndStatus(double price, String status);

    List<Fruit> findByPriceLessThanEqualAndStatus(double price, String status);

    Fruit findByName(String name);
}

먼저 FruitRepository를 다음처럼 GTE, LTE를 만들어줬다.

ckage com.group.libraryapp.service.fruit;

import com.group.libraryapp.domain.fruit.Fruit;
import com.group.libraryapp.domain.fruit.FruitRepository;
import com.group.libraryapp.dto.HW.FruitListResponse;
import com.group.libraryapp.dto.HW.FruitRequest;
import com.group.libraryapp.dto.HW.FruitSoldRequest;
import com.group.libraryapp.dto.HW.FruitStatResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.stream.Collectors;

@Service
public class FruitService {

    private final FruitRepository fruitRepository;

    @Autowired
    public FruitService(FruitRepository fruitRepository) {
        this.fruitRepository = fruitRepository;
    }

    @Transactional
    public FruitRequest addFruit(FruitRequest fruitRequest) {
        Fruit fruit = new Fruit();
        fruit.setName(fruitRequest.getName());
        fruit.setWarehousingDate(fruitRequest.getWarehousingDate());
        fruit.setPrice(fruitRequest.getPrice());
        fruit.setStatus("NOT_SOLD");

        fruitRepository.save(fruit);

        return fruitRequest;
    }

    @Transactional
    public void markFruitAsSold(FruitSoldRequest request) {
        Fruit fruit = fruitRepository.findById(request.getId()).orElseThrow(IllegalArgumentException::new);
        fruit.setStatus("SOLD");
        fruitRepository.save(fruit);
    }

    @Transactional(readOnly = true)
    public FruitStatResponse getFruitStat(String name) {
        Double soldAmount = fruitRepository.getFruitStat(name, "SOLD");
        Double notSoldAmount = fruitRepository.getFruitStat(name, "NOT_SOLD");
        long sold = soldAmount != null ? soldAmount.longValue() : 0;
        long notSold = notSoldAmount != null ? notSoldAmount.longValue() : 0;

        return new FruitStatResponse(sold, notSold);
    }

    @Transactional(readOnly = true)
    public long countFruitsByName(String name) {
        return fruitRepository.countByName(name);
    }

    @Transactional(readOnly = true)
    public List<Fruit> listFruitsByPriceOption(String option, double price) {
        if (option.equalsIgnoreCase("GTE")) {
            return fruitRepository.findByPriceGreaterThanEqualAndStatus(price, "NOT_SOLD");
        } else if (option.equalsIgnoreCase("LTE")) {
            return fruitRepository.findByPriceLessThanEqualAndStatus(price, "NOT_SOLD");
        } else {
            throw new IllegalArgumentException("Invalid option value. Use 'GTE' or 'LTE'.");
        }
    }

    @Transactional(readOnly = true)
    public List<FruitListResponse> getFruitsWithPriceGreaterThanOrEqual(double price) {
        List<Fruit> fruits = fruitRepository.findByPriceGreaterThanEqualAndStatus(price, "NOT_SOLD");
        return fruits.stream()
                .map(this::mapToFruitListResponse)
                .collect(Collectors.toList());
    }

    @Transactional(readOnly = true)
    public List<FruitListResponse> getFruitsWithPriceLessThanOrEqual(double price) {
        List<Fruit> fruits = fruitRepository.findByPriceLessThanEqualAndStatus(price, "NOT_SOLD");
        return fruits.stream()
                .map(this::mapToFruitListResponse)
                .collect(Collectors.toList());
    }

    private FruitListResponse mapToFruitListResponse(Fruit fruit) {
        FruitListResponse response = new FruitListResponse();
        response.setName(fruit.getName());
        response.setPrice(fruit.getPrice());
        response.setWarehousingDate(fruit.getWarehousingDate());
        return response;
    }
}

서비스 부분도 이러한 것들을 추가해서 리스트를 뽑아내게 해줬다.

@GetMapping("/list")
    public ResponseEntity<List<FruitListResponse>> getFruitsByPriceOption(
            @RequestParam("option") String option,
            @RequestParam("price") double price) {
        List<FruitListResponse> fruits;
        if ("GTE".equals(option)) {
            fruits = fruitService.getFruitsWithPriceGreaterThanOrEqual(price);
        } else if ("LTE".equals(option)) {
            fruits = fruitService.getFruitsWithPriceLessThanOrEqual(price);
        } else {
            return ResponseEntity.badRequest().build();
        }
        return ResponseEntity.ok(fruits);
    }

그리고 path를 컨트롤러에 추가해서 작동하게 해줬다.

그러니 이렇게 다음처럼 잘 작동했다!