[JSP] 2022.10.13 (dao.boardCount(boardid) : board 테이블의 boardid값에 해당하는 게시물 건수 리턴)

문제 : dao.boardCount(boardid) : board 테이블의 boardid값에 해당하는 게시물 건수 리턴  

 

📌 boardController.java

@RequestMapping("list")
	public String list (HttpServletRequest request, HttpServletResponse response) { 
		try {
			request.setCharacterEncoding("UTF-8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		HttpSession session = request.getSession();
		if(request.getParameter("boardid") != null) {
			session.setAttribute("boardid",request.getParameter("boardid"));
			session.setAttribute("pageNum","1");
		}
		String boardid = (String)session.getAttribute("boardid");
		if(boardid == null) boardid = "1";
		int pageInt = 1;
		int limit = 10;
		if(request.getParameter("pageNum") != null) {
			session.setAttribute("pageNum",request.getParameter("pageNum"));
		}
		String pageNum = (String)session.getAttribute("pageNum");
		if(pageNum == null) {
			pageNum = "1";
		}
		pageInt = Integer.parseInt(pageNum);
/*
  문제 
 dao.boardCount(boardid) : board 테이블의 boardid값에 해당하는 게시물 건수 리턴
*/		
//		int boardcount = dao.boardCount(boardid);
//		List<Board> list = dao.list(pageInt, limit, boardid); 
		int boardcount = 0;
		List<Board> list = null;
		int boardnum = boardcount - (pageInt - 1) * limit;
		int bottomLine = 3;
		int startPage = (pageInt - 1) / bottomLine * bottomLine + 1;
		int endPage = startPage + bottomLine - 1;
		int maxPage = (boardcount / limit) + (boardcount % limit == 0 ? 0 : 1);
		if(endPage > maxPage) endPage = maxPage;
		String boardName = "공지사항";
		switch(boardid) {
		case "3": 
			boardName = "QNA"; break;
		case "2":
			boardName = "자유게시판"; break;
		}
		request.setAttribute("boardName",boardName);
		request.setAttribute("pageInt",pageInt);
		request.setAttribute("boardid",boardid);
		request.setAttribute("boardcount",boardcount);
		request.setAttribute("list",list);
		request.setAttribute("boardnum",boardnum);
		request.setAttribute("startPage",startPage);
		request.setAttribute("bottomLine",bottomLine);
		request.setAttribute("endPage",endPage);
		request.setAttribute("maxPage",maxPage);
		return "/view/board/list.jsp";
	}
}

---

📌 BoardDao.java 추가

public int boardCount(String boardid) {
		Connection conn = DBConnection.getConnection();
		PreparedStatement pstmt = null;
		String sql = "select count(*) from board where boardid = ?";
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			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, String boardid) {
		Connection conn = DBConnection.getConnection();
		PreparedStatement pstmt = null;
		String sql = "select * from (" + "select rownum rnum, a.* from (" +
		             "select * from board where boardid = ?) a " + 
				     ")  where rnum BETWEEN ? and ? ";
		ResultSet rs = null;
		List<Board> list = new ArrayList<>();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,boardid);
			pstmt.setInt(2,(pageInt-1)*limit + 1);
			pstmt.setInt(3,pageInt*limit);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Board b = new Board();
				b.setNum(rs.getInt("num"));
				b.setWriter(rs.getString("writer"));
				b.setPass(rs.getString("pass"));
				b.setSubject(rs.getString("subject"));
				b.setContent(rs.getString("content"));
				b.setFile1(rs.getString("file1"));
				b.setGrp(rs.getInt("grp"));
				b.setGrpstep(rs.getInt("grpstep"));
				b.setGrplevel(rs.getInt("grplevel"));
				b.setReadcnt(rs.getInt("readcnt"));
				b.setRegdate(rs.getDate("regdate"));
				list.add(b);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(conn,pstmt,rs);
		}
		return null;
	}