방명록 예제(방명록 페이지 이동)
- 방명록 저장
- 로그인 부분, url 리턴
- tblguestbook테이블에서 ip가 도중에 0이 나올 경우
https://m.blog.naver.com/bb_/222844419943
또는
https://velog.io/@thalsghks/23.03.09Jsp
GuestBookMgr.java, MyUtil.java
<GuestBookMgr.java>
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();
flag = rs.next();
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
return flag;
}
// Join Informantion
// 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();
// where 절 이하에 조건이 pk 로 선언된 컬럼일때 if문으로 사용.
if (rs.next()) {
bean.setId(rs.getString(1));
bean.setPwd(rs.getString(2));
bean.setName(rs.getString(3));
bean.setEmail(rs.getString(4));
bean.setHp(rs.getString(5));
bean.setGrade(rs.getString(6));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return bean;
}
// 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 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;
}
// GuestBook Insert
// sql = "insert tblGuestBook(id,contents,ip,regdate,regtime,secret)"
// values(?,?,?,now(),now(),?)";
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();// SQL로 실행
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
}
// GuestBook Update
// sql = "update tblGuestBook set contents=?,ip=?,secret=? "
// "where num=?";
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
// sql = "delete from tblGuestBook where num=?";
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);
}
}
}<MyUtil.java>
package guestbook;
import javax.servlet.http.HttpServletRequest;
public class MyUtil {
//매개변수가 숫자이면 true, 숫자가 아니면 false
public static boolean isNumeric(String s) {
try {
Integer.parseInt(s);
return true;
} catch(NumberFormatException e) {
return false;
}
}
public static int parseInt(HttpServletRequest request,
String name) {
return Integer.parseInt(request.getParameter(name));
}
}
login.jsp, loginProc.jsp, logout.jsp, postGuestBook.jsp, postGuestBookProc.jsp, showGuestBook.jsp, updateGuestBook.jsp, updateGuestBookProc.jsp, deleteGuestBook.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>
<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());
//out.print(result);
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>
<logout.jsp>
<!-- guestbook/logout.jsp -->
<%@page contentType="text/html;charset=UTF-8"%>
<%
session.invalidate();
%>
<script>
alert('로그아웃 되었습니다.');
location.href="login.jsp";
</script>
<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>
<!-- guestbook/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");
%>
<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.println(url);
response.sendRedirect("login.jsp?url="+url);
return;
}
%>
<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());
%>
<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()%>">
<%=writer.getName() %>
</a>
</td>
<td width="150" align="center">
<%if(writer.getHp()==null || writer.getHp().equals("")){
out.print("홈페이지가 없네요");
}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가 동일 활성
// 삭제: 관리자
// 비밀글 : 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>
<updateGuestBook.jsp>
<!-- updateGuestBook.jsp -->
<%@page import="guestbook.GuestBookBean"%>
<%@page import="guestbook.MyUtil"%>
<%@page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="login" scope="session" class="guestbook.JoinBean"/>
<jsp:useBean id="mgr" class="guestbook.GuestBookMgr"/>
<%
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="505" 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="505" 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 -->
<%@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><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");
%>
<결과>
