[JSP & Servlet] 7장 미니 프로젝트 만들기

프로젝트 추가/수정/삭제하기

0) 파일 구조 확인

1) PRJ_MEMBS 테이블 생성 쿼리

- PRJ_MEMBS 테이블 역할 : PROJECT 테이블과 MEMBERS 테이블을 연결해줌

CREATE TABLE PROJECTS (
	PNO INTEGER NOT NULL COMMENT '프로젝트 일련번호',
    PNAME VARCHAR(225) NOT NULL COMMENT '프로젝트명',
    CONTENT TEXT NOT NULL COMMENT '설명',
    STA_DATE DATETIME NOT NULL COMMENT '시작일',
    END_DATE DATETIME NOT NULL COMMENT '종료일',
    STATE INTEGER NOT NULL COMMENT '상태',
    CRE_DATE DATETIME NOT NULL COMMENT '생성일',
    TAGS VARCHAR(225) NOT NULL COMMENT '태그'
)
COMMENT '프로젝트';

ALTER TABLE PROJECTS
ADD CONSTRAINT PK_PROJECTS
PRIMARY KEY (PNO);

ALTER TABLE PROJECTS
MODIFY COLUMN PNO INTEGER NOT NULL AUTO_INCREMENT COMMENT '프로젝트 일련번호';

CREATE TABLE PRJ_MEMBS (
	PNO INTEGER NOT NULL COMMENT '프로젝트 일련번호',
    MNO INTEGER NOT NULL COMMENT '회원 일련번호',
    LEVEL INTEGER NOT NULL COMMENT '등급',
    STATE INTEGER NOT NULL COMMENT '상태',
    MOD_DATE DATETIME NOT NULL COMMENT '상태변경일'
)
COMMENT '프로젝트멤버';

ALTER TABLE PRJ_MEMBS
ADD CONSTRAINT PK_PRJ_MEMBS
PRIMARY KEY(PNO, MNO);

2) Project.java

package spms.vo;

import java.sql.Date;

public class Project {
	protected int no;
	protected String title;
	protected String content;
	protected Date startDate;
	protected Date endDate;
	protected int state;
	protected Date createdDate;
	protected String tags;
	public int getNo() {
		return no;
	}
	public Project setNo(int no) {
		this.no = no;
		return this;
	}
	public String getTitle() {
		return title;
	}
	public Project setTitle(String title) {
		this.title = title;
		return this;
	}
	public String getContent() {
		return content;
	}
	public Project setContent(String content) {
		this.content = content;
		return this;
	}
	public Date getStartDate() {
		return startDate;
	}
	public Project setStartDate(Date startDate) {
		this.startDate = startDate;
		return this;
	}
	public Date getEndDate() {
		return endDate;
	}
	public Project setEndDate(Date endDate) {
		this.endDate = endDate;
		return this;
	}
	public int getState() {
		return state;
	}
	public Project setState(int state) {
		this.state = state;
		return this;
	}
	public Date getCreatedDate() {
		return createdDate;
	}
	public Project setCreatedDate(Date createdDate) {
		this.createdDate = createdDate;
		return this;
	}
	public String getTags() {
		return tags;
	}
	public Project setTags(String tags) {
		this.tags = tags;
		return this;
	}	
}

3) projectDAO

- DAO 객체 인터페이스

package spms.dao;

import java.util.List;

import spms.vo.Project;

public interface ProjectDAO {
	List<Project> selectlist() throws Exception;
	int insert(Project project) throws Exception;
	Project selectOne(int no) throws Exception;
	int update(Project project) throws Exception;
	int delete(int no) throws Exception;
}

4) ProjectListController.java

- selectList()를 호출할 페이지컨트롤러 만들기

package spms.controller;

import java.util.Map;

import spms.dao.ProjectDAO;

public class ProjectListController implements Controller {
	ProjectDAO projectDAO;
	
	public ProjectListController setProjectDAO(ProjectDAO projectDAO) {
		this.projectDAO = projectDAO;
		return this;
	}
	
	@Override
	public String execute(Map<String, Object> model) throws Exception {
		model.put("projects", projectDAO.selectlist());
		return "/project/ProjectList.jsp";
	}
}

5) ProjectList.jsp

- 목록 화면 구현하기

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>프로젝트 목록</title>
</head>
<body>
	<jsp:include page="/Header.jsp"/>
	<h1>프로젝트 목록</h1>
	<p><a href="add.do">신규 프로젝트</a></p>
	<table border="1">
		<tr>
			<th>번호</th>
			<th>제목</th>
			<th>시작일</th>
			<th>종료일</th>
			<th>상태</th>
			<th> </th>
		</tr>
		<c:forEach var="project" items="${projects }">
			<tr>
				<td>${project.no}</td>
				<td><a href="update.do?no=${project.no }">${project.title }</a></td>
				<td>${project.startDate}</td>
				<td>${project.endDate}</td>
				<td>${project.state}</td>
				<td><a href="delete.do?no=${project.no }">[삭제]</a></td>
			</tr>
		</c:forEach>
	</table>
	<jsp:include page="/Tail.jsp"/>
</body>
</html>

6) ProjectAddController

package spms.controller;

import java.util.Map;

import spms.annotation.Component;
import spms.bind.DataBinding;
import spms.dao.ProjectDAO;
import spms.vo.Project;

@Component("/project/add.do")
public class ProjectAddController implements Controller, DataBinding {
	ProjectDAO projectDAO;
	
	public ProjectAddController setProjectDAO(ProjectDAO projectDAO) {
		this.projectDAO = projectDAO;
		return this;
	}
	
	@Override
	public Object[] getDataBinders() {
		return new Object[] {
				"project", spms.vo.Project.class
		};
	}
	
	@Override
	public String execute(Map<String, Object> model) throws Exception {
		Project project = (Project)model.get("project");
		if(project.getTitle() == null) {
			return "/project/ProjectForm.jsp";
		} else {
			projectDAO.insert(project);
			return "redirect:list.do";
		}
	}
}

7) ProjectForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>프로젝트 등록</title>
<style>
	ul {
		padding : 0;
	}
	li {
		list-style : none;
	}
	label {
		float : left;
		text-align : right;
		width : 60px;
	}
</style>
</head>
<body>
	<jsp:include page="/Header.jsp"/>
	<h1>프로젝트 등록</h1>
	<form action="add.do" method="post">
		<ul>
			<li>
				<label for="title">제목</label>
				<input id="title" name="title" type="text" size="50">
			</li>
			<li>
				<label for="content">내용</label>
				<textarea id="content" name="content" rows="5" cols="40"></textarea>
			</li>
			<li>
				<label for="startDate">시작일</label>
				<textarea id="startDate" name="startDate" type="text" placeholder="예)2013-01-01">
			</li>
			<li>
				<label for="endDate">종료일</label>
				<textarea id="endDate" name="endDate" type="text" placeholder="예)2013-01-01">
			</li>
			<li>
				<label for="tags">태그</label>
				<textarea id="tags" name="tags" type="text" placeholder="태그1 태그2 태그3" size="50">
			</li>
		</ul>
		<input type="submit" value="추가">
		<input type="reset" value="취소">
	</form>
	<jsp:include page="/Tail.jsp"/>
</body>
</html>

8) ProjectList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>프로젝트 목록</title>
</head>
<body>
	<jsp:include page="/Header.jsp"/>
	<h1>프로젝트 목록</h1>
	<p><a href="add.do">신규 프로젝트</a></p>
	<table border="1">
		<tr>
			<th>번호</th>
			<th>제목</th>
			<th>시작일</th>
			<th>종료일</th>
			<th>상태</th>
			<th></th>
		</tr>
		<c:forEach var="project" items="${projects }">
			<tr>
				<td>${project.no}</td>
				<td><a href="update.do?no=${project.no }">${project.title }</a></td>
				<td>${project.startDate}</td>
				<td>${project.endDate}</td>
				<td>${project.state}</td>
				<td><a href="delete.do?no=${project.no }">[삭제]</a></td>
			</tr>
		</c:forEach>
	</table>
	<jsp:include page="/Tail.jsp"/>
</body>
</html>

9) ProjectUpdateForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>프로젝트 정보</title>
<style>
	ul {
		padding : 0;
	}
	li {
		list-style : none;
	}
	label {
		float : left;
		text-align : rigth;
		width : 60px;
	}
</style>
</head>
<body>
	<jsp:include page="/Header.jsp"/>
	<h1>프로젝트 정보</h1>
	<form action="update.do" method="post">
		<ul>
			<li>
				<label for="no">번호</label>
				<input type="text" id="no" name="no" size="5" value="${project.no }" readonly>
			</li>
			<li>
				<label for="title">제목</label>
				<input type="text" id="title" name="title" size="50" value="${project.title }">
			</li>
			<li>
				<label for="content">내용</label>
				<textarea id="content" name="content" rows="5" cols="40">${project.content }</textarea>
			</li>
			<li>
				<label for="startDate">시작일</label>
				<input type="text" id="startDate" name="startDate" placeholder="예)2013-01-01" value="${project.startDate }">
			</li>
			<li>
				<label for="endDate">종료일</label>
				<input type="text" id="endDate" name="endDate" placeholder="예)2013-01-01" value="${project.endDate }">
			</li>
			<li>
				<label for="state">상태</label>
				<select id="state" name="state">
					<option value="0" ${project.state==0?"selected":""}>준비</option>
					<option value="1" ${project.state==1?"selected":""}>진행</option>
					<option value="2" ${project.state==2?"selected":""}>완료</option>
					<option value="3" ${project.state==3?"selected":""}>취소</option>
				</select>
			</li>
			<li>
				<label for="tags">태그</label>
				<input type="text" id="tags" name="tags" placeholder="예)태그1 태그2 태그3" size="50" value="${project.tags }">
			</li>
		</ul>
		<input type="submit" value="저장">
		<input type="button" value="삭제" onclick="location.href='delete.do?no=${project.no}'">
		<input type="button" value="취소" onclick="location.href='list.do'">
	</form>
	<jsp:include page="/Tail.jsp"/>
</body>
</html>

10) MySqlProjectDAO.java

package spms.dao;

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 javax.sql.DataSource;

import spms.annotation.Component;
import spms.vo.Project;

@Component("projectDAO")
public class MySqlProjectDAO implements ProjectDAO{
	DataSource ds;
	
	public void setDataSource(DataSource ds) {
		this.ds = ds;
	}
	
	@Override
	public List<Project> selectlist() throws Exception {
		Connection connection = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sqlSelect = "SELECT * FROM PROJECTS ORDER BY PNO DESC";
		
		try {
			connection = ds.getConnection();
			stmt = connection.createStatement();
			rs = stmt.executeQuery(sqlSelect);
			
			ArrayList<Project> projects = new ArrayList<Project>();
			while(rs.next()) {
				projects.add(new Project()
										  .setNo(rs.getInt("PNO"))
										  .setTitle(rs.getString("PNAME"))
										  .setStartDate(rs.getDate("STA_DATE"))
										  .setEndDate(rs.getDate("END_DATE"))
										  .setState(rs.getInt("STATE")));	
			}
			
			return projects;
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(rs != null) {
					rs.close();
				}
			} catch(Exception e) {
				throw e;
			}
			
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				throw e;
			}
			
			try {
				if(connection != null) {
					connection.close();
				}
			} catch(Exception e) {
				throw e;
			}
		}
	}
	
	@Override
	public int insert(Project project) throws Exception {
		Connection connection = null;
		PreparedStatement stmt = null;
		
		String sqlInsert = "INSERT INTO PROJECTS(PNAME, CONTENT, STA_DATE, END_DATE, STATE, CRE_DATE ,TAGS)" +
									   "VALUES(?, ?, ?, ?, 0, NOW(), ?)";
		
		try {
			connection = ds.getConnection();
			stmt = connection.prepareStatement(sqlInsert);
			stmt.setString(1, project.getTitle());
			stmt.setString(2, project.getContent());
			stmt.setDate(3, new java.sql.Date(project.getStartDate().getTime()));
			stmt.setDate(4, new java.sql.Date(project.getEndDate().getTime()));
			stmt.setString(5, project.getTags());
			
			return stmt.executeUpdate();
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				throw e;
			}
			
			try {
				if(connection != null) {
					connection.close();
				}
			} catch(Exception e) {
				throw e;
			}
		}
	}
	
	@Override
	public Project selectOne(int no) throws Exception {
		Connection connection = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String sqlSelectOne = "SELECT * FROM PROJECTS WHERE PNO=" + no;
		
		try {
			connection = ds.getConnection();
			stmt = connection.createStatement();
			rs = stmt.executeQuery(sqlSelectOne);
			if(rs.next()) {
				return new Project()
									.setNo(rs.getInt("PNO"))
									.setTitle(rs.getString("PNAME"))
									.setContent(rs.getString("CONTENT"))
									.setStartDate(rs.getDate("STA_DATE"))
									.setEndDate(rs.getDate("END_DATE"))
									.setState(rs.getInt("STATE"))
									.setCreatedDate(rs.getDate("CRE_DATE"))
									.setTags(rs.getString("TAGS"));
			} else {
				throw new Exception("해당 번호의 프로젝트를 찾을 수 없습니다.");
			}
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(rs != null) {
					rs.close();
				}
			} catch(Exception e) {
				throw e;
			}
			
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				throw e;
			}
			
			try {
				if(connection != null) {
					connection.close();
				}
			} catch(Exception e) {
				throw e;
			}
		}
	}
	
	public int update(Project project) throws Exception {
		Connection connection = null;
		PreparedStatement stmt = null;
		
		String sqlUpdate = "UPDATE PROJECTS SET PNAME=?, CONTENT=?, STA_DATE=?, END_DATE=?, STATE=?, TAGS=? WHERE PNO=?";
		
		try {
			connection = ds.getConnection();
			stmt = connection.prepareStatement(sqlUpdate);
			stmt.setString(1, project.getTitle());
			stmt.setString(2, project.getContent());
			stmt.setDate(3, new java.sql.Date(project.getStartDate().getTime()));
			stmt.setDate(4, new java.sql.Date(project.getEndDate().getTime()));
			stmt.setInt(5, project.getState());
			stmt.setString(6, project.getTags());
			stmt.setInt(7, project.getNo());
			
			return stmt.executeUpdate();
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				throw e;
			}
			
			try {
				if(connection != null) {
					connection.close();
				}
			} catch(Exception e) {
				throw e;
			}
		}
	}
	
	@Override
	public int delete(int no) throws Exception {
		Connection connection = null;
		Statement stmt = null;
		
		String sqlDelete = "DELETE FROM PROJECTS WHERE PNO=" + no;
		
		try {
			connection = ds.getConnection();
			stmt = connection.createStatement();
			return stmt.executeUpdate(sqlDelete);
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				throw e;
			}
			
			try {
				if(connection != null) {
					connection.close();
				}
			} catch(Exception e) {
				throw e;
			}
		}
	}
}

11) ProjectUpdateController.java

package spms.controller;

import java.util.Map;

import spms.annotation.Component;
import spms.bind.DataBinding;
import spms.dao.ProjectDAO;
import spms.vo.Project;

@Component("/project/update.do")
public class ProjectUpdateController implements Controller, DataBinding {
	ProjectDAO projectDAO;
	
	public ProjectUpdateController setProjectDAO(ProjectDAO projectDAO) {
		this.projectDAO = projectDAO;
		return this;
	}
	
	@Override
	public Object[] getDataBinders() {
		return new Object[] {
				"no", Integer.class,
				"project", spms.vo.Project.class
		};
	}
	
	@Override
	public String execute(Map<String, Object> model) throws Exception {
		Project project = (Project)model.get("project");
		
		if(project.getTitle() == null) {
			Integer no = (Integer)model.get("no");
			Project detailInfo = projectDAO.selectOne(no);
			model.put("project", detailInfo);
			return "/project/ProjectUpdateForm.jsp";
		} else {
			projectDAO.update(project);
			return "redirect:list.do";
		}
	}
}

12) ProjectDeleteController.java

package spms.controller;

import java.util.Map;

import spms.annotation.Component;
import spms.bind.DataBinding;
import spms.dao.ProjectDAO;

@Component("/project/delete.do")
public class ProjectDeleteController implements Controller, DataBinding {
	ProjectDAO projectDAO;
	
	public ProjectDeleteController set ProjectDAO(ProjectDAO projectDAO) {
		this.projectDAO = projectDAO;
		return this;
	}
	
	@Override
	public Object[] getDataBinders() {
		return new Object[] {
				"no", Integer.class
		};
	}
	
	@Override
	public String execute(Map<String, Object> model) throws Exception {
		Integer no = (Integer)model.get("no");
		projectDAO.delete(no);
		
		return "redirect:list.do";
	}
}

13) Header.jsp

<%@ page import="spms.vo.Member" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%
	//HttpSession 데이터 보관소에 저장된 member 객체 꺼내서 사용
	//Member member = (Member)session.getAttribute("Member");
%>
<div style="background-color:#00008b; color:#ffffff; height:20px; padding:5px">
SPMS(Simple Project Management System)
<span style="float:right;">
	<a style="color:white;" href="<%=request.getContextPath() %>/project/list.do">프로젝트</a>
	<a style="color:white;" href="<%=request.getContextPath() %>/member/list.do">회원</a>
	
	<c:if test="${empty Member or empty Member.email }">
		<a style="color:white;" href="<%=request.getContextPath() %>/auth/login.do">로그인</a>
	</c:if>
	
	<c:if test="${!empty Member and !empty Member.email }">
		${Member.name }
		<a style="color:white;" href="<%=request.getContextPath() %>/auth/logout.do">로그아웃</a>	
	</c:if>
</span>
</div>

14) 실행 화면

- 새 프로젝트 등록

- 목록에 추가됨

- 제목을 누르면 수정 페이지로 이동

- My Prpject -> My Project2로 변경

- 삭제 버튼을 누르면 프로젝트가 삭제됨

저작자표시 비영리 변경금지

'👨‍💻 2. 웹개발_Back end > 2-4 JSP & Servlet' 카테고리의 다른 글

[JSP & Servlet] 6장 미니 MVC 프레임워크 만들기 (6) 애노테이션을 이용한 객체 관리 (0)	2021.10.19
[JSP & Servlet] 6장 미니 MVC 프레임워크 만들기 (5) 프로퍼티를 이용한 객체관리 (0)	2021.10.18
[JSP & Servlet] 6장 미니 MVC 프레임워크 만들기 (4) 리플랙션 API를 이용하여 프런트 컨트롤러 개선하기 (0)	2021.10.18
[JSP & Servlet] 6장 미니 MVC 프레임워크 만들기 (3) DI를 이용한 빈 의존성 관리 (0)	2021.10.18
[JSP & Servlet] 6장 미니 MVC 프레임워크 만들기 (2) 페이지 컨트롤러의 진화 (0)	2021.10.18