오라클, MySQL 페이징 쿼리문 + iBATIS

 

오라클 ( rownum 지원 )

 

MySQL ( MySQL 8버전 이상 row_number() 지원 )

		select b.* from (
        select row_number() over (order by unq desc) as rn, a.* from (
        
		 select	unq ,title, name, hits, DATE_FORMAT(rdate, '%Y-%m-%d')as rdate
		 from nboard

		 order by unq desc ) as a ) as b
         
         where rn >=11 and rn <= 20;

 

 

 

<, > 사용 할 수 없기 때문에 밑에 처럼 의미 부여를 위해 CDATA 사용해야됨

	<select id="boardDAO.selectNBoardList" resultClass="egovMap">
	

				select b.* from (
        select row_number() over (order by unq desc) as rn, a.* from (
        
		 select	unq ,title, name, hits, DATE_FORMAT(rdate, '%Y-%m-%d')as rdate
		 from nboard

		 order by unq desc ) as a ) as b
        <![CDATA[
         where rn >=11 and rn <= 20
         
         ]]>
         
	</select>

 

 

 

게시글 갯수를 알아야 되기 때문에 count(*) 필요

	<select id="boardDAO.selectNBoardTotal" resultClass="java.lang.Integer">
		select count(*) total from nboard
	</select>

 

 

VO에 get, set 처리 

	private int viewPage = 1;
	private int startIndex = 1;
	private int endIndex = 10;

 

 

Controller 코드 안에 주석 확인 ( 계산식 중요 ) 

	@RequestMapping("/boardList.do")
	public String selectNBoardList(BoardVO vo, ModelMap model) throws Exception{
		
		int total = boardService.selectNBoardTotal(vo);
		
		// (double)12/10 -> ceil(1.2) -> Integer(2.0) -> 2
		// ceil => 1.2, 1.1 이던 전부 2.0으로 변환
		int totalPage = (int) Math.ceil((double)total / 10);
		
		int viewPage = vo.getViewPage();
		// 1-> 1,10 // 2-> 11,20 // 3-> 21,30
		
        // 정상적이지 않는 경로로 들어왔을 때 viewpag = 1 설정
		if(viewPage > totalPage || viewPage < 1) {
			
			viewPage = 1;
			
		}
        
		int startIndex = (viewPage-1)* 10 + 1 ;
		int endIndex = startIndex + (10-1);
		// startIndex : (1-1)*10 + 1 -> 1
		// startIndex : (2-1)*10 + 1 -> 11
		
		// vo에 담기
		vo.setStartIndex(startIndex);
		vo.setEndIndex(endIndex);
		
		List<?> list = boardService.selectNBoardList(vo);
		
		System.out.println("list = " + list);
		
		model.addAttribute("resultList", list);
		
		model.addAttribute("total", total);
		model.addAttribute("totalPage", totalPage);
		
		return "board/boardList";
	}

 

 

SQL WHERE startIndex, endIndex 확인 

	<select id="boardDAO.selectNBoardList" resultClass="egovMap">
	
	
				select b.* from (
        select row_number() over (order by unq desc) as rn, a.* from (
        
		 select	unq ,title, name, hits, DATE_FORMAT(rdate, '%Y-%m-%d')as rdate
		 from nboard

		 order by unq desc ) as a ) as b
         
         <![CDATA[
         where rn >= #startIndex# and rn <= #endIndex#
         
         ]]>
         
	</select>

 

 

JSP 페이지 번호 처리

	<div style="width:600px; margin-top:5px; text-align:center;">
		
		<c:forEach var="i" begin="1" end="${totalPage }">
			
			<a href="boardList.do?viewPage=${i }"> ${i }</a> 
			
		</c:forEach>
		
	</div>