게시물 답변기능
BCommentMgr.java
package ch15;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;
public class BCommentMgr {
private DBConnectionMgr pool;
public BCommentMgr() {
pool = DBConnectionMgr.getInstance();
}
//Comment Insert (read.jsp)
public void insertBComment(BCommentBean bean) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
try {
con = pool.getConnection();
sql = "insert tblBComment(name,comment,regdate,num) "
+ "values(?,?,now(),?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, bean.getName());
pstmt.setString(2, bean.getComment());
pstmt.setInt(3, bean.getNum());
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
}
//Comment List (read.jsp)
public Vector<BCommentBean> getBComment(int num){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
Vector<BCommentBean> vlist = new Vector<>();
try {
con = pool.getConnection();
sql = "select * from tblBComment where num = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
rs = pstmt.executeQuery();
while(rs.next()){
BCommentBean bean = new BCommentBean();
bean.setCnum(rs.getInt("cnum"));
bean.setName(rs.getString("name"));
bean.setComment(rs.getString("comment"));
bean.setRegdate(rs.getString("regdate"));
bean.setNum(rs.getInt("num"));
vlist.addElement(bean);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return vlist;
}
//Comment Count (list.jsp)
public int getBCommentCount(int num) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
int count = 0;
try {
con = pool.getConnection();
sql = "select count(num) from tblBComment where num=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
rs = pstmt.executeQuery();
if(rs.next())
count = rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return count;
}
//Comment Delete (read.jsp)
public void deleteBComment(int cnum) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
try {
con = pool.getConnection();
sql = "delete from tblBComment where cnum=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, cnum);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
}
//Comment All Delete
//(게시물 원글 삭제시 - BoardDeleteServlet)
public void deleteAllBComment(int num) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
try {
con = pool.getConnection();
sql = "delete from tblBComment where num=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
}
}
BoardDeleteServlet.java
package ch15;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;
@WebServlet("/ch15/boardDelete")
public class BoardDeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
HttpSession session = request.getSession();
BoardBean bean = (BoardBean)session.getAttribute("bean");
String dbPass = bean.getPass();
// read.jsp 입력한 값
String inPass = request.getParameter("pass");
if(dbPass.equals(inPass)) {
BoardMgr mgr = new BoardMgr();
BCommentMgr cmgr = new BCommentMgr();
mgr.deleteBoard(bean.getNum());
//관련 댓글 모두 삭제
cmgr.deleteAllBComment(bean.getNum());
String numPerPage = request.getParameter("numPerPage");
String nowPage = request.getParameter("nowPage");
String keyField = request.getParameter("keyField");
String keyWord = request.getParameter("keyWord");
String url = "list.jsp?numPerPage="+numPerPage;
url+="&nowPage="+nowPage;
if(!(keyWord==null||keyWord.equals(""))) {
url+="&keyField="+keyField;
url+="&keyWord="+keyWord;
}
response.sendRedirect(url);
} else {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
out.println("<script>");
out.println("alert('입력하신 비밀번호가 아닙니다')");
out.println("history.back()");
out.println("</script>");
}
}
}
BoardMgr.java
package ch15;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;
import javax.servlet.http.HttpServletRequest;
import org.apache.catalina.ssi.SSICommand;
import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;
public class BoardMgr {
private DBConnectionMgr pool;
public static final String SAVEFOLDER = "C:/Jsp/myapp/src/main/webapp/ch15/fileupload/";
public static final String ENCODING = "UTF-8";
public static final int MAXSIZE = 1024 * 1024 * 20; // 20MB
public BoardMgr() {
pool = DBConnectionMgr.getInstance();
}
// Board Insert
public void insertBoard(HttpServletRequest req) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
try {
File dir = new File(SAVEFOLDER);
if(!dir.exists()/*존재하지 않는다면*/)
dir.mkdirs(); // 상위폴더가 없어도 생성
// mkdir : 상위폴더가 없다면 생성불가
MultipartRequest multi =
new MultipartRequest(req, SAVEFOLDER, MAXSIZE,
ENCODING,new DefaultFileRenamePolicy());
String filename = null;
int filesize = 0;
if(multi.getFilesystemName("filename")!=null) {
filename = multi.getFilesystemName("filename");
filesize = (int)multi.getFile("filename").length();
}
String content = multi.getParameter("content");
String contentType = multi.getParameter("contentType");
if(contentType.equals("TEXT")) {
content = UtilMgr.replace(content, "<", "<");
}
int ref = getMaxNum()+1; // 답변을 위한 ref 값 설정
/////////////////////////////////////////////////////
con = pool.getConnection();
sql = "insert tblBoard(name,content,subject,ref,pos,depth,";
sql += "regdate,pass,count,ip,filename,filesize)";
sql += "values(?, ?, ?, ?, 0, 0, now(), ?, 0, ?, ?, ?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, multi.getParameter("name"));
pstmt.setString(2, content);
pstmt.setString(3, multi.getParameter("subject"));
pstmt.setInt(4, ref);
pstmt.setString(5, multi.getParameter("pass"));
pstmt.setString(6, multi.getParameter("ip"));
pstmt.setString(7, filename);
pstmt.setInt(8, filesize);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
}
// Board Max Num : num의 현재 최대값
public int getMaxNum() {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
int maxNum = 0;
try {
con = pool.getConnection();
sql = "select max(num) from tblBoard";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()) maxNum = rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return maxNum;
}
// Board Total Count : 총 게시물수
public int getTotalCount(String keyField, String keyWord) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
int totalCount = 0;
try {
con = pool.getConnection();
if (keyWord.trim().equals("")||keyWord==null) {
sql = "select count(*) from tblBoard";
pstmt = con.prepareStatement(sql);
}else {
sql = "select count(*) from tblBoard where " + keyField + " like ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%"+keyWord+"%");
}
rs = pstmt.executeQuery();
if (rs.next()) {
totalCount = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return totalCount;
}
// Board List : 검색기능, 페이징 및 블럭
// limit 시작번호, 가져올 개수
public Vector<BoardBean> getBoardList(String keyField, String keyWord, int start, int cnt) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
Vector<BoardBean> vlist = new Vector<>();
try {
con = pool.getConnection();
if (keyWord.trim().equals("")||keyWord==null) {
sql = "select * from tblBoard order by ref desc, pos limit ?, ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, start);
pstmt.setInt(2, cnt);
}else {
sql = "select * from tblBoard where " + keyField + " like ? order by ref desc, pos limit ?, ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%"+keyWord+"%");
pstmt.setInt(2, start);
pstmt.setInt(3, cnt);
}
rs = pstmt.executeQuery();
while(rs.next()) {
BoardBean bean = new BoardBean();
bean.setNum(rs.getInt("num"));
bean.setName(rs.getString("name"));
bean.setSubject(rs.getString("subject"));
bean.setPos(rs.getInt("pos"));
bean.setRef(rs.getInt("ref"));
bean.setDepth(rs.getInt("depth"));
bean.setRegdate(rs.getString("regdate"));
bean.setCount(rs.getInt("count"));
bean.setFilename(rs.getString("filename"));
vlist.addElement(bean);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return vlist;
}
// Board Get : 게시물 한개 읽어오기(13개 컬럼 전체 리턴)
public BoardBean getBoard(int num) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
BoardBean bean = new BoardBean();
try {
con = pool.getConnection();
sql = "select *from tblBoard where num = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
rs = pstmt.executeQuery();
if(rs.next()) {
bean.setNum(rs.getInt("num"));
bean.setContent(rs.getString("content"));
bean.setCount(rs.getInt("count"));
bean.setDepth(rs.getInt("depth"));
bean.setFilename(rs.getString("filename"));
bean.setFilesize(rs.getInt("filesize"));
bean.setIp(rs.getString("ip"));
bean.setName(rs.getString("name"));
bean.setPass(rs.getString("pass"));
bean.setPos(rs.getInt("pos"));
bean.setRef(rs.getInt("ref"));
bean.setRegdate(rs.getString("regdate"));
bean.setSubject(rs.getString("subject"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
return bean;
}
// Count Up : 조회수 증가
public void upCount(int num) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
try {
con = pool.getConnection();
sql = "update tblBoard set count=count+1 where num=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
}
// Board Delete : 파일업로드 파일 삭제 (UtilMgr.delete 메소드 사용)
public void deleteBoard(int num) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
try {
BoardBean bean = getBoard(num);
String filename = bean.getFilename();
if(filename!=null&&!filename.equals("")) {
File f = new File(SAVEFOLDER+filename);
if(f.exists())
UtilMgr.delete(SAVEFOLDER+filename);
}
con = pool.getConnection();
sql = "delete from tblBoard where num=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
}
// Board Update : 파일업로드 수정
// 파일 업로드 수정이 되면 기존에 파일은 삭제가 되어야 한다.
// 기존에 파일이 있지만 파일 업로드 수정이 없으면 그냥 다른 컬럼들만 수정
public void updateBoard(MultipartRequest multi) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
try {
con = pool.getConnection();
int num = Integer.parseInt(multi.getParameter("num"));
String name = multi.getParameter("name");
String subject = multi.getParameter("subject");
String content = multi.getParameter("content");
String filename = multi.getFilesystemName("filename");
if(filename!=null&&!filename.equals("")) {
// 파일 업로드 수정 선택
BoardBean bean = getBoard(num);
String tempfile = bean.getFilename();
if(tempfile!=null&&!tempfile.equals("")) {
File f = new File(SAVEFOLDER+tempfile);
if(f.exists())
UtilMgr.delete(SAVEFOLDER+tempfile);
}
int filesize = (int)multi.getFile("filename").length();
sql = "update tblBoard set name=?, subject=?, content=?,"
+ "filename=?, filesize=? where num=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, subject);
pstmt.setString(3, content);
pstmt.setString(4, filename);
pstmt.setInt(5, filesize);
pstmt.setInt(6, num);
} else {
// 수정 페이지에서 파일 업로드를 선택하지 않았을때
sql = "update tblBoard set name=?, subject=?, content=? "
+ "where num=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, subject);
pstmt.setString(3, content);
pstmt.setInt(4, num);
}
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
return;
}
// Board Reply : 답변글 입력
public void replyBoard(BoardBean bean) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
try {
con = pool.getConnection();
sql = "insert tblBoard(name,content,subject,ref,pos,depth,regdate,"
+ "pass,count,ip)values(?, ?, ?, ?, ?, ?, now(), ?, 0, ?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, bean.getName());
pstmt.setString(2, bean.getContent());
pstmt.setString(3, bean.getSubject());
//////////////////////////////////////////
pstmt.setInt(4, bean.getRef()); // 원글과 동일한 ref (그룹)
pstmt.setInt(5, bean.getPos()+1); // 원글 pos+1 (정렬)
pstmt.setInt(6, bean.getDepth()+1); // 원글 depth+1
//////////////////////////////////////////
pstmt.setString(7, bean.getPass());
pstmt.setString(8, bean.getIp());
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
}
// Board Reply Up : 답변글 위치값 수정
public void replyUpBoard(int ref, int pos) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
try {
con = pool.getConnection();
sql = "update tblBoard set pos=pos+1 where ref=? and pos=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, ref);
pstmt.setInt(2, pos);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
}
// 게시물 1000개 입력
public void post1000(){
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
try {
con = pool.getConnection();
sql = "insert tblBoard(name,content,subject,ref,pos,depth,regdate,pass,count,ip,filename,filesize)";
sql+="values('aaa', 'bbb', 'ccc', 0, 0, 0, now(), '1234',0, '127.0.0.1', null, 0);";
pstmt = con.prepareStatement(sql);
for (int i = 0; i < 300; i++) {
pstmt.executeUpdate();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
}
public static void main(String []args) {
BoardMgr mgr= new BoardMgr();
mgr.post1000();
System.out.println("입력성공");
}
}
BoardReplyServlet.java
package ch15;
import java.io.IOException;
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("/ch15/boardReply")
public class BoardReplyServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
BoardMgr mgr = new BoardMgr();
BoardBean reBean = new BoardBean();
reBean.setName(request.getParameter("name"));
reBean.setSubject(request.getParameter("subject"));
reBean.setContent(request.getParameter("content"));
reBean.setRef(UtilMgr.parseInt(request, "ref"));
reBean.setPos(UtilMgr.parseInt(request, "pos"));
reBean.setDepth(UtilMgr.parseInt(request, "depth"));
reBean.setPass(request.getParameter("pass"));
reBean.setIp(request.getParameter("ip"));
mgr.replyUpBoard(reBean.getRef(), reBean.getPos());
mgr.replyBoard(reBean);
String nowPage = request.getParameter("nowPage");
String numPerPage = request.getParameter("numPerPage");
response.sendRedirect("list.jsp?nowPage="+nowPage+
"&numPerPage="+numPerPage);
}
}
list.jsp
<!-- list.jsp -->
<%@page import="ch15.BoardBean"%>
<%@page import="java.util.Vector"%>
<%@page import="ch15.UtilMgr"%>
<%@page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class="ch15.BoardMgr"/>
<jsp:useBean id="cmgr" class="ch15.BCommentMgr"/>
<%
int totalRecord = 0; // 총 게시물수
int numPerPage = 10; // 페이지당 레코드 개수()
int pagePerBlock = 15;
int totalPage = 0; // 총 페이지 개수
int totalBlock = 0; // 총 블럭 개수
int nowPage = 1; // 현재 페이지
int nowBlock = 1; // 현재 블럭
// 요청된 numPerPage 처리
if(request.getParameter("numPerPage")!=null){
numPerPage = UtilMgr.parseInt(request, "numPerPage");
}
// 검색에 필요한 변수
String keyField = "", keyWord = "";
if(request.getParameter("keyWord")!=null){
keyField = request.getParameter("keyField");
keyWord = request.getParameter("keyWord");
}
// 검색 후에 다시 reset 요청
if(request.getParameter("reload")!=null&&
request.getParameter("reload").equals("true")){
keyField="";keyWord="";
}
totalRecord = mgr.getTotalCount(keyField, keyWord);
//out.print(totalRecord);
if(request.getParameter("nowPage")!=null){
nowPage = UtilMgr.parseInt(request, "nowPage");
}
// sql문에 들어가는 start, cnt 선언
int start = (nowPage*numPerPage)-numPerPage;
int cnt = numPerPage;
// 전체 페이지 개수
totalPage = (int)Math.ceil((double)totalRecord/numPerPage);
// 전체 블록 개수
totalBlock = (int)Math.ceil((double)totalPage/pagePerBlock);
// 현재 블럭 개수
nowBlock = (int)Math.ceil((double)nowPage/pagePerBlock);
//out.println("totalPage : "+totalPage+"<br>");
//out.println("totalBlock : "+totalBlock+"<br>");
//out.println("nowBlock : "+nowBlock+"<br>");
%>
<!DOCTYPE html>
<html>
<head>
<title>JSP Board</title>
<link href="style.css" rel="stylesheet" type="text/css">
<script type="text/javascript">
function check() {
if(document.searchFrm.keyWord.value==""){
alert("검색어를 입력하세요.");
document.searchFrm.keyWord.focus();
return;
}
document.searchFrm.submit();
}
function list() {
document.listFrm.action = "list.jsp";
document.listFrm.submit();
}
function block(block){
document.readFrm.nowPage.value = <%=pagePerBlock%>*(block-1)+1;
document.readFrm.submit();
}
function paging(page){
document.readFrm.nowPage.value = page;
document.readFrm.submit();
}
function numPerFn(numPerPage){
//alert(numPerPage);
document.readFrm.numPerPage.value=numPerPage;
document.readFrm.submit();
}
function read(num){
document.readFrm.num.value=num;
document.readFrm.action="read.jsp";
document.readFrm.submit();
}
</script>
</head>
<body bgcolor="#FFFFCC" >
<div align="center"><br/>
<h2>JSP Board</h2><br/>
<table>
<tr>
<td width="600">
Total : <%=totalRecord%>Articles(<font color="red">
<%=nowPage+"/"+totalPage%>Pages</font>)
</td>
<td align="right">
<form name="npFrm" method="post">
<select name="numPerPage" size="1"
onchange="javascript:numPerFn(this.form.numPerPage.value)">
<option value="5">5개 보기</option>
<option value="10" selected>10개 보기</option>
<option value="15">15개 보기</option>
<option value="30">30개 보기</option>
</select>
<script>document.npFrm.numPerPage.value=<%=numPerPage%></script>
</form>
</td>
</tr>
</table>
<table>
<tr>
<td align="center" colspan="2">
<%
Vector<BoardBean> vlist = mgr.getBoardList(keyField, keyWord, start, cnt);
int listSize = vlist.size();
if(vlist.isEmpty()){
out.println("등록된 게시물이 없습니다.");
} else{
%>
<table cellspacing="0">
<tr align="center" bgcolor="#D0D0D0">
<td width="100">번 호</td>
<td width="250">제 목</td>
<td width="100">이 름</td>
<td width="150">날 짜</td>
<td width="100">조회수</td>
</tr>
<%
for(int i=0;i<numPerPage/*10*/;i++){
if(i==listSize) break;
BoardBean bean = vlist.get(i);
int num = bean.getNum();
String subject = bean.getSubject();
String name = bean.getName();
String regdate = bean.getRegdate();
int depth = bean.getDepth();
int count = bean.getCount();
String filename = bean.getFilename();
// 댓글 count
int bcount = cmgr.getBCommentCount(num);
%>
<tr align="center">
<td><%=totalRecord-start-i%></td>
<td align="left">
<%for(int j=0;j<depth;j++){out.println(" ");} %>
<a href="javascript:read('<%=num%>')">
<%=subject%></a>
<%if(filename!=null&&!filename.equals("")){ %>
<img alt="첨부파일" src="img/icon.gif" align="middle">
<%}%>
<%if(bcount>0){ %>
<font color="red">(<%=bcount%>)</font>
<%} %>
</td>
<td><%=name%></td>
<td><%=regdate%></td>
<td><%=count%></td>
</tr>
<%} //-- for%>
</table>
<% } // --if-else %>
</td>
</tr>
<tr>
<td colspan="2"><br><br></td>
</tr>
<tr>
<td>
<!-- 페이징 및 블럭 Start -->
<!-- 이전블럭 -->
<%if(nowBlock>1){ %>
<a href="javascript:block('<%=nowBlock-1 %>')">prev...</a>
<%} %>
<!-- 페이징 -->
<%
int pageStart = (nowBlock-1)*pagePerBlock+1;
int pageEnd = (pageStart+pagePerBlock)<totalPage ?
pageStart+pagePerBlock:totalPage+1;
for(;pageStart<pageEnd;pageStart++){
%>
<a href="javascript:paging('<%=pageStart %>')">
<%if(nowPage==pageStart){ %><font color="blue"><%} %>
[<%=pageStart %>]
<%if(nowPage==pageStart){ %></font><%} %>
</a>
<% }%>
<!-- 다음블럭 -->
<%if(totalBlock>nowBlock){ %>
<a href="javascript:block('<%=nowBlock+1 %>')">...next</a>
<%} %>
<!-- 페이징 및 블럭 End -->
</td>
<td align="right">
<a href="post.jsp" style="bgcolor:blue">[글쓰기]</a>
<a href="javascript:list()">[처음으로]</a>
</td>
</tr>
</table>
<hr width="750">
<form name="searchFrm">
<table width="600" cellpadding="4" cellspacing="0">
<tr>
<td align="center" valign="bottom">
<select name="keyField" size="1" >
<option value="name"> 이 름</option>
<option value="subject"> 제 목</option>
<option value="content"> 내 용</option>
</select>
<input size="16" name="keyWord">
<input type="button" value="찾기" onClick="javascript:check()">
<input type="hidden" name="nowPage" value="1">
</td>
</tr>
</table>
</form>
<form name="listFrm" method="post">
<input type="hidden" name="reload" value="true">
<input type="hidden" name="nowPage" value="1">
</form>
<form name="readFrm">
<input type="hidden" name="nowPage" value="<%=nowPage%>">
<input type="hidden" name="numPerPage" value="<%=numPerPage%>">
<input type="hidden" name="keyField" value="<%=keyField%>">
<input type="hidden" name="keyWord" value="<%=keyWord%>">
<input type="hidden" name="num">
</form>
</div>
</body>
</html>
read.jsp
<!-- read.jsp -->
<%@page import="java.util.Vector"%>
<%@page import="ch15.BCommentBean"%>
<%@page import="ch15.BoardBean"%>
<%@page import="ch15.UtilMgr"%>
<%@page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class="ch15.BoardMgr"/>
<jsp:useBean id="cmgr" class="ch15.BCommentMgr"/>
<%
// read.jsp?nowPage=1&numPerPage=10&keyField=&keyWord=&num=8
String nowPage = request.getParameter("nowPage");
String numPerPage = request.getParameter("numPerPage");
String keyField = request.getParameter("keyField");
String keyWord = request.getParameter("keyWord");
int num = UtilMgr.parseInt(request, "num");
// 댓글 기능 : insert, delete
String flag = request.getParameter("flag");
if(flag!=null){
if(flag.equals("insert")){
BCommentBean cbean = new BCommentBean();
cbean.setNum(num); // 어떤 게시물
cbean.setName(request.getParameter("cName"));
cbean.setComment(request.getParameter("comment"));
cmgr.insertBComment(cbean);
} else if(flag.equals("delete")){
cmgr.deleteBComment(UtilMgr.parseInt(request, "cnum"));
}
} else{
//list.jsp 게시물 읽음 : 이때 만 조회수 증가
mgr.upCount(num);
}
BoardBean bean = mgr.getBoard(num);
//out.println(bean.getSubject()+" : "+bean.getCount());
String name = bean.getName();
String subject = bean.getSubject();
String regdate = bean.getRegdate();
String content = bean.getContent();
String filename = bean.getFilename();
int filesize = bean.getFilesize();
String ip = bean.getIp();
int count = bean.getCount();
// 읽어온 게시물을 수정, 삭제를 위해서 세션에 저장
session.setAttribute("bean", bean);
%>
<!DOCTYPE html>
<html>
<head>
<title>JSP Board</title>
<link href="style.css" rel="stylesheet" type="text/css">
<script type="text/javascript">
function cInsert() {
if(document.cFrm.comment.value==""){
alert("댓글을 입력하세요.");
document.cFrm.comment.focus();
return;
}
document.cFrm.submit();
}
function list() {
document.listFrm.action = "list.jsp";
document.listFrm.submit();
}
function down(filename) {
document.downFrm.filename.value=filename;
document.downFrm.submit();
}
function delFn(){
const pass = document.getElementById("passId");
//alert(pass.value);
if(pass.value.length==0){
alert("비밀번호 입력하세요");
pass.focus();
return;
}
document.delFrm.pass.value=pass.value;
document.delFrm.submit();
}
function cDel(cnum) {
document.cFrm.cnum.value=cnum;
document.cFrm.flag.value="delete";
document.cFrm.submit();
}
</script>
</head>
<body bgcolor="#FFFFCC">
<br/><br/>
<table align="center" width="600" cellspacing="3">
<tr>
<td bgcolor="#9CA2EE" height="25" align="center">글읽기</td>
</tr>
<tr>
<td colspan="2">
<table cellpadding="3" cellspacing="0" width="100%">
<tr>
<td align="center" bgcolor="#DDDDDD" width="10%"> 이 름 </td>
<td bgcolor="#FFFFE8"><%=name%></td>
<td align="center" bgcolor="#DDDDDD" width="10%"> 등록날짜 </td>
<td bgcolor="#FFFFE8"><%=regdate%></td>
</tr>
<tr>
<td align="center" bgcolor="#DDDDDD"> 제 목</td>
<td bgcolor="#FFFFE8" colspan="3"><%=subject%></td>
</tr>
<tr>
<td align="center" bgcolor="#DDDDDD">첨부파일</td>
<td bgcolor="#FFFFE8" colspan="3">
<% if(filename!=null&&!filename.equals("")){ %>
<a href="javascript:down('<%=filename%>')"><%=filename%></a>
<font color="blue">
(<%=UtilMgr.intFormat(filesize)%>bytes)
</font>
<% }else{out.println("첨부된 파일이 없습니다.");} %>
</td>
</tr>
<tr>
<td align="center" bgcolor="#DDDDDD">비밀번호</td>
<td bgcolor="#FFFFE8" colspan="3">
<input type="password" name="pass" id="passId">
</td>
</tr>
<tr>
<td colspan="4"><br/><pre><%=content%></pre><br/></td>
</tr>
<tr>
<td colspan="4" align="right">
IP주소 : <%=ip%> / 조회수 <%=count%>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="center" colspan="2">
<!-- 댓글 입력폼 Start -->
<form method="post" name="cFrm">
<table>
<tr align="center">
<td width="50">이 름</td>
<td align="left">
<input name="cName" size="10" value="aaa">
</td>
</tr>
<tr align="center">
<td>내 용</td>
<td>
<input name="comment" size="50">
<input type="button" value="등록" onclick="cInsert()"></td>
</tr>
</table>
<input type="hidden" name="flag" value="insert">
<input type="hidden" name="num" value="<%=num%>">
<input type="hidden" name="cnum">
<input type="hidden" name="nowPage" value="<%=nowPage%>">
<input type="hidden" name="numPerPage" value="<%=numPerPage%>">
<%if(!(keyWord==null||keyWord.equals(""))){ %>
<input type="hidden" name="keyField" value="<%=keyField%>">
<input type="hidden" name="keyWord" value="<%=keyWord%>">
<%}%>
</form>
<!-- 댓글 입력폼 End -->
<hr/>
<!-- 댓글 List Start -->
<%
Vector<BCommentBean> cvlist=cmgr.getBComment(num);
if(!cvlist.isEmpty()){
//out.println(cvlist.size());
%>
<table>
<%
for(int i=0;i<cvlist.size();i++){
BCommentBean cbean = cvlist.get(i);
int cnum = cbean.getCnum();
String cname = cbean.getName();
String comment = cbean.getComment();
String cregdate = cbean.getRegdate();
%>
<tr>
<td colspan="3" width="600"><b><%=cname%></b></td>
</tr>
<tr>
<td>댓글:<%=comment%></td>
<td align="right"><%=cregdate%></td>
<td align="center" valign="middle">
<input type="button" value="삭제" onclick="cDel('<%=cnum%>')">
</td>
</tr>
<tr>
<td colspan="3"><br></td>
</tr>
<% } // --for%>
</table>
<%} //--if %>
<!-- 댓글 List End -->
[ <a href="javascript:list()" >리스트</a> |
<a href="update.jsp?nowPage=<%=nowPage%>&num=<%=num%>&numPerPage=<%=numPerPage%>" >수 정</a> |
<a href="reply.jsp?nowPage=<%=nowPage%>&numPerPage=<%=numPerPage%>" >답 변</a> |
<a href="javascript:delFn()">삭 제</a> ]<br/>
</td>
</tr>
</table>
<form method="post" name="downFrm" action="download.jsp">
<input type="hidden" name="filename">
</form>
<form name="listFrm">
<input type="hidden" name="nowPage" value="<%=nowPage%>">
<input type="hidden" name="numPerPage" value="<%=numPerPage%>">
<%if(!(keyWord==null||keyWord.equals(""))){%>
<input type="hidden" name="keyField" value="<%=keyField%>">
<input type="hidden" name="keyWord" value="<%=keyWord%>">
<%}%>
</form>
<form name="delFrm" action="boardDelete" method="post">
<input type="hidden" name="nowPage" value="<%=nowPage%>">
<input type="hidden" name="numPerPage" value="<%=numPerPage%>">
<%if(!(keyWord==null||keyWord.equals(""))){%>
<input type="hidden" name="keyField" value="<%=keyField%>">
<input type="hidden" name="keyWord" value="<%=keyWord%>">
<%}%>
<input type="hidden" name="pass">
</form>
</body>
</html>reply.jsp
<!-- reply.jsp -->
<%@page contentType="text/html; charset=UTF-8"%>
<!-- read.jsp에서 원글을 session 저장 -->
<jsp:useBean id="bean" scope="session" class="ch15.BoardBean"/>
<%
String nowPage = request.getParameter("nowPage");
String numPerPage = request.getParameter("numPerPage");
String subject = bean.getSubject();
String content = bean.getContent();
%>
<html>
<head>
<title>JSPBoard</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#FFFFCC">
<div align="center">
<br><br>
<table width="600" cellpadding="3">
<tr>
<td bgcolor="#CCCC00" height="21" align="center">답변하기</td>
</tr>
</table>
<form method="post" action="boardReply" >
<table width="600" cellpadding="7">
<tr>
<td>
<table>
<tr>
<td width="20%">성 명</td>
<td width="80%">
<input name="name" size="30" maxlength="20"></td>
</tr>
<tr>
<td>제 목</td>
<td>
<input name="subject" size="50" value="답변 : <%=subject%>" maxlength="50"></td>
</tr>
<tr>
<td>내 용</td>
<td>
<textarea name="content" rows="12" cols="50">
<%=content %>
========답변 글을 쓰세요.=======
</textarea>
</td>
</tr>
<tr>
<td>비밀 번호</td>
<td>
<input type="password" name="pass" size="15" maxlength="15"></td>
</tr>
<tr>
<td colspan="2" height="5"><hr/></td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="답변등록" >
<input type="reset" value="다시쓰기">
<input type="button" value="뒤로" onClick="history.back()"></td>
</tr>
</table>
</td>
</tr>
</table>
<input type="hidden" name="ip" value="<%=request.getRemoteAddr()%>" >
<input type="hidden" name="nowPage" value="<%=nowPage%>">
<input type="hidden" name="numPerPage" value="<%=numPerPage%>">
<input type="hidden" name="ref" value="<%=bean.getRef()%>">
<input type="hidden" name="pos" value="<%=bean.getPos()%>">
<input type="hidden" name="depth" value="<%=bean.getDepth()%>">
</form>
</div>
</body>
</html>결과화면
투표
db 테이블 생성
DROP TABLE IF EXISTS tblPollList;
create table tblPollList(
num int primary key auto_increment,
question varchar(200) not null,
sdate date,
edate date,
wdate date,
type smallint default 1,
active smallint default 1
);
DROP TABLE IF EXISTS tblPollItem;
create table tblPollItem(
listnum int not null,
itemnum smallint default 0,
item varchar(50) not null,
count int,
primary key(listnum, itemnum)
);
PollItemBean.java
package ch16;
public class PollItemBean {
private int listnum;
private int itemnum;
private String []item;
private int count;
public int getListnum() {
return listnum;
}
public void setListnum(int listnum) {
this.listnum = listnum;
}
public int getItemnum() {
return itemnum;
}
public void setItemnum(int itemnum) {
this.itemnum = itemnum;
}
public String[] getItem() {
return item;
}
public void setItem(String[] item) {
this.item = item;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
}
PollListBean.java
package ch16;
public class PollListBean {
private int num;
private String question;
private String sdate;
private String edate;
private String wdate;
private int type;
private int active;
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getQuestion() {
return question;
}
public void setQuestion(String question) {
this.question = question;
}
public String getSdate() {
return sdate;
}
public void setSdate(String sdate) {
this.sdate = sdate;
}
public String getEdate() {
return edate;
}
public void setEdate(String edate) {
this.edate = edate;
}
public String getWdate() {
return wdate;
}
public void setWdate(String wdate) {
this.wdate = wdate;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
public int getActive() {
return active;
}
public void setActive(int active) {
this.active = active;
}
}
PollMgr.java
package ch16;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;
public class PollMgr {
private DBConnectionMgr pool;
public PollMgr() {
pool = DBConnectionMgr.getInstance();
}
// Max Num : 가장 최신의 num값 리턴
public int getMaxNum() {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
int maxNum = 0;
try {
con = pool.getConnection();
sql = "select max(num) from tblPollList";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()) maxNum = rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return maxNum;
}
// Poll Insert : 설문과 Item 같이 저장
public boolean InsertPoll(PollListBean plBean, PollItemBean piBean) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
boolean flag = false;
try {
con = pool.getConnection();
sql = "insert tblPollList(question,sdate,edate,wdate,type)"
+ "values(?,?,?,now(),?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, plBean.getQuestion());
pstmt.setString(2, plBean.getSdate());
pstmt.setString(3, plBean.getEdate());
pstmt.setInt(4, plBean.getType()); // 1복수, 0은 단일
int cnt = pstmt.executeUpdate();
pstmt.close();
if(cnt==1) { // 정상적인 tblPollList 저장
sql = "insert tblPollItem values(?,?,?,0)";
pstmt = con.prepareStatement(sql);
int listNum = getMaxNum(); // 방금 저장한 리스트의 num 값
String item[] = piBean.getItem();
for (int i = 0; i < item.length; i++) {
if(item[i]==null||item[i].trim().equals(""))
break;
pstmt.setInt(1, listNum);
pstmt.setInt(2, i);
pstmt.setString(3, item[i]);
if(pstmt.executeUpdate()==1)
flag = true;
} // --for
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
return flag;
}
// Poll List
public Vector<PollListBean> getPollList(){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
Vector<PollListBean> vlist = new Vector<PollListBean>();
try {
con = pool.getConnection();
sql = "select * from tblPollList order by num desc";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
PollListBean plBean = new PollListBean();
plBean.setNum(rs.getInt("num"));
plBean.setQuestion(rs.getString("question"));
plBean.setSdate(rs.getString("sdate"));
plBean.setEdate(rs.getString("edate"));
vlist.addElement(plBean);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return vlist;
}
// Poll Read
public PollListBean getPoll(int num) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
PollListBean plBean = new PollListBean();
try {
con = pool.getConnection();
sql = "select num, question, type, active from tblPollList where num =?";
pstmt = con.prepareStatement(sql);
if(num==0)
num = getMaxNum();
pstmt.setInt(1, num);
rs = pstmt.executeQuery();
if(rs.next()) {
plBean.setNum(rs.getInt(1));
plBean.setQuestion(rs.getString(2));
plBean.setType(rs.getInt(3));
plBean.setActive(rs.getInt(4));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return plBean;
}
// Poll Item List
public Vector<String> getItemList(int listNum){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
Vector<String> vlist = new Vector<String>();
try {
con = pool.getConnection();
sql = "select item from tblPollItem where listNum = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, listNum);
if(listNum==0)
listNum = getMaxNum(); // 가장 최신의 설문의 아이템 리턴
rs = pstmt.executeQuery();
while(rs.next()) {
vlist.addElement(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return vlist;
}
// Count Sum : 설문 투표수
// Poll Update : 투표실행
// Poll Veiw : 결과보기
// Max Item Count : 아이템 중에 가장 높은 투표값
}
pollInsert.jsp
<%@ page contentType="text/html; charset=UTF-8" %>
<html>
<head>
<title>JSP Poll</title>
<link href="style.css" rel="stylesheet" type="text/css">
<script type="text/javascript">
function send() {
f = document.frm;
f.sdate.value = f.sdateY.value+"-"
+ f.sdateM.value+"-"+f.sdateD.value;
f.edate.value = f.edateY.value+"-"
+ f.edateM.value+"-"+f.edateD.value;
f.submit();
}
</script>
</head>
<body bgcolor="#FFFFCC">
<div align="center">
<br />
<h2>Poll Program</h2>
<hr width="600" />
<b>설문작성</b>
<hr width="600" />
<form name="frm" method="post" action="pollInsertProc.jsp">
<table border="1" width="500">
<tr>
<td><b>질문</b></td>
<td colspan="2"><input name="question" size="30"></td>
</tr>
<tr>
<td rowspan="10"><b>항목</b></td>
<%
for (int i = 1; i <= 4; i++) {
out.println("<td>" + (i * 2 - 1)
+ ": <input name='item'></td>");
out.println("<td>" + (i * 2)
+ ": <input name='item'></td>");
out.println("</tr>");
if (i == 9) {
out.println("");
} else {
out.println("<tr>");
}
}//for end
%>
<tr>
<td>시작일</td>
<td colspan="2"><select name="sdateY">
<option value="2023">2023
<option value="2024">2024
</select>년 <select name="sdateM">
<%
for (int i = 1; i <= 12; i++) {
out.println("<option value='" + i + "'>" + i);
}
%>
</select>월 <select name="sdateD">
<%
for (int i = 1; i <= 31; i++) {
out.println("<option value='" + i + "'>" + i);
}
%>
</select>일</td>
</tr>
<tr>
<td>종료일</td>
<td colspan=2><select name="edateY">
<option value="2023">2023
<option value="2024">2024
</select>년 <select name="edateM">
<%
for (int i = 1; i <= 12; i++) {
out.println("<option value='" + i + "'>" + i);
}
%>
</select>월 <select name="edateD">
<%
for (int i = 1; i <= 31; i++) {
out.println("<option value='" + i + "'>" + i);
}
%>
</select>일</td>
</tr>
<tr>
<td>복수투표</td>
<td colspan=2>
<input type="radio" name="type" value="1" checked>yes
<input type="radio" name="type" value="0">no
</td>
</tr>
<tr>
<td colspan=3>
<input type="button" value="작성하기" onclick="send()">
<input type="reset" value="다시쓰기">
<input type="button" value="리스트" onClick="javascript:location.href='pollList.jsp'">
</td>
</tr>
</table>
<input type="hidden" name="sdate">
<input type="hidden" name="edate">
</form>
</div>
</body>
</html>pollInsertProc.jsp
<%@page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class="ch16.PollMgr"/>
<jsp:useBean id="plbean" class="ch16.PollListBean"/>
<jsp:setProperty property="*" name="plbean"/>
<jsp:useBean id="pibean" class="ch16.PollItemBean"/>
<jsp:setProperty property="*" name="pibean"/>
<%
boolean result = mgr.InsertPoll(plbean, pibean);
String msg = "설문 추가 실패";
String url = "pollInsert.jsp";
if(result){
msg = "설문 추가 성공";
url = "pollList.jsp";
}
%>
<script>
alert("<%=msg%>");
location.href = "<%=url%>";
</script>
pollForm.jsp
<!-- pollList.jsp -->
<%@page import="ch16.PollListBean"%>
<%@page import="java.util.Vector"%>
<%@page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class="ch16.PollMgr"/>
<html>
<head>
<title>JSP Poll</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#FFFFCC">
<div align="center">
<h2>Poll Program</h2>
<hr width="60%">
<jsp:include page="pollForm.jsp"/>
<b>설문 리스트</b>
<table>
<tr>
<td>
<table border="1">
<tr>
<th width="50">번호</th>
<th width="250" align="left">질문</th>
<th width="200">시작일~종료일</th>
</tr>
<%
Vector<PollListBean> vlist = mgr.getPollList();
for(int i=0;i<vlist.size();i++){
PollListBean plBean = vlist.get(i);
int num = plBean.getNum();
String question = plBean.getQuestion();
String sdate = plBean.getSdate();
String edate = plBean.getEdate();
%>
<tr>
<td align="center"><%=vlist.size()-i%></td>
<td><a href="pollList.jsp?num=<%=num%>"><%=question%></a></td>
<td align="center"><%=sdate+"~"+edate%></td>
</tr>
<%}//---for%>
</table>
</td>
</tr>
<tr>
<td align="center">
<a href="pollInsert.jsp">설문작성하기</a>
</td>
</tr>
</table>
</div>
</body>
</html>
pollForm
<%@page import="java.util.Vector"%>
<%@page import="ch16.PollListBean"%>
<%@page import="ch15.UtilMgr"%>
<%@page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class = "ch16.PollMgr"/>
<%
int num = 0;
if(request.getParameter("num") != null){
num = UtilMgr.parseInt(request, "num");
}
PollListBean plBean = mgr.getPoll(num);
Vector<String> vItem = mgr.getItemList(num);
String question = plBean.getQuestion();
int type = plBean.getType();
int active = plBean.getActive();
int sumCount = 0; //미구현
//out.print(question +" : " + vItem.size());
%>
<form action="pollFormProc.jsp">
<table border="1">
<tr>
<td colspan="2" width="300">
Q : <%=question%> <font color="blue">(<%=sumCount%>)</font>
</td>
</tr>
<tr>
<td colspan="2">
<%
for(int i=0;i<vItem.size();i++){
String item = vItem.get(i);
%>
<%if(type==1){%>
<input type="checkbox" name="itemnum" value="<%=i%>">
<%}else if(type==0){ %>
<input type="radio" name="itemnum" value="<%=i%>">
<%}%>
<%=item%><br>
<%}%>
</td>
</tr>
<tr>
<td width="150">
<%if(active==1){%>
<input type="submit" value="투표">
<%}else{%>
투표종료
<%}%>
</td>
<td>
<input type="button" value="결과"
onclick="javascript:window.open('pollView.jsp?num=<%=num%>'
,'pollView','width=500, height=350')">
</td>
</tr>
</table>
<input type="hidden" name="num" value="<%=num%>">
</form>
결과화면
