<aside> ⚠️ API 개발에 익숙해지며 MySQL DB에 저장해보기

</aside>

1. 과일 정보 저장 POST API

// Fruit.java

package com.inflearn.Inflearn.Study.day04.entity;

import lombok.Getter;

import java.time.LocalDate;

@Getter
public class Fruit {
    private Long id;
    private String name;
    private LocalDate warehousingDate;
    private Long price;
    private boolean isSold; // 0 -> false, 1 -> true

    public Fruit(String name, LocalDate warehousingDate, Long price) {
        this.name = name;
        this.warehousingDate = warehousingDate;
        this.price = price;
    }
}

// FruitRequest.java

package com.inflearn.Inflearn.Study.day04.dto;

import lombok.Getter;
import lombok.NoArgsConstructor;

import java.time.LocalDate;

@NoArgsConstructor // 기본 생성자
@Getter
public class FruitRequest {
    private String name;
    private LocalDate warehousingDate;
    private Long price;

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

// FruitController.java

@RestController
public class FruitController {

    private final JdbcTemplate jdbcTemplate;

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

    @PostMapping("/api/v1/fruit")
    public void saveFruit(@RequestBody FruitRequest request) {
        String sql = "insert into fruit(name, warehousingDate, price) values (?, ?, ?)";
        Fruit fruit = new Fruit(request.getName(), request.getWarehousingDate(), request.getPrice());
        jdbcTemplate.update(sql, fruit.getName(), fruit.getWarehousingDate(), fruit.getPrice());
    }
}

• 결과

2. 과일 판매 변경 PUT API

// FruitUpdateRequest.java

package com.inflearn.Inflearn.Study.day04.dto;

import lombok.Getter;
import lombok.NoArgsConstructor;

@Getter
@NoArgsConstructor
public class FruitUpdateRequest {
    private Long id;

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

// FruitController.java

package com.inflearn.Inflearn.Study.day04.controller;

import com.inflearn.Inflearn.Study.day04.dto.FruitRequest;
import com.inflearn.Inflearn.Study.day04.dto.FruitSoldResponse;
import com.inflearn.Inflearn.Study.day04.dto.FruitUpdateRequest;
import com.inflearn.Inflearn.Study.day04.entity.Fruit;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

@RestController
public class FruitController {

    private final JdbcTemplate jdbcTemplate;

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

    @PostMapping("/api/v1/fruit")
    public void saveFruit(@RequestBody FruitRequest request) {
        String sql = "insert into fruit(name, warehousingDate, price) values (?, ?, ?)";
        Fruit fruit = new Fruit(request.getName(), request.getWarehousingDate(), request.getPrice());
        jdbcTemplate.update(sql, fruit.getName(), fruit.getWarehousingDate(), fruit.getPrice());
    }

    @PutMapping("/api/v1/fruit")
    public void sellFruit(@RequestBody FruitUpdateRequest updateRequest) {
        String sql = "update fruit set is_sold = 1 where id = ? ";
        jdbcTemplate.update(sql, updateRequest.getId());
    }
}

• 결과

3. 팔린 과일 / 팔리지 않은 과일 정보 조회 GET API

// FruitSoldResponse.java

package com.inflearn.Inflearn.Study.day04.dto;

import lombok.Getter;
import lombok.NoArgsConstructor;

@Getter
@NoArgsConstructor
public class FruitSoldResponse {
    private Long salesAmount;
    private Long noSalesAmount;

    public FruitSoldResponse(Long salesAmount, Long noSalesAmount) {
        this.salesAmount = salesAmount;
        this.noSalesAmount = noSalesAmount;
    }
}

// FruitController.java

package com.inflearn.Inflearn.Study.day04.controller;

import com.inflearn.Inflearn.Study.day04.dto.FruitRequest;
import com.inflearn.Inflearn.Study.day04.dto.FruitSoldResponse;
import com.inflearn.Inflearn.Study.day04.dto.FruitUpdateRequest;
import com.inflearn.Inflearn.Study.day04.entity.Fruit;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

@RestController
public class FruitController {

    private final JdbcTemplate jdbcTemplate;

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

    @PostMapping("/api/v1/fruit")
    public void saveFruit(@RequestBody FruitRequest request) {
        String sql = "insert into fruit(name, warehousingDate, price) values (?, ?, ?)";
        Fruit fruit = new Fruit(request.getName(), request.getWarehousingDate(), request.getPrice());
        jdbcTemplate.update(sql, fruit.getName(), fruit.getWarehousingDate(), fruit.getPrice());
    }

    @PutMapping("/api/v1/fruit")
    public void sellFruit(@RequestBody FruitUpdateRequest updateRequest) {
        String sql = "update fruit set is_sold = 1 where id = ? ";
        jdbcTemplate.update(sql, updateRequest.getId());
    }

    @GetMapping("/api/v1/fruit/stat")
    public FruitSoldResponse getFruitIsSoldOrNot(@RequestParam String name) {
        Long soldPrice = jdbcTemplate.queryForObject(
                "select sum(price) from fruit where name = ? and is_sold = 1", Long.class, name
        );
        Long notSoldPrice = jdbcTemplate.queryForObject(
                "select sum(price) from fruit where name = ? and is_sold = 0", Long.class, name
        );
        FruitSoldResponse response = new FruitSoldResponse(soldPrice, notSoldPrice);
        return response;
    }
}

• 결과

4. 한 걸음 더!

<aside> ⚠️ 왜 int말고 long을 사용할까?

</aside>

• int는 32bit(4byte)이고 long은 64bit(8byte)이다. 즉, long이 int보다 저장할 수 있는 데이터의 범위가 넓다.(-2147483648 ~ 2147483647 vs -9223372036854775808 ~ 9223372036854775807)