Spring3.0 - MVC web & JDBC 게시판만들기

JDBC 와 Spring JDBC 모두 실습해보자.

게시판 만들기 - Spring mvc , Spring dao(JDBC) , Spring orm(mybatis) 

1 .Spring Legacy Project 생성 (Spring MVC Project)

프로젝트의 규모가 커지면 DTO, DAO 를 다 별도의 패키지에 관리한다.

2. dto 생성

package com.jdbc.dto; public class BoardDTO { private int num; private String name,pwd,email,subject,content,ipAddr,created; private int hitCount; public int getNum() { return num; } public void setNum(int num) { this.num = num; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getSubject() { return subject; } public void setSubject(String subject) { this.subject = subject; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } public String getIpAddr() { return ipAddr; } public void setIpAddr(String ipAddr) { this.ipAddr = ipAddr; } public String getCreated() { return created; } public void setCreated(String created) { this.created = created; } public int getHitCount() { return hitCount; } public void setHitCount(int hitCount) { this.hitCount = hitCount; } }

3. dao 생성

package com.jdbc.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import javax.sql.DataSource; import com.jdbc.dto.BoardDTO; public class BoardDAO { private DataSource dataSource; public void setDataSource(DataSource dataSource) throws Exception{ this.dataSource = dataSource; //원래는 메소드실행시 마다 conn = dataSource.getConnection();를 작성해줘야 하지만 편의상 setter에 기입 conn = dataSource.getConnection(); } Connection conn = null; //1.num의 최대값 public int getMaxNum(){ int maxNum = 0; PreparedStatement pstmt = null; ResultSet rs = null; String sql; try { sql = "select nvl(max(num),0) from board"; 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; } //입력(created.jsp->created_ok.jsp) public int insertData(BoardDTO dto){ int result = 0; PreparedStatement pstmt = null; String sql; try { sql = "insert into board (num,name,pwd,email,subject,content,"; sql+= "ipAddr,hitCount,created) "; sql+= "values(?,?,?,?,?,?,?,0,sysdate)"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, dto.getNum()); pstmt.setString(2, dto.getName()); pstmt.setString(3, dto.getPwd()); pstmt.setString(4, dto.getEmail()); pstmt.setString(5, dto.getSubject()); pstmt.setString(6, dto.getContent()); pstmt.setString(7, dto.getIpAddr()); result = pstmt.executeUpdate(); pstmt.close(); } catch (Exception e) { System.out.println(e.toString()); } return result; } //전체데이터 public List<BoardDTO> getList(int start, int end, String searchKey, String searchValue){ List<BoardDTO> lists = new ArrayList<BoardDTO>(); PreparedStatement pstmt = null; ResultSet rs = null; String sql; try { searchValue = "%" + searchValue + "%"; sql = "select * from ("; sql+= "select rownum rnum,data.* from("; sql+= "select num,name,subject,hitCount,"; sql+= "to_char(created,'YYYY-MM-DD') created "; sql+= "from board where " + searchKey + " like ? order by num desc) data) "; sql+= "where rnum >= ? and rnum <= ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, searchValue); pstmt.setInt(2, start); pstmt.setInt(3, end); rs = pstmt.executeQuery(); while(rs.next()){ BoardDTO dto = new BoardDTO(); dto.setNum(rs.getInt("num")); dto.setName(rs.getString("name")); dto.setSubject(rs.getString("subject")); dto.setHitCount(rs.getInt("hitCount")); dto.setCreated(rs.getString("created")); lists.add(dto); } rs.close(); pstmt.close(); } catch (Exception e) { System.out.println(e.toString()); } return lists; } //전체 데이터수 구하기 public int getDataCount(String searchKey,String searchValue){ int result = 0; PreparedStatement pstmt = null; ResultSet rs = null; String sql; try { searchValue = "%" + searchValue + "%"; sql = "select nvl(count(*),0) from board "; sql+= "where " + searchKey + " like ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, searchValue); rs = pstmt.executeQuery(); if(rs.next()){ result = rs.getInt(1); } rs.close(); pstmt.close(); } catch (Exception e) { System.out.println(e.toString()); } return result; } //조회수증가 public int updateHitCount(int num){ int result = 0; PreparedStatement pstmt = null; String sql; try { sql = "update board set hitCount=hitCount + 1 where num=?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, num); result = pstmt.executeUpdate(); pstmt.close(); } catch (Exception e) { System.out.println(e.toString()); } return result; } //한명의 데이터 출력 public BoardDTO getReadData(int num){ BoardDTO dto = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql; try { sql = "select num,name,pwd,email,subject,content,ipAddr,"; sql+= "hitCount,created from board where num=?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, num); rs = pstmt.executeQuery(); if(rs.next()){ dto = new BoardDTO(); dto.setNum(rs.getInt("num")); dto.setName(rs.getString("name")); dto.setPwd(rs.getString("pwd")); dto.setEmail(rs.getString("email")); dto.setSubject(rs.getString("subject")); dto.setContent(rs.getString("content")); dto.setIpAddr(rs.getString("ipAddr")); dto.setHitCount(rs.getInt("hitCount")); dto.setCreated(rs.getString("created")); } rs.close(); pstmt.close(); } catch (Exception e) { System.out.println(e.toString()); } return dto; } //삭제 public int deleteData(int num){ int result = 0; PreparedStatement pstmt = null; String sql; try { sql = "delete board where num=?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, num); result = pstmt.executeUpdate(); pstmt.close(); } catch (Exception e) { System.out.println(e.toString()); } return result; } //수정 public int updateData(BoardDTO dto){ int result = 0; PreparedStatement pstmt = null; String sql; try { sql = "update board set name=?, pwd=?, email=?, subject=?,"; sql+= "content=? where num=?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, dto.getName()); pstmt.setString(2, dto.getPwd()); pstmt.setString(3, dto.getEmail()); pstmt.setString(4, dto.getSubject()); pstmt.setString(5, dto.getContent()); pstmt.setInt(6, dto.getNum()); result = pstmt.executeUpdate(); pstmt.close(); } catch (Exception e) { System.out.println(e.toString()); } return result; } }

4. 페이징처리를 위해 util 패키지 와 클래스 생성

package com.jdbc.util; public class MyUtil { //전체 페이지수 구하기 //numPerPage : 한화면에 표시할 데이터의 갯수 //dataCount : 전체 데이터의 갯수 public int getPageCount(int numPerPage, int dataCount){ int pageCount = 0; pageCount = dataCount / numPerPage; if(dataCount % numPerPage != 0) pageCount++; return pageCount; } //페이징 처리 메소드 //currentPage :현재 표시할 페이지 //totalPage : 전체 페이지수 //listUrl : 링크를 설정할 url public String pageIndexList(int currentPage, int totalPage, String listUrl){ int numPerBlock = 5; //1◀이전 6 7 8 9 10 다음▶11(6-10까지 표시되는 페이지 갯수) int currentPageSetup; //표시할 첫 페이지(6)의 – 1 해준 값(5,10,15,20...) int page; StringBuffer sb = new StringBuffer(); if(currentPage==0 || totalPage==0) //데이터가 없을 경우 return ""; //abc.jsp?a=1 if(listUrl.indexOf("?") != -1)  //주소줄에 ?표가 있다면 listUrl = listUrl + "&"; else listUrl = listUrl + "?"; //표시할 첫 페이지의 – 1 해준 값 currentPageSetup = (currentPage/numPerBlock)*numPerBlock; if(currentPage % numPerBlock == 0) currentPageSetup = currentPageSetup - numPerBlock; //◀이전 if(totalPage > numPerBlock && currentPageSetup > 0){ sb.append("<a href=\"" + listUrl + "pageNum=" + currentPageSetup + "\">◀이전</a>&nbsp;"); } //바로가기 페이지 page = currentPageSetup + 1; while(page <= totalPage && page <= (currentPageSetup + numPerBlock)){ if(page == currentPage){ sb.append("<font color=\"Fuchsia\">" + page + "</font>&nbsp;"); }else{ sb.append("<a href=\"" + listUrl + "pageNum=" + page + "\">" + page + "</a>&nbsp;"); } page++; } //다음▶ if(totalPage - currentPageSetup > numPerBlock){ sb.append("<a href=\"" + listUrl + "pageNum=" + page + "\">다음▶</a>&nbsp;"); } return sb.toString(); } }

5. resources에 css,js파일 추가

6. views에 jsp 페이지 추가 

① created.jsp - 게시글 작성페이지

<%@ page contentType="text/html; charset=UTF-8"%> <% 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="/springweb/resources/css/style.css" type="text/css"/> <link rel="stylesheet" href="/springweb/resources/css/created.css" type="text/css"/> <script type="text/javascript" src="/springweb/resources/js/util.js"></script> <script type="text/javascript"> function sendIt(){ f = document.myForm; str = f.subject.value; str = str.trim(); if(!str){ alert("\n제목을 입력하세요."); f.subject.focus(); return; } f.subject.value = str; str = f.name.value; str = str.trim(); if(!str){ alert("\n이름을 입력하세요."); f.name.focus(); return; } f.name.value = str; if(f.email.value){ if(!isValidEmail(f.email.value)){ alert("\n정상적인 E-Mail을 입력하세요."); f.email.focus(); return; } } str = f.content.value; str = str.trim(); if(!str){ alert("\n내용을 입력하세요."); f.content.focus(); return; } f.content.value = str; str = f.pwd.value; str = str.trim(); if(!str){ alert("\n패스워드를 입력하세요."); f.pwd.focus(); return; } f.pwd.value = str; f.action = "<%=cp%>/created_ok.action"; f.submit(); } </script> </head> <body> <div id="bbs"> <div id="bbs_title"> 게 시 판(Spring3.0) </div> <form action="" name="myForm" method="post"> <div id="bbsCreated"> <div class="bbsCreated_bottomLine"> <dl> <dt>제&nbsp;&nbsp;&nbsp;&nbsp;목</dt> <dd> <input type="text" name="subject" size="74" maxlength="100" class="boxTF"/> </dd> </dl> </div> <div class="bbsCreated_bottomLine"> <dl> <dt>작성자</dt> <dd> <input type="text" name="name" size="35" maxlength="20" class="boxTF"/> </dd> </dl> </div> <div class="bbsCreated_bottomLine"> <dl> <dt>E-Mail</dt> <dd> <input type="text" name="email" size="35" maxlength="50" class="boxTF"/> </dd> </dl> </div> <div id="bbsCreated_content" > <dl> <dt>내&nbsp;&nbsp;&nbsp;&nbsp;용</dt> <dd> <textarea rows="12" cols="63" name="content" class="boxTA"></textarea> </dd> </dl> </div> <div class="bbsCreated_noLine"> <dl> <dt>패스워드</dt> <dd> <input type="password" name="pwd" size="35" maxlength="7" class="boxTF"/> </dd> </dl> </div> </div> <div id="bbsCreated_footer"> <input type="button" value=" 등록하기 " class="btn2" onclick="sendIt();"/> <input type="reset" value=" 다시입력 " class="btn2" onclick="document.myForm.subject.focus();"/> <input type="button" value=" 작성취소 " class="btn2" onclick="javascript:location.href='<%=cp%>/list.action';"/> </div> </form> </div> </body> </html>

② list.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>Insert title here</title> <link rel="stylesheet" href="/springweb/resources/css/style.css" type="text/css"/> <link rel="stylesheet" href="/springweb/resources/css/list.css" type="text/css"/> <script type="text/javascript"> function sendIt(){ var f = document.searchForm; f.action = "<%=cp%>/list.action"; f.submit(); } </script> </head> <body> <div id="bbsList"> <div id="bbsList_title"> 게 시 판(Spring3.0) </div> <div id="bbsList_header"> <div id="leftHeader"> <form action="" name="searchForm" method="post"> <select name="searchKey" class="selectField"> <option value="subject">제목</option> <option value="name">작성자</option> <option value="content">내용</option> </select> <input type="text" name="searchValue" class="textField"> <input type="button" value=" 검색 " class="btn2" onclick="sendIt();"/> </form> </div> <div id="rightHeader"> <input type="button" value=" 글올리기 " class="btn2" onclick="javascript:location.href='<%=cp%>/created.action';"/> </div> </div> <div id="bbsList_list"> <div id="title"> <dl> <dt class="num">번호</dt> <dt class="subject">제목</dt> <dt class="name">작성자</dt> <dt class="created">작성일</dt> <dt class="hitCount">조회수</dt> </dl> </div> <div id="lists"> <c:forEach var="dto" items="${lists}"> <dl> <dd class="num">${dto.num }</dd> <dd class="subject"> <a href="${articleUrl}&num=${dto.num}"> ${dto.subject }</a></dd> <dd class="name">${dto.name }</dd> <dd class="created">${dto.created }</dd> <dd class="hitCount">${dto.hitCount }</dd> </dl> </c:forEach> </div> <div id="footer"> <p> <c:if test="${dataCount!=0 }"> ${pageIndexList } </c:if> <c:if test="${dataCount==0 }"> 등록된 게시물이 없습니다. </c:if> </p> </div> </div> </div> </body> </html>

③ article.jsp - 단일 게시글 조회페이지

<%@ page contentType="text/html; charset=UTF-8"%> <% 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>Insert title here</title> <link rel="stylesheet" href="/springweb/resources/css/style.css" type="text/css"/> <link rel="stylesheet" href="/springweb/resources/css/article.css" type="text/css"/> </head> <body> <div id="bbs"> <div id="bbs_title"> 게 시 판(Spring3.0) </div> <div id="bbsArticle"> <div id="bbsArticle_header"> ${dto.subject } </div> <div class="bbsArticle_bottomLine"> <dl> <dt>작성자</dt> <dd>${dto.name }</dd> <dt>줄수</dt> <dd>${lineSu }</dd> </dl> </div> <div class="bbsArticle_bottomLine"> <dl> <dt>등록일</dt> <dd>${dto.created }</dd> <dt>조회수</dt> <dd>${dto.hitCount }</dd> </dl> </div> <div id="bbsArticle_content"> <table width="600" border="0"> <tr><td style="padding: 20px 80px 20px 62px;" valign="top" height="200"> ${dto.content } </td></tr> </table> </div> </div> <div class="bbsArticle_noLine" style="text-align: right;"> From : ${dto.ipAddr } </div> <div id="bbsArticle_footer"> <div id="leftFooter"> <input type="button" value=" 수정 " class="btn2" onclick="javascript:location.href='<%=cp%>/updated.action?num=${dto.num}&pageNum=${pageNum}'"/> <input type="button" value=" 삭제 " class="btn2" onclick="javascript:location.href='<%=cp%>/deleted.action?num=${dto.num}&pageNum=${pageNum}'"/> </div> <div id="rightFooter"> <input type="button" value=" 리스트 " class="btn2" onclick="javascript:location.href='<%=cp%>/list.action?${params}'"/> </div> </div> </div> </body> </html>

④ updated.jsp - 게시글 수정 페이지

<%@ page contentType="text/html; charset=UTF-8"%> <% 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="/springweb/resources/css/style.css" type="text/css"/> <link rel="stylesheet" href="/springweb/resources/css/created.css" type="text/css"/> <script type="text/javascript" src="/springweb/resources/js/util.js"></script> <script type="text/javascript"> function sendIt(){ f = document.myForm; str = f.subject.value; str = str.trim(); if(!str){ alert("\n제목을 입력하세요."); f.subject.focus(); return; } f.subject.value = str; str = f.name.value; str = str.trim(); if(!str){ alert("\n이름을 입력하세요."); f.name.focus(); return; } /* if(!isValidKorean(str)){ alert("\n이름을 정확히 입력하세요."); f.name.focus(); return; } */ f.name.value = str; if(f.email.value){ if(!isValidEmail(f.email.value)){ alert("\n정상적인 E-Mail을 입력하세요."); f.email.focus(); return; } } str = f.content.value; str = str.trim(); if(!str){ alert("\n내용을 입력하세요."); f.content.focus(); return; } f.content.value = str; str = f.pwd.value; str = str.trim(); if(!str){ alert("\n패스워드를 입력하세요."); f.pwd.focus(); return; } f.pwd.value = str; f.action = "<%=cp%>/updated_ok.action"; f.submit(); } </script> </head> <body> <div id="bbs"> <div id="bbs_title"> 게 시 판(Spring3.0) </div> <form action="" name="myForm" method="post"> <div id="bbsCreated"> <div class="bbsCreated_bottomLine"> <dl> <dt>제&nbsp;&nbsp;&nbsp;&nbsp;목</dt> <dd> <input type="text" name="subject" value="${dto.subject }" size="74" maxlength="100" class="boxTF"/> </dd> </dl> </div> <div class="bbsCreated_bottomLine"> <dl> <dt>작성자</dt> <dd> <input type="text" name="name" value="${dto.name }" size="35" maxlength="20" class="boxTF"/> </dd> </dl> </div> <div class="bbsCreated_bottomLine"> <dl> <dt>E-Mail</dt> <dd> <input type="text" name="email" value="${dto.email }" size="35" maxlength="50" class="boxTF"/> </dd> </dl> </div> <div id="bbsCreated_content" > <dl> <dt>내&nbsp;&nbsp;&nbsp;&nbsp;용</dt> <dd> <textarea rows="12" cols="63" name="content" class="boxTA">${dto.content }</textarea> </dd> </dl> </div> <div class="bbsCreated_noLine"> <dl> <dt>패스워드</dt> <dd> <input type="password" name="pwd" value="${dto.pwd }" size="35" maxlength="7" class="boxTF"/> </dd> </dl> </div> </div> <div id="bbsCreated_footer"> <input type="hidden" name="num" value="${dto.num }"/> <input type="hidden" name="pageNum" value="${pageNum }"/> <input type="button" value=" 수정하기 " class="btn2" onclick="sendIt();"/> <input type="button" value=" 수정취소 " class="btn2" onclick="javascript:location.href='<%=cp%>/list.action';"/> </div> </form> </div> </body> </html>

7. pom.xml - Maven을 통한 라이브러리 설치

8. servlet-context.xml

util과 mvc에서 사용하는 객체 생성태그는 조금 다름 <bean> <beans>

<?xml version="1.0" encoding="UTF-8"?> <beans:beans xmlns="http://www.springframework.org/schema/mvc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:beans="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd"> <!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure --> <!-- Enables the Spring MVC @Controller programming model --> <annotation-driven /> <!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory --> <resources mapping="/resources/**" location="/resources/" /> <!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory --> <beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <beans:property name="prefix" value="/WEB-INF/views/" /> <beans:property name="suffix" value=".jsp" /> </beans:bean> <context:component-scan base-package="com.jdbc.springweb" /> <beans:bean id="boardDAO" class="com.jdbc.dao.BoardDAO"> <beans:property name="dataSource" ref="dataSource"/> </beans:bean> <beans:bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/> <beans:property name="url" value="jdbc:oracle:thin:@192.168.16.16:1521:TestDB"/> <beans:property name="username" value="SUZI"/> <beans:property name="password" value="A123"/> </beans:bean> <beans:bean id="myUtil" class="com.jdbc.util.MyUtil"/> </beans:beans>

9. BoardController

package com.jdbc.springweb; import java.net.URLDecoder; import java.net.URLEncoder; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.servlet.ModelAndView; import com.jdbc.dao.BoardDAO; import com.jdbc.dto.BoardDTO; import com.jdbc.util.MyUtil; @Controller public class BoardController { @Autowired @Qualifier("boardDAO")//Bean 객체 생성 BoardDAO dao; @Autowired MyUtil myUtil;//Bean 객체 생성 @RequestMapping(value = "/", method = RequestMethod.GET) public String home() { return "index"; } /* @RequestMapping(value = "/created.action", method = {RequestMethod.GET,RequestMethod.POST}) public String created(HttpServletRequest request, HttpServletResponse response) throws Exception{ return "bbs/created"; } */ @RequestMapping(value = "/created.action") public ModelAndView created() { ModelAndView mav = new ModelAndView(); mav.setViewName("bbs/created"); return mav; } @RequestMapping(value = "/created_ok.action", method = RequestMethod.POST) public String created_ok(BoardDTO dto,HttpServletRequest request, HttpServletResponse response) throws Exception{ int maxNum = dao.getMaxNum(); dto.setNum(maxNum+1); dto.setIpAddr(request.getRemoteAddr()); dao.insertData(dto); return "redirect:/list.action"; } @RequestMapping(value = "/list.action", method = {RequestMethod.GET, RequestMethod.POST}) public String list(HttpServletRequest request, HttpServletResponse response) throws Exception{ String cp = request.getContextPath(); String pageNum = request.getParameter("pageNum"); int currentPage = 1; if(pageNum != null) currentPage = Integer.parseInt(pageNum); String searchKey = request.getParameter("searchKey"); String searchValue = request.getParameter("searchValue"); if(searchKey == null){ searchKey = "subject"; searchValue = ""; }else{ if(request.getMethod().equalsIgnoreCase("GET")) searchValue = URLDecoder.decode(searchValue, "UTF-8"); } //전체데이터갯수 int dataCount = dao.getDataCount(searchKey, searchValue); //전체페이지수 int numPerPage = 10; int totalPage = myUtil.getPageCount(numPerPage, dataCount); if(currentPage > totalPage) currentPage = totalPage; int start = (currentPage-1)*numPerPage+1; int end = currentPage*numPerPage; List<BoardDTO> lists = dao.getList(start, end, searchKey, searchValue); //페이징 처리 String param = ""; if(!searchValue.equals("")){ param = "searchKey=" + searchKey; param+= "&searchValue=" + URLEncoder.encode(searchValue, "UTF-8"); } String listUrl = cp + "/list.action"; if(!param.equals("")){ listUrl = listUrl + "?" + param; } String pageIndexList = myUtil.pageIndexList(currentPage, totalPage, listUrl); //글보기 주소 정리 String articleUrl = cp + "/article.action?pageNum=" + currentPage; if(!param.equals("")) articleUrl = articleUrl + "&" + param; //포워딩 될 페이지에 데이터를 넘긴다 request.setAttribute("lists", lists); request.setAttribute("pageIndexList",pageIndexList); request.setAttribute("dataCount",dataCount); request.setAttribute("articleUrl",articleUrl); return "bbs/list"; } /* @RequestMapping(value = "/article.action", method = {RequestMethod.GET, RequestMethod.POST}) public String article(HttpServletRequest request, HttpServletResponse response) throws Exception{ */ @RequestMapping(value = "/article.action", method = {RequestMethod.GET, RequestMethod.POST}) public ModelAndView article(HttpServletRequest request, HttpServletResponse response) throws Exception{ String cp = request.getContextPath(); int num = Integer.parseInt(request.getParameter("num")); String pageNum = request.getParameter("pageNum"); String searchKey = request.getParameter("searchKey"); String searchValue = request.getParameter("searchValue"); if(searchKey != null) searchValue = URLDecoder.decode(searchValue, "UTF-8"); //조회수 증가 dao.updateHitCount(num); BoardDTO dto = dao.getReadData(num); if(dto==null){ //return "redirect:/list.action"; } int lineSu = dto.getContent().split("\n").length; dto.setContent(dto.getContent().replaceAll("\n", "<br/>")); String param = "pageNum=" + pageNum; if(searchKey!=null){ param += "&searchKey=" + searchKey; param += "&searchValue=" + URLEncoder.encode(searchValue, "UTF-8"); } /* Model request.setAttribute("dto", dto); request.setAttribute("params",param); request.setAttribute("lineSu",lineSu); request.setAttribute("pageNum",pageNum); View return "bbs/article"; */ ModelAndView mav = new ModelAndView(); //View mav.setViewName("bbs/article"); //Model mav.addObject("dto",dto); mav.addObject("params",param); mav.addObject("lineSu",lineSu); mav.addObject("pageNum",pageNum); return mav; } @RequestMapping(value = "/updated.action", method = {RequestMethod.GET, RequestMethod.POST}) public String updated(HttpServletRequest request, HttpServletResponse response) throws Exception{ String cp = request.getContextPath(); int num = Integer.parseInt(request.getParameter("num")); String pageNum = request.getParameter("pageNum"); BoardDTO dto = dao.getReadData(num); if(dto == null){ return "redirect:/list.action"; } request.setAttribute("dto", dto); request.setAttribute("pageNum", pageNum); return "bbs/updated"; } @RequestMapping(value = "/updated_ok.action", method = {RequestMethod.GET, RequestMethod.POST}) public String updated_ok(BoardDTO dto, HttpServletRequest request, HttpServletResponse response) throws Exception{ String pageNum = request.getParameter("pageNum"); dao.updateData(dto); return "redirect:/list.action?pageNum=" +pageNum; } @RequestMapping(value = "/deleted.action", method = {RequestMethod.GET, RequestMethod.POST}) public String deleted(HttpServletRequest request, HttpServletResponse response) throws Exception{ String pageNum = request.getParameter("pageNum"); int num =Integer.parseInt(request.getParameter("num")); dao.deleteData(num); return "redirect:/list.action?pageNum=" +pageNum; } }

게시글 작성 페이지

게시글 리스트 조회 페이지

게시글 조회 페이지

게시글 수정 페이지

---

이제 Spring 에서 제공하는 JDBC Template를 사용해보자 

1. Spring JDBC 라이브러리 

- pom.xml에 추가

<!-- spring-jdbc --> <dependency>   <groupId>org.springframework</groupId>   <artifactId>spring-jdbc</artifactId>   <version>${org.springframework-version}</version> </dependency>

2. servlet-context.xml 

<beans:bean id="boardDAO2" class="com.jdbc.dao.BoardDAO2"> <beans:property name="jdbcTemplate" ref="jdbcTemplate"/> </beans:bean> <beans:bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <beans:constructor-arg ref="dataSource"/> </beans:bean> <beans:bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/> <beans:property name="url" value="jdbc:oracle:thin:@192.168.16.16:1521:TestDB"/> <beans:property name="username" value="SUZI"/> <beans:property name="password" value="A123"/> </beans:bean>

3. BoardDAO2 - JdbcTemplate 활용하여 생성한 DAO 클래스

package com.jdbc.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import com.jdbc.dto.BoardDTO; public class BoardDAO2 { private JdbcTemplate  jdbcTemplate; public void setJdbcTemplate(JdbcTemplate jdbcTemplate) throws Exception{ this.jdbcTemplate = jdbcTemplate; } public int getMaxNum(){ int maxNum = 0; StringBuilder sql = new StringBuilder(100);//여기서 매개변수가 정수로 들어갈경우 100자 저장하겟다는 의미 sql.append("select nvl(max(num),0) from board"); maxNum = jdbcTemplate.queryForInt(sql.toString()); return maxNum; } public void insertData(BoardDTO dto){ StringBuilder sql = new StringBuilder(200); sql.append("insert into board (num,name,pwd,email,subject,content,"); sql.append("ipAddr,hitCount,created) "); sql.append("values(?,?,?,?,?,?,?,0,sysdate)"); jdbcTemplate.update(sql.toString(), dto.getNum(),dto.getName(),dto.getPwd(),dto.getEmail(),dto.getSubject(),dto.getContent(),dto.getIpAddr()); } public List<BoardDTO> getList(int start, int end,String searchKey, String searchValue){ StringBuilder sql = new StringBuilder(1000); searchValue = "%" + searchValue + "%"; sql.append("select * from (") .append("select rownum rnum,data.* from(") .append("select num,name,subject,hitCount,") .append("to_char(created,'YYYY-MM-DD') created ") .append("from board where " + searchKey + " like ? order by num desc) data) ") .append("where rnum >= ? and rnum <= ?"); List<BoardDTO> lists =jdbcTemplate.query(sql.toString(), new Object[] {searchValue,start,end}, new RowMapper<BoardDTO>() { @Override public BoardDTO mapRow(ResultSet rs, int rowNum) throws SQLException { BoardDTO dto = new BoardDTO(); dto.setNum(rs.getInt("num")); dto.setName(rs.getString("name")); dto.setSubject(rs.getString("subject")); dto.setHitCount(rs.getInt("hitCount")); dto.setCreated(rs.getString("created")); return dto; } }); return lists; } public int getDataCount(String searchKey,String searchValue){ int result = 0; StringBuilder sql = new StringBuilder(200); searchValue = "%" + searchValue + "%"; sql.append("select nvl(count(*),0) from board ") .append("where " + searchKey + " like ?"); result = jdbcTemplate.queryForInt(sql.toString(),searchValue); return result; } public void updateHitCount(int num){ StringBuilder sql = new StringBuilder(100); sql.append("update board set hitCount=hitCount + 1 where num=?"); jdbcTemplate.update(sql.toString(),num); } public BoardDTO getReadData(int num){ StringBuilder sql = new StringBuilder(200); sql.append("select num,name,pwd,email,subject,content,ipAddr,") .append("hitCount,created from board where num=?"); BoardDTO dtoOne = jdbcTemplate.queryForObject(sql.toString(), new RowMapper<BoardDTO>() { @Override public BoardDTO mapRow(ResultSet rs, int rowNum) throws SQLException { BoardDTO dto = new BoardDTO(); dto.setNum(rs.getInt("num")); dto.setName(rs.getString("name")); dto.setPwd(rs.getString("pwd")); dto.setEmail(rs.getString("email")); dto.setSubject(rs.getString("subject")); dto.setContent(rs.getString("content")); dto.setIpAddr(rs.getString("ipAddr")); dto.setHitCount(rs.getInt("hitCount")); dto.setCreated(rs.getString("created")); return dto; } },num); return dtoOne; } public void deleteData(int num){ StringBuilder sql = new StringBuilder(200); sql.append("delete board where num=?"); jdbcTemplate.update(sql.toString(),num); } public void updateData(BoardDTO dto){ StringBuilder sql = new StringBuilder(200); sql.append("update board set name=?, pwd=?, email=?, subject=?,") .append("content=? where num=?"); jdbcTemplate.update(sql.toString(), dto.getName(), dto.getPwd(), dto.getEmail(), dto.getSubject(), dto.getContent(), dto.getNum()); } }

4. 컨트롤러의 dao 객체를 BoardDAO2클래스로 변경