인프런 웜업 0기 - 과제 4
package com.group.libraryapp.controller.api.v1; import com.group.libraryapp.dto.fruit.request.FruitSellRequest; import com.group.libraryapp.dto.fruit.request.FruitCreateRequest; import com.group.libraryapp.dto.fruit.response.FruitResponse; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.web.bind.annotation.*; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; @RestController public class FruitController { private final JdbcTemplate jdbcTemplate; public FruitController(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @PostMapping("api/v1/fruit") // POST /fruit public void saveFruit(@RequestBody FruitCreateRequest request) { String sql = "INSERT INTO fruit (name, warehousingDate, price) VALUES (?, ?, ?)"; jdbcTemplate.update(sql, request.getName(), request.getWarehousingDate(), request.getPrice()); } @PutMapping("api/v1/fruit") // PUT /fruit public void sellFruit(@RequestBody FruitSellRequest request) { String checkSql = "SELECT * FROM fruit WHERE id = ?"; // 해당 id가 존재하지않을 시 예외처리 boolean isNotExist = jdbcTemplate.query(checkSql, (rs, rowNum) -> 0, request.getId()).isEmpty(); if (isNotExist) { throw new IllegalArgumentException(); } String sql = "UPDATE fruit SET sold = 1 WHERE id = ?"; jdbcTemplate.update(sql, request.getId()); } @GetMapping("api/v1/fruit/stat") // GET /fruit public FruitResponse getFruit(@RequestParam String name) { String checkSql = "SELECT * FROM fruit WHERE name = ?"; boolean isNotExist = jdbcTemplate.query(checkSql, (rs, rowNum) -> 0, name).isEmpty(); if (isNotExist) { throw new IllegalArgumentException(); } // 1) sold 여부에 따른 각 price의 최종합계를 구해온 뒤, 리스트에 추가. // 2) 최종 list를 순회하면서 sold 여부에 따른 최종합계 재계산후 반환. String sql = "SELECT sold, SUM(price) AS total_price FROM fruit WHERE name = ? GROUP BY sold"; List<FruitResponse> responses = jdbcTemplate.query(sql, (rs, rowNum) -> { FruitResponse response = new FruitResponse(0, 0); if (rs.getBoolean("sold")) response.setSalesAmount(rs.getLong("total_price")); else response.setNotSalesAmount(rs.getLong("total_price")); return response; }, name); FruitResponse finalResponse = new FruitResponse(0, 0); for (FruitResponse response : responses) { finalResponse.setSalesAmount(finalResponse.getSalesAmount() + response.getSalesAmount()); finalResponse.setNotSalesAmount(finalResponse.getNotSalesAmount() + response.getNotSalesAmount()); } return finalResponse; } } Fruit 테이블 생성 Post를 이용하여 Fruit 테이블에 'apple' 레코드 생성레코드 생성확인apple의 'sold' 필드를 true로 설정.레코드 변경여부 확인2개의 apple들을 더 추가하고, sold 필드는 설정하지않았음.sold 여부에 따른 최종가격 받아오기 확인