2단계: "왜 수정 날짜를 수정하려고 하니!"

[문제]

벌써 500 Internal Server Error 메시지를 두 번이나 봤다. 이쯤 되니까 놀랍지도 않았다. 이번엔 또 뭘 잘못 했을까, 차분한 마음으로 설명을 읽었다.

 

[원인]

package com.spring.weekthree.repository;

import com.spring.weekthree.dto.PlanResponseDto;
import com.spring.weekthree.entity.Plan;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

// [1/2] 인터페이스
public interface PlanRepository {
    PlanResponseDto save(Plan plan);

    List<PlanResponseDto> fetchAllPlans(String name, LocalDate updatedDate);

    Optional<Plan> fetchPlanById(Long id);

    int updatePatchInRepository (
            Long id,
            String name,
            LocalDate updatedDate,
            String title,
            String task
    );
    /*
    [오답] LocalDate updatedDate
    [정답] LocalDate plannedDate
     */

    void deletePlan (Long id);
}

package com.spring.weekthree.repository;

import com.spring.weekthree.dto.PlanResponseDto;
import com.spring.weekthree.entity.Plan;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

// [2/2] Data Access Layer(Repository Layer)
@Repository
public class JdbcTemplatePlanRepository implements PlanRepository {
    private final JdbcTemplate jdbcTemplate;

    public JdbcTemplatePlanRepository(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public PlanResponseDto save(Plan plan) {
        SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
        jdbcInsert.withTableName("planner").usingGeneratedKeyColumns("id");

        Map<String, Object> parameters = new HashMap<>();

        parameters.put("name", plan.getName());
        parameters.put("password", plan.getPassword());
        parameters.put("plannedDate", plan.getPlannedDate());
        parameters.put("title", plan.getTitle());
        parameters.put("task", plan.getTask());
        parameters.put("createdDateTime", plan.getCreatedDateTime());
        parameters.put("updatedDateTime", plan.getUpdatedDateTime());
        
        Number key = jdbcInsert.executeAndReturnKey(new MapSqlParameterSource(parameters));

        return new PlanResponseDto(
                key.longValue(),
                plan.getName(),
                plan.getPlannedDate(),
                plan.getTitle(),
                plan.getTask(),
                plan.getCreatedDateTime(),
                plan.getUpdatedDateTime());
    }

    @Override
    public List<PlanResponseDto> fetchAllPlans(String name, LocalDate updatedDate) {
        return jdbcTemplate.query("SELECT * FROM planner", plannerRowMapper());
    }

    @Override
    public Optional<Plan> fetchPlanById(Long id) {
        List<Plan> result = jdbcTemplate.query("SELECT * FROM planner WHERE id = ?", plannerRowMapperEach(), id);
        return result.stream().findAny();
    }

    @Override
    public int updatePatchInRepository(
            Long id,
            String name,
            LocalDate updatedDate,
            String title,
            String task
            /*
            [오답] LocalDate updatedDate
            [정답] LocalDate plannedDate
            */
    ) {
        return jdbcTemplate.update(
                "UPDATE planner SET name = ?, updatedDate = ?, title = ?, task = ? WHERE id = ?",
                name,
                updatedDate,
                title,
                task,
                id);
    } 
    /*
    [오답] updatedDate
    [정답] plannedDate
     */

    @Override
    public void deletePlan(Long id) {

    }

    private RowMapper<PlanResponseDto> plannerRowMapper() {
        return new RowMapper<PlanResponseDto>() {
            @Override
            public PlanResponseDto mapRow(ResultSet rs, int rowNum) throws SQLException {
                return new PlanResponseDto(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getDate("plannedDate").toLocalDate(),
                        rs.getString("title"),
                        rs.getString("task"),
                        rs.getTimestamp("createdDateTime").toLocalDateTime(),
                        rs.getTimestamp("updatedDateTime").toLocalDateTime()
                );
            }
        };
    }

    private RowMapper<Plan> plannerRowMapperEach() {
        return new RowMapper<Plan>() {
            @Override
            public Plan mapRow(ResultSet rs, int rowNum) throws SQLException {
                return new Plan(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("password"),
                        rs.getDate("plannedDate").toLocalDate(),
                        rs.getString("title"),
                        rs.getString("task"),
                        rs.getTimestamp("createdDateTime").toLocalDateTime(),
                        rs.getTimestamp("updatedDateTime").toLocalDateTime()
                );
            }
        };
    }
}

Unknown column 'updatedDate' in 'field List' 메시지는 곧 알 수 없는 열이 있다는 뜻이었다. 메시지를 읽은 동시에 한숨이 절로 나왔다. '일정 관리 앱이니까 당연히 일정 날짜도 데이터로 주고받아야 하지 않나? 작성 날짜와 수정 날짜만 있으면 안 되지.' 생각하며 만들어 둔 변수를 이번에 '드디어' 착각했다. 언젠가 한 번 헷갈리겠다 싶더라니.

 

[해결]

package com.spring.weekthree.repository;

import com.spring.weekthree.dto.PlanResponseDto;
import com.spring.weekthree.entity.Plan;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

// [1/2] 인터페이스 수정하기
public interface PlanRepository {
    PlanResponseDto save(Plan plan);

    List<PlanResponseDto> fetchAllPlans(String name, LocalDate updatedDate);

    Optional<Plan> fetchPlanById(Long id);

    int updatePatchInRepository (
            Long id,
            String name,
            LocalDate plannedDate,
            String title,
            String task
    );
    /*
    [수정 전] LocalDate updatedDate
    [수정 후] LocalDate plannedDate
     */

    void deletePlan (Long id);
}

package com.spring.weekthree.repository;

import com.spring.weekthree.dto.PlanResponseDto;
import com.spring.weekthree.entity.Plan;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

// [2/2] Data Access Layer(Repository Layer) 수정하기
@Repository
public class JdbcTemplatePlanRepository implements PlanRepository {
    private final JdbcTemplate jdbcTemplate;

    public JdbcTemplatePlanRepository(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public PlanResponseDto save(Plan plan) {
        SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
        jdbcInsert.withTableName("planner").usingGeneratedKeyColumns("id");

        Map<String, Object> parameters = new HashMap<>();

        parameters.put("name", plan.getName());
        parameters.put("password", plan.getPassword());
        parameters.put("plannedDate", plan.getPlannedDate());
        parameters.put("title", plan.getTitle());
        parameters.put("task", plan.getTask());
        parameters.put("createdDateTime", plan.getCreatedDateTime());
        parameters.put("updatedDateTime", plan.getUpdatedDateTime());

        Number key = jdbcInsert.executeAndReturnKey(new MapSqlParameterSource(parameters));

        return new PlanResponseDto(
                key.longValue(),
                plan.getName(),
                plan.getPlannedDate(),
                plan.getTitle(),
                plan.getTask(),
                plan.getCreatedDateTime(),
                plan.getUpdatedDateTime());
    }

    @Override
    public List<PlanResponseDto> fetchAllPlans(String name, LocalDate updatedDate) {
        return jdbcTemplate.query("SELECT * FROM planner", plannerRowMapper());
    }

    @Override
    public Optional<Plan> fetchPlanById(Long id) {
        List<Plan> result = jdbcTemplate.query("SELECT * FROM planner WHERE id = ?", plannerRowMapperEach(), id);
        return result.stream().findAny();
    }

    @Override
    public int updatePatchInRepository(
            Long id,
            String name,
            LocalDate plannedDate,
            String title,
            String task
            /*
            [수정 전] LocalDate updatedDate
            [수정 후] LocalDate plannedDate
             */
    ) {
        return jdbcTemplate.update(
                "UPDATE planner SET " +
                        "name = ?, " +
                        "plannedDate = ?, " +
                        "title = ?, " +
                        "task = ?, " +
                        "updatedDateTime = CURRENT_TIMESTAMP " +
                        "WHERE id = ?",
                name,
                plannedDate,
                title,
                task,
                id);
    }
    /*
    [수정 전] 
    LocalDate updatedDate
    [수정 후] 
    (1) LocalDate plannedDate
    (2) 쿼리(query)문에 추가: updatedDateTime = CURRENT_TIMESTAMP
     */

    @Override
    public void deletePlan(Long id) {

    }

    private RowMapper<PlanResponseDto> plannerRowMapper() {
        return new RowMapper<PlanResponseDto>() {
            @Override
            public PlanResponseDto mapRow(ResultSet rs, int rowNum) throws SQLException {
                return new PlanResponseDto(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getDate("plannedDate").toLocalDate(),
                        rs.getString("title"),
                        rs.getString("task"),
                        rs.getTimestamp("createdDateTime").toLocalDateTime(),
                        rs.getTimestamp("updatedDateTime").toLocalDateTime()
                );
            }
        };
    }

    private RowMapper<Plan> plannerRowMapperEach() {
        return new RowMapper<Plan>() {
            @Override
            public Plan mapRow(ResultSet rs, int rowNum) throws SQLException {
                return new Plan(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("password"),
                        rs.getDate("plannedDate").toLocalDate(),
                        rs.getString("title"),
                        rs.getString("task"),
                        rs.getTimestamp("createdDateTime").toLocalDateTime(),
                        rs.getTimestamp("updatedDateTime").toLocalDateTime()
                );
            }
        };
    }
}

200 OK 메시지가 떠서 완료된 줄 알았으나 updatedDateTime이 해결되지 않아 챗GPT에 물어봐서 해결했다. CURRENT_TIMESTAMP를 처음 써봤는데, 두 번 눌러야 시간이 바뀌는 점이 좀 아쉽긴 하지만 그럭저럭 만족했다. 데이터를 전송하는 즉시 바뀌지 않는 문제는 과제를 마친 후 튜터님께 여쭤봐야겠다. 어쨌거나 두 번째 수정부터는 수정 날짜와 시간도 잘 나온다. 이렇게 또 산 하나를 넘었다.

 

[결과 수치화]

[수정 전] 500 Internal Server Error 1건 발생

[수정 후] 500 Internal Server Error 0건 발생, 201 Created 메시지 생성 및 데이터베이스 연동 성공