[Spring] buyprod - list 검색, 페이징

Spring

[Spring] buyprod - list 검색, 페이징

아잠만_ 2024. 7. 26. 16:40

JSP

list.jsp

<%@ page language="java" contentType="text/html; charset=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>
<head>
<title></title>
<style>
.flex{
	display: flex;
	width: 350px;
	background: rgb(240,240,240);
	border-radius: 30px;
	margin: 10px;
}
.month{
	width: 40px;
	padding : 1px auto;
	background: white;
	border-radius: 100px;
	font-weight: bold;
	margin: 1px;
	display: flex;
	justify-content: center;
	align-items: center;
}
.qty{
	text-align: right;
	width: 25%;
	margin: 10px;
}
.amt{
	text-align: right;
	width: 55%;
	margin: 10px;
}
.flex:hover{
	background: rgb(240,190,190);
	color: gray;
	font-weight: bold;
}
</style>
</head>
<body>
	<h1>월별 매입 현황</h1>
	<form>
		<select id="year" name="year">
			<c:forEach var="yr" begin="2020" end="2024">
			<option value="${yr}"
			<c:if test="${param.year==yr or (param.year=='' and yr==2020)}">selected</c:if>
			>${yr}</option>
			</c:forEach>
		</select>
		<label>매입금액합계</label>
		<input type="number" name="amt" placeholder="검색할 매입금액의 합계"
			value ="<c:if test="${param.amt!=null}">${param.amt}</c:if>"
		>
		<input type="submit" value="검색">
	</form>
	<!-- items : 배열, Collection(list, Set), Map(hashTable, hashMap, sortedMap) -->
		<c:forEach var="data" items="${data.content}">
		<div class="flex">
			<div class="month">${data.buyDateMonth}월</div>
			<div class="qty"><fmt:formatNumber value="${data.buyQty}" type="number" pattern="#,###"/> 개</div>
			<div class="amt"><fmt:formatNumber value="${data.amt}" type="number" pattern="#,###"/> 원</div>
		</div>
		</c:forEach>
		<p>${data.startPage} to ${data.endPage}</p>
<!-- startPage가 5보다 클 때에만 [이전] 활성화 -->
	<c:if test="${data.startPage gt 5}">
	<a href="/buyprod/list?currentPage=${data.startPage-5}&year=${param.year}&amt=${param.amt}">[이전]</a>
	</c:if>
	<c:forEach var="pNo" begin="${data.startPage}"
	 			  end="${data.endPage}">
	<a href="/buyprod/list?currentPage=${pNo}&year=${param.year}&amt=${param.amt}">[${pNo}]</a>
	</c:forEach>
	<!-- endPage < totalPages일때만 [다음]활성 -->
	<c:if test="${data.endPage lt data.totalPages}">
	<a href="/buyprod/list?currentPage=${data.startPage+5}&year=${param.year}&amt=${param.amt}">[다음]</a>
	</c:if>
</body>
</html>

JAVA

BuyprodVO.java

package kr.or.ddit.vo;

import java.util.Date;

import lombok.Data;

@Data
public class BuyprodVO {
	private int rnum;
	private Date buyDate;
	private String buyProd;
	private int buyQty;
	private int buyCost;

	private int year;
	private int buyDateMonth;
	private int amt;
}

ArticlePage.java

package kr.or.ddit.util;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

//페이징 관련 정보 + 게시글 정보
// new ArticlePage<FreeBoardVO>(total, currentPage, size, content);
public class ArticlePage<T> {
	//전체글 수
	private int total;
	// 현재 페이지 번호
	private int currentPage;
	// 전체 페이지수 
	private int totalPages;
	// 블록의 시작 페이지 번호
	private int startPage;
	//블록의 종료 페이지 번호
	private int endPage;
	//검색어
	private Map<String, Object> keyword;
	//요청URL
	private String url = "";
	//select 결과 데이터
	private List<T> content;
	//페이징 처리
	private String pagingArea = "";
	
	//생성자(Constructor) : 페이징 정보를 생성
	//					753				1				10			select결과10행
	public ArticlePage(int total, int currentPage, int size, List<T> content, Map<String,Object> keyword) {
		//size : 한 화면에 보여질 목록의 행 수
		this.total = total;//753
		this.currentPage = currentPage;//1
		this.content = content;
		this.keyword = keyword;
		
		//전체글 수가 0이면?
		if(total==0) {
			totalPages = 0;//전체 페이지 수
			startPage = 0;//블록 시작번호
			endPage = 0; //블록 종료번호
		}else {//글이 있다면
			//전체 페이지 수 = 전체글 수 / 한 화면에 보여질 목록의 행 수
			//3 = 31 / 10
			totalPages = total / size;//75
			
			//나머지가 있다면, 페이지를 1 증가
			if(total % size > 0) {//나머지3
				totalPages++;//76
			}
			
			//페이지 블록 시작번호를 구하는 공식
			// 블록시작번호 = 현재페이지 / 페이지크기 * 페이지크기 + 1
			startPage = currentPage / 5 * 5 + 1;//1
			
			//현재페이지 % 페이지크기 => 0일 때 보정
			if(currentPage % 5 == 0) {
				startPage -= 5;
			}
			
			//블록종료번호 = 시작페이지번호 + (페이지크기 - 1)
			//[1][2][3][4][5][다음]
			endPage = startPage + (5 - 1);//5
			
			//종료페이지번호 > 전체페이지수
			if(endPage > totalPages) {
				endPage = totalPages;
			}
		}
		
		pagingArea += "<div class='dataTables_paginate paging_simple_numbers' id='example2_paginate'>";
		pagingArea += "<ul class='pagination justify-content-center'>";
		pagingArea += "<li class='paginate_button page-item previous "; 
		if(this.startPage<6) {
			pagingArea += "disabled ";
		}
		pagingArea += "'";
		pagingArea += "id='example2_previous'>";
		pagingArea += "<a href='"+createUrl(this.startPage-5)+"' aria-controls='example2' data-dt-idx='0' tabindex='0' ";
		pagingArea += "class='page-link'><</a></li>";
		
		for(int pNo=this.startPage;pNo<=this.endPage;pNo++) {		
		pagingArea += "<li class='paginate_button page-item ";
			if(this.currentPage == pNo) {
				pagingArea += "active";
			}
			pagingArea += "'>";
			pagingArea += "<a href='"+createUrl(pNo)+"' aria-controls='example2' data-dt-idx='1' tabindex='0' ";
			pagingArea += "class='page-link'>"+pNo+"</a>";
			pagingArea += "</li>";
		}
		pagingArea += "<li class='paginate_button page-item next "; 
		if(this.endPage>=this.totalPages) {
			pagingArea += "disabled";
		}
		pagingArea += "' id='example2_next'><a ";
		pagingArea += "href='"+createUrl(this.startPage+5)+"' aria-controls='example2' data-dt-idx='7' ";
		pagingArea += "tabindex='0' class='page-link'>></a></li>";
		pagingArea += "</ul>";
		pagingArea += "</div>";
	}//end 생성자
	
    // page 번호를 매개변수로 받아 url을 만들어주는 메서드
	public String createUrl(int currentPage) {
		String curl = this.url+"?currentPage="+currentPage+"&";
		List<String> keyset = new ArrayList<String>(keyword.keySet());
		for(String key : keyset) {
			String val = ""+keyword.get(key);
			if(keyword.containsKey(val)) {
				val="";
			}
			curl += key+"="+val+"&";
		}
		return curl.substring(0,curl.length()-1);
	};

	public int getTotal() {
		return total;
	}

	public void setTotal(int total) {
		this.total = total;
	}

	public int getCurrentPage() {
		return currentPage;
	}

	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}

	public int getTotalPages() {
		return totalPages;
	}

	public void setTotalPages(int totalPages) {
		this.totalPages = totalPages;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public List<T> getContent() {
		return content;
	}

	public void setContent(List<T> content) {
		this.content = content;
	}
	
	//전체 글의 수가 0인가?
	public boolean hasNoArticles() {
		return this.total == 0;
	}
	
	//데이터가 있나?
	public boolean hasArticles() {
		return this.total > 0;
	}
	
	public void setPagingArea(String pagingArea) {
		this.pagingArea = pagingArea;
	}

	public Map<String, Object> getKeyword() {
		return keyword;
	}

	public void setKeyword(Map<String, Object> keyword) {
		this.keyword = keyword;
	}

	//페이징 블록을 자동화
	public String getPagingArea() {
		return this.pagingArea;
	}
}

BuyprodController.java

package kr.or.ddit.controller;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import kr.or.ddit.service.BuyprodService;
import kr.or.ddit.util.ArticlePage;
import kr.or.ddit.vo.BuyprodVO;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Controller
@RequestMapping(value="/buyprod")
public class BuyprodController {
	@Autowired
	BuyprodService service;
	
	@GetMapping("/list")
	public String list(Model model, @RequestParam Map<String, Object> map, @RequestParam(value="currentPage", required=false, defaultValue="1") int currentPage) {
		if(map.get("year")==null || map.get("year")=="") map.put("year", 2020);
		if(map.get("amt")==null || map.get("amt")=="") map.put("amt",0);
		int size = 3;
		map.put("size", size);
		int total = this.service.getTotal(map);
		log.info("total "+total);
		map.put("currentPage", currentPage);
		log.info("map >>"+map);
		List<BuyprodVO> list = this.service.list(map);
		map.remove("size");
		map.remove("currentPage");
		model.addAttribute("data", new ArticlePage<BuyprodVO>(total, currentPage, size, list, map));
		return "buyprod/list";
	}
}

BuyprodService.java

package kr.or.ddit.service;

import java.util.List;
import java.util.Map;

import kr.or.ddit.vo.BuyprodVO;

public interface BuyprodService {

	public List<BuyprodVO> list(Map<String, Object> map);

	public int getTotal(Map<String, Object> map);

}

BuyprodServiceImpl.java

package kr.or.ddit.service.impl;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import kr.or.ddit.dao.BuyprodDao;
import kr.or.ddit.service.BuyprodService;
import kr.or.ddit.vo.BuyprodVO;

@Service
public class BuyprodServiceImpl implements BuyprodService{

	@Autowired
	BuyprodDao dao;
	
	@Override
	public List<BuyprodVO> list(Map<String, Object> map) {
		return this.dao.list(map);
	}

	@Override
	public int getTotal(Map<String, Object> map) {
		return this.dao.getTotal(map);
	}

}

BuyprodDao.java

package kr.or.ddit.dao;

import java.util.List;
import java.util.Map;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import kr.or.ddit.vo.BuyprodVO;

@Repository
public class BuyprodDao {
	@Autowired
	SqlSessionTemplate sst;

	public List<BuyprodVO> list(Map<String, Object> map) {
		return this.sst.selectList("buyprod.list", map);
	}

	public int getTotal(Map<String, Object> map) {
		return this.sst.selectOne("buyprod.getTotal", map);
	}
}

Mybatis

typeAlias

   <typeAliases>
       <typeAlias type="kr.or.ddit.vo.BuyprodVO" alias="buyprodVo" /> 
   </typeAliases>

buyprod_SQL.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="buyprod">

	<select id="list" resultType="buyprodVo" parameterType="hashMap">
	<![CDATA[
	with t as(
		SELECT 
		    ROW_NUMBER() OVER (ORDER BY TO_NUMBER(REPLACE(B.BUY_DATE, 12, 0)) ASC) AS RNUM,
		    B.BUY_DATE AS buy_date_month,
		    NVL(A.BUY_QTY, 0) AS BUY_QTY,
		    NVL(A.AMT, 0) AS AMT
		FROM
		    (
		        SELECT 
		            EXTRACT(MONTH FROM BUY_DATE) AS BUY_DATE,
		            SUM(BUY_QTY) AS BUY_QTY,
		            SUM(BUY_QTY * BUY_COST) AS AMT
		        FROM 
		            BUYPROD
		        WHERE 
		            EXTRACT(YEAR FROM BUY_DATE) = #{year}
		        GROUP BY 
		            EXTRACT(MONTH FROM BUY_DATE)
		        HAVING 
		            SUM(BUY_QTY * BUY_COST) >= #{amt}
		    ) A 
		RIGHT OUTER JOIN
		    (
		        SELECT LEVEL AS BUY_DATE 
		        FROM DUAL
		        CONNECT BY LEVEL <= 12
		    ) B 
		ON 
		    A.BUY_DATE = B.BUY_DATE
	)
	select t.*
	from t
	WHERE  T.RNUM BETWEEN (#{currentPage}*#{size}) - (#{size}-1) AND (#{currentPage}*#{size})	
	]]>
	</select>
	
	<select id="getTotal" parameterType="hashMap" resultType="int">
		<![CDATA[
		SELECT count(*)
	FROM
	(
	    SELECT EXTRACT(MONTH FROM BUY_DATE) BUY_DATE
	         , SUM(BUY_QTY) BUY_QTY
	         , SUM(BUY_QTY * BUY_COST) AMT
	    FROM   BUYPROD
	    WHERE  EXTRACT(YEAR FROM BUY_DATE) = TO_CHAR(#{year})
	    GROUP BY EXTRACT(MONTH FROM BUY_DATE)
	    HAVING SUM(BUY_QTY * BUY_COST) >= #{amt}
	) A RIGHT OUTER JOIN
	(
	    SELECT LEVEL BUY_DATE FROM DUAL
	    CONNECT BY LEVEL <= 12
	) B
	ON(A.BUY_DATE = B.BUY_DATE)
		]]>
	</select>
</mapper>