Servlet - 이미지 게시판 만들기

1. DB 테이블 생성

CREATE TABLE IMAGETEST

(NUM NUMBER PRIMARY KEY,

SUBJECT VARCHAR2(20) NOT NULL,

SAVEFILENAME VARCHAR2(50) NOT NULL)

2. DTO 생성

public class imageTestDTO {

private int num;

private String subject;

private String saveFileName;

getter,setter 작성

}

3. DAO 생성

public class imageTestDAO {

private Connection conn = null;

public imageTestDAO(Connection conn){

this.conn = conn;

}

//레코드번호

public int getMaxNum(){

int maxNum = 0;

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql;

try {

sql = "select nvl(max(num),0) from imageTest";

pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

if(rs.next()){

maxNum = rs.getInt(1);

}

rs.close();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return maxNum;

}

//파일데이터 입력

public void insertData(imageTestDTO dto){

PreparedStatement pstmt = null;

String sql;

try {

sql = "insert into imageTest (num,subject,saveFileName) values (?,?,?)";

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, dto.getNum());

pstmt.setString(2, dto.getSubject());

pstmt.setString(3, dto.getSaveFileName());

pstmt.executeUpdate();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

}

//파일데이터 전체조회

public List<imageTestDTO> getList(int start, int end){

List<imageTestDTO> lists = new ArrayList<imageTestDTO>();

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql;

try {

sql = "select * from (select rownum rnum,num,subject,saveFileName ";

sql += "from imageTest order by num desc) ";

sql += "where rnum >= ? and rnum <=? ";

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, start);

pstmt.setInt(2, end);

rs = pstmt.executeQuery();

while(rs.next()){

imageTestDTO dto = new imageTestDTO();

dto.setNum(rs.getInt("num"));

dto.setSubject(rs.getString("subject"));

dto.setSaveFileName(rs.getString("saveFileName"));

lists.add(dto);

}

rs.close();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return lists;

}

//특정 데이터 조회

public imageTestDTO getReadData(int num){

imageTestDTO dto = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql;

try {

sql = "select num,subject,saveFileName from imageTest where num = ? ";

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, num); //매개변수로 받은 num. where조건에 넣어줌

rs = pstmt.executeQuery();

if(rs.next()){

dto = new imageTestDTO();

dto.setNum(rs.getInt("num"));

dto.setSubject(rs.getString("subject"));

dto.setSaveFileName(rs.getString("saveFileName"));

}

rs.close();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return dto;

}

//파일데이터 삭제

public void deleteData(int num){

PreparedStatement pstmt = null;

String sql;

try {

sql = "delete imageTest where num=?";

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1,num);

pstmt.executeUpdate();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

}

//데이터 세기

public int getDataCount(){

int totalDataCount = 0;

PreparedStatement pstmt =null;

ResultSet rs = null;

String sql;

try {

sql = "select nvl(count(*),0) from imageTest ";

pstmt= conn.prepareStatement(sql);

rs = pstmt.executeQuery();

if(rs.next())

totalDataCount = rs.getInt(1);

rs.close();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return totalDataCount;

}

}

4. Servlet class 생성

public class ImageTestServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

protected void forward(HttpServletRequest req, HttpServletResponse resp,

String url) throws ServletException, IOException {

RequestDispatcher rd = req.getRequestDispatcher(url);

rd.forward(req, resp);

}

@Override

protected void doGet(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

doPost(req, resp);

}

@Override

protected void doPost(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

Connection conn = DBCPConn.getConnection();

imageTestDAO dao = new imageTestDAO(conn);

// 페이징

MyUtil myUtil = new MyUtil();

String cp = req.getContextPath();

String uri = req.getRequestURI();

String url;

// 파일 업로드 위치 지정

String root = getServletContext().getRealPath("/");

String path = root + File.separator + "pds" + File.separator+ "imageFile";

File f = new File(path);

if (!f.exists()) {

f.mkdirs();

}

if (uri.indexOf("write.do") != -1) {

url = "/imageTest/write.jsp";

forward(req, resp, url);

} else if (uri.indexOf("write_ok.do") != -1) {

String encType = "UTF-8";

int maxSize = 10 * 1024 * 1024;

// 파일 업로드

MultipartRequest mr = new MultipartRequest(req, path, maxSize,

encType, new DefaultFileRenamePolicy());

// DB에 파일정보 입력

// 업로드한 파일로부터 정보 추출

if (mr.getFile("uploadFile") != null) {// null이 아니면 파일이 제대로 업로드된것

imageTestDTO dto = new imageTestDTO();

int maxNum = dao.getMaxNum();

dto.setNum(maxNum + 1);

dto.setSubject(mr.getParameter("subject"));

dto.setSaveFileName(mr.getFilesystemName("uploadFile"));

dao.insertData(dto);

}

// list.do 페이지로 리다이렉트

url = cp + "/image/list.do";

resp.sendRedirect(url);

} else if (uri.indexOf("list.do") != -1) {

String pageNum = req.getParameter("pageNum");

int currentPage = 1; // 처음 띄우는 리스트 페이지

if (pageNum != null) {

currentPage = Integer.parseInt(pageNum);

}

int dataCount = dao.getDataCount();

int numPerPage = 9;

int totalPage = myUtil.getPageCount(numPerPage, dataCount);

if (currentPage > totalPage)

currentPage = totalPage;

int start = (currentPage - 1) * numPerPage + 1;

int end = currentPage * numPerPage;

String listUrl = cp + "/image/list.do";

List<imageTestDTO> lists = dao.getList(start, end);

String pageIndexList = myUtil.pageIndexList(currentPage, totalPage,

listUrl);

// 삭제경로

String deletePath = cp + "/image/delete.do";

// 이미지파일경로

String imagePath = cp + "/pds/imageFile";

req.setAttribute("imagePath", imagePath);

int totalArticle = dao.getDataCount();

// 파일정보 테이블을 리스트로 전달

req.setAttribute("lists", lists);

req.setAttribute("pageNum", pageNum);

req.setAttribute("currentPage", currentPage);

req.setAttribute("deletePath", deletePath);

req.setAttribute("pageIndexList", pageIndexList);

req.setAttribute("totalArticle", totalArticle);

req.setAttribute("totalPage", totalPage);

// list.jsp 페이지로 포워드

url = "/imageTest/list.jsp";

forward(req, resp, url);

} else if (uri.indexOf("delete.do") != -1) {

int num = Integer.parseInt(req.getParameter("num"));

int pageNum = Integer.parseInt(req.getParameter("pageNum"));

imageTestDTO dto = dao.getReadData(num);

// 물리적 파일 삭제

FileManager.doFileDelete(dto.getSaveFileName(), path);

// 테이블 정보 삭제

dao.deleteData(num);

// 삭제 진행 후 리스트 페이지로 리다이렉트

url = cp + "/image/list.do?pageNum=" + pageNum;

resp.sendRedirect(url);

}

}

}

WEB.XML 페이지 매핑정보 등록

<!--서블릿 이미지 업로드 셋팅 -->

<servlet>

<servlet-name>imageServlet</servlet-name>

<servlet-class>com.imageTest.ImageTestServlet</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>imageServlet</servlet-name>

<url-pattern>/image/*</url-pattern>

</servlet-mapping>

JSP 생성 ① - 이미지 업로드 페이지

<%@ page contentType="text/html; charset=UTF-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%

request.setCharacterEncoding("UTF-8");

String cp = request.getContextPath();

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>이미지 등록</title>

<link rel="stylesheet" href="<%=cp %>/imageTest/css/style.css" type="text/css" />

<link rel="stylesheet" href="<%=cp %>/imageTest/css/created.css" type="text/css" />

<script type="text/javascript">

function sendIt() {

   f = document.myForm;

  

   str = f.subject.value;

   str = str.trim();

   if(!str) {

       alert("제목을 입력하세요 !!!");

       f.subject.focus();

       return;

   }

   f.subject.value = str;

   str = f.uploadFile.value;

   if(!str) {

       alert("이미지 파일을 선택 하세요 !!!");

       f.uploadFile.focus();

       return;

   }

  

   f.action="/study/image/write_ok.do";

   f.submit();

}

</script>

</head>

<body>

<form action="<%=cp%>/image/write_ok.do" method="post" enctype="multipart/form-data" name="myForm" >

<table cellpadding="2" id="bbs">

<tr>

<td id="bbs_title" colspan="3">

이미지 등록

</td>

</tr>

<tr><td height="1" colspan="3" style="border-bottom:1px solid #DBDBDB;"></td></tr>

<tr>

<td align="center" width="100">제&nbsp;&nbsp;&nbsp;&nbsp;목</td>

<td colspan="2" >

<input type="text" name="subject" class="boxTF" size="74"/></td>

</tr>

<tr><td height="1" colspan="3" style="border-bottom:1px solid #DBDBDB;"></td></tr>

<tr>

<td align="center">파&nbsp;&nbsp;&nbsp;&nbsp;일</td>

<td class="bbsCreated_bottomLine" colspan="2" >

<input type="file" name="uploadFile" class="boxTF" size="74"/></td>

</tr>

<tr><td height="1" colspan="3" style="border-bottom:1px solid #DBDBDB;"></td></tr>

<tr id="bbsCreated_footer">

<td colspan="3" align="center">

<input type="button" value="파일등록" onclick="sendIt()"/>

<input type="button" value="다시입력" onclick="document.myForm.subject.focus();"/>

<input type="button" value="작성취소" onclick="javascript:location.href='<%=cp %>/image/list.do';"/></td>

</tr>

</table>

</form>

</body>

</html>

JSP 생성 ②- 이미지 리스트 조회 페이지

<%@ page contentType="text/html; charset=UTF-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%

request.setCharacterEncoding("UTF-8");

String cp = request.getContextPath();

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>이미지 게시판</title>

<link rel="stylesheet" href="<%=cp %>/imageTest/css/style.css" type="text/css" />

<link rel="stylesheet" href="<%=cp %>/imageTest/css/list.css" type="text/css" />

</head>

<body>

<br/><br/>

<table width="600" align="center" style="font-family: 돋움; font-size: 10pt;" cellspacing="2" cellpadding="1" >

<tr id="bbsList">

<td id="bbsList_title" colspan="3">

이미지 게시판

</td>

</tr>

<tr>

<td align="left" colspan="2" width="400">

Total ${totalArticle } articles, ${totalPage } pages / Now Page is ${currentPage }

</td>

<td align="right" colspan="1" width="200">

<input type="button" value="게시물등록" onclick="javascript:location='<%=cp%>/image/write.do';"/>

</td>

</tr>

<tr><td style="border-bottom:2px solid #DBDBDB;" colspan="3"></td></tr>

<%

int newLine = 0;

int articleCount=0;

int cnt = 0;

%>

<c:forEach var="dto" items="${lists }">

<%

if(newLine==0){

out.print("<tr>");

}

newLine++;

articleCount++;

%>

<td align="center" width="190">

<input type="hidden" value="${dto.num }" name="num" />

<a href="${imagePath }/${dto.saveFileName }">

<img alt="" src="${imagePath }/${dto.saveFileName }" width="190" height="190"><br>

${dto.subject }

</a>

<a href="${deletePath}?num=${dto.num }&pageNum=${pageNum }">삭제</a>

</td>

<%

if(newLine==3){

out.print("</tr>");

newLine = 0;

}

%>

</c:forEach>

<%

while(newLine>0&&newLine<3){

out.print("<td width='180'></td>");

newLine++;

}

out.print("</tr>");

%>

<tr><td style="border-bottom:2px solid #DBDBDB;" colspan="3"></td></tr>

<tr>

<td align="center" colspan="3">

<c:if test="${dataCount!=0 }">

${pageIndexList }

</c:if>

<c:if test="${dataCount==0 }">

등록된 파일이 없습니다

</c:if>

</td>

</tr>

</table>

</body>

</html>

---

DB연동시 ORA-12505 오류 해결방법  

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

 )

1. Lsnrctl services 명령어 사용하여 sid 확인

2. sid 를 찾지 못하므로 확인 후 listener.ora, tnsnames.ora 에서 설정이 올바르게 되어 있는지 확인

경로 : C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN

 
3. SID 설정이 알맞게 되어 있는지 확인 후 없으면 추가

수정 후 반드시 리스너를 restart 해야한다.

4. DBCP 생성시 사용하는 환경설정 값이 올바른지 확인