Servlet + JSP STEP 4 - Memo

★ Memo

 

■ 필요 프로젝트 및 파일

• Memo - Dynamic Web Project
• Context root : memo
• Generate web.xml 생성
• 파일 생성
• lib  > ojdbc.jar, mylibrary.jar, jstl-1.2.jar

■ lombok.jar 파일 다운

• 이클립스 종료 후 , lombok.jar파일 실행
• 실행 후, eclipse.exe 경로 선택 후, install
• lombok -> getter, setter, toString 등 오버라이딩으로 대신 처리해주는 jar파일

 

■ SqlDeveloper 실행

• 데이터 베이스 생성

drop table tblMemo;

create table tblCategory (
    seq number primary key,         --번호(PK)
    name varchar2(50) not null,     -- 카테고리명
    icon varchar2(100) not null,     -- 아이콘
    color varchar2(10) not null      -- 색상
);
create sequence seqCategory;

create table tblMemo (
    seq number primary key,     -- 번호(PK)
    memo varchar2(2000) not null,  -- 메모
    regdate date default sysdate not null, --작성시각
    cseq number not null references tblCategory(seq) -- 카테고리
);
create sequence seqMemo;

insert into tblCategory values (seqCategory.nextVal, '할일', 'receipt_long', 'F97B22');
insert into tblCategory values (seqCategory.nextVal, '장보기', 'shopping_cart', 'FF6969');
insert into tblCategory values (seqCategory.nextVal, '코딩', 'developer_mode', '2A2F4F');

insert into tblMemo values (seqMemo.nextVal, '메모입니다.', default, 1);

commit;

 

■ 필요 상세 파일

- "com.test.memo"
	- "List.java" //목록
	- "Add.java" //글쓰기
	- "Edit.java" //수정하기
	- "Del.java" //삭제하기
    	- "MemoDAO.java"
    	- "MemoDTO.java"
    	- "CategoryDTO.java"
	
- webapp > WEB-INF > "views"
	- "list.jsp" //목록
	- "add.jsp" //글쓰기
	- "edit.jsp" //수정하기
	- "del.jsp" //삭제하기
	> views > "inc"
            - "asset.jsp"
            - "header.jsp"

 

■ List.java

package com.test.memo;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/list.do")
public class List extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		//List.java
		
		//1. DB 작업 > select
		//2. 결과 반환 > JSP 호출하기
		
		MemoDAO dao = new MemoDAO();
		
		java.util.List<MemoDTO> list = dao.list();

		req.setAttribute("list", list);

		RequestDispatcher dispatcher = req.getRequestDispatcher("/WEB-INF/views/list.jsp");
		dispatcher.forward(req, resp);
	}

}

 

■ Add.java

package com.test.memo;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/add.do")
public class Add extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		//Add.java
		
		//1. DB 작업 > select 카테고리
		
		MemoDAO dao = new MemoDAO();
		
		java.util.List<CategoryDTO> clist = dao.clist(); 
		
		req.setAttribute("clist", clist);

		RequestDispatcher dispatcher = req.getRequestDispatcher("/WEB-INF/views/add.jsp");
		dispatcher.forward(req, resp);
	}//doGet
	
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		//AddOk.java 역할
		//1. 데이터 가져오기
		//2. DB 작업 > insert
		//3. 피드백
		
		//1. 
		// 인코딩
		req.setCharacterEncoding("UTf-8");
		
		String memo = req.getParameter("memo");
		String category = req.getParameter("category");
		
		//2. 
		MemoDAO dao = new MemoDAO();
		
		MemoDTO dto = new MemoDTO();
		dto.setMemo(memo);
		dto.setCseq(category);
		
		int result = dao.add(dto);
		
		if(result == 1) {
			resp.sendRedirect("/memo/list.do");
		} else {
			PrintWriter writer = resp.getWriter();
			writer.write("<script>alert('failed'); history.back();</script>");
			writer.close();
		}
	}

}

 

■ Edit.java

package com.test.memo;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/edit.do")
public class Edit extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		//Edit.java
		
		//1. 번호 가져오기
		//2. DB 작업 > select
		//3. 결과 반환 > JSP 호출하기
		
		String seq = req.getParameter("seq");
		
		MemoDAO dao = new MemoDAO();
		
		MemoDTO dto = dao.get(seq);
		
		java.util.List<CategoryDTO> clist = dao.clist(); 
		
		req.setAttribute("clist", clist);
		
		req.setAttribute("dto", dto);

		RequestDispatcher dispatcher = req.getRequestDispatcher("/WEB-INF/views/edit.jsp");
		dispatcher.forward(req, resp);
	}
	
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		//EditOk.java 역할
		//1. 데이터 가져오기
		//2. DB 작업 > Update
		//3. 피드백
		
		//1. 
		// 인코딩
		req.setCharacterEncoding("UTf-8");
		
		String memo = req.getParameter("memo");
		String category = req.getParameter("category");
		String seq = req.getParameter("seq");
		
		
		//2. 
		MemoDAO dao = new MemoDAO();
		
		MemoDTO dto = new MemoDTO();
		dto.setMemo(memo);
		dto.setCseq(category);
		dto.setSeq(seq); //수정할 메모 번호
		
		int result = dao.edit(dto);
		
		if(result == 1) {
			resp.sendRedirect("/memo/list.do");
		} else {
			PrintWriter writer = resp.getWriter();
			writer.write("<script>alert('failed'); history.back();</script>");
			writer.close();
		}
		
	}

}

 

■ Del.java

package com.test.memo;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/del.do")
public class Del extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		//Del.java
		
		//delOk.java 역할
		//1. 데이터 가져오기
		//2. DB 작업 > delete
		//3. 피드백
		
		//1. 
		// 인코딩
		req.setCharacterEncoding("UTf-8");
		
		String seq = req.getParameter("seq");
		
		
		//2. 
		MemoDAO dao = new MemoDAO();
		
		int result = dao.del(seq);
		
		if(result == 1) {
			resp.sendRedirect("/memo/list.do");
		} else {
			PrintWriter writer = resp.getWriter();
			writer.write("<script>alert('failed'); history.back();</script>");
			writer.close();
		}
		
	}

}

 

■ MemoDAO.java

package com.test.memo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.test.my.DBUtil;

public class MemoDAO {

   private Connection conn;
   private Statement stat;
   private PreparedStatement pstat;
   private ResultSet rs;
   
   public MemoDAO() {
      this.conn = DBUtil.open();
   }

   public List<CategoryDTO> clist() {

      try {
         
         String sql = "select seq, name from tblCategory order by name asc";
         
         stat = conn.createStatement();
         rs = stat.executeQuery(sql);
         
         List<CategoryDTO> clist = new ArrayList<CategoryDTO>();
         
         while (rs.next()) {
            
            CategoryDTO dto = new CategoryDTO();
            
            dto.setSeq(rs.getString("seq"));
            dto.setName(rs.getString("name"));
            
            clist.add(dto);
            
         }
         
         return clist;
         
         
      } catch (Exception e) {
         e.printStackTrace();
      }
      
      return null;
   }

   public int add(MemoDTO dto) {

      try {
         
         String sql = "insert into tblMemo values (seqMemo.nextVal, ?, default, ?)";
         
         pstat = conn.prepareStatement(sql);
         pstat.setString(1, dto.getMemo());
         pstat.setString(2, dto.getCseq());
         
         return pstat.executeUpdate();
         
      } catch (Exception e) {
         e.printStackTrace();
      }
      
      return 0;
   }

   public List<MemoDTO> list() {

      try {
         
         String sql = "select \r\n"
               + "    tblMemo.*,\r\n"
               + "    (select icon from tblCategory where seq = tblMemo.cseq) as icon,\r\n"
               + "    (select color from tblCategory where seq = tblMemo.cseq) as color\r\n"
               + "from tblMemo order by seq desc";
         
         stat = conn.createStatement();
         rs = stat.executeQuery(sql);
         
         List<MemoDTO> list = new ArrayList<MemoDTO>();
         
         while (rs.next()) {
            
            MemoDTO dto = new MemoDTO();
            
            dto.setSeq(rs.getString("seq"));
            dto.setMemo(rs.getString("memo"));
            dto.setRegdate(rs.getString("regdate"));
            dto.setCseq(rs.getString("cseq"));
            
            dto.setIcon(rs.getString("icon"));
            dto.setColor(rs.getString("color"));
            
            list.add(dto);
            
         }
         
         return list;
         
      } catch (Exception e) {
         e.printStackTrace();
      }
      
      return null;
   }

	public MemoDTO get(String seq) {
			
		try {
			
			String sql = "select * from tblMemo where seq = ?";
			
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, seq);
			
			rs = pstat.executeQuery();
			
			if (rs.next()) {
				
				MemoDTO dto = new MemoDTO();
				
				dto.setSeq(rs.getString("seq"));
				dto.setMemo(rs.getString("memo"));
				dto.setRegdate(rs.getString("regdate"));
				dto.setCseq(rs.getString("cseq"));
				
				return dto;
			}
				
		} catch (Exception e) {
				// TODO: handle exception
			}
		
		return null;
	}

	public int edit(MemoDTO dto) {
		
		try {
	         
	         String sql = "update tblMemo set memo = ?, cseq = ? where seq = ?";
	         
	         pstat = conn.prepareStatement(sql);
	         pstat.setString(1, dto.getMemo());
	         pstat.setString(2, dto.getCseq());
	         pstat.setString(3, dto.getSeq());
	         
	         return pstat.executeUpdate();
	         
	      } catch (Exception e) {
	         e.printStackTrace();
	      }
		
		return 0;
	}

	public int del(String seq) {
		
		try {
	         
	         String sql = "delete from tblMemo where seq = ?";
	         
	         pstat = conn.prepareStatement(sql);
	         pstat.setString(1, seq);
	         
	         return pstat.executeUpdate();
	         
	      } catch (Exception e) {
	         e.printStackTrace();
	      }
		
		return 0;
	}
     
}

 

■ MemoDTO.java

package com.test.memo;

import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

//코드 자동생성 + 코드 축소
/*@Setter
@Getter
@ToString*/
@Data //@ToString + @Getter + @Setter + @EqualsAndHashCode + @RequiredArgsConstructor

public class MemoDTO {
	private String seq;
	private String memo;
	private String regdate;
	private String cseq;
}

 

■ CategoryDTO.java

package com.test.memo;

import lombok.Data;

@Data
public class CategoryDTO {
	private String seq;
	private String name;
	private String icon;
	private String color;
}

 

■ list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Memo</title>
<%@ include file="/WEB-INF/views/inc/asset.jsp" %>
<style>
   
   main {
      padding: 10px;
   }
   
   main::after {
      content: '';
      display: block;
      clear: both;
   }
   
   .item {
      /* display: inline-block; */
      float: left;
      width: 180px;
      height: 200px;
      margin: 10px 5px;
      position: relative;
      left: 0;
      top: 0;
   }
   
   .item > span {
      position: absolute;
      right: 3px;
      top: 0px;
      font-size: 2rem;
   }
   
   .item > div:nth-child(2) {
      margin: 30px 15px;
   }

   .item > div:nth-child(3) {
      position: absolute;
      right: 5px;
      bottom: 3px;
      font-size: 12px;
      color: #999;
   }
   
   .item > div:nth-child(4) {
      position: absolute;
      left: 3px;
      bottom: 3px;
      font-size: 12px;
      display: none;
   }

   .item:hover > div:nth-child(4) {
      display: block;
   }
   
   .item > div:nth-child(4) > span {
   		cursor : pointer;
   }
   
   
   
</style>
</head>
<body>
   <!-- list.jsp -->
   <h1>Memo <small>List</small></h1>
   
   <main>

      <c:forEach items="${list}" var="dto">
      <div class="item" style="background-color: #${dto.color};">
         <span class="material-symbols-outlined">${dto.icon}</span>
         <div>${dto.memo}</div>
         <div>${dto.regdate}</div>
         <div>
            <span onclick="location.href='/memo/edit.do?seq=${dto.seq}';">[e]</span>
            <span onclick="del(${dto.seq})">[d]</span>
         </div>
      </div>
      </c:forEach>
      
   </main>
   
   <div>
      <input type="button" value="추가하기"
         onclick="location.href='/memo/add.do';">
   </div>


<script>

	function del(seq) {
		
		if (confirm('delete')) {
			location.href = '/memo/del.do?seq=' + seq;
		}
		
	}

</script>
</body>
</html>

 

■ add.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Memo</title>
<%@ include file="/WEB-INF/views/inc/asset.jsp" %>
<style>
	th {
		width : 200px;
	}
	
	textarea {
		width : 520px;
	}

	select {
		width : 535px;
	}
</style>
</head>
<body>
	<!-- add.jsp -->
	<h1>Memo <samll>Add</samll></h1>
	
	<form method="POST" action="/memo/add.do">
		<table>
			<tr>
				<th>메모</th>
				<td><textarea name="memo" id="memo" required></textarea></td>
			</tr>
			<tr>
				<th>카테고리</th>
				<td>
					<select name="category" id="category">
						<c:forEach items="${clist}" var="cdto">
							<option value="${cdto.seq}">${cdto.name}</option>
						</c:forEach>
					</select>
				</td>
			</tr>
		</table> 
		
		<div>
			<input type="button" value="돌아가기" onclick="location.href='/memo/list.do';">
			<input type="submit" value="추가하기" class="primary">
		</div>
	</form>

<script>
</script>
</body>
</html>

 

■ edit.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Memo</title>
<%@ include file="/WEB-INF/views/inc/asset.jsp" %>
<style>
	th {
		width : 200px;
	}
	
	textarea {
		width : 520px;
	}

	select {
		width : 535px;
	}
</style>
</head>
<body>
	<!-- add.jsp -->
	<h1>Memo <small>Edit</small></h1>
	
	<form method="POST" action="/memo/edit.do">
		<table>
			<tr>
				<th>메모</th>
				<td><textarea name="memo" id="memo" required>${dto.memo}</textarea></td>
			</tr>
			<tr>
				<th>카테고리</th>
				<td>
					<select name="category" id="category">
						<c:forEach items="${clist}" var="cdto">
							<option value="${cdto.seq}">${cdto.name}</option>
						</c:forEach>
					</select>
				</td>
			</tr>
		</table> 
		
		<div>
			<input type="button" value="돌아가기" onclick="location.href='/memo/list.do';">
			<input type="submit" value="수정하기" class="primary">
		</div>
		<input type="hidden" name="seq" value="${dto.seq}">
	</form>

<script>

// 	alert(${dto.cseq});
	$('#category').val(${dto.cseq});
	
</script>
</body>
</html>

 

■ del.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Memo</title>
<%@ include file="/WEB-INF/views/inc/asset.jsp" %>
<style>
	

</style>
</head>
<body>
	<!-- del -->
	<h1>Memo <small>부제</small></h1>
	
	콘텐츠 


<script>
</script>
</body>
</html>

 

■ asset.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<!--  inc > asset.jsp -->
<link rel="stylesheet" href="https://me2.do/5BvBFJ57">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.4/jquery.min.js"></script>

 

 

★ 실행 결과 

• 리스트

 

• 추가

 

• 수정