Programming/JSP
[JSP] JAVA코드 DAO / DTO 생성2 (파일 업로드 다운로드 포함)
너굴위
2023. 10. 11. 19:59
728x90
반응형
< 이전 포스팅 정리 >
jsp
pattern
.jsp -hardcoding
mvc model1 mvc(model, view, controller)
view(응답처리), controller(요청처리) => jsp
model => java(beans) => back-end : design(X) / data(O)
DAO - 각 페이지별 메서드
(D)TO - 데이터 전송
view(응답처리) => front-end : design(O) / data(X)
mvc model2(*)
게시판 DAO / DTO 를 통해 데이터 다루기 (PDSModel1Ex01)
- 기존 ok자바파일을 통해 다루어지던 데이터들을 DAO와 DTO를 통해 set / get 할 수 있도록 한다.
- DAO - 각 페이지별 메서드
- (D)TO - 데이터 전송
//board_list1.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="model1.BoardTO" %>
<%@ page import="model1.BoardDAO" %>
<%@ page import="java.util.ArrayList" %>
<%
BoardDAO dao = new BoardDAO();
ArrayList<BoardTO> datas = dao.boardList();
int totalRecord=datas.size();
StringBuilder sbHTML = new StringBuilder();
for(BoardTO to: datas) {
String seq = to.getSeq();
String subject = to.getSubject();
String writer = to.getWriter();
String wdate = to.getWdate();
String hit = to.getHit();
int wgap = to.getWgap();
long filesize = to.getFilesize();
sbHTML.append("<tr>");
sbHTML.append("<td> </td>");
sbHTML.append("<td>"+seq+"</td>");
sbHTML.append("<td class='left'><a href='board_view1.jsp?seq="+seq+"'>"+subject+"</a> ");
if(wgap == 0) {
sbHTML.append("<img src='../../images/icon_new.gif' alt='NEW'>");
}
sbHTML.append("</td>");
sbHTML.append("<td>"+writer+"</td>");
sbHTML.append("<td>"+wdate+"</td>");
sbHTML.append("<td>"+hit+"</td>");
if(filesize != 0) {
sbHTML.append("<td><img src='../../images/icon_file.gif' /></td>");
} else {
sbHTML.append("<td> </td>");
}
sbHTML.append("</tr>");
}
%>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1.0,minimum-scale=1.0,maximum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="../../css/board.css">
</head>
<body>
<!-- 상단 디자인 -->
<div class="con_title">
<h3>게시판</h3>
<p>HOME > 게시판 > <strong>게시판</strong></p>
</div>
<div class="con_txt">
<div class="contents_sub">
<div class="board_top">
<div class="bold">
<p>총 <span class="txt_orange">1</span>건</p>
</div>
</div>
<!--게시판-->
<div class="board">
<table>
<tr>
<th width="3%"> </th>
<th width="5%">번호</th>
<th>제목</th>
<th width="10%">글쓴이</th>
<th width="17%">등록일</th>
<th width="5%">조회</th>
<th width="3%"> </th>
</tr>
<%=sbHTML.toString() %>
</table>
</div>
<div class="btn_area">
<div class="align_right">
<input type="button" value="쓰기" class="btn_write btn_txt01" style="cursor: pointer;" onclick="location.href='board_write1.jsp'" />
</div>
</div>
<!--//게시판-->
</div>
</div>
<!--//하단 디자인 -->
</body>
</html>
//board_write1.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1.0,minimum-scale=1.0,maximum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="../../css/board.css">
<script type="text/javascript">
window.onload = function() {
document.getElementById( 'wbtn' ).onclick = function() {
if( document.wfrm.info.checked == false ) {
alert( '동의를 하셔야 합니다.' );
return;
}
if( document.wfrm.writer.value.trim() == '' ) {
alert( '글쓴이를 입력 하셔야 합니다.' );
return;
}
if( document.wfrm.subject.value.trim() == '' ) {
alert( '제목을 입력 하셔야 합니다.' );
return;
}
if( document.wfrm.password.value.trim() == '' ) {
alert( '비밀번호를 입력 하셔야 합니다.' );
return;
}
document.wfrm.submit();
};
};
</script>
</head>
<body>
<!-- 상단 디자인 -->
<div class="con_title">
<h3>게시판</h3>
<p>HOME > 게시판 > <strong>게시판</strong></p>
</div>
<div class="con_txt">
<form action="board_write1_ok.jsp" method="post" name="wfrm" enctype="multipart/form-data">
<div class="contents_sub">
<!--게시판-->
<div class="board_write">
<table>
<tr>
<th class="top">글쓴이</th>
<td class="top">
<input type="text" name="writer" value="" class="board_view_input_mail" maxlength="5" />
</td>
</tr>
<tr>
<th>제목</th>
<td><input type="text" name="subject" value="" class="board_view_input" /></td>
</tr>
<tr>
<th>비밀번호</th>
<td><input type="password" name="password" value="" class="board_view_input_mail"/></td>
</tr>
<tr>
<th>내용</th>
<td><textarea name="content" class="board_editor_area"></textarea></td>
</tr>
<tr>
<th>이메일</th>
<td>
<input type="text" name="mail1" value="" class="board_view_input_mail"/> @ <input type="text" name="mail2" value="" class="board_view_input_mail"/>
</td>
</tr>
<tr>
<th>첨부파일</th>
<td>
<input type="file" name="upload" value="" />
</td>
</tr>
</table>
<table>
<tr>
<br />
<td style="text-align:left;border:1px solid #e0e0e0;background-color:f9f9f9;padding:5px">
<div style="padding-top:7px;padding-bottom:5px;font-weight:bold;padding-left:7px;font-family: Gulim,Tahoma,verdana;">※ 개인정보 수집 및 이용에 관한 안내</div>
<div style="padding-left:10px;">
<div style="width:97%;height:95px;font-size:11px;letter-spacing: -0.1em;border:1px solid #c5c5c5;background-color:#fff;padding-left:14px;padding-top:7px;">
1. 수집 개인정보 항목 : 회사명, 담당자명, 메일 주소, 전화번호, 홈페이지 주소, 팩스번호, 주소 <br />
2. 개인정보의 수집 및 이용목적 : 제휴신청에 따른 본인확인 및 원활한 의사소통 경로 확보 <br />
3. 개인정보의 이용기간 : 모든 검토가 완료된 후 3개월간 이용자의 조회를 위하여 보관하며, 이후 해당정보를 지체 없이 파기합니다. <br />
4. 그 밖의 사항은 개인정보취급방침을 준수합니다.
</div>
</div>
<div style="padding-top:7px;padding-left:5px;padding-bottom:7px;font-family: Gulim,Tahoma,verdana;">
<input type="checkbox" name="info" value="1" class="input_radio"> 개인정보 수집 및 이용에 대해 동의합니다.
</div>
</td>
</tr>
</table>
</div>
<div class="btn_area">
<div class="align_left">
<input type="button" value="목록" class="btn_list btn_txt02" style="cursor: pointer;" onclick="location.href='board_list1.jsp'" />
</div>
<div class="align_right">
<input type="button" id="wbtn" value="쓰기" class="btn_write btn_txt01" style="cursor: pointer;" />
</div>
</div>
<!--//게시판-->
</div>
</form>
</div>
<!-- 하단 디자인 -->
</body>
</html>
//board_write1_ok.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="model1.BoardDAO" %>
<%@ page import="model1.BoardTO" %>
<%@ page import="com.oreilly.servlet.multipart.DefaultFileRenamePolicy" %>
<%@ page import="com.oreilly.servlet.MultipartRequest" %>
<%@ page import="java.io.File" %>
<%
String uploadPath = "F:\\자바 국비\\java모음\\java파일모음\\jsp-workspace\\PDSModel1Ex01\\src\\main\\webapp\\upload";
int maxFileSize = 2 * 1024 * 1024;
String encType = "utf-8";
MultipartRequest multi
= new MultipartRequest(request, uploadPath, maxFileSize, encType, new DefaultFileRenamePolicy() );
BoardTO to = new BoardTO();
to.setSubject(multi.getParameter( "subject" ));
to.setWriter(multi.getParameter( "writer" ));
to.setMail("");
if( !multi.getParameter( "mail1" ).equals("")
&& !multi.getParameter( "mail2" ).equals("") ) {
to.setMail(multi.getParameter( "mail1" ) + "@" + multi.getParameter( "mail2" ));
}
to.setPassword(multi.getParameter( "password" ));
to.setContent(multi.getParameter( "content" ));
to.setWip(request.getRemoteAddr());
to.setFilename(multi.getFilesystemName( "upload" ));
to.setFilesize(0);
if( multi.getFile( "upload") != null ) {
to.setFilesize(multi.getFile( "upload" ).length());
}
BoardDAO dao = new BoardDAO();
int flag = dao.boardWriteOk(to);
out.println( "<script type='text/javascript'>" );
if( flag == 0 ) {
out.println( "alert( '글쓰기 성공' );" );
out.println( "location.href='./board_list1.jsp';" );
} else if( flag == 1 ) {
out.println( "alert( '글쓰기 실패' );" );
out.println( "history.back();" );
}
out.println( "</script>" );
%>
//board_view1.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="model1.BoardTO" %>
<%@ page import="model1.BoardDAO" %>
<%
StringBuilder sbHTML = new StringBuilder();
request.setCharacterEncoding("utf-8");
String seq = request.getParameter("seq");
BoardTO to = new BoardTO();
to.setSeq(request.getParameter("seq"));
BoardDAO dao= new BoardDAO();
to = dao.boardView(to);
String subject=to.getSubject();
String writer =to.getWriter();
String mail=to.getMail();
String wdate=to.getWdate();
String wip=to.getWip();
String hit=to.getHit();
String content=to.getContent();
String filename = to.getFilename();
long filesize = to.getFilesize();
String file="";
if( filesize !=0 ) {
file = "<a href='../../download.jsp?filename="+filename+"'>"+filename+"(" + filesize + "Kbyte)</a>";
}
%>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1.0,minimum-scale=1.0,maximum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="../../css/board.css">
</head>
<body>
<!-- 상단 디자인 -->
<div class="con_title">
<h3>게시판</h3>
<p>HOME > 게시판 > <strong>게시판</strong></p>
</div>
<div class="con_txt">
<div class="contents_sub">
<!--게시판-->
<div class="board_view">
<table>
<tr>
<th width="10%">제목</th>
<td width="60%"><%=subject %></td>
<th width="10%">등록일</th>
<td width="20%"><%=wdate %></td>
</tr>
<tr>
<th>글쓴이</th>
<td><%=writer %>(<%=mail %>)(<%=wip %>)</td>
<th>조회</th>
<td><%=hit %></td>
</tr>
<tr>
<th>첨부 파일</th>
<td><%=file%> </td>
<th></th>
<td></td>
</tr>
<tr>
<td colspan="4" height="200" valign="top" style="padding: 20px; line-height: 160%"><%=content %></td>
</tr>
</table>
</div>
<div class="btn_area">
<div class="align_left">
<input type="button" value="목록" class="btn_list btn_txt02" style="cursor: pointer;" onclick="location.href='board_list1.jsp'" />
</div>
<div class="align_right">
<input type="button" value="수정" class="btn_list btn_txt02" style="cursor: pointer;" onclick="location.href='board_modify1.jsp?seq=<%=seq %>'" />
<input type="button" value="삭제" class="btn_list btn_txt02" style="cursor: pointer;" onclick="location.href='board_delete1.jsp?seq=<%=seq %>'" />
<input type="button" value="쓰기" class="btn_write btn_txt01" style="cursor: pointer;" onclick="location.href='board_write1.jsp'" />
</div>
</div>
<!--//게시판-->
</div>
</div>
<!-- 하단 디자인 -->
</body>
</html>
//board_modify1.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="model1.BoardTO" %>
<%@ page import="model1.BoardDAO" %>
<%
request.setCharacterEncoding( "utf-8" );
BoardTO to = new BoardTO();
to.setSeq(request.getParameter( "seq" ));
BoardDAO dao= new BoardDAO();
to=dao.boardModify(to);
String seq=to.getSeq();
String subject =to.getSubject();
String writer=to.getWriter();
String content=to.getContent();
String mail[]=null;
String filename = to.getFilename();
long filesize = to.getFilesize();
//글쓴이 제목 내용 이메일 채워져서 나옴 (비밀번호 빼고)
if(to.getMail().equals("")){ //mail의 내용이 있다면
mail=new String[]{"",""}; //mail의 공간을 2개로 나눔 (공백으로 공간 마련하기)
}else{
mail=to.getMail().split("@"); //@을 기준으로 앞뒤를 나누어 문자열을 얻어 배열에 저장
}
%>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1.0,minimum-scale=1.0,maximum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="../../css/board.css">
<script type="text/javascript">
window.onload = function() {
document.getElementById( 'mbtn' ).onclick = function() {
if( document.mfrm.subject.value.trim() == '' ) {
alert( '제목을 입력하셔야 합니다.' );
return;
}
if( document.mfrm.password.value.trim() == '' ) {
alert( '비밀번호를 입력하셔야 합니다.' );
return;
}
document.mfrm.submit();
};
};
</script>
</head>
<body>
<!-- 상단 디자인 -->
<div class="con_title">
<h3>게시판</h3>
<p>HOME > 게시판 > <strong>게시판</strong></p>
</div>
<div class="con_txt">
<form action="./board_modify1_ok.jsp" method="post" name="mfrm" enctype="multipart/form-data">
<input type="hidden" name="seq" value="<%=seq %>" />
<div class="contents_sub">
<!--게시판-->
<div class="board_write">
<table>
<tr>
<th class="top">글쓴이</th>
<td class="top">
<input type="text" name="writer" value="<%=writer %>" class="board_view_input_mail" maxlength="5" readonly/>
</td>
</tr>
<tr>
<th>제목</th>
<td><input type="text" name="subject" value="<%=subject %>" class="board_view_input" /></td>
</tr>
<tr>
<th>비밀번호</th>
<td><input type="password" name="password" value="" class="board_view_input_mail"/></td>
</tr>
<tr>
<th>내용</th>
<td><textarea name="content" class="board_editor_area"><%=content %></textarea></td>
</tr>
<tr>
<th>이메일</th>
<td>
<input type="text" name="mail1" value="<%=mail[0] %>" class="board_view_input_mail"/> @ <input type="text" name="mail2" value="<%=mail[1] %>" class="board_view_input_mail"/>
</td>
</tr>
<tr>
<th>첨부파일</th>
<td>
기존 파일명 : <%=filename %><br /><br />
<input type="file" name="upload" value="" />
</td>
</tr>
</table>
</div>
<div class="btn_area">
<div class="align_left">
<input type="button" value="목록" class="btn_list btn_txt02" style="cursor: pointer;" onclick="location.href='board_list1.jsp'" />
<input type="button" value="보기" class="btn_list btn_txt02" style="cursor: pointer;" onclick="location.href='board_view1.jsp?seq=<%=seq %>'" />
</div>
<div class="align_right">
<input type="button" id="mbtn" value="수정" class="btn_write btn_txt01" style="cursor: pointer;" />
</div>
</div>
<!--//게시판-->
</div>
</form>
</div>
<!-- 하단 디자인 -->
</body>
</html>
//board_modify1_ok.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="model1.BoardTO" %>
<%@ page import="model1.BoardDAO" %>
<%@ page import="com.oreilly.servlet.multipart.DefaultFileRenamePolicy" %>
<%@ page import="com.oreilly.servlet.MultipartRequest" %>
<%@ page import="java.io.File" %>
<%
String uploadPath = "F:\\자바 국비\\java모음\\java파일모음\\jsp-workspace\\PDSModel1Ex01\\src\\main\\webapp\\upload\\";
int maxFileSize = 2 * 1024 * 1024;
String encType = "utf-8";
MultipartRequest multi
= new MultipartRequest( request, uploadPath, maxFileSize, encType, new DefaultFileRenamePolicy() );
BoardTO to = new BoardTO();
to.setSeq( multi.getParameter( "seq" ) );
to.setSubject( multi.getParameter( "subject" ) );
to.setMail( "" );
if( !multi.getParameter( "mail1" ).equals("")
&& !multi.getParameter( "mail2" ).equals("") ) {
to.setMail( multi.getParameter( "mail1" ) + "@" + multi.getParameter( "mail2" ) );
}
to.setPassword( multi.getParameter( "password" ) );
to.setContent( multi.getParameter( "content" ) );
to.setFilename( multi.getFilesystemName( "upload" ) );
to.setFilesize( 0 );
if( multi.getFile( "upload") != null ) {
to.setFilesize( multi.getFile( "upload" ).length() );
}
BoardDAO dao = new BoardDAO();
int flag = dao.boardModifyOk( to );
String seq = to.getSeq();
out.println( "<script type='text/javascript'>" );
if(flag == 0) {
out.println( "alert('글수정 성공');" );
out.println( "location.href='board_view1.jsp?seq=" + seq + "';" );
} else if(flag == 1) {
out.println( "alert('비밀번호 오류');" );
out.println( "history.back();" );
} else {
out.println( "alert('글수정 에러');" );
out.println( "history.back();" );
}
out.println( "</script>" );
%>
//board_delete1.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="model1.BoardTO" %>
<%@ page import="model1.BoardDAO" %>
<%
request.setCharacterEncoding("utf-8");
BoardTO to = new BoardTO();
to.setSeq(request.getParameter("seq"));
BoardDAO dao= new BoardDAO();
to = dao.boardDelete(to);
String seq = to.getSeq();
String subject =to.getSubject();
String writer=to.getWriter();
%>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1.0,minimum-scale=1.0,maximum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="../../css/board.css">
<script type="text/javascript">
window.onload = function() {
document.getElementById('dbtn').onclick = function() {
//alert('click');
if(document.dfrm.password.value.trim() == '') {
alert('비밀번호를 입력하셔야 합니다.');
return;
}
document.dfrm.submit();
};
};
</script>
</head>
<body>
<!-- 상단 디자인 -->
<div class="con_title">
<h3>게시판</h3>
<p>HOME > 게시판 > <strong>게시판</strong></p>
</div>
<div class="con_txt">
<form action="./board_delete1_ok.jsp" method="post" name="dfrm">
<input type="hidden" name="seq" value="<%=seq %>">
<div class="contents_sub">
<!--게시판-->
<div class="board_write">
<table>
<tr>
<th class="top">글쓴이</th>
<td class="top"><input type="text" name="writer" value="<%=writer %>" class="board_view_input_mail" maxlength="5" readonly/></td>
</tr>
<tr>
<th>제목</th>
<td><input type="text" name="subject" value="<%=subject %>" class="board_view_input" readonly/></td>
</tr>
<tr>
<th>비밀번호</th>
<td><input type="password" name="password" value="" class="board_view_input_mail"/></td>
</tr>
</table>
</div>
<div class="btn_area">
<div class="align_left">
<input type="button" value="목록" class="btn_list btn_txt02" style="cursor: pointer;" onclick="location.href='board_list1.jsp'" />
<input type="button" value="보기" class="btn_list btn_txt02" style="cursor: pointer;" onclick="location.href='board_view1.jsp?seq=<%=seq %>'" />
</div>
<div class="align_right">
<input type="button" id="dbtn" value="삭제" class="btn_write btn_txt01" style="cursor: pointer;" />
</div>
</div>
<!--//게시판-->
</div>
</form>
</div>
<!-- 하단 디자인 -->
</body>
</html>
//board_delete1_ok.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="model1.BoardDAO" %>
<%@ page import="model1.BoardTO" %>
<%
request.setCharacterEncoding("utf-8");
BoardTO to = new BoardTO();
to.setSeq(request.getParameter("seq"));
to.setPassword(request.getParameter("password"));
BoardDAO dao= new BoardDAO();
int flag = dao.boardDeleteOk(to);
out.println("<script type='text/javascript'>;");
if(flag == 0) {
//정상
out.println("alert('글 삭제 성공');");
out.println("location.href='./board_list1.jsp';");
} else if(flag == 1){
//비밀번호 오류
out.println("alert('비밀번호 오류');");
out.println("history.back();");
} else if(flag == 2){
//시스템 오류
out.println("alert('글 삭제 실패');");
out.println("history.back();");
}
out.println("</script>");
%>
=> 기존 게시판에 import문을 DAO와 TO로 설정
DAO / DTO
//BoardDAO.java
package model1;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class BoardDAO {
private DataSource dataSource;
private String uploadPath = "F:\\자바 국비\\java모음\\java파일모음\\jsp-workspace\\PDSModel1Ex01\\src\\main\\webapp\\upload\\";
public BoardDAO() {
try {
Context initCtx = new InitialContext();
Context envCtx = (Context)initCtx.lookup("java:comp/env");
this.dataSource = (DataSource)envCtx.lookup( "jdbc/mariadb2" );
} catch (NamingException e) {
// TODO Auto-generated catch block
System.out.println("에러 : " + e.getMessage());
}
}
public void boardWrite() {
}
public int boardWriteOk(BoardTO to) {
Connection conn = null;
PreparedStatement pstmt = null;
int flag = 1;
try {
conn = this.dataSource.getConnection();
String sql = "insert into pds_board1 values ( 0, ?, ?, ?, ?, ?, ?, ?, 0, ?, now() )";
pstmt = conn.prepareStatement( sql );
pstmt.setString( 1, to.getSubject() );
pstmt.setString( 2, to.getWriter() );
pstmt.setString( 3, to.getMail() );
pstmt.setString( 4, to.getPassword() );
pstmt.setString( 5, to.getContent() );
pstmt.setString( 6, to.getFilename() );
pstmt.setLong( 7, to.getFilesize() );
pstmt.setString( 8, to.getWip() );
if( pstmt.executeUpdate() == 1 ) {
flag = 0;
}
} catch( SQLException e ) {
System.out.println( "[에러] " + e.getMessage() );
} finally {
if( pstmt != null ) try{pstmt.close();}catch(SQLException e) {};
if( conn != null ) try{conn.close();}catch(SQLException e) {};
}
return flag;
}
public ArrayList<BoardTO> boardList() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ArrayList<BoardTO> datas = new ArrayList<>();
try{
conn = this.dataSource.getConnection();
String sql = "select seq, subject, writer, filesize, date_format(wdate, '%Y-%m-%d') wdate, hit, datediff(now(), wdate) wgap from pds_board1 order by seq desc";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
BoardTO to = new BoardTO();
to.setSeq(rs.getString("seq"));
to.setSubject(rs.getString("subject"));
to.setWriter(rs.getString("writer"));
to.setWdate(rs.getString("wdate"));
to.setHit(rs.getString("hit"));
to.setWgap(rs.getInt("wgap"));
to.setFilesize(rs.getLong("filesize"));
to.setWdate(rs.getString("wdate"));
to.setHit(rs.getString("hit"));
to.setWgap(rs.getInt("wgap"));
datas.add(to);
}
}catch(SQLException e) {
System.out.println("에러 : " + e.getMessage());
} finally {
if(rs!=null) try {rs.close();}catch(SQLException e) {};
if(pstmt!=null)try{pstmt.close();} catch(SQLException e) {};
if(conn!=null)try{conn.close();} catch(SQLException e) {};
}
return datas;
}
public BoardTO boardView(BoardTO to) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = this.dataSource.getConnection();
//조회수 증가
String sql = "update board1 set hit=hit+1 where seq=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,to.getSeq());
pstmt.executeQuery();
sql = "select subject, writer, mail, wip, wdate, hit, filename, filesize, content from pds_board1 where seq=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, to.getSeq());
rs = pstmt.executeQuery();
if(rs.next()) {
to.setSubject(rs.getString("subject"));
to.setWriter(rs.getString("writer"));
to.setMail(rs.getString("mail"));
to.setWdate(rs.getString("wdate"));
to.setWip(rs.getString("wip"));
to.setHit(rs.getString("hit"));
to.setFilename(rs.getString( "filename" ) == null ? "" : rs.getString( "filename" ));
to.setFilesize(rs.getLong("filesize")/1024);
to.setContent(rs.getString("content")==null ? "" : rs.getString("content").replaceAll("\n","<br />"));
}
}catch(SQLException e) {
System.out.println("에러 : " + e.getMessage());
} finally {
if(rs!=null)try{rs.close();}catch(SQLException e) {};
if(pstmt!=null)try{pstmt.close();}catch(SQLException e) {};
if(conn!=null)try{conn.close();}catch(SQLException e) {};
}
return to;
}
public BoardTO boardModify(BoardTO to) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = this.dataSource.getConnection();
String sql = "select subject, writer, mail, content, filename, filesize from pds_board1 where seq=?";
pstmt = conn.prepareStatement( sql );
pstmt.setString( 1, to.getSeq() );
rs = pstmt.executeQuery();
if( rs.next() ) {
to.setSubject(rs.getString( "subject" ));
to.setWriter(rs.getString( "writer" ));
to.setMail(rs.getString("mail"));
to.setContent(rs.getString( "content" ));
to.setFilename(rs.getString( "filename" ) == null ? "" : rs.getString( "filename" ));
to.setFilesize(rs.getLong( "filesize")/1024);
}
}catch( SQLException e ) {
System.out.println( "[에러] : " + e.getMessage() );
} finally {
if(rs!=null)try{rs.close();}catch(SQLException e) {};
if(pstmt!=null) try{pstmt.close();}catch(SQLException e) {};
if(conn!=null)try{conn.close();}catch(SQLException e) {};
}
return to;
}
public int boardModifyOk(BoardTO to) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int flag = 2;
try {
conn = this.dataSource.getConnection();
String sql = "select filename from pds_board1 where seq=?";
pstmt = conn.prepareStatement( sql );
pstmt.setString(1, to.getSeq() );
rs = pstmt.executeQuery();
String oldfilename = null;
if( rs.next() ) {
oldfilename = rs.getString( "filename" );
}
// 새로운 첨부파일이 있으면
if( to.getFilename() != null ) {
sql = "update pds_board1 set subject=?, mail=?, content=?, filename=?, filesize=? where seq=? and password=?";
pstmt = conn.prepareStatement( sql );
pstmt.setString( 1, to.getSubject() );
pstmt.setString( 2, to.getMail() );
pstmt.setString( 3, to.getContent() );
pstmt.setString( 4, to.getFilename() );
pstmt.setLong( 5, to.getFilesize() );
pstmt.setString( 6, to.getSeq() );
pstmt.setString( 7, to.getPassword() );
} else {
sql = "update pds_board1 set subject=?, mail=?, content=? where seq=? and password=?";
pstmt = conn.prepareStatement( sql );
pstmt.setString( 1, to.getSubject() );
pstmt.setString( 2, to.getMail() );
pstmt.setString( 3, to.getContent() );
pstmt.setString( 4, to.getSeq() );
pstmt.setString( 5, to.getPassword() );
}
int result = pstmt.executeUpdate();
if( result == 0 ) {
flag = 1;
// 잘못 업로드된 파일 삭제
if( to.getFilename() != null ) {
File file = new File( uploadPath, to.getFilename() );
file.delete();
}
} else if( result == 1 ) {
flag = 0;
// 정상 상태 / 새로운 파일 업로드 / 기존 파일 삭제
if( to.getFilename() != null && oldfilename != null ) {
File file = new File( uploadPath, oldfilename );
file.delete();
}
}
} catch( SQLException e ) {
System.out.println( "[에러] " + e.getMessage() );
} finally {
if( rs != null ) try { rs.close(); } catch(SQLException e) {}
if( pstmt != null ) try { pstmt.close(); } catch(SQLException e) {}
if( conn != null ) try { conn.close(); } catch(SQLException e) {}
}
return flag;
}
public BoardTO boardDelete(BoardTO to) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = this.dataSource.getConnection();
String sql = "select subject, writer from pds_board1 where seq=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, to.getSeq());
rs = pstmt.executeQuery();
if(rs.next() ) {
to.setSubject(rs.getString("subject"));
to.setWriter(rs.getString("writer"));
}
}catch(SQLException e) {
System.out.println("에러 : " + e.getMessage());
} finally {
if(rs!=null)try{rs.close();}catch(SQLException e) {};
if(pstmt!=null) try{pstmt.close();}catch(SQLException e) {};
if(conn!=null)try{conn.close();}catch(SQLException e) {};
}
return to;
}
public int boardDeleteOk(BoardTO to) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// 2 : 시스템 에러, 1 : 비밀번호 오류, 0 :정
int flag = 2;
try {
conn = this.dataSource.getConnection();
String sql = "select filename from pds_board1 where seq=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,to.getSeq());
rs = pstmt.executeQuery();
String filename = null;
if(rs.next()) {
filename = rs.getString("filename");
}
// 절대 비밀번호를 프로그램으로 가져오지 말것.
// 비밀번호를 암호화 할것
sql = "delete from pds_board1 where seq=? and password=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, to.getSeq());
pstmt.setString(2, to.getPassword());
// 0, 1 출력
int result = pstmt.executeUpdate();
if(result == 0) {
// 비밀번호 오류
flag = 1;
} else if(result == 1) {
// 비밀번호 정상
flag = 0;
// 실제 파일 삭제
if(filename != null) {
File file = new File(uploadPath, filename);
file.delete();
}
}
} catch(SQLException e) {
System.out.println("에러 : " + e.getMessage());
} finally {
if(pstmt!=null)try{pstmt.close();}catch(SQLException e) {};
if(conn!=null)try{conn.close();}catch(SQLException e) {};
}
return flag;
}
}
//BoardTO.java
package model1;
public class BoardTO {
private String seq;
private String subject;
private String writer;
private String mail;
private String password;
private String content;
private String filename;
private long filesize;
private String hit;
private String wip;
private String wdate;
private int wgap;
public String getSeq() {
return seq;
}
public void setSeq(String seq) {
this.seq = seq;
}
public String getSubject() {
return subject;
}
public void setSubject(String subject) {
this.subject = subject;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public String getMail() {
return mail;
}
public void setMail(String mail) {
this.mail = mail;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getFilename() {
return filename;
}
public void setFilename(String filename) {
this.filename = filename;
}
public long getFilesize() {
return filesize;
}
public void setFilesize(long filesize) {
this.filesize = filesize;
}
public String getHit() {
return hit;
}
public void setHit(String hit) {
this.hit = hit;
}
public String getWip() {
return wip;
}
public void setWip(String wip) {
this.wip = wip;
}
public String getWdate() {
return wdate;
}
public void setWdate(String wdate) {
this.wdate = wdate;
}
public int getWgap() {
return wgap;
}
public void setWgap(int wgap) {
this.wgap = wgap;
}
}이모티콘 게시판으로 복습하기
1. jsp로 이루어진 게시판 실행
2. model1으로 변경
DB에 한글 적용: ALTER TABLE (테이블명) convert to charset utf8;
8080포트 죽이기
netstat -a -o
taskkill /f /pid PID번호
728x90
반응형