DB - SQL
-- 상품 테이블 create table tbl_product ( pseq number PRIMARY KEY , -- product_seq 시퀀스 객체로 자동 일련번호 부여 name VARCHAR2(100) DEFAULT 0, -- 상품명 kind char(1), -- 상품 종류 price1 number DEFAULT 0, -- 원가 price2 number default 0, -- 판매가 price3 number DEFAULT 0, -- 판매가-원가 content VARCHAR2(3000) null, -- 상품 내용 image VARCHAR2(150) DEFAULT 'default.jpg', useyn char(1) DEFAULT 'y', -- 상품 사용유무 체크 y: 사용가능 n: 사용불가능 bestyn char(1) DEFAULT 'n', -- 베스트상품인지 여부 체크 y:베스트 상품 n:베스트 상품 아님 indate date default sysdate -- 등록일 ) TABLESPACE macaronics ;
DTO
package net.macaronics.web.dto; import java.sql.Timestamp; public class ProductVO { private int pseq; //product_seq 시퀀스 객체로 자동 일련번호 부여 private String name; //상품명 private String kind ; //char(1) 상품 종류 private int price1 ; // number DEFAULT 0, -- 원가 private int price2; // number default 0, -- 판매가 private int price3; // number DEFAULT 0, -- 판매가-원가 private String content; //VARCHAR2(3000) null, -- 상품 내용 private String image; // VARCHAR2(150) DEFAULT 'default.jpg', private String useyn; // char(1) DEFAULT 'y', -- 상품 사용유무 체크 y: 사용가능 n: 사용불가능 private String bestyn; // char(1) DEFAULT 'n', -- 베스트상품인지 여부 체크 y:베스트 상품 n:베스트 상품 아님 private Timestamp indate ; //date default sysdate
AdminProductListAction
package net.macaronics.web.admin.controller; import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import config.Paging; import net.macaronics.web.admin.controller.dao.AdminProductDAO; import net.macaronics.web.controll.action.Action; import net.macaronics.web.dto.ProductVO; public class AdminProductListAction implements Action { private static final Logger logger =LogManager.getLogger(AdminProductListAction.class); @Override public void execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String url="admin/product/productList.jsp"; //1.페이징 객체 생성 페이지의 넘버값을 읽어드림 Paging paging =new Paging(request.getParameter("pageNum")); AdminProductDAO productDAO =AdminProductDAO.getInstance(); //2.페이징 객체에 전체페이지를 넘겨줌, Paging 클래스에서 페이지 계산 처리 됨 String key=request.getParameter("key"); if(key==null)key="%"; int count=productDAO.totalRecord(key); paging.setTotalCount(count); //3.페이지가 넘어갈 url 주소 paging.paginHtml("MacaronicsServlet?command=admin_product_list&key="+key); logger.info("AdminProductListAction - {}, {}, {} " , count, paging.getStartRow(), paging.getEndRow()); List<ProductVO> productList=productDAO.listProduct(paging,key ); request.setAttribute("key", key); request.setAttribute("paging", paging); request.setAttribute("count", count); request.setAttribute("productList", productList); request.getRequestDispatcher(url).forward(request, response); } }
Paging
package config; public class Paging { //화면에 보여질 게시글의 개수를 지정 private int pageSize=10; private int count =0; //전체 글의 갯수를 저장하는 변수 private int number =0; //페이지 넘버링 변수 private String pageNum; private int startRow; private int endRow; private int currentPage; private int pageCount; private int startPage; private int pageBlock=10;//카운터링 처리 숫자 private int endPage; private int prev; //이전 private int next; //다음 private String html; public Paging(String pageNum) { //만약 처음 boardList.jsp를 클릭하거나 수정 삭제 등 다른 게시글에서 이 페이지로 넘어오면 pageNum값이 없기에 null 처리를 해줌 if(pageNum==null){ pageNum="1"; } this.pageNum=pageNum; //전체 보고자 하는 페이지숫자를 저장 currentPage =Integer.parseInt(pageNum); } public void setTotalCount(int count){ this.count=count; //현재 페이지에 보여줄 시작 번호를 설정 = 데이터 베이스에서 불러올 시작번호 startRow =(currentPage-1) *pageSize+1; endRow =currentPage * pageSize; //테이블에 표시할 번호를 지정 this.number =count - (currentPage -1 ) * pageSize; //페이지 계산 pageCaculator(); } public void pageCaculator(){ if(count >0){ pageCount =count /pageSize + (count%pageSize == 0 ? 0 :1) ; //카우터링 숫자를 얼마까지 보여줄건지 결정 //시작 페이지 숫자를 설정 startPage =1; if(currentPage %10 !=0){ startPage =(int)(currentPage/10)*10+1; }else{ startPage =((int)(currentPage/10)-1)*10+1; } endPage =startPage+pageBlock-1;//화면에 보여질 페이지의 마지막 숫자 if(endPage > pageCount) endPage =pageCount; //이전 다음 if(startPage >pageSize) prev =startPage-10; //다음 if(endPage < pageCount) next=startPage+10; } } public void paginHtml(String url){ String html= "<div class='pagination' class='text-center'><ul >"; //처음 if(startPage >pageSize){ html +="<li><a href='"+url+"&pageNum="+1+"' >" + " 처음</a></li>"; } //이전이라는 링크를 만들건지 파악 if(startPage >pageSize){ html +="<li><a href='"+url+"&pageNum="+prev+"' >" + " «</a></li>"; } //페이징 처리 String active=""; for(int i=startPage; i<endPage+1; i++){ if(i!=0){ if(i==Integer.parseInt(pageNum)) active ="class='active' "; else active=""; html +="<li " +active+" ><a href='"+url+"&pageNum="+i+"'>"+i+"</a></li>"; } } //다음 이라는 링크를 만들건지 파악 if(endPage < pageCount ){ html +="<li><a href='"+url+"&pageNum="+next+"' >" + " »</a></li>"; } //다음 이라는 링크를 만들건지 파악 if(endPage < pageCount ){ html +="<li><a href='"+url+"&pageNum="+pageCount+"' >" + " 마지막</a></li>"; } html +="</ul></nav>"; this.html=html; } public int getNumber() { return number; } public void setNumber(int number) { this.number = number; } public int getStartRow() { return startRow; } public void setStartRow(int startRow) { this.startRow = startRow; } public int getEndRow() { return endRow; } public void setEndRow(int endRow) { this.endRow = endRow; } public String getHtml() { return html; } public void setHtml(String html) { this.html = html; } }
DAO
AdminProductDAO
public class AdminProductDAO { private static final Logger logger =LogManager.getLogger(AdminProductDAO.class); private SqlSession sqlSession; private static AdminProductDAO instance; private AdminProductDAO(){ } public static AdminProductDAO getInstance(){ if(instance==null){ instance =new AdminProductDAO(); } return instance; } //전체 상품 수 가져오기 public int totalRecord(String product_name){ int total_pages =0; try{ sqlSession=MybatisService.getFactory().openSession(); if(product_name==null || product_name.equals("")){ product_name="%"; } total_pages=sqlSession.selectOne("adminProduct.totalRecord", product_name); }catch(Exception e){ e.printStackTrace(); }finally{ MybatisService.sessionClose(sqlSession); } return total_pages; } //상품목록 페이징 처리 // sql ex) // select * from // ( // select rownum as num , t.* from (select PSEQ, NAME, KIND, PRICE1, PRICE2, PRICE3, IMAGE, USEYN, BESTYN, INDATE // // from TBL_PRODUCT where name like '%'|| '남성' ||'%' order by indate desc ) t ) // // where num >= 1 and num <= 10; public List<ProductVO> listProduct(Paging page, String product_name){ List<ProductVO> list =new ArrayList<>(); try{ sqlSession=MybatisService.getFactory().openSession(); if(product_name==null || product_name.equals("")){ product_name="%"; } Map<String, Object> map =new HashMap<>(); map.put("start", page.getStartRow()); map.put("end", page.getEndRow()); map.put("proudct_name", product_name); list=sqlSession.selectList("adminProduct.listProduct" ,map ); }catch(Exception e){ e.printStackTrace(); }finally{ MybatisService.sessionClose(sqlSession); } return list; } }
Mysql
adminProduct.xml
<select id="totalRecord" resultType="int"> <![CDATA[ select count(*) from TBL_PRODUCT where name like '%'|| #{proudct_name} ||'%' ]]> </select> <select id="listProduct" resultType="net.macaronics.web.dto.ProductVO"> <![CDATA[ select * from ( select rownum as num , t.* from (select PSEQ, NAME, KIND, PRICE1, PRICE2, PRICE3, IMAGE, USEYN, BESTYN, INDATE from TBL_PRODUCT where name like '%'|| #{proudct_name} ||'%' order by indate desc ) t ) where num >= #{start} and num <= #{end} ]]> </select>
View
productList.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %> <!DOCTYPE html> <html class="no-js"> <head> <jsp:include page="../../include/AdminHeader.jsp" /> </head> <body> <jsp:include page="../../include/AdminTopFixMenu.jsp" /> <div class="container-fluid"> <div class="row-fluid"> <div class="span3" id="sidebar"> <jsp:include page="../../include/AdminLeftMenu.jsp" /> </div> <!--/span--> <div class="span9" id="content"> <div class="row-fluid"> <div class="alert alert-success"> <button type="button" class="close" data-dismiss="alert">×</button> <h4>상품목록</h4> </div> <div class="navbar"> <div class="navbar-inner"> <ul class="breadcrumb"> <i class="icon-chevron-left hide-sidebar"><a href='#' title="Hide Sidebar" rel='tooltip'> </a></i> <i class="icon-chevron-right show-sidebar" style="display:none;"><a href='#' title="Show Sidebar" rel='tooltip'> </a></i> <li> <form action="MacaronicsServlet" > 상품명 <input type="text" name="key" value="${key =='%' ? '':key }"> <input type="hidden" value="admin_product_list" name="command"> <input type="submit" class="success" value="검색"> </form> <a href="#"></a> </li> </ul> </div> </div> <div class="navbar"> <div class="navbar-inner"> <ul class="breadcrumb"> <i class="icon-chevron-left hide-sidebar"><a href='#' title="Hide Sidebar" rel='tooltip'> </a></i> <i class="icon-chevron-right show-sidebar" style="display:none;"><a href='#' title="Show Sidebar" rel='tooltip'> </a></i> <li> 검색된 상품 수 ${count } 개 <input type="submit" value="전체보기" class="primary" onclick="location.href='MacaronicsServlet?command=admin_product_list'"> <input type="submit" value="상품 등록"> <a href="#"></a> </li> </ul> </div> </div> </div> <div> <div class="table-responsive"> <table class="table"> <tr class="success"> <th>번호 </th> <th>상품명</th> <th>원가</th> <th>판매가</th> <th>등록일</th> <th>사용유무</th> </tr> <c:if test="${empty count or count==0 }" > <tr> <td colspan="6" class="text-center"> 검색된 상품이 없습니다.</td> </tr> </c:if> <c:forEach items="${productList}" var="productVO"> <tr> <td>${productVO.pseq }</td> <td>${productVO.name }</td> <td>${productVO.price1 }</td> <td>${productVO.price2 }</td> <td><fmt:formatDate type="date" value="${productVO.indate }" /></td> <td>${productVO.useyn }</td> </tr> </c:forEach> <tfoot> <tr> <td colspan="6" > ${paging.html } </td> </tr> </tfoot> </table> </div> </div> </div> </div> </div> <hr> <jsp:include page="../../include/AdminFooter.jsp" />
결과 화면
제작 : macaronics.net - Developer Jun Ho Choi
소스 : https://github.com/braverokmc79/jsp_sin
${request.getContextPath() } 처리를 안한 부분이 있으므로
루트 설정( http://macaronics.net/index.php/m01/jsp/view/1352) 및 server.xml 에서 DB 컨넥션 설정은 필수 설정이다.
댓글 ( 4)
댓글 남기기