4-1

create table fruits
(
    id              bigint auto_increment,
    name            varchar(20),
    warehousingDate date,
    price           int,
    isSale         boolean default 0,
    primary key (id)
);

insert into fruits (name, warehousingDate, price) values ('사과',  '2024-02-21', 1000);

show tables;
select * from fruits;

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

import java.time.LocalDate;

public class fruitsRequestDto {
    private String name;
    private LocalDate warehousingDate;
    private int price;

    public String getName() {
        return name;
    }

    public LocalDate getWarehousingDate() {
        return warehousingDate;
    }

    public int getPrice() {
        return price;
    }
}

package com.group.libraryapp.controller.fruits;

import com.group.libraryapp.dto.fruits.request.fruitsRequestDto;
import org.aspectj.lang.annotation.RequiredTypes;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class fruitsController {
    private JdbcTemplate jdbcTemplate;

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

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

4-2

@PutMapping("/api/v1/fruits")
public void updateSaleState(@RequestBody fruitSaleRequestDto request){
    String saleSql = "select * from fruits where id = ?";
    boolean isFruitNotExist = jdbcTemplate.query(saleSql, (rs, rowNum) -> 0, request.getId()).isEmpty();

    if(isFruitNotExist){
        throw new IllegalStateException("존재하지 않는 과일입니다.");
    }

    String sql = "update fruits set isSale = 1 where id = ?";
    jdbcTemplate.update(sql, request.getId());
}

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

public class fruitSaleRequestDto {
    private long id;

    public long getId() {
        return id;
    }
}

4-3

@GetMapping("api/v1/fruits")
public List<fruitTotalPriceDto> getFruitsTotalPrice(@RequestParam String name){
    String sql = "select " +
            "(select sum(price) from fruits where isSale = 1) as salePrice, " +
            "(select sum(price) from fruits where isSale = 0) as noSalePrice";

    return jdbcTemplate.query(sql, (rs, rowNum) -> {
        long salePrice = rs.getLong("salePrice");
        long noSalePrice = rs.getLong("noSalePrice");
        return new fruitTotalPriceDto(salePrice, noSalePrice);
    });
}





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

public class fruitTotalPriceDto {
    private long salePrice;
    private long noSalePrice;

    public fruitTotalPriceDto(long salePrice, long noSalePrice) {
        this.salePrice = salePrice;
        this.noSalePrice = noSalePrice;
    }

    public long getSalePrice() {
        return salePrice;
    }

    public long getNoSalePrice() {
        return noSalePrice;
    }
}