인프런 워밍업 클럽 1기_BE 4일차 과제

 

테이블 생성 SQL

create table fruit
(
    id    bigint auto_increment,
    name  varchar(25),
    price bigint,
    warehousingDate date,
    status boolean default FALSE,
    primary key (id)
);

 

FruitStoreController.java

@RestController
public class FruitStore {

    private final JdbcTemplate jdbcTemplate;

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

    @PostMapping("/api/v1/fruit")
    public void saveFruitInfo(@RequestBody FruitRequest request) {
        String sql = "INSERT INTO fruit(name, price, warehousingDate) VALUES (?, ?, ?) ";
        jdbcTemplate.update(sql, request.getName(), request.getPrice(), request.getWarehousingDate());
    	}
    }

 

FruitRequest.java

import java.time.LocalDate;

public class FruitRequest {

    private final String name;

    private final LocalDate warehousingDate;

    private final long price;

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

    public String getName() {
        return name;
    }

    public LocalDate getWarehousingDate() {
        return warehousingDate;
    }



    public long getPrice() {
        return price;
    }
}

 

Response

 

Table

자바에서 int는 4비트이고 long은 8비트 자료형으로 long을 사용하면 더 많은 데이터를 저장 할 수 있다

 

FruitStoreController.java

@RestController
public class FruitStore {

    private final JdbcTemplate jdbcTemplate;

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

    @PostMapping("/api/v1/fruit")
    public void saveFruitInfo(@RequestBody FruitRequest request) {
        String sql = "INSERT INTO fruit(name, price, warehousingDate) VALUES (?, ?, ?) ";
        jdbcTemplate.update(sql, request.getName(), request.getPrice(), request.getWarehousingDate());
    	}
        
        @PutMapping("/api/v1/fruit")
    public void sellFruitInfo(@RequestBody SellInfoRequest request) {
        String sql = "UPDATE fruit SET status = TRUE WHERE id = ?";
        jdbcTemplate.update(sql, request.getId());
    	}
    }

 

SellInfoRequest.java

package com.group.libraryapp.ex.fruitstore.dto.request;

import com.fasterxml.jackson.annotation.JsonProperty;

public class SellInfoRequest {
    @JsonProperty("id")
    private final long id;

    public SellInfoRequest( @JsonProperty("id") long id) {
        this.id = id;
    }

    public long getId() {
        return id;
    }
}

JSON의 id가 SellInfoRequest의 id 값을 매핑을 못해서 @JsonProperty 어노테이션을 이용해서 매핑

Response

 

Table

 

 

FruitStoreController.java

package com.group.libraryapp.ex.fruitstore.controller;

import com.group.libraryapp.ex.fruitstore.dto.request.FruitRequest;
import com.group.libraryapp.ex.fruitstore.dto.request.SellInfoRequest;
import com.group.libraryapp.ex.fruitstore.dto.response.ShowFruitResponse;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

@RestController
public class FruitStoreController {

    private final JdbcTemplate jdbcTemplate;

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

    @PostMapping("/api/v1/fruit")
    public void saveFruitInfo(@RequestBody FruitRequest request) {
        String sql = "INSERT INTO fruit(name, price, warehousingDate) VALUES (?, ?, ?) ";
        jdbcTemplate.update(sql, request.getName(), request.getPrice(), request.getWarehousingDate());
    }

    @PutMapping("/api/v1/fruit")
    public void sellFruitInfo(@RequestBody SellInfoRequest request) {
        String sql = "UPDATE fruit SET status = TRUE WHERE id = ?";
        jdbcTemplate.update(sql, request.getId());
    }

    @GetMapping("/api/v1/fruit/stat")
    public ShowFruitResponse showfruitInfo(@RequestParam String name) {
        String saleSql = "SELECT sum(price) from fruit where status = TRUE";
        String notSaleSql = "SELECT sum(price) from fruit where status = FALSE";

        Long salesAmount = jdbcTemplate.queryForObject(saleSql, Long.class);
        Long notSalesAmount = jdbcTemplate.queryForObject(notSaleSql, Long.class);

        return new ShowFruitResponse(salesAmount, notSalesAmount);

    }
}

 

쿼리 2개를 한번에 적용하는 법이 감이 안와서 이이삭님 노션을 참고해서 queryForObject()를 사용해서 SQL을 자바 객체로 반환하였다.

ShowFruitResponse.java

package com.group.libraryapp.ex.fruitstore.dto.response;

public class ShowFruitResponse {

    private final long salesAmount;

    private final long notSalesAmount;

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

    public long getSalesAmount() {
        return salesAmount;
    }

    public long getNotSalesAmount() {
        return notSalesAmount;
    }
}

 

Response

 

출처

https://www.inflearn.com/course/%EC%9E%90%EB%B0%94-%EC%8A%A4%ED%94%84%EB%A7%81%EB%B6%80%ED%8A%B8-%EC%84%9C%EB%B2%84%EA%B0%9C%EB%B0%9C-%EC%98%AC%EC%9D%B8%EC%9B%90#

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

 

https://toastlee.notion.site/4-14-16-API-d8c03af0db414a2389a917606712a3d5

4일차(14~16강) - 데이터베이스를 만드는 API | Notion

 

틀린 내용이나 부족한 부분 댓글로 알려주시면 추가로 공부해서 수정하겠습니다 감사합니다.