[MyBatis] 게시판 페이징 (Pagination) Mysql&Oracle

안녕하세요 yunamom 입니다 :D

이번포스팅에서는 MyBatis 게시판 페이징 하는 과정을 알아보겠습니다 😊

결과물

 

dto/Board.java [ OPEN ]

package com.board.dto;

import java.text.SimpleDateFormat;
import java.util.Date;

public class Board {

	int unq;
	String title;
	String name;
	String content;
	int hits;
	String rdate;

	public int getUnq() {
		return unq;
	}

	public void setUnq(int unq) {
		this.unq = unq;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	public int getHits() {
		return hits;
	}

	public void setHits(int hits) {
		this.hits = hits;
	}

	public String getRdate() {
		return rdate;
	}

	public void setRdate(Date rdate) {
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy년 MM월 dd일");
		String strNowDate = simpleDateFormat.format(rdate);
		
		this.rdate = strNowDate;
	}
}

 

model/Pagination.java [ OPEN ]

package com.board.model;

public class Pagination {

	/* 현재 페이지, 디폴트 값은 1 */
	int pg = 1;

	/* 페이지 당 레코드 수, 디폴트 값은 5 */
	int sz = 5;

	/* 전체 레코드 수 */
	int recordCount;

	public String getQueryString() {
		return String.format("pg=%d&sz=%d", pg, sz);
	}

	public int getPg() {
		return pg;
	}

	public void setPg(int pg) {
		this.pg = pg;
	}

	public int getSz() {
		return sz;
	}

	public void setSz(int sz) {
		this.sz = sz;
	}

	public int getRecordCount() {
		return recordCount;
	}

	public void setRecordCount(int recordCount) {
		this.recordCount = recordCount;
	}
}

 

mapper/BoardMapper.java [ OPEN ]

package com.board.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.board.dto.Board;
import com.board.model.Pagination;

@Mapper
public interface BoardMapper {
	/* 이 메소드는 xml 에 구현하겠습니다. */
	List<Board> findAll(Pagination pagination);

	@Select("SELECT COUNT(*) FROM board")
	int count();
	
	@Select("SELECT * FROM board WHERE unq = #{unq}")
	Board findOne(int unq);
	
	/* 조회수 증가 */
	public int updatehits(int unq);
	
	@Insert("INSERT board (title, content, name, hits, rdate) "
	      + "VALUES (#{title}, #{content}, #{name}, 0, sysdate())")
	@Options(useGeneratedKeys=true, keyProperty = "unq")
	void insert(Board board);

	@Delete("DELETE FROM board WHERE unq = #{unq}")
	void delete(int unq);
}

 

mapper/BoardMapper.xml [ OPEN ]

<?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="com.board.mapper.BoardMapper">
	<select id="findAll" resultType="com.board.dto.Board">
		<bind name="start" value="(pg - 1)*sz" />
		SELECT * FROM board
		ORDER BY unq DESC
		LIMIT #{start}, #{sz}
	</select>
	
	<update id="updatehits" >
		UPDATE board SET hits = hits+1 WHERE unq = #{unq}
	</update>
</mapper>

✨Oracle 데이터베이스 (ROWNUM 을 이용한 2가지 방법) XE11g

<?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="com.board.mapper.BoardMapper">
<select id="findAll" resultType="com.board.dto.Board">

        SELECT * FROM 
              (SELECT A.*,Floor((ROWNUM-1)/#{sg+1})PAGE,ROWNUM 
                 FROM
              (SELECT *
                 FROM board ORDER BY unq DESC)A) 
        WHERE PAGE = #{pg}
       
</select>
</mapper>

<?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="com.board.mapper.BoardMapper">
	<select id="findAll" resultType="com.board.dto.Board">
    
		<bind name="start" value="(pg - 1)*sz" />
        SELECT * FROM
              (SELECT A.*,ROWNUM limit
                 FROM
              (SELECT * 
                 FROM board ORDER BY unq DESC)A) 
        WHERE limit > #{start} AND limit <= #{pg*sz};
    
	</select>
</mapper>

 

controller/BoardController.java [ OPEN ]

package com.board.controller;

import java.util.List;

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.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import com.board.dto.Board;
import com.board.mapper.BoardMapper;
import com.board.model.Pagination;

@Controller
@RequestMapping("/board")
public class BoardController {
	
	@Autowired BoardMapper boardMapper;
		
	@RequestMapping("list")
	public String list(Model model, Pagination pagination) {
		List<Board>boards = boardMapper.findAll(pagination);
		/* 마지막 페이지 번호 */
		pagination.setRecordCount(boardMapper.count());
		/* 전체 게시글 갯수 */
		model.addAttribute("total" , boardMapper.count());
		/* 한페이지 게시물 불러오기 */
		model.addAttribute("boards" , boards);
		return "board/list";
	}
}

 

WEB-INF/tags/pagination.tag  *중요  [ OPEN ]

<%@ tag description="pagination" pageEncoding="UTF-8"%>
<%@ tag import="java.util.ArrayList" %>
<%@ attribute name="recordCount" type="java.lang.Integer" required="true" %>
<%@ attribute name="pageSize" type="java.lang.Integer" required="true" %>
<%@ attribute name="queryStringName" type="java.lang.String" required="true" %>
<%!
private class Page {
	
	int page;
	String label;
	Page(int page, String label) {
		this.page = page;
		this.label = label;
	}
	
}
%>

<%
int recordCount = (Integer)jspContext.getAttribute("recordCount");
int pageSize = (Integer)jspContext.getAttribute("pageSize");
String name = (String)jspContext.getAttribute("queryStringName");

int currentPage = 1;
if (request.getParameter(name) != null)
	currentPage = Integer.parseInt(request.getParameter(name));

int pageCount = recordCount / pageSize;
if (pageCount * pageSize < recordCount ) pageCount++;

String queryString = request.getQueryString();
if (queryString == null){
	queryString = name + "=@@@";
}else if(queryString.matches(".*" + name + "=[0-9]+.*")){
	queryString = queryString.replaceAll(name + "=[0-9]+", name + "=@@@");
}else{
	queryString = queryString + "&" + name + "=@@@";
}
String url = request.getAttribute("javax.servlet.forward.request_uri") + "?" + queryString;

if(currentPage > pageCount) currentPage = pageCount;
int base = ((currentPage - 1) / 5) * 5;

ArrayList<Page> pages = new ArrayList<Page>();

/* 이전 */
if(base > 0) 
	pages.add(new Page(base, "&lt;"));

for(int i=1; i<=5; i++){
	int n = base + i;
	if (n > pageCount) break;
	pages.add(new Page(n, String.valueOf(n)));
}
int n = base + 6;
/* 다음 */
if (n <= pageCount)
	pages.add(new Page(n, "&gt;"));
%>
<!-- 화면 하단에 페이지 번호 목록을 출력하기 위한 확장 태그 구현 -->
<table class="pagination">
	<tr>
	<%for (Page p : pages) { %>
	<td class='<%=p.page == currentPage ? "active" : "" %>'>
		<a href='<%=url.replace("@@@", String.valueOf(p.page)) %>'><%=p.label %></a>
	</td><%} %>
	</tr>
</table>

 

WEB-INF/views/board/list.jsp [ OPEN ]

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib tagdir="/WEB-INF/tags" prefix="my" %>
<c:url var="R" value="../" />
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>∙ 게시판 목록 ∙</title>
<link rel="stylesheet" href="${R}css/style.css">
</head>
<body>
<%@include file="../include/top.jsp" %>
<section>
<div class="wrapper">
<div class="container">
	<h1></h1>
	<div class="left"><div class="box">전체 글(${ total })</div></div>
	<div class="right"><a href="write" class="btn">write</a></div>
	<table class="list">
	<colgroup>
		<col width="10%">
		<col width="50%">
		<col width="20%">
		<col width="10%">
	</colgroup>
		<tr>
			<th>No.</th>
			<th>Title</th>
			<th >name</th>	
			<th >hit</th>
		</tr>
		
		<c:forEach var="board" items="${ boards }" varStatus="status">
		<tr onClick="location='detail?unq=${ board.unq }&${ pagination.queryString }'" >
			<td>${ (total)-(status.index)-(pagination.sz*(pagination.pg-1)) }</td>
			<td class="title">${ board.title }</td>
			<td>${ board.name }</td>	
			<td>${ board.hits }</td>
		</tr>
		</c:forEach>
	</table>
	<div class="wrapper">
	<my:pagination pageSize="${ pagination.sz }" recordCount="${ pagination.recordCount }"
	queryStringName="pg" /></div>
</div>
</div>
</section>
</body>
</html>

 

resources/static/css/style.css [ OPEN ]

@charset "UTF-8";
* {
	margin: 0;
	padding: 0;
}
html {
	background: #e1c9c9;
}
body {
	max-width: 1300px;
	margin: 0 auto;
	font-family: gulim;
	text-align: center;
	font-size: 19px;
	color: #595a5a;
}
header {
	padding: 5rem;	
	border-radius:0.8rem 0.8rem 0 0 ;
	color: #fff;
}
nav ul {
	height: 50px;
	line-height: 50px;
	background: rgba(255,255,255,0.2);
}
nav ul li {
	list-style: none;
	float: left;
	padding: 0 15px;
	font-size: 20px;
}
a {
	text-decoration: none;
	color: #fff;
	cursor: pointer;
}

section {
	margin: 5%;
}
.wrapper {
	display: flex;
	justify-content: center;
	align-items: center;
}
div.container {
	min-width: 800px;
	margin: 10px auto;
	overflow: auto;
}
.left {
	float: left;
}
.right {
	float: right;
}
.box {
	display: inline-block;
	margin: 0.5rem 0; /* 위아래 , 양옆 */	
	padding: 0.5rem 0;
	color: #333;
	font-size: 15px;
}
.btn {
	display: inline-block;
	margin: 0; /* 위아래 , 양옆 */	
	padding: 0.5rem 1rem;
	border: 0;
	border-radius: 0.5rem;
	cursor: pointer;
	background: linear-gradient(rgba(255,255,255,0.1), #ddd);
	text-decoration: none;
	color: #595a5a;
}
.btn:hover {
	background: none;
	text-decoration: underline;
}


.smallbtn {
	margin: 0;
	padding: 0.2rem 0.5rem;
	border: 1px solid gray;
	border-radius: 0.5rem;
	cursor: pointer;
	background: linear-gradient(#fff, #ddd);
	text-decoration: none;
	color: #333;
}

.list {
	width: 100%;
	border-top: 1px solid rgba(127, 127, 127, 0.2);
	border-collapse: collapse;
}

.list a {
	color: #333;
}

.list td, .list th{
	padding: 0.7rem;
	border-bottom: 1px solid rgba(127, 127, 127, 0.2);
}

.list th{
	font-size: 19px;
	letter-spacing: px;
}

.title {
	cursor: pointer;
	text-align: left;
}

.title:hover {
	background-color: rgba(255,255,255,0.05);
	text-decoration: underline;
}

.write {
	width: 100%;
}

input {
	width: 90%;
	padding: 0.5rem;
	border: 0;
	border-bottom: 1px solid rgba(127, 127, 127, 0.6);
	font-size: 25px;
	background: none;
}

textarea {
	margin: 10px;
	width: 90%;
	border: 0;
	border-bottom: 1px solid rgba(127, 127, 127, 0.6);
	height: 300px;
	font-size: 17px;
	resize: none;
	background: none;
	
}

input:focus, textarea:focus {
	outline: none;
}

table.list select {
	width: 120px;
	height: 30px;
	font-size: 16px;
	background-color: rgba(255,255,255,0.6);
}

tr[data-url]:hover {
	cursor: pointer;
	background-color: rgba(250,250,250,0.2);
}
.content {
	text-align: left; 
	height: 400px;
	font-size: 18px;
}
iframe {
	border-radius: 0.8rem;
}
.pagination {
	border-collapse: collapse;
	margin-top: 1rem;
}
.pagination td {
	text-align: center;
}
.pagination td:hover {
	background-color: #e3d4d4;
	border-radius: 1rem;
}
.pagination td.active {
	background-color: #e3d4d4;
	border-radius: 1rem;
}
.pagination a {
	text-decoration: none;
	color: #595a5a;
	display: inline-block;
	width: 25px;
	padding: 6px;
}
.pagination td.active a {
	color: white;
}

pre { 
	white-space: pre-wrap; 
	font-family: gulim;
}