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

데이터 베이스를 사용해 만드는 API

• 기존의 코드들을 사용했을 때는 서버가 꺼졌다 켜지면 데이터가 날아갔다.
• 데이터 베이스를 사용해서 데이터를 저장하고 계속해서 쓸 수 있도록 하자. 
• 데이터베이스를 사용하는 방법엔 Jdbc, JPA, Data JPA가 있다. 
• 우선은 JDBC를 이용해서 데이터 베이스를 사용하자

---

진도표 4일차와 연결됩니다

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

 

 @PostMapping("api/v1/fruit")
    public void saveFruit(@RequestBody FruitRequest request){
        String sql = "Insert into fruit(name, warehousingDate, price,sold) Values(?,?,?,?)";
        jdbcTemplate.update(sql, request.getName(), request.getWarehousingDate(), request.getPrice(),false);
////        fruits.add(new Fruit(request.getName(), request.getWarehousingDate(), request.getPrice()));
//        return new FruitResponse(fruits.get(fruits.size() - 1));

    }

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

    @GetMapping("api/v1/fruit/stat")
    public List<FruitSoldResponse> fruitSoldResponse(@RequestParam("name") String name){
        String sql ="select"+
                "(select sum(price) from fruit where sold = 1) as salesAmount, " +
                "(select sum(price) from fruit where sold = 0) as notSalesAmount";

        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            long salesAmount = rs.getLong("salesAmount");
            long notSalesAmount = rs.getLong("notSalesAmount");
            return new FruitSoldResponse(salesAmount, notSalesAmount);
        });
    }

 

Controller 를 이용해서 jdbcTemplate을 사용해 쿼리를 연결하였다. 

 

# DTO

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

import java.time.LocalDate;

public class FruitRequest {
    private String name;
    private String warehousingDate;
    private long price;

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

    public String getName() {
        return name;
    }

    public String getWarehousingDate() {
        return warehousingDate;
    }

    public long getPrice() {
        return price;
    }
}

package com.group.libraryapp.dto.example.response;

import com.group.libraryapp.domain.user.Fruit;
import com.group.libraryapp.dto.example.request.FruitRequest;

public class FruitResponse {
    private String name;
    private String warehousingDate;
    private long price;

    public FruitResponse(Fruit fruit) {
        this.name = fruit.getName();
        this.warehousingDate = fruit.getWarehousingDate().toString();
        this.price = fruit.getPrice();
    }

    public String getName() {
        return name;
    }

    public String getWarehousingDate() {
        return warehousingDate;
    }

    public long getPrice() {
        return price;
    }
}

 

package com.group.libraryapp.dto.example.response;

public class FruitSoldResponse {
    private long salesAmount;
    private long notsalesAmount;

    public FruitSoldResponse(long salesAmount, long notsalesAmount) {
        this.salesAmount = salesAmount;
        this.notsalesAmount = notsalesAmount;
    }

    public long getSalesAmount() {
        return salesAmount;
    }

    public void setSalesAmount(long salesAmount) {
        this.salesAmount = salesAmount;
    }

    public long getNotsalesAmount() {
        return notsalesAmount;
    }

    public void setNotsalesAmount(long notsalesAmount) {
        this.notsalesAmount = notsalesAmount;
    }
}