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>
