[인프런 워밍업 클럽] 4일차 - 추가적인 API 만들기

✨ 문제 풀기 전에 먼저 필요한 테이블을 생성하자!

create table fruit
(
    id               bigint auto_increment,
    name             varchar(30),
    warehousing_date date,
    price            bigint,
    is_sold          boolean default false,
    primary key (id)
);

 

📌 문제 1

문제 1

👩🏻‍💻 Controller 코드

@RequestMapping("/api/v1")
@RestController
public class FruitController {

    private final JdbcTemplate jdbcTemplate;

    public FruitController(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @PostMapping("/fruit")
    public void saveFruit(@RequestBody FruitCreateRequest request) {
        String sql = "INSERT INTO fruit (name, warehousing_date, price) VALUES (?, ?, ?)";
        jdbcTemplate.update(sql, request.getName(), request.getWarehousingDate(), request.getPrice());
    }
}

 

👩🏻‍💻 Request DTO 코드

package com.group.libraryapp.dto.homework4.request;

import java.time.LocalDate;

public class FruitCreateRequest {

    private String name;
    private LocalDate warehousingDate;
    private Long price;

    public String getName() {
        return name;
    }

    public LocalDate getWarehousingDate() {
        return warehousingDate;
    }

    public Long getPrice() {
        return price;
    }
}

 

📌 한 걸음 더!

int 타입을 사용하면 값이 약 21억을 넘으면 오버플로우가 발생하기 때문에 값 범위가 더 넓은 long 타입을 사용합니다.

 

📌 문제 2

문제 2

👩🏻‍💻 Controller 코드

@RequestMapping("/api/v1")
@RestController
public class FruitController {

    private final JdbcTemplate jdbcTemplate;

    public FruitController(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @PutMapping("/fruit")
    public void updateSoldFruit(@RequestBody FruitUpdateRequest request) {
        String readSql = "SELECT * FROM fruit WHERE id = ?";
        boolean isFruitNotExist = jdbcTemplate.query(readSql, (rs, rowNum) -> 0, request.getId()).isEmpty();
        if (isFruitNotExist) {
            throw new IllegalArgumentException();
        }
        String sql = "UPDATE fruit SET is_sold = true WHERE id = ?";
        jdbcTemplate.update(sql, request.getId());
    }
}

 

👩🏻‍💻 Request DTO 코드

package com.group.libraryapp.dto.homework4.request;

public class FruitUpdateRequest {

    private long id;

    public long getId() {
        return id;
    }
}

 

🔍 Postman 결과

 

🔍 DB 결과

id 3번 사과의 is_sold 값이 0에서 1로 변경됨

 

📌 문제 3

문제 3

👩🏻‍💻 Controller 코드

@RequestMapping("/api/v1")
@RestController
public class FruitController {

    private final JdbcTemplate jdbcTemplate;

    public FruitController(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @GetMapping("/fruit/stat")
    public FruitStatResponse getPriceOfFruit(@RequestParam String name) {
        String salesSql = "SELECT * FROM fruit WHERE is_sold = true and name = ?";
        String notSalesSql = "SELECT * FROM fruit WHERE is_sold = false and name = ?";

        List<Fruit> salesList = getList(salesSql, name);
        List<Fruit> notSalesList = getList(notSalesSql, name);

        long salesAmount = salesList.stream().mapToLong(Fruit::getPrice).sum();
        long notSalesAmount = notSalesList.stream().mapToLong(Fruit::getPrice).sum();

        return new FruitStatResponse(salesAmount, notSalesAmount);
    }

    private List<Fruit> getList(String sql, String fruitName) {
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            long id = rs.getLong("id");
            String name = rs.getString("name");
            Long price = rs.getLong("price");
            boolean isSold = rs.getBoolean("is_sold");
            return new Fruit(id, name, price, isSold);
        }, fruitName);
    }
}

 

 

👩🏻‍💻 Response DTO 코드

package com.group.libraryapp.dto.homework4.response;

public class FruitStatResponse {

    private long salesAmount;
    private long notSalesAmount;

    public FruitStatResponse(long salesAmount, long notSalesAmount) {
        this.salesAmount = salesAmount;
        this.notSalesAmount = notSalesAmount;
    }

    public long getSalesAmount() {
        return salesAmount;
    }

    public long getNotSalesAmount() {
        return notSalesAmount;
    }
}

 

📌 한 걸음 더!

- FruitController 수정

@GetMapping("/fruit/stat")
public FruitStatResponse getPriceOfFruit(@RequestParam String name) {
    String salesSql = "SELECT SUM(price) FROM fruit WHERE is_sold = true AND name = ? GROUP BY name";
    String notSalesSql = "SELECT SUM(price) FROM fruit WHERE is_sold = false AND name = ? GROUP BY name";

    long salesAmount = jdbcTemplate.queryForObject(salesSql, Long.class, name);
    long notSalesAmount = jdbcTemplate.queryForObject(notSalesSql, Long.class, name);

    return new FruitStatResponse(salesAmount, notSalesAmount);
}

 

🔍 Postman 결과

 

 

 

 

강의 링크

https://inf.run/XKQg