게시판 - webstudy-board - 게시판 글목록

1. DB 수정

1) board 테이블에 deleteBoard 컬럼 추가

-- 컬럼 추가 - deleteBoard는 Not null, default는 N(삭제 시 Y로 바꾼다) alter table board add deleteBoard varchar(4) not null default 'N'

2) row number select 테스트

select row_number() over(order by boardNo asc) as rowNum,boardTitle,boardContent,clientId, boardDate, boardCount from board where deleteBoard='N' order by rowNum desc;

 

2. Template

1) WebContent - view - template - sidebar.jsp 수정

<a href="${pageContext.request.contextPath}/DispatcherServlet?command=boardList">게시판 보기</a>

 

3. View

1) WebContent - view - board - boardList.jsp  추가

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <div class="cardRight "> <a href="#">글쓰기</a> </div> <div class="card"> <table> <tr> <th>번호</th> <th>제목</th> <th>작성자ID</th> <th>작성일자</th> <th>조회수</th> </tr> <c:forEach var="boardDTO" items="${requestScope.boardList}"> <tr> <td>${boardDTO.boardRowNo}</td> <td><a href="#">${boardDTO.boardTitle}</a></td> <td>${boardDTO.clientId}</td> <td>${boardDTO.boardDate}</td> <td>${boardDTO.boardCount}</td> </tr> </c:forEach> </table> </div>

 

4. Model

1) src - model.BoardDTO.java 수정

package model; public class BoardDTO { private int boardRowNo; private int boardNo; private String boardTitle; private String boardContent; private String clientId; private String boardDate; private int boardCount; public BoardDTO() { super(); } public BoardDTO(int boardNo, String boardTitle, String boardContent, String clientId, String boardDate, int boardCount) { super(); this.boardNo = boardNo; this.boardTitle = boardTitle; this.boardContent = boardContent; this.clientId = clientId; this.boardDate = boardDate; this.boardCount = boardCount; } public BoardDTO(int boardRowNo, int boardNo, String boardTitle, String boardContent, String clientId, String boardDate, int boardCount) { super(); this.boardRowNo = boardRowNo; this.boardNo = boardNo; this.boardTitle = boardTitle; this.boardContent = boardContent; this.clientId = clientId; this.boardDate = boardDate; this.boardCount = boardCount; } public int getBoardRowNo() { return boardRowNo; } public void setBoardRowNo(int boardRowNo) { this.boardRowNo = boardRowNo; } public int getBoardNo() { return boardNo; } public void setBoardNo(int boardNo) { this.boardNo = boardNo; } public String getBoardTitle() { return boardTitle; } public void setBoardTitle(String boardTitle) { this.boardTitle = boardTitle; } public String getBoardContent() { return boardContent; } public void setBoardContent(String boardContent) { this.boardContent = boardContent; } public String getClientId() { return clientId; } public void setClientId(String clientId) { this.clientId = clientId; } public String getBoardDate() { return boardDate; } public void setBoardDate(String boardDate) { this.boardDate = boardDate; } public int getBoardCount() { return boardCount; } public void setBoardCount(int boardCount) { this.boardCount = boardCount; } @Override public String toString() { StringBuilder builder = new StringBuilder(); builder.append("BoardDTO [boardRowNo="); builder.append(boardRowNo); builder.append(", boardNo="); builder.append(boardNo); builder.append(", boardTitle="); builder.append(boardTitle); builder.append(", boardContent="); builder.append(boardContent); builder.append(", clientId="); builder.append(clientId); builder.append(", boardDate="); builder.append(boardDate); builder.append(", boardCount="); builder.append(boardCount); builder.append("]"); return builder.toString(); } }

2) src - model.BoardDAO.java에 getBoardList 메소드 추가

//1. board 전체 리스트 가져오기 public ArrayList<BoardDTO> getBoardList() throws SQLException { Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; ArrayList<BoardDTO> boardList= new ArrayList<BoardDTO>(); try { con=getConnection(); StringBuilder sql = new StringBuilder(); sql.append("select row_number() over(order by boardNo asc) as rowNum,boardNo,boardTitle,boardContent,clientId, boardDate,boardCount "); sql.append("from board "); sql.append("where deleteBoard='N' "); sql.append("order by rowNum desc"); pstmt=con.prepareStatement(sql.toString()); rs=pstmt.executeQuery(); while(rs.next()) { BoardDTO boardDTO=new BoardDTO(rs.getInt(1),rs.getInt(2),rs.getString(3),null,rs.getString(5),rs.getString(6),rs.getInt(7)); boardList.add(boardDTO); } }finally { closeAll(rs, pstmt, con); } return boardList; }

3) src - test.TestGetBoardList 추가

package test; import java.util.ArrayList; import model.BoardDAO; import model.BoardDTO; public class TestGetBoardList { public static void main(String[] args) { try { ArrayList<BoardDTO> boardList=BoardDAO.getInstance().getBoardList(); System.out.println(boardList); } catch (Exception e) { e.printStackTrace(); } } }

 

5. Controller

1) src - controller.HandlerMapping.java에 boardList추가

else if(command.equals("boardList")) // 글목록 페이지 이동 c=new BoardListController();

2) src - controller.BoardListController.java 추가

package controller; import java.util.ArrayList; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import model.BoardDAO; import model.BoardDTO; public class BoardListController implements Controller { @Override public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception { HttpSession session = request.getSession(false); if(session==null||session.getAttribute("clientDTO")==null){ return "redirect:index.jsp"; } ArrayList<BoardDTO> boardList=BoardDAO.getInstance().getBoardList(); request.setAttribute("boardList", boardList); request.setAttribute("url", "/view/board/boardList.jsp"); return "/view/template/home.jsp"; } }

 

[참고]

row_number() 사용 방법 https://www.fromdual.com/mariadb-10-2-window-function-examples

auto_increment 사용 방법 https://mariadb.com/kb/en/library/documentation/columns-storage-engines-and-plugins/data-types/auto_increment/

mariadb 테이블 수정 https://knoow.tistory.com/90

mariadb 테이블 수정2 http://blog.naver.com/PostView.nhn?blogId=imf4&logNo=220779978513&parentCategoryNo=&categoryNo=26&viewDate=&isShowPopularPosts=false&from=postView

mariadb 테이블 수정3 http://tcpschool.com/mysql/mysql_constraint_default