Spring) 할일 목록 조회하기(두 조건 상이하게 정렬하기), 할일 추가하기

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

할일 목록 조회하기

 

DB

-- todo 테이블 생성
create table todo(
    no number,
    todo varchar2(2000) not null,
    created_at date default sysdate,
    completed_at date,
    constraint pk_todo_no primary key(no)
);

create sequence seq_todo_no;

insert into todo values(seq_todo_no.nextval, '우산 청소하기', default, null);
insert into todo values(seq_todo_no.nextval, '형광등 교체', default, null);
insert into todo values(seq_todo_no.nextval, '장 보기', default, null);
insert into todo values(seq_todo_no.nextval, '차에 물 퍼내기', default, null);

select * from todo;

update todo set completed_at = sysdate where no = 4;
update todo set completed_at = sysdate where no = 2;
commit;

select * from todo;

 

Dto

Todo

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Todo {
	private int no;
	@NonNull
	private String todo;
	private LocalDateTime createdAt;
	private LocalDateTime completedAt;
}

 

Controller

TodoController

@Controller
@RequestMapping("/todo")
@Slf4j
public class TodoController {
	@Autowired
	private TodoService todoService;
	
	@GetMapping("/todoList.do")
	public void todoList(Model model) {
		try {
			List<Todo> list = todoService.selectAllTodo();
			log.debug("list = {}", list);
			model.addAttribute("list", list);
		} catch(Exception e) {
			log.error(e.getMessage(), e);
			throw e;
		}
	}
}

 

Service

TodoService interface 생략

TodoServiceImpl

@Service
public class TodoServiceImpl implements TodoService {
	@Autowired
	private TodoDao todoDao;
	
	@Override
	public List<Todo> selectAllTodo() {
		return todoDao.selectAllTodo();
	}
}

 

Dao

TodoDao interface

@Mapper
public interface TodoDao {
	
	@Select("select * from (select * from todo where completed_at is null order by no) "
			+ "union all "
			+ "select * from (select * from todo where completed_at is not null order by completed_at desc)")
	List<Todo> selectAllTodo();

}

완료하지 않은 할 일은 asc정렬, 완료한 할 일은 desc 정렬을 위해 union all을 사용하였습니다.

union all 외에도 두 조건에 따라 정렬을 다르게 할 필요가 있다면 아래와 같은 방법들을 사용할 수도 있습니다.

(decode, case when, nulls first ...)

select * from todo order by decode(completed_at, null, 1), completed_at desc;

select * from todo order by (case when completed_at is null then 1 end), completed_at desc;

select * from todo order by completed_at desc nulls first, no;

 

todoList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<jsp:include page="/WEB-INF/views/common/header.jsp">
	<jsp:param value="할일" name="title"/>
</jsp:include>

<style>
div#todo-container{width:60%; margin:0 auto;text-align:center;}
</style>
<div id="todo-container">
    <form action="${pageContext.request.contextPath}/todo/insertTodo.do" class="form-inline" method="post">
        <input type="text" class="form-control col-sm-10 ml-1" name="todo" placeholder="할일" required/>&nbsp;
        <button class="btn btn-outline-success" type="submit" >저장</button>
    </form>

    <br />
    <!-- 할일목록 -->
	<table class="table">
	    <tr>
	      <th>번호</th>
	      <th>완료여부</th>
	      <th>할일</th>
	      <th>생성일</th>
	      <th>완료일</th>
	      <th>삭제</th>
	    </tr>
	    <c:if test="${empty list}">
	    	<tr>
	    		<td colspan="6" class="text-center">작성한 할일이 없습니다.</td>
	    	</tr>
	    </c:if>
	    <c:if test="${not empty list}">
	    	<c:forEach items="${list}" var="todo">
			    <tr>
			    	<td>${todo.no}</td>
			    	<td>
			    		<input type="checkbox" name="completed" id="completed" ${not empty todo.completedAt ? 'checked' : ''}/>
			    	</td>
			    	<td>${todo.todo}</td>
			    	<td>
			    		<fmt:parseDate value="${todo.createdAt}" var="createdAt" pattern="yyyy-MM-dd'T'HH:mm" />
			    		<fmt:formatDate value="${createdAt}" pattern="yy/MM/dd HH:mm"/>
			    	</td>
			    	<td>
			    		<c:if test="${not empty todo.completedAt}">			    		
				    		<fmt:parseDate value="${todo.completedAt}" var="completedAt" pattern="yyyy-MM-dd'T'HH:mm" />
				    		<fmt:formatDate value="${completedAt}" pattern="yy/MM/dd HH:mm"/>
			    		</c:if>
			    	</td>
			    	<td>
			    		<button class="btn btn-outline-danger" type="button" >삭제</button>
			    	</td>
			    </tr>
	    	</c:forEach>
	    </c:if>
	</table>
</div>
<jsp:include page="/WEB-INF/views/common/footer.jsp"></jsp:include>

이처럼 완료하지 않은 할일은 asc 정렬, 완료한 할일은 desc 정렬 처리가 된 것을 확인할 수 있습니다.

 

---

할 일 추가

 

Controller

TodoController

@PostMapping("/insertTodo.do")
public String insertTodo(Todo todo, RedirectAttributes redirectAttr) {
    try {
        int result = todoService.insertTodo(todo);
        redirectAttr.addFlashAttribute("msg", "할일을 추가하였습니다.");

        return "redirect:/todo/todoList.do";
    } catch(Exception e) {
        log.error(e.getMessage(), e);
        throw e;
    }
}

 

Service

TodoService interface 생략

TodoServiceImpl

@Override
public int insertTodo(Todo todo) {
    return todoDao.insertTodo(todo);
}

 

Dao

TodoDao interface

@Insert("insert into todo values (seq_todo_no.nextval, #{todo}, default, null)")
int insertTodo(Todo todo);