GuestBook Example 이어서

GuestBookMgr 추가

package guestbook;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Vector;

public class GuestBookMgr {

	private DBConnectionMgr pool;
	private final SimpleDateFormat SDF_DATE =
			new SimpleDateFormat("yyyy'년'  M'월' d'일' (E)");
	private final SimpleDateFormat SDF_TIME =
			new SimpleDateFormat("H:mm:ss");
	
	public GuestBookMgr() {
		pool = DBConnectionMgr.getInstance();
	}
	
	//Join Login 
	//select id from tblJoin where id = ? and pwd = ?
	public boolean loginJoin(String id, String pwd) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		boolean flag = false;
		try {
			con = pool.getConnection();
			sql = "select id from tblJoin where id = ? and pwd = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, pwd);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				flag = true;
			}
			//flag = rs.next();//결과값 있다면 true, 없다면 false
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt, rs);
		}
		return flag;
	}
	
	
	//Join Information
	//select * from tblJoin where id = ?
	public JoinBean getJoin(String id){
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		JoinBean bean = new JoinBean();
		try {
			con = pool.getConnection();
			sql = "select * from tblJoin where id = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();			
			if(rs.next())
			{
				bean.setId(rs.getString("id"));
				bean.setPwd(rs.getString("pwd"));
				bean.setName(rs.getString("name"));
				bean.setEmail(rs.getString("email"));
				bean.setHp(rs.getString("hp"));
				bean.setGrade(rs.getString("grade"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt, rs);
		}
		return bean;
	}
	//GuestBook Insert
	public void insertGuestBook(GuestBookBean bean) {
		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			con = pool.getConnection();
			sql = "insert tblGuestBook(id,contents,ip,regdate,regtime,secret)"
				+ "values(?,?,?,now(),now(),?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, bean.getId());
			pstmt.setString(2, bean.getContents());
			pstmt.setString(3, bean.getIp());
			pstmt.setString(4, bean.getSecret());
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt);
		}
		
	}
	//GuestBook Update
	public void updateGuestBook(GuestBookBean bean) {
		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			con = pool.getConnection();
			sql = "update tblGuestBook set contents=?,ip=?,secret=? "
				+ "where num=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, bean.getContents());
			pstmt.setString(2, bean.getIp());
			pstmt.setString(3, bean.getSecret());
			pstmt.setInt(4, bean.getNum());
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt);
		}
	}
	//GuestBook Delete
	public void deleteGuestBook(int num) {
		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			con = pool.getConnection();
			sql = "delete from tblGuestBook where num=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1,num);
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt);
		}
	}
	//GuestBook List
	public Vector<GuestBookBean> listGuestBook(String id, String grade){
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		Vector<GuestBookBean> vlist = new Vector<GuestBookBean>();
		try {
			con = pool.getConnection();
			if(grade.equals("1")) { //관리자
				sql = "select * from tblGuestBook order by num desc";
				pstmt = con.prepareStatement(sql);
			}else if(grade.equals("0")) { //일반사용자
				sql = "select * from tblGuestBook "
					+ " where id = ? or secret = '0' order by num desc";
				pstmt = con.prepareStatement(sql);
				pstmt.setString(1, id);
			}
			rs = pstmt.executeQuery();
			while(rs.next()) {
				GuestBookBean bean = new GuestBookBean();
				bean.setNum(rs.getInt("num"));
				bean.setId(rs.getString("id"));
				bean.setContents(rs.getString("contents"));
				bean.setIp(rs.getString("ip"));
				String tempDate = SDF_DATE.format(rs.getDate("regDate"));
				bean.setRegdate(tempDate);
				String tempTime = SDF_TIME.format(rs.getTime("regTime"));
				bean.setRegtime(tempTime);
				bean.setSecret(rs.getString("secret"));
				vlist.addElement(bean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt, rs);
		}
		return vlist;
	}

	//GuestBook Read
	
	//GuestBook Get
	public GuestBookBean getGuestBook(int num) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		GuestBookBean bean = new GuestBookBean();
		try {
			con = pool.getConnection();
			sql = "select * from tblGuestBook where num = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				bean.setNum(rs.getInt("num"));
				bean.setId(rs.getString("id"));
				bean.setContents(rs.getString("contents"));
				bean.setIp(rs.getString("ip"));
				String tempDate = SDF_DATE.format(rs.getDate("regDate"));
				bean.setRegdate(tempDate);
				String tempTime = SDF_TIME.format(rs.getTime("regTime"));
				bean.setRegtime(tempTime);
				bean.setSecret(rs.getString("secret"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt, rs);
		}
		return bean;
	}
}

showGuestBook.jsp

<!-- showGuestBook.jsp -->
<%@page import="java.util.Vector"%>
<%@page import="guestbook.GuestBookBean"%>
<%@page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class="guestbook.GuestBookMgr"/>
<%
	String id = (String)session.getAttribute("idKey");
	if(id==null){
		//현재 접속된 url을 리턴
		StringBuffer url = request.getRequestURL();
		//out.print(url);
		response.sendRedirect("login.jsp?url="+url);
	}
%>
<html>
<title>GuestBook</title>
<script type="text/javascript">
	function updateFn(num){
		url = "updateGuestBook.jsp?num="+num;
		window.open(url,"GuestBook Update","width=540, height=300");
	}
</script>
<link href="css/style.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#996600">
<div align="center">
<%@include file="postGuestBook.jsp" %>
<table width="520" cellspacing="0" cellpadding="3">
	<tr bgcolor="#F5F5F5"> 
		<td><b><%=login.getName()%></b></td>
		<td align="right"><a href="logout.jsp">로그아웃</a></b></td>
	</tr>
</table>
<!-- GuestBook List Start -->
<%
	Vector<GuestBookBean> vlist = mgr.listGuestBook(id, login.getGrade());
	//out.print(vlist.size());
	if(vlist.isEmpty()){
%>		
	<table width="520" cellspacing="0" cellpadding="7">
		<tr>
			<td>등록된 글이 없습니다.</td>
		</tr>
	</table>
	<%}else{
		for(int i=0;i<vlist.size();i++){
			//방명록 글
			GuestBookBean bean = vlist.get(i);
			//방명록 글쓴이
			JoinBean writer = mgr.getJoin(bean.getId());
			//out.println(bean.getContents()+ "<br>");
			//out.println(writer.getName()+ "<p>");
%>
<table  width="520" border="1" bordercolor="#000000" cellspacing="0" cellpadding="0">
	<tr>
		<td>
			<table bgcolor="#F5F5F5">
				<tr>
					<td width="225">NO : <%=vlist.size()-i %> </td>					
					<td width="225">
						<img src="img/face.bmp" border="0" alt="이름">
						<a href="mailto:<%=writer.getEmail()%>"></a>
							<%=writer.getName()%>
					</td>					
					<td width="150" align="center">
						<%	if(writer.getHp()==null||writer.getHp().equals("")){
							out.println("홈페이지가 없습니다.");
						}else{
						%>
						<a href="http://"<%=writer.getHp() %>">
							<img alt="홈페이지" src="img/hp.bmp" border="0">
						</a>
					<% }%>
					</td>					
				</tr>
				<tr>
					<td colspan="3"><%=bean.getContents() %></td>					
				</tr>
				<tr>
					<td>IP : <%=bean.getIp() %></td>					
					<td><%=bean.getRegdate()+" "+bean.getRegtime() %></td>			
					<td>		
					<%
						//수정, 삭제 : 로그인 id와 writer가 동일한 경우에는 활성화
						//삭제 : 관리자
						//비밀글 : secret 1일때
					boolean chk = login.getId().equals(writer.getId());
					if(chk||login.getGrade().equals("1")){
						if(chk){
					%>
						<a href="javascript:updateFn('<%=bean.getNum()%>')">[수정]</a>
					<%}//---if2%>
						<a href="deleteGuestBook.jsp?num=<%=bean.getNum()%>">[삭제]</a>
					<%if(bean.getSecret().equals("1")){%>	
					비밀글
					<%
						}//---if3
					}//---if1
					%>
					</td>						
				</tr>
			</table>
		</td>
	</tr>
</table>
<% 
		}//-- GuestBook for
	}//-- GuestBook if-else%>
<!-- GuestBook List End -->
</div>
</body>
</html>

postGuestBook.jsp

<%@page import="guestbook.JoinBean"%>
<%@page pageEncoding="UTF-8"%>
<jsp:useBean id="login" scope="session" class="guestbook.JoinBean"/>
<%
		//JoinBean login = (JoinBean)session.getAttribute("login");
%>
<html>
<head>
<title>GuestBook</title>
<script type="text/javascript">
	function checkInputs() {
		frm = document.postFrm;
		if(frm.contents.value==""){
			alert("내용을 입력해 주세요.");
			frm.contents.focus();
			return;
		}
		frm.submit();
	}
</script>
</head>
<body>
	<div align="center">
		<table cellspacing="0" cellpadding="3">
			<tr>
				<td bgcolor="#F5F5F5"><font size="4"><b>글올리기</b></font></td>
			</tr>
		</table>
		<form name="postFrm" method="post" action="postGuestBookProc.jsp">
			<table border="1" bordercolor="#000000" cellspacing="0" cellpadding="0">
				<tr>
					<td>
						<table>
							<tr>
								<td height="40" align="center">
								<img src="img/face.bmp" border="0" alt="성명"> 
								<input title="이름을 적어주세요" name="name" size="9" 
								maxlength="20" value="<%=login.getName()%>" readonly>
								<img src="img/email.bmp" border="0" alt="메일">
								<input title="전자메일 주소를 적는 곳이군요" name="email" size="20"
									maxlength="80" value="<%=login.getEmail()%>"> 
								<img src="img/hp.bmp" border="0" alt="홈페이지"> 
								<input title="홈페이지도 있으면 알려주세요." name="hp" size="20"
									maxlength="80" value="<%=login.getHp()%>">
									</td>
							</tr>
							<tr>
								<td align="center">
									<textarea title="좋은 글 남겨주세요"
										name="contents" cols="60" rows="6">하이~~~</textarea>
								</td>
							</tr>
							<tr>
								<td width="500" height="30" colspan="3" align="center">
									<!-- table start -->
									<input type="hidden" name="id" value="<%=login.getId()%>">
									<input type="hidden" name="ip" value="<%=request.getRemoteAddr()%>">
									<input type="button" value="글올리기" onclick="javascript:checkInputs()"> 
									<input type="reset" value="고치기">
									<input type="checkbox" name="secret" value="1">비밀글
									<!--table end  -->
								</td>
							</tr>
						</table>
					</td>
				</tr>
			</table>
		</form>
	</div>
</body>
</html>

postGuestBookProc.jsp

<!-- postGuestBookProc.jsp -->
<%@ page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class="guestbook.GuestBookMgr"/>
<jsp:useBean id="bean" class="guestbook.GuestBookBean"/>
<jsp:setProperty property="*" name="bean"/>
<%
	if(bean.getSecret()==null){
		bean.setSecret("0"); //비밀글을 체크를 안한 경우
	}
	mgr.insertGuestBook(bean);
	response.sendRedirect("showGuestBook.jsp");
%>

login.jsp 추가

<!-- guestbook/login.jsp -->
<%@page contentType="text/html;charset=UTF-8"%>
<jsp:useBean id="login" class="guestbook.JoinBean" scope="session"/>
<%
	String id = (String)session.getAttribute("idKey");
	String url = request.getParameter("url");	
%>
<title>로그인</title>
<link href="css/style.css" rel="stylesheet" type="text/css">
<body bgcolor="#996600">
<br><br>
<div align="center">
<%
		if(id!=null){
			%>
				<b><%=login.getName()%></b>님 환영합니다.<br>
				<a href="showGuestBook.jsp" >방명록 </a>&nbsp;
				<a href="logout.jsp" >로그아웃</a>
			<%
		}else{
%>
<h2>GuestBook 로그인</h2>
<form method="post" action="loginProc.jsp">
<table border="1">
	<tr>
		<td>id</td>
		<td> <input name="id" value="aaa">
		</td>
	</tr>
	<tr>
		<td>pwd</td>
		<td><input name="pwd" value="1234"></td>
	</tr>
	<tr>
		<td align="center" colspan="2">
		<input type="hidden" name="url" value="<%=url %>">
		<input type="submit" value="로그인">
		</td>
	</tr>
</table>
</form>
<%}%>
</div>
</body>

loginProc.jsp 추가

<!-- guestbook/loginProc.jsp -->
<%@page contentType="text/html;charset=UTF-8"%>
<jsp:useBean id="mgr" class="guestbook.GuestBookMgr"/>
<jsp:useBean id="login" class="guestbook.JoinBean" scope="session"/>
<!-- login.jsp 요청한 id,pwd 저장 -->
<jsp:setProperty property="*" name="login"/>
<%
	String url = "login.jsp";
	if(request.getParameter("url")!=null&&!request.getParameter("url").equals("null")){
		url = request.getParameter("url");
	}
	boolean result = mgr.loginJoin(login.getId(), login.getPwd());
	String msg = "로그인 실패";
	if(result){
		msg = "로그인 성공";	
		login = mgr.getJoin(login.getId());
		session.setAttribute("idKey", login.getId());
		session.setAttribute("login", login);
	}
%>
<script>
	alert("<%=msg%>");
	location.href="<%=url%>";
</script>

deleteGuestBook.jsp

<!-- deleteGuestBook.jsp -->
<%@page import="guestbook.MyUtil"%>
<%@ page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class="guestbook.GuestBookMgr"/>
<%
	int num =0;
	if(request.getParameter("num")!=null){
		num=MyUtil.parseInt(request, "num");
		mgr.deleteGuestBook(num);
		//방명록 원글 삭제 시 관련된 댓글도 모두 삭제
	}
	response.sendRedirect("showGuestBook.jsp");
%>

updateGuestBook.jsp

<!-- updateGuestBook.jsp -->
<%@page import="guestbook.GuestBookBean"%>
<%@page import="guestbook.MyUtil"%>
<%@ page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class="guestbook.GuestBookMgr"/>
<jsp:useBean id="login" class="guestbook.JoinBean" scope="session"/>
<%
	int num =0;
	GuestBookBean bean = null;

	if(request.getParameter("num")!=null){
		num=MyUtil.parseInt(request, "num");
		bean = mgr.getGuestBook(num);	
	}
%>
<html>
<head>
<title>GuestBook</title>
<link href="css/style.css" rel="stylesheet" type="text/css">
</head>
<body>
	<div align="center">
		<table width="500" cellspacing="0" cellpadding="3">
			<tr>
				<td bgcolor="#F5F5F5"><font size="4"><b>글수정하기</b></font></td>
			</tr>
		</table>
		<form method="post" action="updateGuestBookProc.jsp?num=<%=num%>">
			<table width="520" border="1" cellspacing="0" cellpadding="0">
				<tr>
					<td>
						<table>
							<tr>
								<td height="40" align="center">
									<img src="img/face.bmp" border="0" alt="성명"> 
									<input name="name" size="9" value="<%=login.getName()%>" readonly> 
									<img src="img/email.bmp" border="0" alt="메일"> 
									<input name="email" size="20" value="<%=login.getEmail()%>"> 
									<img src="img/hp.bmp" border="0" alt="홈페이지"> 
									<input title="홈페이지도 있으면 알려주시어요" name="hp" size="20" value="<%=login.getHp()%>">
								</td>
							</tr>
							<tr>
								<td align="center">
									<textarea name="contents" cols="60" 
									rows="6"><%=bean.getContents()%></textarea>
								</td>
							</tr>
							<tr>
								<td width="500" height="30" colspan="3" align="center">
								<input type="hidden" name="id" value="<%=bean.getId()%>">
								<input type="hidden" name="ip" value="<%=request.getRemoteAddr()%>">
								<input type="submit" value="글수정"> 
								<input type="reset" value="고치기"> 
								<input type="checkbox" name="secret" value="1"
								<%if(bean.getSecret().equals("1")) out.print("checked"); %>
								>비밀글
								</td>
							</tr>
						</table>
					</td>
				</tr>
			</table>
		</form>
	</div>
</body>
</html>

updateGuestBookProc.jsp

<!-- updateGuestBookProc.jsp -->
<%@ page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class="guestbook.GuestBookMgr"/>
<jsp:useBean id="bean" class="guestbook.GuestBookBean"/>
<jsp:setProperty property="*" name="bean"/>
<%
	if(bean.getSecret()==null)
		bean.setSecret("0");
	mgr.updateGuestBook(bean);
%>
<!-- 수정과 동시에 자신의 창은 close하고 수정된 방명록 글이 적용되려면
showGuestBook.jsp 새로고침 -->
<script type="text/javascript">
	opener.location.reload();
	self.close();
</script>