1.BoardBean.java - 속성 추가
private String b_fname;
private int b_fsize;
public String getB_fname() {
return b_fname;
}
public void setB_fname(String b_fname) {
this.b_fname = b_fname;
}
public int getB_fsize() {
return b_fsize;
}
public void setB_fsize(int b_fsize) {
this.b_fsize = b_fsize;
}2.BoardDBBean.java - 메소드 수정(속성 추가)
public int insertBoard(BoardBean board) throws Exception {
String sql = "SELECT MAX(B_ID) FROM boardT"; // id 최대값
int re = -1;
int number;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int id = board.getB_id();
int ref = board.getB_ref();
int step = board.getB_step();
int level = board.getB_level();
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if (rs.next()) {
number = rs.getInt(1) + 1;
} else {
number = 1;
}
if (id != 0) { // 답변 글쓰기 이면 업데이트 쿼리 실행
sql = "UPDATE boardT SET B_STEP = B_STEP+1 WHERE B_REF = ? AND B_STEP > ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, ref);
pstmt.setInt(2, step);
pstmt.executeUpdate();
step = step + 1;
level = level + 1;
} else {
ref = number;
step = 0;
level = 0;
}
sql = "INSERT INTO boardT VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, number);
pstmt.setString(2, board.getB_name());
pstmt.setString(3, board.getB_email());
pstmt.setString(4, board.getB_title());
pstmt.setString(5, board.getB_content());
pstmt.setTimestamp(6, board.getB_date());
pstmt.setInt(7, board.getB_hit());
// 조회수 컬럼의 초기값을 0으로 설정 > 데이터 구조에서 디폴트값을 0으로 설정(글쓰면 조회수 0이기 때문)
pstmt.setString(8, board.getB_pwd());
pstmt.setString(9, board.getB_ip());
pstmt.setInt(10, ref);
pstmt.setInt(11, step);
pstmt.setInt(12, level);
pstmt.setString(13, board.getB_fname()); // 파일 이름
pstmt.setInt(14, board.getB_fsize()); // 파일 크기
pstmt.executeUpdate();
re=1;
} catch (SQLException ex) {
System.out.print("글쓰기 실패");
ex.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return re;
}
public ArrayList<BoardBean> listBoard(String pageNumber) throws Exception {
String sql ="SELECT B_ID, B_NAME, B_EMAIL, B_TITLE, B_CONTENT, B_DATE\r\n" +
", B_HIT, B_PWD, B_IP, B_REF, B_STEP, B_LEVEL \r\n" +
", B_FNAME, B_FSIZE FROM boardT ORDER BY B_REF DESC, B_STEP";
String sql2 = "SELECT COUNT(B_ID) FROM boardT";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ResultSet pageSet = null;
int dbCount = 0; // 글 갯수
int absolutePage = 1;
ArrayList<BoardBean> list = new ArrayList<BoardBean>();
try {
conn = getConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
/*
* ResultSet.TYPE_SCROLL_SENSITIVE : ResultSet의 위치 이동, 업데이트 내용 반영
* ResultSet.CONCUR_UPDATABLE : 데이터 변경 가능
* */
pageSet = stmt.executeQuery(sql2);
if (pageSet.next()) {
dbCount = pageSet.getInt(1);
pageSet.close();
}
if (dbCount % BoardBean.pageSize == 0) { // 데이터가 80개면 pageSize = 10이므로 전체 페이지는 8개
BoardBean.pageCount = dbCount / BoardBean.pageSize;
} else { // 데이터가 82개면 pageSize = 10이므로 전체 페이지는 80/10 +1 = 9개
BoardBean.pageCount = dbCount / BoardBean.pageSize+1;
}
if (pageNumber != null) { // 글을 보다가 목록으로 돌아오면 페이지번호를 가지고 있으므로 해당 페이지의 목록을 보여주게 하기 위함
BoardBean.pageNum = Integer.parseInt(pageNumber);
absolutePage = (BoardBean.pageNum - 1) * BoardBean.pageSize + 1;
}
rs = stmt.executeQuery(sql);
if (rs.next()) {
rs.absolute(absolutePage);
int count = 0;
while (count < BoardBean.pageSize) { // 10번 반복
BoardBean bb = new BoardBean();
bb.setB_id(rs.getInt(1));
bb.setB_name(rs.getString(2));
bb.setB_email(rs.getString(3));
bb.setB_title(rs.getString(4));
bb.setB_content(rs.getString(5));
bb.setB_date(rs.getTimestamp(6));
bb.setB_hit(rs.getInt(7));
bb.setB_pwd(rs.getString(8));
bb.setB_ip(rs.getString(9));
bb.setB_ref(rs.getInt(10));
bb.setB_step(rs.getInt(11));
bb.setB_level(rs.getInt(12));
bb.setB_fname(rs.getString(13));
bb.setB_fsize(rs.getInt(14));
list.add(bb); // 반복하면서 list 객체에 bb객체 내용을 담음
if (rs.isLast()) {
break; // 결과가 마지막이면 반복 종료
} else {
rs.next();
}
count++;
}
}
} catch (SQLException ex) {
System.out.print("조회 실패");
ex.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return list;
}
public BoardBean getBoard(int b_id, boolean hitAdd) throws Exception {
String sql = "";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
BoardBean bb = null;
try {
if (hitAdd == true) { // 게시글 볼때는 true, 수정할 때는 false
sql = "UPDATE boardT SET B_HIT=B_HIT+1 WHERE B_ID = ?";
// 조회수 수정하는 쿼리문 해당 메소드가 실행될 때 조회수 1씩 증가해서 업데이트 쿼리 실행
conn = getConnection();
;
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, b_id);
pstmt.executeUpdate(); // 쿼리 실행
} else {
conn = getConnection();
}
sql = "SELECT B_ID, B_EMAIL, B_TITLE, B_NAME, B_CONTENT, B_DATE, B_HIT, B_PWD"
+ ", B_IP, B_REF, B_STEP, B_LEVEL, B_FNAME, B_FSIZE FROM boardT where B_ID = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, b_id);
rs = pstmt.executeQuery();
if (rs.next()) {
bb = new BoardBean();
bb.setB_id(b_id);
bb.setB_name(rs.getString("B_NAME"));
bb.setB_email(rs.getString("B_EMAIL"));
bb.setB_title(rs.getString("B_TITLE"));
bb.setB_content(rs.getString("B_CONTENT"));
bb.setB_date(rs.getTimestamp("B_DATE"));
bb.setB_hit(rs.getInt("B_HIT"));
bb.setB_pwd(rs.getString("B_PWD"));
bb.setB_ip(rs.getString("B_IP"));
bb.setB_ref(rs.getInt("B_REF"));
bb.setB_step(rs.getInt("B_STEP"));
bb.setB_level(rs.getInt("B_LEVEL"));
bb.setB_fname(rs.getString("B_FNAME"));
bb.setB_fsize(rs.getInt("B_FSIZE"));
}
} catch (SQLException ex) {
System.out.print("조회 실패");
ex.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return bb;
}3.list.jsp - 파일 업로드 확인을 위한 수정
<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.sql.Timestamp"%>
<%@page import="magic.board.BoardBean"%>
<%@page import="java.util.ArrayList"%>
<%@page import="magic.board.BoardDBBean"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8"); %>
<%
int b_id,b_hit,b_level, b_fsize;
String b_name, b_email, b_title, b_content, b_fname;
Timestamp b_date;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
// Timestamp로 받아온 날짜의 형식을 지정
BoardDBBean bdb = BoardDBBean.getInstance();
String pageNum = request.getParameter("pageNum");
if(pageNum == null){
pageNum = "1";
}
ArrayList<BoardBean> list = bdb.listBoard(pageNum);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<center>
<h1>게시판에 등록된 글 목록 보기</h1>
<table width="600">
<tr>
<td align="right">
<a href="write.jsp?pageNum=<%= pageNum %>">글 쓰 기</a>
</td>
</tr>
</table>
<table border="1" width="800">
<tr height="25">
<td width="40" align="center">번호</td>
<td width="80" align="center">첨부파일</td>
<td width="450" align="center">글제목</td>
<td width="120" align="center">작성자</td>
<td width="130" align="center">작성일</td>
<td width="60" align="center">조회수</td>
</tr>
<%
for(int i=0; i < list.size(); i++) {
BoardBean board = list.get(i); // 배열에 넣은 역순으로 board 객체에 값을 넣어줌
b_id=board.getB_id();
b_name=board.getB_name();
b_email=board.getB_email();
b_title= board.getB_title();
b_date= board.getB_date();
b_hit= board.getB_hit();
b_level = board.getB_level();
b_fname = board.getB_fname();
b_fsize = board.getB_fsize();
%>
<tr height="25" bgcolor="#E8E8E8" onmouseover="this.style.backgroundColor='#AEBAB4'"
onmouseout="this.style.backgroundColor='#E8E8E8'">
<td align="center"><%= b_id %></td>
<td>
<%
if(b_fsize > 0) {
%>
<img src="../images/zip.gif" />
<%
}
%>
</td>
<td id="title" >
<%
if(b_level > 0){
for(int j=0; j<b_level; j++){
%>
<%
}
%>
<img src="../images/AnswerLine.gif" />
<%
}
%>
<a href="show.jsp?b_id=<%=b_id %>&pageNum=<%= pageNum %>"><%= b_title %></a>
</td>
<td align="center">
<a href="mailto:<%= b_email %>>"><!-- 작성자에게 메일을 보내는 a태그 -->
<%= b_name %>
</a>
</td>
<td align="center"><%= sdf.format(b_date) %></a>
</td>
</td>
<td align="center"><%= b_hit %></a>
</td>
</tr>
<% } %>
</table>
<%= BoardBean.pageNumber(4) %>
</center>
</body>
</html>4.write.jsp - 파일 업로드를 위한 수정
<%@page import="magic.board.BoardBean"%>
<%@page import="magic.board.BoardDBBean"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="board.js" charset="UTF-8"></script>
</head>
<body>
<%
int bid = 0,ref = 1,level = 0,step = 0;
String title="";
if(request.getParameter("b_id") != null) {
bid = Integer.parseInt(request.getParameter("b_id"));
}
BoardDBBean bdb = BoardDBBean.getInstance();
BoardBean bb = bdb.getBoard(bid, false);
if(bb != null) {
ref = bb.getB_ref();
step = bb.getB_step();
level = bb.getB_level();
title = bb.getB_title();
}
String pageNum = request.getParameter("pageNum");
%>
<center>
<h1>글 올 리 기</h1>
<form method="post" action="write_ok.jsp?pageNum=<%= pageNum %>" name="write_frm" enctype="multipart/form-data">
<table>
<tr height="30">
<td width="80">작성자</td>
<td width="100">
<input type="text" id="name" name="b_name" size="10" maxlength="20"/>
</td>
<td width="80">이메일</td>
<td width="240">
<input type="text" id="email" name="b_email" size="24" maxlength="50" />
</td>
</tr>
<tr>
<td width="80">글제목</td>
<td colspan="3" width="460">
<%
if(bid == 0) { // id값이 0이 아니면 제목에 [답변]: 제목이 달려있는 상태
%>
<input type="text" id="title" name="b_title" size="58" maxlength="80" />
<%
} else {
%>
<input type="text" id="title" name="b_title" size="58" maxlength="80"
value="[답변]:<%= title %>" />
<%
}
%>
</td>
</tr>
<tr height="30">
<td width="80">파 일</td>
<td colspan="3" width="140"><input type="file" name="b_fname" size="40" maxlength="100" /></td>
</tr>
<tr>
<td colspan="4">
<textarea cols="70" rows="10" id="content" name="b_content" maxlength="3000"></textarea>
</td>
<tr>
<tr>
<td width="80">암 호</td>
<td width="200">
<input type="password" id="pwd" name="b_pwd" size="12" maxlength="12" />
</td>
</tr>
<tr>
<td><input type="hidden" name="b_id" value="<%= bid %>" /></td>
<td><input type="hidden" name="b_ref" value="<%= ref %>" /></td>
<td><input type="hidden" name="b_step" value="<%= step %>" /></td>
<td><input type="hidden" name="b_level" value="<%= level %>" /></td>
</tr>
<tr height="50">
<td colspan="4" align="center">
<input type="button" value="글쓰기" onclick="check_ok()" />
<input type="reset" value="다시작성" />
<input type="button" value="글목록" onclick="location.href='list.jsp?pageNum=<%= pageNum %>'" />
</td>
</tr>
</table>
</form>
</center>
</body>
</html>5.write_ok.jsp - 파일 업로드를 위한 수정
<%@page import="com.jspsmart.upload.File"%>
<%@page import="com.jspsmart.upload.SmartUpload"%>
<%@page import="java.net.InetAddress"%>
<%@page import="magic.board.BoardDBBean"%>
<%@page import="java.sql.Timestamp"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8"); %>
<jsp:useBean class="magic.board.BoardBean" id="board"></jsp:useBean>
<jsp:setProperty property="*" name="board"/>
<%
SmartUpload upload = new SmartUpload();
upload.initialize(pageContext); // 객체 초기화
upload.upload();
String fName = null;
int fSize = 0;
File file = upload.getFiles().getFile(0);
if(!file.isMissing()){ // isMissing() : 파일이 없음
fName = file.getFileName();
fSize = file.getSize();
file.saveAs("/upload/"+file.getFileName()); // upload 폴더에 파일 이름을 읽어 저장
}
%>
<%
InetAddress address = InetAddress.getLocalHost();
String ip = address.getHostAddress();
String content = upload.getRequest().getParameter("b_content");
content = content.replace("\r\n", "<br>");
board.setB_name(upload.getRequest().getParameter("b_name"));
board.setB_email(upload.getRequest().getParameter("b_email"));
board.setB_title(upload.getRequest().getParameter("b_title"));
board.setB_content(content);
board.setB_pwd(upload.getRequest().getParameter("b_pwd"));
board.setB_id(Integer.parseInt(upload.getRequest().getParameter("b_id")));
board.setB_ref(Integer.parseInt(upload.getRequest().getParameter("b_ref")));
board.setB_step(Integer.parseInt(upload.getRequest().getParameter("b_step")));
board.setB_level(Integer.parseInt(upload.getRequest().getParameter("b_level")));
board.setB_date(new Timestamp(System.currentTimeMillis()));
board.setB_ip(ip);
board.setB_fname(fName);
board.setB_fsize(fSize);
BoardDBBean db = BoardDBBean.getInstance();
int re = db.insertBoard(board);
if(re == 1){
response.sendRedirect("list.jsp");
}else{
response.sendRedirect("write.jsp");
}
%>6.show.jsp - 업로드한 파일 확인
<%@page import="java.text.SimpleDateFormat"%>
<%@page import="magic.board.BoardBean"%>
<%@page import="magic.board.BoardDBBean"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
BoardDBBean bdb = BoardDBBean.getInstance();
BoardBean bb = bdb.getBoard(Integer.parseInt(request.getParameter("b_id")),true);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
%>
<center>
<h1>글 내 용 보 기</h1>
<table border="1" width="600" cellspacing="0">
<tr height="30" align="center">
<td width="100">글번호</td>
<td width="200"><%= bb.getB_id() %></td>
<td width="100">조회수</td>
<td width="200"><%= bb.getB_hit() %></td>
</tr>
<tr height="30" align="center">
<td width="100" >작성자</td>
<td width="200"><%= bb.getB_name() %></td>
<td width="100" >작성일</td>
<td width="200"><%= sdf.format(bb.getB_date()) %></td>
<tr height="30">
<td width="100" align="center">글제목</td>
<td width="200" colspan="3"><%= bb.getB_title() %></td>
</tr>
<tr height="30">
<td width="100" align="center">글내용</td>
<td width="200" colspan="3"><%= bb.getB_content() %></td>
</tr>
<tr height="30">
<td colspan="4" align="right">
<input type="button" value="글수정" onclick="location.href='edit.jsp?b_id=<%= bb.getB_id() %>'" />
<input type="button" value="글삭제" onclick="location.href='delete.jsp?b_id=<%= bb.getB_id() %>'" />
<input type="button" value="답변글" onclick="location.href='write.jsp?b_id=<%= bb.getB_id() %>'" />
<input type="button" value="글목록" onclick="location.href='list.jsp'" />
</td>
</tr>
</table>
</center>
</body>
</html>7.delete_ok.jsp - 업로드한 파일 삭제를 위한 수정
<%@page import="java.io.File"%>
<%@page import="magic.board.BoardBean"%>
<%@page import="java.util.ArrayList"%>
<%@page import="magic.board.BoardDBBean"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String pageNum = request.getParameter("pageNum");
String pwd = request.getParameter("pwd");
int b_id = Integer.parseInt(request.getParameter("b_id"));
BoardDBBean bdb = BoardDBBean.getInstance();
BoardBean board = bdb.getBoard(b_id, false);
String fName = board.getB_fname();
String fPath = "D:\\woosung\\space_jsp\\.metadata\\.plugins\\org.eclipse.wst.server.core\\tmp1\\wtpwebapps\\magicWebApp\\upload\\";
int re = bdb.deleteBoard(pwd, b_id);
if(re == 1){
if(fName != null){
File file = new File(fPath+fName); // 생성자 매개변수: 폴더 경로 + 파일 이름
file.delete();
}
%>
<script>
alert("글이 삭제되었습니다.");
location.href= "list.jsp?pageNum=<%= pageNum %>";
</script>
<%
} else if(re == 0) {
%>
<script>
alert("비밀번호가 틀렸습니다.")
history.go(-1);
</script>
<%
}else if(re == -1) {
%>
<script>
alert("삭제에 실패했습니다.")
history.go(-1);
</script>
<%
}
%>'java,jsp,spring > JSP' 카테고리의 다른 글
| JSP 게시판 - 파일 다운로드 (1) | 2022.09.15 |
|---|---|
| JSP 게시판 페이징 (1) | 2022.09.13 |
| JSP 게시판 답글 기능 (1) | 2022.09.11 |
| JSP 게시판 (4) 글 삭제 (0) | 2022.09.06 |
| JSP 게시판 (3) 글 수정 (0) | 2022.09.06 |