171
No
오라클 페이지 나누기
쿼리
select * from
(
select rownum as rn , A.*
from (select rownum , bno, content, title, viewcnt, writer, userid, username ,regdate
from v_free_board
where bno >0
and title like '%' || '755' || '%'
order by bno desc , regdate desc
) A
) where rn BETWEEN 1 and 10
Mapper.xml
<select id="countArticle" resultType="int">
select count(*) from v_free_board where bno > 0
<include refid="search"></include>
</select>
<sql id="search">
<if test="search_option !=null">
<if test="search_option =='title' || search_option =='writer' || search_option=='content' || search_option=='username' ">
and ${search_option} like '%' || #{keyword} || '%'
</if>
</if>
<if test="search_option == 'all'">
and ( title like '%' || #{keyword} || '%' )
or ( writer like '%' || #{keyword} || '%' )
or ( content like '%' || #{keyword} || '%' )
</if>
</sql>
<select id="boardList" resultType="BoardVO">
select * from
(
select rownum as rn , A.*
from (select rownum , bno, content, title, viewcnt, writer, userid, username ,regdate
from v_free_board
where bno >0
<include refid="search"></include>
order by bno desc , regdate desc
) A
) where rn BETWEEN #{start} and #{end}
</select>
Pager.class
public class Pager {
//페이지당 게시물 수
public static final int PAGE_SCALE =10;
//화면당 페이지 수
public static final int BLOCK_SCALE =10;
private Integer curPage; //현재 페이지
private int prevPage; //이전 페이지
private int nextPage; // 다음 페이지
private int totPage; // 전체 페이지 개수
private int totBlock; //전체 페이지 블록 개수
private int curBlock; //현재 페이지 블록
private int prevBlock; //이전 페이지 블록
private int nextBlock; //다음 페이지 블록
// where rn between #{start } and #{end}
private int pageBegin; //#{start}
private int pageEnd; // #{end}
private int blockBegin;// 현재 페이지에 시작번호 [이전]
private int blockEnd; // 현재 페이지에 끝 번호 [끝]
//생성자
//Pager(레코드 갯수 , 현재 페이지 번호
public Pager(int count, int curPage) {
curBlock =1; //현재 페이지 블록 번호
this.curPage =curPage; // 현재 페이지 설정
setTotPage(count);//전페 페이지 개수 계산
//between #{start} and #{end} 에 입력될 값 계산
setPageRange();
setTotBlock(); //전체 페이지 블록 개수 계산
//페이지 블록의 시작, 끝 번호 계산
setBlockRange();
}
public Pager(){
}
private void setBlockRange() {
//현재 페이지에 몇번째 페이지 블록에 속하는지 계산
curBlock=(int)Math.ceil((curPage-1)/BLOCK_SCALE)+1;
//현재 페이지 블록의 시작, 끝 번호 계산
blockBegin =(curBlock -1) * BLOCK_SCALE +1;
blockEnd=blockBegin+BLOCK_SCALE-1;
//마지막 블록이 범위를 초과하지 않도록 처리
if(blockEnd > totPage) blockEnd=totPage;
//[이전] 을 눌렸을 때 이동할 페이지 번호
prevPage=(curBlock==1)? 1:(curBlock-1)*BLOCK_SCALE;
//[다음] 을 눌렸을 때 이동할 페이지 번호
nextPage=curBlock > totBlock ?
(curBlock * BLOCK_SCALE) : (curBlock*BLOCK_SCALE)+1;
if(nextPage >= totPage) nextPage = totPage;
}
private void setPageRange() {
//where rn between #{start } and #{end} 에 입력 될 값
// 시작 번호 = (현재 페이지 -1 ) * 페이지당 게시물 수 +1;
// ex (5-1) * 10 +1 = 41페이지
pageBegin=(curPage-1)* PAGE_SCALE+1;
//끝번호 = 시작번호 + 페이지당 게시물 수 -1;
pageEnd=pageBegin + PAGE_SCALE-1;
}
public Integer getCurPage() {
return curPage;
}
public void setCurPage(Integer curPage) {
this.curPage = curPage;
}
public int getPrevPage() {
return prevPage;
}
public void setPrevPage(int prevPage) {
this.prevPage = prevPage;
}
public int getNextPage() {
return nextPage;
}
public void setNextPage(int nextPage) {
this.nextPage = nextPage;
}
public int getTotPage() {
return totPage;
}
public void setTotPage(int count) {
//991/10 =99.1 올림 처리
// Math.ceil(실수) 올림 처리
this.totPage = (int)Math.ceil(count*1.0/PAGE_SCALE);
}
public int getTotBlock() {
return totBlock;
}
//페이지 블록의 갯수 계산 (총 100 페이지라면 10개 블록)
public void setTotBlock() {
this.totBlock = (int)Math.ceil(totPage/BLOCK_SCALE);
}
public int getCurBlock() {
return curBlock;
}
public void setCurBlock(int curBlock) {
this.curBlock = curBlock;
}
public int getPrevBlock() {
return prevBlock;
}
public void setPrevBlock(int prevBlock) {
this.prevBlock = prevBlock;
}
public int getNextBlock() {
return nextBlock;
}
public void setNextBlock(int nextBlock) {
this.nextBlock = nextBlock;
}
public int getPageBegin() {
return pageBegin;
}
public void setPageBegin(int pageBegin) {
this.pageBegin = pageBegin;
}
public int getPageEnd() {
return pageEnd;
}
public void setPageEnd(int pageEnd) {
this.pageEnd = pageEnd;
}
public int getBlockBegin() {
return blockBegin;
}
public void setBlockBegin(int blockBegin) {
this.blockBegin = blockBegin;
}
public int getBlockEnd() {
return blockEnd;
}
public void setBlockEnd(int blockEnd) {
this.blockEnd = blockEnd;
}
}
PageAndSearch
public class PageAndSearch extends Pager {
private String search_option;
private String keyword;
public String searchQuery(Integer curPage){
UriComponents uriComponents =
UriComponentsBuilder.newInstance()
.queryParam("curPage", curPage )
.queryParam("search_option", getSearch_option())
.queryParam("keyword", getKeyword())
.build();
return uriComponents.toUriString();
}
public String getSearch_option() {
return search_option;
}
public void setSearch_option(String search_option) {
this.search_option = search_option;
}
public String getKeyword() {
return keyword;
}
public void setKeyword(String keyword) {
this.keyword = keyword;
}
@Override
public String toString() {
return "PageAndSearch [search_option=" + search_option + ", keyword=" + keyword + "]";
}
}
Controller
@RequestMapping(value="/listAll.do", method=RequestMethod.GET)
public ModelAndView boardList( @ModelAttribute("pas") PageAndSearch pas){
ModelAndView mv =new ModelAndView();
int count =boardService.countArticle(pas.getSearch_option(), pas.getKeyword());
//페이지 나누기 관련 처리
if(pas.getCurPage()==null){pas.setCurPage(1);}
Pager pager =new Pager(count, pas.getCurPage());
int start =pager.getPageBegin();
int end=pager.getPageEnd();
logger.info(" **************** " + pas.toString() + " 메서드 : " + pas.searchQuery(1) +
" count : " + count + " start : " + start + " end : " + end);
List<BoardVO> list=boardService.boardList(start, end, pas.getSearch_option(), pas.getKeyword());
Map<String, Object> map =new HashMap<>();
map.put("countList", count);
map.put("list", list);
map.put("pager", pager);
/*map.put("search_option", pas.getSearch_option());
map.put("keyword", pas.getKeyword());
*/
mv.addObject("map", map);
mv.addObject("pageAndSearch", pas);
mv.setViewName(JSP_PAGE+"list");
return mv;
}
View
<!-- Start Blog -->
<section id="aa-blog">
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="aa-blog-area">
<div class="row">
<div class="col-md-8">
<div class="aa-blog-content">
<div class="row">
<div class="box">
<div class="box-header">
<h3 class="box-title" style="margin-bottom: 10px;">Free 게시판</h3>
<div class="box-tools">
<form method="get" action="/board/listAll.do" name="serarchForm">
<div class="row" style="margin-top: 20px; margin-bottom: 20px;">
<div class="col-md-4">
<select name="search_option" class="form-control" >
<option value="" <c:if test="${ pageAndSearch.search_option =='' }">selected="selected"</c:if>>----</option>
<option value="writer" <c:if test="${ pageAndSearch.search_option =='writer' }">selected="selected"</c:if>>아이디</option>
<option value="username" <c:if test="${ pageAndSearch.search_option =='username' }">selected="selected"</c:if>>이름</option>
<option value="content" <c:if test="${ pageAndSearch.search_option =='content' }">selected="selected"</c:if>>내용</option>
<option value="title" <c:if test="${ pageAndSearch.search_option =='title' }">selected="selected"</c:if>>제목</option>
<option value="all" <c:if test="${ pageAndSearch.search_option =='all' }">selected="selected"</c:if>>이름+내용+제목</option>
</select>
</div>
<div class="col-md-4">
<input type="text" class="form-control" name="keyword" value="${pageAndSearch.keyword }">
</div>
<div class="col-md-4">
<button type="submit" class="btn btn-warning">검색</button>
</div>
</div>
</form>
<c:if test="${ not empty loginUser}" >
<p class="input-group input-group-sm" style="margin-top :10px;"><button id="btnWrite" class="btn btn-primary" >글쓰기</button></p>
</c:if>
</div>
<p>
<span class="label label-danger"> ${map.countList }</span> 개의 게시물이 있습니다.
</p>
<p>
</div>
<!-- /.box-header -->
<div class="box-body table-responsive no-padding">
<table class="table table-hover">
<tbody><tr>
<th>번호</th>
<th>제목</th>
<th>글쓴이</th>
<th>등록일</th>
<th>조회수</th>
</tr>
<c:forEach items="${map.list }" var="row">
<tr>
<td>${row.bno }</td>
<td class="rowTitle"> <a href="/board/view.do${pageAndSearch.searchQuery(param.curPage) }&bno=${row.bno }">${row.title }</a></td>
<td>${ row.writer }</td>
<td><span class="label label-success">
<fmt:formatDate value="${ row.regdate }" pattern="yyyy-MM-dd hh:mm"/></span>
</td>
<td> <span class="badge bg-red">${ row.viewcnt }</span></td>
</tr>
</c:forEach>
</tbody></table>
</div>
<!-- /.box-body -->
</div>
</div>
<div class="row">
<div class="col-md-12">
<div class="aa-properties-content-bottom">
<nav>
<ul class="pagination">
<c:if test="${map.pager.curBlock > 1 }">
<li>
<a aria-label="Previous" href="javascript:list('${pageAndSearch.searchQuery(1) }')">
<span aria-hidden="true">시작</span>
</a>
</li>
</c:if>
<c:if test="${map.pager.curBlock > 1 }">
<li>
<a aria-label="Previous" href="javascript:list('${pageAndSearch.searchQuery(map.pager.prevPage) }')">
<span aria-hidden="true">«</span>
</a>
</li>
</c:if>
<c:forEach begin="${ map.pager.blockBegin }" end="${ map.pager.blockEnd }" var="page">
<c:choose>
<c:when test="${ page ==map.pager.curPage }">
<li class="active">
<a href="javascript:list('${pageAndSearch.searchQuery(page) }')" >${page}</a>
</li>
</c:when>
<c:otherwise>
<li >
<a href="javascript:list('${pageAndSearch.searchQuery(page) }')" >${page}</a>
</li>
</c:otherwise>
</c:choose>
</c:forEach>
<c:if test="${ map.pager.curBlock < map.pager.totBlock }">
<li>
<a aria-label="Next" href="javascript:list('${ pageAndSearch.searchQuery(map.pager.nextPage) }')">
<span aria-hidden="true">»</span>
</a>
</li>
</c:if>
<c:if test="${map.pager.curPage < map.pager.totPage }">
<li>
<a aria-label="Next" href="javascript:list('${ pageAndSearch.searchQuery(map.pager.totPage) }')">
<span aria-hidden="true">끝</span>
</a>
</li>
</c:if>
</ul>
</nav>
</div>
</div>
</div>
</div>
</div>
<!-- Start blog sidebar -->
<script>
$(document).ready(function(){
$("#btnWrite").click(function(){
location.href="/board/write.do";
});
});
function list(page){
location.href='/board/listAll.do'+page;
}
</script>
댓글 ( 4)
댓글 남기기