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+"' >"
   				+ "  &laquo;</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+"' >"
   				+ "  &raquo;</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">&times;</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'>&nbsp;</a></i>
	                                    <i class="icon-chevron-right show-sidebar" style="display:none;"><a href='#' title="Show Sidebar" rel='tooltip'>&nbsp;</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'>&nbsp;</a></i>
	                                    <i class="icon-chevron-right show-sidebar" style="display:none;"><a href='#' title="Show Sidebar" rel='tooltip'>&nbsp;</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 컨넥션 설정은 필수 설정이다.