[JSP & Servlet] 프로젝트 관리

글을 작성하면서 DataBinding 하는 부분은 빼고 작성하도록 하겠다. 이 부분은 작성하지 않아도 코드를 수정하여 충분히 구현할 수 있을 것이라 본다.

 

📌테이블 생성

---

멤버(Members) 테이블

CREATE TABLE members(
	mno INT PRIMARY KEY,       --회원일련번호
    email VARCHAR(40) NOT NULL,--이메일 
    pwd VARCHAR(100) NOT NULL, --암호
    mname VARCHAR(50) NOT NULL,--이름
    cre_date DATETIME NOT NULL,--가입일
    mod_date DATETIME NOT NULL --마지막암호변경일
)
COMMENT '회원기본정보';
 
CREATE UNIQUE INDEX UIX_MEMBERS
ON members(  --회원 기본
    email ASC  --이메일
);

 

프로젝트(projects) 테이블

CREATE TABLE projects(
	pno INT AUTO_INCREMENT PRIMARY KEY, --프로젝트일련번호
	pname VARCHAR(255) NOT NULL,        --프로젝트명
	content TEXT NOT NULL,              --설명
	sta_date DATETIME NOT NULL,         --시작일
	end_date DATETIME NOT NULL,         --종료일
	state INT NOT NULL,                 --상태
	cre_date DATETIME NOT NULL,         --생성일
	tags VARCHAR(255) NULL              --태그
)
COMMENT '프로젝트';

 

프로젝트 멤버(projects member) 테이블

CREATE TABLE prj_membs(
	pno INT,
    mno INT,
    level INT NOT NULL,
    state INT NOT NULL,
    mod_date DATETIME NOT NULL,
    PRIMARY KEY(pno, mno)
);

 

 

 

📌VO(DTO)

---

Project 클래스

package spms.vo;
 
import java.util.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;
  }
}

 

 

 

 

 

 

📌DAO

---

DB를 MySQL를 사용할 수도 Oracle를 사용할 수도 있다. 만약 DB를 바꿔야하는 경우 그때마다 코드를 변경해야 하기 때문에 매우 번거로워 진다. 그렇기 때문에 구체적으로 클래스 이름을 명시하는 대신에 인터페이스를 사용하여, 그 자리에 다양한 구현체(인터페이스를 구현한 클래스)를 놓는 방식을 사용한다.

 

즉, 사용할 의존 객체에 대해 선택 폭을 넓히고 향후 확장을 고려하여 교체하기 쉽게 만들고 싶다면 인터페이스 문법을 토해 사용 규칙을 정의하고 그 규칙에 따라 호출하도록 코드를 작성하면 된다.

 

💡 DAO 인터페이스 생성 - ProjectDao

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;
}

 

💡DAO 구현체 생성 - MySqlProjectDao

안에 들어갈 코드들은 아래 내용을 작성하면서 채워넣도록 하겠다.

 

 

 

 

 

📌리스너(Listener) & 객체 관리 만들기

---

이 프로젝트에선 Listener 파일을 만들어 웹 애플리케이션이 시작할 때 객체 및 DB 커넥션을 생성 및 연결할 것이며, 프로퍼티 파일을 만들고 어노테이션을 활용하여 객체 관리를 할 것이다.

이에 대한 것은 다음 링크를 보면 참고 할 수 있다.

https://yeo-computerclass.tistory.com/233

리스너(Listener)란?

https://yeo-computerclass.tistory.com/234

프로퍼티(Property)를 이용한 객체 관리 (feat. 구현) + Annotation으로 지정하기

 

💡ContextLoaderListener

package spms.listeners;
 
import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;
 
import spms.context.ApplicationContext;
 
 
@WebListener    //리스너 배치 방법
public class ContextLoaderListener implements ServletContextListener {
	static ApplicationContext applicationContext;
	
	public static ApplicationContext getApplicationContext() {
		return applicationContext;
	}
 
	@Override
	public void contextInitialized(ServletContextEvent event) {
		try {
			
			ServletContext sc = event.getServletContext();
			
			String propertiesPath = sc.getRealPath(sc.getInitParameter("contextConfigLocation"));
			applicationContext = new ApplicationContext(propertiesPath);
					
			
		} catch (Throwable e) {
			e.printStackTrace();
		}
	}
 
	@Override
	public void contextDestroyed(ServletContextEvent event) {
	}
}

 

 

💡Component

package spms.annotation;
 
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
 
@Retention(RetentionPolicy.RUNTIME)
public @interface Component {
  String value() default "";
}

 

💡ApplicationContext

package spms.context;
 
import java.io.FileReader;
import java.lang.reflect.Method;
import java.util.Hashtable;
import java.util.Properties;
import java.util.Set;
 
import javax.naming.Context;
import javax.naming.InitialContext;
 
import org.reflections.Reflections;
 
import spms.annotation.Component;
 
// 프로퍼티 + 어노테이션을 이용한 객체 관리
public class ApplicationContext {
  Hashtable<String,Object> objTable = new Hashtable<String,Object>();
  
  public Object getBean(String key) {
    return objTable.get(key);
  }
  
  public ApplicationContext(String propertiesPath) throws Exception {
    Properties props = new Properties();
    props.load(new FileReader(propertiesPath));
    
    prepareObjects(props);
    prepareAnnotationObjects();
    injectDependency();
  }
  
  private void prepareAnnotationObjects() 
      throws Exception{
    Reflections reflector = new Reflections("");
    
    Set<Class<?>> list = reflector.getTypesAnnotatedWith(Component.class);
    String key = null;
    for(Class<?> clazz : list) {
      key = clazz.getAnnotation(Component.class).value();
      objTable.put(key, clazz.newInstance());
    }
  }
 
  private void prepareObjects(Properties props) throws Exception {
    Context ctx = new InitialContext();
    String key = null;
    String value = null;
    
    for (Object item : props.keySet()) {
      key = (String)item;
      value = props.getProperty(key);
      if (key.startsWith("jndi.")) {
        objTable.put(key, ctx.lookup(value));
      } else {
        objTable.put(key, Class.forName(value).newInstance());
      }
    }
  }
  
  private void injectDependency() throws Exception {
    for (String key : objTable.keySet()) {
      if (!key.startsWith("jndi.")) {
        callSetter(objTable.get(key));
      }
    }
  }
 
  private void callSetter(Object obj) throws Exception {
    Object dependency = null;
    for (Method m : obj.getClass().getMethods()) {
      if (m.getName().startsWith("set")) {
        dependency = findObjectByType(m.getParameterTypes()[0]);
        if (dependency != null) {
          m.invoke(obj, dependency);
        }
      }
    }
  }
  
  private Object findObjectByType(Class<?> type) {
    for (Object obj : objTable.values()) {
      if (type.isInstance(obj)) {
        return obj;
      }
    }
    return null;
  }
}

(자세한 설명은 위 링크에 있다.)

 

 

 

📌Header, Tail(Footer), Error 페이지

---

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" %>
       
<div style="background-color:#00008b;color:#ffffff;height:20px;padding: 5px;">
Header 부분입니다.
 
<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 sessionScope.member or 
              empty sessionScope.member.email}">
<a style="color:white;" href="<%=request.getContextPath()%>/auth/login.do">로그인</a>
</c:if>  
            
<c:if test="${!empty sessionScope.member and 
              !empty sessionScope.member.email}">
${sessionScope.member.name}
(<a style="color:white;" 
  href="<%=request.getContextPath()%>/auth/logout.do">로그아웃</a>)
</c:if>
</span>
</div>

 

Tail.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<div style="background-color:#f0fff0;height:20px;padding:5px; margin-top:10px">
Project 관리 시스템 &copy; 2022
</div>

 

Error.jsp

<%@ page 
	language="java" 
	contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
	"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>시스템 오류!</title>
</head>
<body>
<p>이곳은 Error Page입니다.
요청을 처리하다가 Error가 발생하였습니다.</p>
</body>
</html>

 

 

 

📌프로젝트 목록 List 만들기

---

 

• 프로젝트 목록을 List하는 JSP 만든다.
• [신규 등록] 클릭하면 프로젝트 등록 폼 출력
• [프로젝트 이름] 클릭하면 프로젝트의 상세 정보를 봄
• [삭제] 클릭하면 프로젝트 삭제됨

 

DAO 구현체에 메소드 추가 - MySqlProjectDao

@Override
	public List<Project> selectList() throws Exception {
		// TODO Auto-generated method stub
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT pno, pname, sta_date, end_date, state " +
								   "FROM projects " + 
					               "ORDER BY pno DESC");
			
			List<Project> projects = new ArrayList<Project>();
			
			while(rs.next()) {
				projects.add(new Project().setNo(rs.getInt(0))
						                  .setTitle(rs.getString(1))
						                  .setStartDate(rs.getDate(2))
						                  .setEndDate(rs.getDate(3))
						                  .setState(rs.getInt(4)));
			}
			
			return projects;
			
		}catch (Exception e){
			throw e;
		}finally {
			try{if(rs != null) rs.close();} catch(Exception e) {}
			try{if(stmt != null) rs.close();} catch(Exception e) {}
			try{if(conn != null) rs.close();} catch(Exception e) {}
		}
	}

 

 

페이지 컨트롤러 생성 -  ProjectListController

package spms.controls;
 
import java.util.Map;
 
import spms.annotation.Component;
import spms.dao.ProjectDao;
 
@Component("/project/list.do")
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 {
		// TODO Auto-generated method stub
		model.put("projects", projectDao.selectList());
		return "/project/ProjectList.jsp";
	}
}

 

 

뷰 컴포넌트 생성 - ProjectList.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>프로젝트 목록</title>
</head>
<body>
<jsp:include page="/Header.jsp"></jsp:include>
<h1>프로젝트 List 목록</h1>
<p><a href='add.do'>신규 프로젝트</a></p>
 
<table border="1">
<tr>
	<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"></jsp:include>
</body>
</html>

 

 

 

 

 

 

 

 

📌프로젝트 등록 구현

---

DAO 구현체에 메소드 추가 - MySqlProjectDao

	@Override
	public int insert(Project project) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement("INSERT INTO PROJECTS"
										+ "(PNAME, CONTENT, STA_DATE, END_DATE, STATE, CRE_DATE, TAGS)"
										+ " VALUES (?,?,?,?,0,NOW(),?)");
			pstmt.setString(1, project.getTitle());
			pstmt.setString(2, project.getContent());
			pstmt.setDate(3, new java.sql.Date(project.getStartDate().getTime()));
			pstmt.setDate(4, new java.sql.Date(project.getEndDate().getTime()));
			pstmt.setString(5, project.getTags());
			
			return pstmt.executeUpdate();
		}catch(Exception e) {
			throw e;
		}finally {
			try { if(pstmt!=null) pstmt.close();} catch(Exception e) {}
			try { if(conn!=null) conn.close();} catch(Exception e) {}
		}
		
	}

 

페이지 컨트롤러 생성 -  ProjectAddController

package spms.controls;
 
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;
	}
 
	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";
		}
	}
}

 

뷰 컴포넌트 생성 - 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" type='text' name='title' size="50"></li>
	<li><label for="content">내용</label>
  		<textarea id="content" name='content' rows="5" cols="40"></textarea></li>
	<li><label for="sdate">시작일</label>
  		<input id="sdate" type='text' name='startDate' placeholder="YYYY-MM-DD"></li>
	<li><label for="edate">종료일</label>
  		<input id="edate" type='text' name='endDate' placeholder="YYYY-MM-DD"></li>
	<li><label for="tags">태그</label>
  		<input id="tags" type='text' name='tags' placeholder="예)태그1 태그2 태그3" size="50"></li>
</ul>
<input type='submit' value='추가'>
<input type='reset' value='취소'>
</form>
<jsp:include page="/Tail.jsp"/>
</body>
</html>

 

 

 

 

 

 

📌프로젝트 변경 구현

---

DAO 구현체에 메소드 추가 - MySqlProjectDao

	@Override
	public Project selectOne(int no) throws Exception {
		Connection connection = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			connection = ds.getConnection();
			stmt = connection.createStatement();
			rs = stmt.executeQuery("SELECT PNO,PNAME,CONTENT,STA_DATE,END_DATE,STATE,CRE_DATE,TAGS"
								+ " FROM PROJECTS WHERE PNO=" + no);
			
			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) {}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {}
			try {
				if (connection != null)
					connection.close();
			} catch (Exception e) {}
		}
	}
 
	@Override
	public int update(Project project) throws Exception {
		Connection connection = null;
		PreparedStatement stmt = null;
		
		try {
			connection = ds.getConnection();
			stmt = connection.prepareStatement("UPDATE PROJECTS SET " 
											+ " PNAME=?," 
											+ " CONTENT=?," 
											+ " STA_DATE=?,"
											+ " END_DATE=?," 
											+ " STATE=?," 
											+ " TAGS=?" 
											+ " WHERE PNO=?");
			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) {}
			try {
				if (connection != null)
					connection.close();
			} catch (Exception e) {}
		}
	}

 

페이지 컨트롤러 생성 -  ProjectUpdateController

package spms.controls;
 
import java.util.Map;
import spms.annotation.Component;
import spms.bind.DataBinding;
import spms.controls.Controller;
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;
  }
  
  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";
    }
  }
}

 

뷰 컴포넌트 생성 - 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: right;
	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 id="no" type='text'
				name='no' size="5" value="${project.no}" readonly></li>
			<li><label for="title">제목</label> <input id="title" type='text'
				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="sdate">시작일</label> <input id="sdate" type='text'
				name='startDate' placeholder="YYYY-MM-DD" value="${project.startDate}"></li>
			<li><label for="edate">종료일</label> <input id="edate" type='text'
				name='endDate' placeholder="YYYY-MM-DD" 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 id="tags" type='text'
				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>

 

 

 

 

 

📌프로젝트 삭제 구현

---

DAO 구현체에 메소드 추가 - MySqlProjectDao

	@Override
	public int delete(int no) throws Exception {
		// TODO Auto-generated method stub
		Connection connection = null;
		Statement stmt = null;
 
		try {
			connection = ds.getConnection();
			stmt = connection.createStatement();
			return stmt.executeUpdate("DELETE FROM PROJECTS WHERE PNO=" + no);
 
		} catch (Exception e) {
			throw e;
 
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {}
			try {
				if (connection != null)
					connection.close();
			} catch (Exception e) {}
		}
	}

 

페이지 컨트롤러 생성 -  ProjectDeleteController

package spms.controls;
 
import java.util.Map;
 
import spms.annotation.Component;
import spms.bind.DataBinding;
import spms.controls.Controller;
import spms.dao.ProjectDao;
 
@Component("/project/delete.do")
public class ProjectDeleteController implements Controller, DataBinding {
	ProjectDao projectDao;
 
	public ProjectDeleteController setProjectDao(ProjectDao projectDao) {
		this.projectDao = projectDao;
		return this;
	}
 
	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";
	}
}

 

 

 

 

최종 MySqlProjectDao.java

package spms.dao;
 
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
 
import javax.naming.Context;
import javax.naming.InitialContext;
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 {
		// TODO Auto-generated method stub
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT pno, pname, sta_date, end_date, state " +
								   "FROM projects " + 
					               "ORDER BY pno DESC");
			
			List<Project> projects = new ArrayList<Project>();
			
			while(rs.next()) {
				projects.add(new Project().setNo(rs.getInt(1))
						                  .setTitle(rs.getString(2))
						                  .setStartDate(rs.getDate(3))
						                  .setEndDate(rs.getDate(4))
						                  .setState(rs.getInt(5)));
			}
			
			return projects;
			
		}catch (Exception e){
			throw e;
		}finally {
			try{if(rs != null) rs.close();} catch(Exception e) {}
			try{if(stmt != null) rs.close();} catch(Exception e) {}
			try{if(conn != null) rs.close();} catch(Exception e) {}
		}
	}
 
	@Override
	public int insert(Project project) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement("INSERT INTO PROJECTS"
										+ "(PNAME, CONTENT, STA_DATE, END_DATE, STATE, CRE_DATE, TAGS)"
										+ " VALUES (?,?,?,?,0,NOW(),?)");
			pstmt.setString(1, project.getTitle());
			pstmt.setString(2, project.getContent());
			pstmt.setDate(3, new java.sql.Date(project.getStartDate().getTime()));
			pstmt.setDate(4, new java.sql.Date(project.getEndDate().getTime()));
			pstmt.setString(5, project.getTags());
			
			return pstmt.executeUpdate();
		}catch(Exception e) {
			throw e;
		}finally {
			try { if(pstmt!=null) pstmt.close();} catch(Exception e) {}
			try { if(conn!=null) conn.close();} catch(Exception e) {}
		}
		
	}
 
	@Override
	public Project selectOne(int no) throws Exception {
		Connection connection = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			connection = ds.getConnection();
			stmt = connection.createStatement();
			rs = stmt.executeQuery("SELECT PNO,PNAME,CONTENT,STA_DATE,END_DATE,STATE,CRE_DATE,TAGS"
								+ " FROM PROJECTS WHERE PNO=" + no);
			
			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) {}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {}
			try {
				if (connection != null)
					connection.close();
			} catch (Exception e) {}
		}
	}
 
	@Override
	public int update(Project project) throws Exception {
		Connection connection = null;
		PreparedStatement stmt = null;
		
		try {
			connection = ds.getConnection();
			stmt = connection.prepareStatement("UPDATE PROJECTS SET " 
											+ " PNAME=?," 
											+ " CONTENT=?," 
											+ " STA_DATE=?,"
											+ " END_DATE=?," 
											+ " STATE=?," 
											+ " TAGS=?" 
											+ " WHERE PNO=?");
			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) {}
			try {
				if (connection != null)
					connection.close();
			} catch (Exception e) {}
		}
	}
 
	@Override
	public int delete(int no) throws Exception {
		// TODO Auto-generated method stub
		Connection connection = null;
		Statement stmt = null;
 
		try {
			connection = ds.getConnection();
			stmt = connection.createStatement();
			return stmt.executeUpdate("DELETE FROM PROJECTS WHERE PNO=" + no);
 
		} catch (Exception e) {
			throw e;
 
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {}
			try {
				if (connection != null)
					connection.close();
			} catch (Exception e) {}
		}
	}
}