[인프런 워밍업 클럽_0기] BE 네 번째 과제 (진도표 4일차)

배너를 클릭하면 해당 사이트로 이동

 

강의 출처

자바와 스프링 부트로 생애 최초 서버 만들기, 누구나 쉽게 개발부터 배포까지! [서버 개발 올인

 

 

진도표 4일차와 연결됩니다
우리는 GET API와 POST API를 만드는 방법을 배웠습니다. 👍 추가적인 API 들을 만들어 보며 API 개발에 익숙해져 봅시다!

4일차 문제 1, 문제 2, 문제 3은 이어지는 문제입니다!

 

문제 풀기 전 테이블 생성

컬럼 정하기

문제1에서 필요한 컬럼 → NAME(String), WAREHOUSINGDATE(LocalDate), PRICE(long)

문제2에서 필요한 컬럼 → SALES_STATUS(int default 0, 판매 시 1로 변경)

문제3에서 필요한 컬럼 → X

 

테이블 생성하기

create table fruit (
    id bigint auto_increment,
    name varchar(25),
    warehousingDate date,
    price long,
    salesYN int default 0,
    primary key (id)
);

 

 

문제 1

 

• dto - FruitSaveRequest

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

import java.time.LocalDate;

public class FruitSaveRequest {
    private String name;
    private LocalDate warehousingDate;
    private long price;

    public FruitSaveRequest(String name, LocalDate warehousingDate, long price) {
        this.name = name;
        this.warehousingDate = warehousingDate;
        this.price = price;
    }

    public String getName() {
        return name;
    }

    public LocalDate getWarehousingDate() {
        return warehousingDate;
    }

    public long getPrice() {
        return price;
    }
}

 

• dto - FruitController

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

    private JdbcTemplate jdbcTemplate;

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

    @PostMapping("/fruit")
    public void save(@RequestBody FruitSaveRequest request) {
        String sql = "insert into fruit (name, warehousingDate, price) values (?, ?, ?)";
        jdbcTemplate.update(sql, request.getName(), request.getWarehousingDate(), request.getPrice());
    }
}

 

반응형

 

• 포스트맨 테스트

 

• DB 확인

 

 

문제 2

 

• dto - FruitUpdateRequest

public class FruitUpdateRequest {

    private long id;

    public FruitUpdateRequest() {
    }

    public FruitUpdateRequest(long id) {
        this.id = id;
    }

    public long getId() {
        return id;
    }
}

 

• FruitController

@PutMapping("/fruit")
public void update(@RequestBody FruitUpdateRequest request) {
    String sql =  "update fruit set salesYN = 1 where id = ?";
    jdbcTemplate.update(sql, request.getId());
}

 

• 포스트맨

 

• DB 확인

 

반응형

 

문제 3

 

DB 데이터 수정 및 추가

update fruit set price = 3000 where id = 1; // 첫 번째 데이터 가격 수정
insert into fruit (name, warehousingDate, price) values ('사과', '2024-02-22', 4000);
insert into fruit (name, warehousingDate, price, salesYN) values ('사과', '2024-02-21', 3000, 1);

 

• dto - FruitStatResponse

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;
    }
}

 

• domain - Fruit

public class Fruit {

    private long id;
    private String name;
    private long price;
    private int salesYN;

    public Fruit(long id, String name, long price, int salesYN) {
        this.id = id;
        this.name = name;
        this.price = price;
        this.salesYN = salesYN;
    }

    public long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public long getPrice() {
        return price;
    }

    public int getSalesYN() {
        return salesYN;
    }
}

 

반응형

 

• FruitController

@GetMapping("/fruit/stat")
public FruitStatResponse fruitStat (@RequestParam String name) {
    String salesSql = "select * from fruit where salesYN = 1 and name = ?";
    String notSalesSql = "select * from fruit where salesYN = 0 and name = ?";

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

    long salesAmount = 0;
    long notSalesAmount = 0;

    for (Fruit fruit : salesList) {
        salesAmount += fruit.getPrice();
    }

    for (Fruit fruit : notSalesList) {
        notSalesAmount += fruit.getPrice();
    }

    return new FruitStatResponse(salesAmount, notSalesAmount);
}

private List<Fruit>  getList(String sql, String fruitNm) {
    return jdbcTemplate.query(sql, (rs, rowNum) -> {
        long id = rs.getLong("id");
        String name = rs.getString("name");
        long price = rs.getLong("price");
        int salesYN = rs.getInt("salesYN");
        return new Fruit(id, name, price, salesYN);
    }, fruitNm);
}

 

• 포스트맨 테스트

 

한 걸음 더!

데이터 추가

insert into fruit (name, warehousingDate, price) value ('바나나', '2023-02-12', 30000);

 

반응형

 

• FruitController 수정

@GetMapping("/fruit/stat")
public FruitStatResponse fruitStat (@RequestParam String name) {

    String salesSql = "select sum(price) as sum from fruit where salesYN = 1 group by name having name = ?";
    String notSalesSql = "select sum(price) as sum from fruit where salesYN = 0 group by name having name = ?";

    long salesAmount = getSum(salesSql, name);
    long notSalesAmount = getSum(notSalesSql, name);

    return new FruitStatResponse(salesAmount, notSalesAmount);
}


private long getSum(String sql, String fruitNm) {
    return jdbcTemplate.queryForObject(sql, Long.class, fruitNm);
}