[인프런 워밍업 스터디 클럽 0기 - BE] #4 과제

 

문제 1

 

 

 

Domain

public class Fruit {

    private String name;
    private Long price;
    private LocalDate localDate;

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

    public String getName() {
        return name;
    }

    public Long getPrice() {
        return price;
    }

    public LocalDate getLocalDate() {
        return localDate;
    }
}

 

 

DTO 

 

public class FruitCreateRequest {
    private String name;
    private Long price;
    private LocalDate localDate;

    public FruitCreateRequest(String name, Long price, LocalDate localDate) {
        this.name = name;
        this.price = price;
        this.localDate = localDate;
    }

    public String getName() {
        return name;
    }

    public Long getPrice() {
        return price;
    }

    public LocalDate getLocalDate() {
        return localDate;
    }
}

 

 

Controller

 

@RestController
public class FruitController {

    private final JdbcTemplate jdbcTemplate;

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

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

    }

}

 

 

 

 

 

 

한걸음 더!

 

int는 32bit이고 long은 64bit이다.  long이 int보다 저장할 수 있는 데이터의 범위가 넓다.

API에서 long을 사용한 이유는 long이 더 넓은 범위의 정수를 저장하기 때문이다.

 

문제 2

 

 

DTO

 

public class FruitUpdateRequest {

    private long id;


    public FruitUpdateRequest(long id, long sold) {
        this.id = id;
    }

    public long getId() {
        return id;
    }

}

 

 

Controller

 

@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, isDate, price) values (?, ?, ?)";
        Fruit fruit = new Fruit(request.getName(), request.getIsDate(), request.getPrice());
        jdbcTemplate.update(sql, fruit.getName(), fruit.getIsDate(), fruit.getPrice());
    }

     @PutMapping("/api/v1/fruit")
    public void soldFruit(@RequestBody FruitUpdateRequest request) {
        String readSql = "select * from fruit where id = ?";
        boolean isFruitNotExist = jdbcTemplate.query(readSql, (rs, rowNum) -> 0, request.getId()).isEmpty();
        if (isFruitNotExist) {
            throw new IllegalArgumentException();
        }
        String sql = "update fruit set sold = 1 where id = ?";
        jdbcTemplate.update(sql, request.getId());
    }
}

 

 

 

sold 칼럼을 추가하여 Fruit를 생성했다.

sold는 default 값을 0으로 설정하여 자동으로 0으로 생성되게 만들었다.

판매가완료된 것은 0 -> 1로 바꾸어 확인할 수가 있다.

 

 

 

 

문제 3

 

 

 

 

Controller

 

package com.group.libraryapp.controller.fruit;

import com.group.libraryapp.domain.fruit.Fruit;
import com.group.libraryapp.dto.fruit.reponse.FruitSoldResponse;
import com.group.libraryapp.dto.fruit.request.FruitCreateRequest;
import com.group.libraryapp.dto.fruit.request.FruitUpdateRequest;
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 FruitCreateRequest request){
        String sql = "insert into fruit(name, stocked_date, price) values (?,?,?)";
        Fruit fruit = new Fruit(request.getName(),request.getPrice(),request.getLocalDate());
        jdbcTemplate.update(sql,request.getName(),fruit.getLocalDate(),fruit.getPrice());

    }
    @PutMapping("/api/v1/fruit")
    public void soldFruit(@RequestBody FruitUpdateRequest request) {
        String readSql = "select * from fruit where id = ?";
        boolean isFruitNotExist = jdbcTemplate.query(readSql, (rs, rowNum) -> 0, request.getId()).isEmpty();
        if (isFruitNotExist) {
            throw new IllegalArgumentException();
        }
        String sql = "update fruit set sold = 1 where id = ?";
        jdbcTemplate.update(sql, request.getId());
    }

    @GetMapping("/api/v1/fruit/stat")
    public FruitSoldResponse getFruitIsSoldOrNot(@RequestParam String name) {
        Long sold = jdbcTemplate.queryForObject(
                "select sum(price) from fruit where name = ? and sold = 1", (rs, rowNum) -> {
                    Long sum = rs.getLong(1);
                    return (sum != null) ? sum : 0L;
                }
        ,name);
        Long notSold = jdbcTemplate.queryForObject(
                "select coalesce(sum(price),0) from fruit where name = ? and sold = 0", Long.class, name
        );
        FruitSoldResponse response = new FruitSoldResponse(sold, notSold);
        return response;
    }
}

 

 

sum을활용해 팔린 과일과 팔리지 않은 과일을 구별하였다.

 

Long notSold = jdbcTemplate.queryForObject(
        "select sum(price) from fruit where name = ? and sold = 0", Long.class, name
);
FruitSoldResponse response = new FruitSoldResponse(sold, notSold);
return response;

 

하지만 위에처럼 구현했을 때 팔린 과일만 있을 경우 오류가 발생하였다.

왜냐면 notSold에 null이 들어가기 때문이다 notSold는 long타입이다.

그래서 2가지 방법을 채택했다.

 

 

1. 람다를 이용한 null경우 0L을 반환

 

Long sold = jdbcTemplate.queryForObject(
        "select sum(price) from fruit where name = ? and sold = 1", (rs, rowNum) -> {
            Long sum = rs.getLong(1);
            return (sum != null) ? sum : 0L;
        }
,name);

 

 

2. coalesce함수를 이용해서 결괏값이 없을 경우 0을 반환하는 sql문

 

  Long notSold = jdbcTemplate.queryForObject(
            "select coalesce(sum(price),0) from fruit where name = ? and sold = 0", Long.class, name
    );
    FruitSoldResponse response = new FruitSoldResponse(sold, notSold);
    return response;
}

 

 

 

 

 

한 걸음 더!

 

 

@GetMapping("/api/v1/fruit/stat")
    public FruitSoldResponse getFruitIsSoldOrNot(@RequestParam String name) {
        Map<Long, Long> result = jdbcTemplate.query(
                        "select sold, sum(price) from fruit where name = ? group by sold",
                        (rs, rowNum) -> {
                            Long isSold = rs.getLong("sold");
                            long sum = rs.getLong(2);
                            return Map.entry(isSold, sum);
                        },
                        name
                ).stream()
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));

        Long sold = result.getOrDefault(1L, 0L);
        Long notSold = result.getOrDefault(0L, 0L);

        return new FruitSoldResponse(sold, notSold);
    }

 

 

위에서는 sum만 사용했는데 이번엔 group by 를사용해서 조회를 했다.

이번에는 map에 getOrDefault를 이용해서 값이 없을 경우 0으로 반환되게 했다.