[JSP 게시판 만들기] 27.pager에서 마지막 번호 처리하기

===NoticeDetailController.java===

사용자가 요청할 때 전달했던 id값, date서비스를 받아서 사용자에게 돌려주기 위한 출력로직만 남긴다.
->사용자 입력과 출력만 관할하게 한다. 

service.getNotice(id) 메소드를 통해 id값에 해당하는 데이터를 notice 변수에 넣고
notice를 setAtrribute한다.

===detail.jsp===

그래서 notice를 n으로 바꿔준다

 

===DetailController.java===

package com.mybulletin.web.contoller;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mybulletin.web.entity.Notice;
import com.mybulletin.web.service.NoticeService;

@WebServlet("/detail")
public class DetailController extends HttpServlet {
	
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		int id = Integer.parseInt(request.getParameter("id")); 	
		
		NoticeService service = new NoticeService();
		Notice notice = service.getNotice(id);
	
		request.setAttribute("n", notice);
		request.getRequestDispatcher("WEB-INF/view/notice/detail.jsp").forward(request, response);
	
	
	}	
}

 

===ListController.java===

package com.mybulletin.web.contoller;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mybulletin.web.entity.Notice;
import com.mybulletin.web.service.NoticeService;
@WebServlet("/list")
public class ListContoller extends HttpServlet{
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		//list?f=title&q=a
		String field_ = request.getParameter("f");
        String query_ = request.getParameter("q");
        String page_ = request.getParameter("p");
        
        String field = "title";
        if(field_ != null && !field_.equals(""))
            field = field_;
        
        String query = "";
        if(query_ != null && !query_.equals(""))
            query = query_;        
        
        int page = 1;
        if(page_ !=null && !page_.equals(""))
            page = Integer.parseInt(page_);
        
        NoticeService service = new NoticeService(); 
        List<Notice> list = service.getNoticeList(field, query, page);
        int count = service.getNoticeCount(field, query);
        System.out.println(count);
        
        request.setAttribute("list", list);
        request.setAttribute("count", count);
        request.getRequestDispatcher("WEB-INF/view/notice/list.jsp").forward(request, response);            
        }        
    }

 

===NoticeService.java===

package com.mybulletin.web.service;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mybulletin.web.entity.Notice;

public class NoticeService {
	public List<Notice> getNoticeList(){
	
		return getNoticeList("title", "", 1);	
	}
	public List<Notice> getNoticeList(int page){
		
		return getNoticeList("title", "", page);	
	}
	public List<Notice> getNoticeList(String field, String query, int page){
		
		List<Notice> list = new ArrayList<>();
		
		String sql = "SELECT * FROM ( " + 
				"    SELECT ROWNUM NUM, NOTICE.* " + 
				"    FROM NOTICE  WHERE "+field+" LIKE ? ORDER BY REGDATE DESC " + 
				"    ) " + 
				"WHERE NUM BETWEEN ? AND ? " ;
		
		// 1, 11, 21, 31...-> an = 1+ (page -1) *10
		//10,20, 30,..... -> page* 10		

		String url = "jdbc:oracle:thin:@localhost:1521/myoracle";			
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, "ora_user2", "0000");		
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+query+"%");
			pstmt.setInt(2, 1+(page-1)*10);
			pstmt.setInt(3, page*10);
			rs = pstmt.executeQuery();
			
			while(rs.next()){ 	
				
			int id = rs.getInt("ID");
			String title = rs.getString("TITLE");		
			String writerId = rs.getString("WRITER_ID"); 
			Date regdate = rs.getDate("REGDATE"); 	
			String hit = rs.getString("HIT"); 
			String files = rs.getString("FILES");
			String content = rs.getString("Content");
			
			Notice notice = new Notice(id, title, writerId, regdate, hit, files, content);	
			
				list.add(notice);
			}				
			
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch(SQLException e) {
				e.printStackTrace();
			}
			finally {
				try {
					if(rs !=null) rs.close();
					if(pstmt !=null) pstmt.close();
				    if(conn !=null) conn.close();
				}catch (Exception e) {
					e.printStackTrace();
				}			
			}					
			return list;	
	}

	
	public int getNoticeCount() { 
		return 0;
	}

	 public int getNoticeCount(String field, String query) {  
		 
		 int count = 0;
			
		  String sql = "SELECT COUNT(ID) COUNT FROM (" + 
					"    SELECT ROWNUM NUM, NOTICE.* " + 
					"    FROM NOTICE  WHERE "+field+" LIKE ? ORDER BY REGDATE DESC " + 				
					"    ) " ;
			
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs= null;
			String url = "jdbc:oracle:thin:@localhost:1521/myoracle";	
			
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver"); 
				conn = DriverManager.getConnection(url, "ora_user2", "0000");		
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+query+"%");
				
				rs = pstmt.executeQuery();
				
				if(rs.next())
				count = rs.getInt("count");
							
				
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				} catch(SQLException e) {
					e.printStackTrace();
				}
				finally {
					try {
						if(rs !=null) rs.close();
						if(pstmt !=null) pstmt.close();
					    if(conn !=null) conn.close();
					}catch (Exception e) {
						e.printStackTrace();
					}			
				}			
		return count;
	}

	 
	public Notice getNotice(int id) {
		Notice notice = null;
		
		String sql = "SELECT * FROM NOTICE WHERE ID=?";		
		
		String url = "jdbc:oracle:thin:@localhost:1521/myoracle";			
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, "ora_user2", "0000");		
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){ 	
				
			int nid = rs.getInt("ID");
			String title = rs.getString("TITLE");		
			String writerId = rs.getString("WRITER_ID"); 
			Date regdate = rs.getDate("REGDATE"); 	
			String hit = rs.getString("HIT"); 
			String files = rs.getString("FILES");
			String content = rs.getString("Content");
			
			notice = new Notice(nid, title, writerId, regdate, hit, files, content);								
				}				
			
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch(SQLException e) {
				e.printStackTrace();
			}
			finally {
				try {
					if(rs !=null) rs.close();
					if(pstmt !=null) pstmt.close();
				    if(conn !=null) conn.close();
				}catch (Exception e) {
					e.printStackTrace();
				}			
			}			
		
		return notice;
	}
	
	public Notice getNextNotice(int id) {
		Notice notice = null;
		String sql = "SELECT * FROM NOTICE " + 
				"    WHERE ID = (" + 
				"    SELECT ID FROM NOTICE " + 
				"    WHERE REGDATE >(SELECT REGDATE FROM NOTICE WHERE ID =?) " + 
				"    AND ROWNUM =1 " +  
				" )";
		
		String url = "jdbc:oracle:thin:@localhost:1521/myoracle";			
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, "ora_user2", "0000");		
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){ 	
				
			int nid = rs.getInt("ID");
			String title = rs.getString("TITLE");		
			String writerId = rs.getString("WRITER_ID"); 
			Date regdate = rs.getDate("REGDATE"); 	
			String hit = rs.getString("HIT"); 
			String files = rs.getString("FILES");
			String content = rs.getString("Content");
			
			notice = new Notice(nid, title, writerId, regdate, hit, files, content);								
				}				
			
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch(SQLException e) {
				e.printStackTrace();
			}
			finally {
				try {
					if(rs !=null) rs.close();
					if(pstmt !=null) pstmt.close();
				    if(conn !=null) conn.close();
				}catch (Exception e) {
					e.printStackTrace();
				}			
			}			
		return notice;
	}
	public Notice getPrevNotice(int id) {
		Notice notice = null;
		String sql = "SELECT ID FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC)" + 
					"WHERE REGDATE < (SELECT REGDATE FROM NOTICE WHERE ID =?)" + 
					"AND ROWNUM = 1";
		
		String url = "jdbc:oracle:thin:@localhost:1521/myoracle";			
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, "ora_user2", "0000");		
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){ 	
				
			int nid = rs.getInt("ID");
			String title = rs.getString("TITLE");		
			String writerId = rs.getString("WRITER_ID"); 
			Date regdate = rs.getDate("REGDATE"); 	
			String hit = rs.getString("HIT"); 
			String files = rs.getString("FILES");
			String content = rs.getString("Content");
			
			notice = new Notice(nid, title, writerId, regdate, hit, files, content);								
				}				
			
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch(SQLException e) {
				e.printStackTrace();
			}
			finally {
				try {
					if(rs !=null) rs.close();
					if(pstmt !=null) pstmt.close();
				    if(conn !=null) conn.close();
				}catch (Exception e) {
					e.printStackTrace();
				}			
			}			
		return notice;
	}
}

 

===detail.jsp===

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ taglib prefix = "c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@taglib prefix = "fmt" uri = "http://java.sun.com/jsp/jstl/fmt" %>
<%@taglib prefix = "fn" uri = "http://java.sun.com/jsp/jstl/functions" %>

<!DOCTYPE html>
<html>
	<head>
	  	<meta charset="UTF-8">
	    <title>게시글</title>	 
	</head>  
    <body>		
	    <div>
	    	<table>
				<tbody>
					<tr>				
						<th>제목</th>
						<td colspan="3">${n.title}</td>
					</tr>
					<tr>
						<th>작성일</th>
						<td>
							<fmt:formatDate pattern = "yyyy/MM/dd hh:mm:ss" value="${n.regdate}"/>
						</td>
					</tr>
					<tr>
						<th>작성자</th>
						<td>${n.writerId}</td>
						<th>조회수</th>
						<td><fmt:formatNumber value = "${n.hit}" /></td>
					</tr>
					<tr>
						<th>첨부파일</th>
						<td colspan="3">
							<c:forTokens var= "fileName" items="${n.files}" delims="," varStatus="st">
								<c:set var = "style" value = ""/>
								<c:if test="${fn:endsWith(fileName , '.zip')}">
									<c:set var = "style" value = "font-weight: bold; color:red;" />
								</c:if>							
								<a href = "${fileName}" style = "${style}" >${fn:toUpperCase(fileName)}</a>
								<c:if test="${!st.last}">
								/
								</c:if>
							</c:forTokens>
						</td>
					</tr>
					<tr class="content">
						<td colspan="4">${n.content}</td>
					</tr>
				</tbody>
			</table>	
	    </div>	
		<div>
			<a href="list">목록</a>
		</div>
				
    </body>    
</html>

===list.jsp===

<%@page import="com.mybulletin.web.entity.Notice"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%> 
<%@taglib prefix = "c" uri ="http://java.sun.com/jsp/jstl/core" %>
<%@taglib prefix = "fmt" uri = "http://java.sun.com/jsp/jstl/fmt" %>
<%@taglib prefix = "fn" uri = "http://java.sun.com/jsp/jstl/functions" %>
<!DOCTYPE html>
<html>
    <head>
    <meta charset="UTF-8">
    <title>JSP게시판만들기</title>
    <link href="css/list.css" type="text/css" rel="stylesheet" />
    
    </head>
    <body>    
        <div >
            <h3>글 검색 폼</h3>
            <form>
                <fieldset>
                    <legend>글 검색 필드</legend>                    
                    <label>검색분류</label>
                        <select name = "f">
                            <option ${(param.f == "title")? "selected" : ""} value = "title">제목</option>
                            <option ${(param.f == "writer_id")? "selected" : ""} value = "writer_id">작성자</option>
                        </select>
                    <label>검색어</label>
                        <input type = "text" name = "q" value = "${param.q}"/>
                        <input type = "submit" value = "검색">                
                </fieldset>        
            </form>    
        </div>
        
        <div>
            <h3 >글 목록</h3>
            <table >
                <thead>                
                    <tr>
                        <th>번호</th>
                        <th>제목</th>
                        <th>작성자</th>
                        <th>작성일</th>
                        <th >조회수</th>
                    </tr>
                </thead>                            
                <tbody>    
                
                <c:forEach var = "n" items = "${list}">
                    <tr>
                        <td>${n.id}</td>
                        <td><a href="detail?id=${n.id} ">${n.title}</a></td>
                        <td>${n.writerId}</td>
                        <td>
                            <fmt:formatDate pattern = "yyyy/MM/dd" value="${n.regdate}"/>
                        </td>
                        <td><fmt:formatNumber value = "${n.hit}" /></td>
                    </tr>                
                </c:forEach>
                
                <%-- <%}%> --%>                                        
                </tbody>
            </table>
        </div>
        
            <c:set var = "page" value = "${(param.p==null)? 1: param.p}"/>
            <c:set var ="startNum" value = "${page-(page-1)%5}"/>    
            <c:set var ="lastNum" value = "${ fn:substringBefore(Math.ceil(count/10), '.')  }"/>            
 
        <div >
            <!--현재 페이지 -->
            <div><span>${(empty param.p)? 1 : param.p}</span> / ${lastNum} pages</div>
        </div>        
                
        <div>
                    
            <c:if test="${startNum > 1}">
                <a href="?p=${startNum-1}&t=&q=" >이전</a>                
            </c:if>
            <c:if test="${startNum <= 1}">
                <span onclick="alert('이전 페이지가 없습니다.');">이전</span>            
            </c:if>
                            
            <span>
                <c:forEach var = "i" begin= "0" end = "4">
                    <c:if test="${(startNum+i) <= lastNum }">
                        <a class = "text-${(page==(startNum+i))? 'orange': '' } text-bold" href="?p=${startNum+i}&f=${param.f}&q=${param.q}" >${startNum+i}</a>
                    </c:if>
                </c:forEach>
            </span>    
            
            <c:if test="${startNum+4 < lastNum}">
                <a href="?p=${startNum+5}&t=&q=" >다음</a>
            </c:if>
            <c:if test="${startNum+4 > lastNum}">
                <span onclick="alert('다음 페이지가 없습니다.');">다음</span>            
            </c:if>
        </div>
    
    </body>
</html>