#09. [Side Project 1] JSP 로그인, 게시판 만들기 (9) :: ★제일 중요한 페이징(Paging)

페이징 번호 매기기

검색시 페이징 처리

■ BbsDao.java

public List<BbsDto> getBbsPagingList(String choice, String searchWord, int page) {
        
        /*
            1. row 번호
            2. 검색
            3. 정렬
            4. 범위 1 ~ 10
        */
        
        String sql = " SELECT SEQ, ID, REF, STEP, DEPTH, "
                + " TITLE, CONTENT, WDATE, "
                + " DEL, READCOUNT "
                + " FROM ";
        
        sql += "(SELECT ROW_NUMBER()OVER(ORDER BY REF DESC, STEP ASC) AS RNUM, " + 
                "    SEQ, ID, REF, STEP, DEPTH, TITLE, CONTENT, WDATE, DEL, READCOUNT " + 
                " FROM BBS ";
        
        String sqlWord = "";
        if(choice.equals("title")) {
            sqlWord = " WHERE TITLE LIKE '%" + searchWord.trim() + "%' AND DEL=0 ";
        }else if(choice.equals("writer")) {
            sqlWord = " WHERE ID='" + searchWord.trim() + "'"; 
        }else if(choice.equals("content")) {
            sqlWord = " WHERE CONTENT LIKE '%" + searchWord.trim() + "%' ";
        } 
        sql = sql + sqlWord;
        
        sql += " ORDER BY REF DESC, STEP ASC) ";
        sql += " WHERE RNUM >= ? AND RNUM <= ? ";
        
        int start, end;
        start = 1 + 10 * page;    // 시작 글의 번호
        end = 10 + 10 * page;    // 끝 글의 번호
        
        /*
        String sql = " SELECT SEQ, ID, REF, STEP, DEPTH, "
                        + " TITLE, CONTENT, WDATE, "
                        + " DEL, READCOUNT "
                        + " FROM BBS ";                    
                        
        String sqlWord = "";
        if(choice.equals("title")) {
            sqlWord = " WHERE TITLE LIKE '%" + searchWord.trim() + "%' AND DEL=0 ";
        }else if(choice.equals("writer")) {
            sqlWord = " WHERE ID='" + searchWord.trim() + "'"; 
        }else if(choice.equals("content")) {
            sqlWord = " WHERE CONTENT LIKE '%" + searchWord.trim() + "%' ";
        } 
        sql = sql + sqlWord;
                
        sql += " ORDER BY REF DESC, STEP ASC ";
        */
        
        
        
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        
        List<BbsDto> list = new ArrayList<BbsDto>();
        
        try {
            conn = DBConnection.getConnection();
            System.out.println("1/6 getBbsList success");
            
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, start);
            psmt.setInt(2, end);
            System.out.println("2/6 getBbsList success");
            
            rs = psmt.executeQuery();
            System.out.println("3/6 getBbsList success");
            
            while(rs.next()) {
                int i = 1;
                BbsDto dto = new BbsDto(rs.getInt(i++), 
                                        rs.getString(i++), 
                                        rs.getInt(i++), 
                                        rs.getInt(i++), 
                                        rs.getInt(i++), 
                                        rs.getString(i++), 
                                        rs.getString(i++), 
                                        rs.getString(i++), 
                                        rs.getInt(i++), 
                                        rs.getInt(i++));                
                list.add(dto);
            }
            System.out.println("4/6 getBbsList success");
            
        } catch (Exception e) {            
            e.printStackTrace();
        } finally {
            DBClose.close(psmt, conn, rs);            
        }
        return list;
    }
    
    

 

■ bbslist.jsp

<%
BbsDao dao = BbsDao.getInstance();

String spageNumber = request.getParameter("pageNumber");
int pageNumber = 0; // 현재 페이지
if(spageNumber != null && !spageNumber.equals("")){
	pageNumber = Integer.parseInt(spageNumber);
}
System.out.println("pageNumber:" + pageNumber);

// List<BbsDto> list = dao.getBbsList();
//List<BbsDto> list = dao.getBbsList(choice, searchWord);
List<BbsDto> list = dao.getBbsPagingList(choice, searchWord, pageNumber);

// int len = dao.getAllBbs();
int len = dao.getAllBbs(choice, searchWord);
System.out.println("총 글의 갯수:" + len);

int bbsPage = len / 10;		// 예: 12개 -> 2page
if(len % 10 > 0){
	bbsPage = bbsPage + 1;	// -> 2
}



%>    

....(생략)

<%
for(int i = 0;i < bbsPage; i++){
	if(pageNumber == i){	// 1 [2] [3] 
		%>
		<span style="font-size: 15pt; color: #0000ff; font-weight: bold;">
			<%=i+1 %>
		</span>&nbsp;
		<%
	}
	else{	// 그외 페이지
		%>
		<a href="#none" title="<%=i+1 %>페이지" onclick="goPage(<%=i %>)" 
			style="font-size: 15pt; color: #000; font-weight:bold;  text-decoration: none">
			[<%=i+1 %>]
		</a>&nbsp;		
		<%
	}	
}
%>

<script>
function goPage( pageNum ) {	
	
	var choice = document.getElementById("choice").value;
	var word = document.getElementById("search").value;
	
//	location.href = "bbslist.jsp?pageNumber=" + pageNum;
	location.href = "bbslist.jsp?searchWord=" + word + "&choice=" + choice + "&pageNumber=" + pageNum;
}
</script>