day54 - JSP(공지사항 페이지)

[BoardController,java]

package controller;

import java.io.IOException;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.oreilly.servlet.MultipartRequest;

import dao.BoardDao;
import dao.MemberDao;
import model.Board;
import model.Member;

public class BoardController extends Action{
	public String hello(HttpServletRequest request, HttpServletResponse response) throws Exception {
		// TODO Auto-generated method stub
		request.setAttribute("hello", "hello 테스트 입니다.");
		
		return "/view/hello.jsp";
	}
	
	public String list(HttpServletRequest request, HttpServletResponse response) throws Throwable {
		/*
		 * 게시물 목록 보기 
		 * 1. pageNum 파라미터 존재. pageNum 파라미터 없으면 1로 설정. 
		 * 2. 10건의 게시물 출력. => db에서
		 *    해당 페이지에 출력되는 게시물만 조회. 순서 : 최근 게시물 순으로 
		 * 3. 화면에 출력.
		 * 4. boardid 파라메터가 없으면 session을 수정하지 않음 default는 1임
		 */

		// TODO Auto-generated method stub
		HttpSession session = request.getSession();
		
		//pageNum 이 넘어와야 pageNum이 바뀜
		if(request.getParameter("pageNum") != null) {
			session.setAttribute("page", request.getParameter("pageNum"));
		}
		
		//boardid가 넘어와야 세션이 변경됨 : 현재 게시판 분류에 따른 입력, 수정을 적용
		if(request.getParameter("boardid") != null) {
			session.setAttribute("boardid", request.getParameter("boardid"));
			session.setAttribute("pageNum", "1");
		}
		
		String pageNum = (String) session.getAttribute("pageNum");
		if(pageNum == null) pageNum = "1";
		int pageInt = Integer.parseInt(pageNum);
		
		String boardid = (String)session.getAttribute("boardid");
		if(boardid == null) boardid = "1";
		
		int limit = 3; // 한 페이지에 출력할 게시물 건수
		BoardDao dao = new BoardDao();
		int boardcount = dao.boardCount(boardid); //등록된 전체 게시물의 건수
		/*
		 * pageInt-현재 페이지 넘버, 
		 * limit-한페이지에 출력할 게시물 건수
		 * boardcount--등록된 전체 게시물의 건수
		 * boardid-공지사항(1),자유게시판(2), QnA(3)
		 */

		List<Board> list = dao.list(pageInt, limit, boardcount, boardid); // 화면에 출력된 게시물 데이터
		// 13 ---> boardcount/limit : 4 + 1
		int maxpage = (int)(boardcount/limit) + (boardcount % limit == 0 ? 0 : 1);
		int bottomLine = 3;
		//page 1,2,3 : 1, 4,5,6 : 2
		int startpage = 1 + (pageInt -1) * limit;
		int endpage = 3 + (pageInt -1) * limit;
		
		int boardnum = boardcount - (pageInt -1 )*limit ;
		String boardName = "";
		
		switch(boardid) {
			case "1": {
				boardName = "공지사항"; 
				break;
			}
			case "2": {
				boardName = "자유게시판"; 
				break;
			}
			case "3": {
				boardName = "QnA"; 
				break;
			}
			default:
				boardName="공지사항";
		}
		
		request.setAttribute("boardcount", boardcount);
		request.setAttribute("list", list);
		request.setAttribute("boardnum", boardnum);
		request.setAttribute("startpage", startpage);
		request.setAttribute("endpage", endpage);
		request.setAttribute("bottomLine", bottomLine);
		request.setAttribute("maxpage", maxpage);
		request.setAttribute("pageNum", pageNum);
		request.setAttribute("boardName", boardName);
		
		
		
		return "/view/board/list.jsp";
	}
	
	public String memberInput(HttpServletRequest request, HttpServletResponse response) throws Exception {
		// TODO Auto-generated method stub
		request.setAttribute("input", "member Input 테스트 입니다.");
		
		return "/view/memberInput.jsp";
	}
	
	public String writenForm(HttpServletRequest request, HttpServletResponse response) throws Exception {
		// TODO Auto-generated method stub

		return "/view/board/writenForm.jsp";
	}
	
	public String writePro(HttpServletRequest request, HttpServletResponse response) throws Exception {
		
		String uploadpath = request.getServletContext().getRealPath("/") + "upfile";
		int size = 10 * 1024 * 1024;
		MultipartRequest multi;
		
		//board session 저장 내용: defualt 1
		String boardid = (String)request.getSession().getAttribute("boardid");
		if(boardid == null) boardid="1";
		
		try {
			multi = new MultipartRequest(request, uploadpath, size, "utf-8");
			Board board = new Board();
			board.setName(multi.getParameter("name"));
			board.setSubject(multi.getParameter("subject"));
			board.setPass(multi.getParameter("pass"));
			board.setContent(multi.getParameter("content"));
			board.setFile1(multi.getParameter("file1"));
			board.setBoardid(boardid);
			if(board.getFile1() == null) board.setFile1("");
			
			BoardDao dao = new BoardDao();
			
			String msg = "게시물 등록 실패";
			String url = "board/writenForm";
			if(dao.insert(board)) {
				msg = "게시물 등록 성공";
				url = "board/list";
			}
			request.setAttribute("msg", msg);
			request.setAttribute("url", url);
			
		}catch(IOException e) {
			e.printStackTrace();
		}
		return "/view/alert.jsp";
		
	}
}

 

[BoardDao.java]

package dao;

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

import model.Board;
import util.DBConnection;

public class BoardDao {
	public boolean insert(Board board) {
		Connection conn = DBConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		System.out.println("insert:"+board);
		int num = 0;
		String sql1 = "select multiboardseq.nextval from dual";
		String sql2 = "insert into multiboard " + "(num, name, pass, subject, content, file1, regdate,"
				+ "readcnt, ref, reflevel, refstep, boardid)" + "  values  (?,?,?,?,?,?,sysdate,0,?,?,?,?)";
		//---------------------------
		int ref = 0, reflevel = 0, refstep = 0;
		//---------------------------
		try {
			pstmt = conn.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			if(rs.next()) num = rs.getInt(1);
			// ---------------- 답글인 경우
			if(board.getNum() > 0) {
				ref = board.getRef();
				reflevel = board.getReflevel() + 1;
				refstep = board.getRefstep() + 1;
			}else {
				
				ref = num;
			}
			// ---------------- 답글인 경우
			pstmt = conn.prepareStatement(sql2);
			pstmt.setInt(1, num);
			pstmt.setString(2, board.getName());
			pstmt.setString(3, board.getPass());
			pstmt.setString(4, board.getSubject());
			pstmt.setString(5, board.getContent());
			pstmt.setString(6, board.getFile1());
			pstmt.setInt(7, ref);
			pstmt.setInt(8, reflevel);
			pstmt.setInt(9, refstep);
			pstmt.setString(10, board.getBoardid());
			pstmt.executeUpdate();
			return true;
				
			}catch(SQLException e) {
				e.printStackTrace();
		}
		finally {
			DBConnection.close(conn, pstmt, null);
		}
		return false;
	}
	
	public int boardCount(String boardid) {
		Connection conn = DBConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("select count(*) count from multiboard where boardid  = ?");
			pstmt.setString(1, boardid);
			rs = pstmt.executeQuery();
			rs.next();
			return rs.getInt(1);
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBConnection.close(conn, pstmt, rs);
		}
		return 0;
	}
	
	public List<Board> list(int pageInt, int limit, int boardcount, String boardid){
		Connection conn = DBConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Board> list = new ArrayList<Board>();
		//----------------------
		int start = (pageInt - 1) * limit + 1;
		int end = start + limit -1;
		String sql = "select * from (select rownum rnum, a.* "
				+ " from (select * from multiboard where boardid = ? order by ref desc, refstep) a) "
				+ "where rnum between ? and ?";
		System.out.println(sql);
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, boardid);
			pstmt.setInt(2, start);
			pstmt.setInt(3, end);
			System.out.println(start + ":" + end);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				Board b = new Board();
				b.setNum(rs.getInt("num"));
				b.setName(rs.getString("name"));
				b.setPass(rs.getString("pass"));
				b.setSubject(rs.getString("subject"));
				b.setContent(rs.getString("content"));
				b.setFile1(rs.getString("file1"));
				b.setRef(rs.getInt("ref"));
				b.setReflevel(rs.getInt("reflevel"));
				b.setRefstep(rs.getInt("refstep"));
				b.setReadcnt(rs.getInt("readcnt"));
				b.setRegdate(rs.getTimestamp("regdate"));
				list.add(b);
				
				
			}
			return list;
			
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBConnection.close(conn, pstmt, rs);
		}
		return null;
	}

}