게시글 목록

Servlet/JSP 답변형 게시판 만들기 - 게시글 목록

개발 순서

1. VO
2. DAO 테스트
3. DAO
4. Service
5. Controller
6. View

com.coco.vo.BoardVO

테이블 이름과 동일하게 변수를 선언한다.

public class BoardVO {
    private int bno;
    private int p_bno, level;    //level은 답글의 개수다.
    private String title, content, imgName, id;
    private Date regdate;

    ...Getter Setter toString 생성자 생략...

level은 답글의 개수를 나타내는 칼럼이다.
실제로 존재하지 않는 컬럼이지만 쿼리로 직접 만들어서 쓴다.

com.test.dao.BoardDAOTest

게시물 목록을 불러오는 쿼리를 테스트한다.

public class BoardDAOTest {
    private final static Logger log = Logger.getGlobal();
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/servletex?serverTimezone=Asia/Seoul";
    private static final String USER = "servlet";
    private static final String PW = "1234";

    @Test
    public void getListTest() throws ClassNotFoundException {

        List<BoardVO> list = new ArrayList<>();

        Class.forName(DRIVER);

        String sql = "SELECT CASE WHEN LEVEL-1 > 0 then CONCAT(CONCAT(REPEAT('    ', level  - 1),''), t.title)\r\n" + 
                "                 ELSE t.title\r\n" + 
                "           END AS title\r\n" + 
                "     , t.bno\r\n" + 
                "     , t.p_bno\r\n" + 
                "     ,t.content\r\n" + 
                "     ,t.id\r\n" + 
                "     ,regdate\r\n" + 
                "     , fnc.level\r\n" + 
                "  FROM\r\n" + 
                "     (SELECT fnc_hierarchy() AS id, @level AS level\r\n" + 
                "        FROM (SELECT @start_with:=0, @id:=@start_with, @level:=0) vars\r\n" + 
                "          JOIN t_board\r\n" + 
                "         WHERE @id IS NOT NULL) fnc\r\n" + 
                "  JOIN t_board t ON fnc.id = t.bno";

        log.info(sql);

        try(    //try with resources
            Connection conn = DriverManager.getConnection(URL, USER, PW);
            PreparedStatement pstmt = conn.prepareCall(sql);
            ResultSet rs = pstmt.executeQuery();
            ) {

            log.info("conn : " + conn);
            log.info("pstmt : " + pstmt);

            assertNotNull(conn);

            while(rs.next()) {
                int level = rs.getInt("level");
                int bno = rs.getInt("bno");
                int p_bno = rs.getInt("p_bno");
                String title = rs.getString("title");
                String content = rs.getString("content");
                String id = rs.getString("id");
                Date regdate = rs.getDate("regdate");

                BoardVO vo = new BoardVO(bno, p_bno, level, title, content, id, regdate);
                list.add(vo);

                assertNotNull(vo);
                log.info("vo : " + vo);
            } // while

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

테스트 결과

BoardDAO

테스트 코드와 약간 다르다.
JNID사용

package com.coco.dao;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.coco.vo.BoardVO;

public class BoardDAO {
    private final static Logger log = Logger.getGlobal();
    DataSource ds;

    public BoardDAO() {
        try {
            Context ctx = new InitialContext();
            Context envCtx = (Context)ctx.lookup("java:/comp/env");
            ds = (DataSource)envCtx.lookup("jdbc/mysql");
        } catch (NamingException e) {
            e.printStackTrace();
        }
    } //BoardDAO()

    public List<BoardVO> getList() {
        List<BoardVO> list = new ArrayList<>();

        String sql = "SELECT CASE WHEN LEVEL-1 > 0 then CONCAT(CONCAT(REPEAT('    ', level  - 1),''), t.title)\r\n" + 
                "                 ELSE t.title\r\n" + 
                "           END AS title\r\n" + 
                "     , t.bno\r\n" + 
                "     , t.p_bno\r\n" + 
                "     ,t.content\r\n" + 
                "     ,t.id\r\n" + 
                "     ,regdate\r\n" + 
                "     , fnc.level\r\n" + 
                "  FROM\r\n" + 
                "     (SELECT fnc_hierarchy() AS id, @level AS level\r\n" + 
                "        FROM (SELECT @start_with:=0, @id:=@start_with, @level:=0) vars\r\n" + 
                "          JOIN t_board\r\n" + 
                "         WHERE @id IS NOT NULL) fnc\r\n" + 
                "  JOIN t_board t ON fnc.id = t.bno";

        log.info(sql);

        try(    //try with resources
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareCall(sql);
            ResultSet rs = pstmt.executeQuery();
            ) {

            while(rs.next()) {
                int level = rs.getInt("level");
                int bno = rs.getInt("bno");
                int p_bno = rs.getInt("p_bno");
                String title = rs.getString("title");
                String content = rs.getString("content");
                String id = rs.getString("id");
                Date regdate = rs.getDate("regdate");

                BoardVO vo = new BoardVO(bno, p_bno, level, title, content, id, regdate);
                list.add(vo);

            } // while
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    } //getList()
}

com.coco.BoardService

public interface BoardService {
    List<BoardVO> getList();
}

public class BoardServiceImpl implements BoardService {

    private BoardDAO dao;

    public BoardServiceImpl() {
        dao = new BoardDAO();
    }

    @Override
    public List<BoardVO> getList() {
        return dao.getList();
    }
}

com.coco.filter.SetFilter

모든 요청과 응답이 해당 필터를 거치도록 만들었다.
컨트롤러에서 직접 인코딩 해주지 않아도 된다.

@WebFilter("/*")
public class SetFilter implements Filter {
    public void init(FilterConfig fConfig) throws ServletException {
        System.out.println("set Encoding");
        System.out.println("set ContentType");

    }

    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        request.setCharacterEncoding("utf-8");
        chain.doFilter(request, response);
        response.setContentType("text/html;charset=utf-8");
    }

    public void destroy() {

    }
}

com.coco.controller.BoardController

//'/board' 경로로 들어오는 모든 요청은 이 컨트롤러를 거친다.
@WebServlet("/board")
public class BoardController extends HttpServlet {
    private final static Logger log = Logger.getGlobal();

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doHandle(request, response);
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doHandle(request, response);
    }

    private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //서비스 - 컨트롤러 연결
        BoardService boardService = new BoardServiceImpl();
        String nextPage = ""; //이동할 페이지
        String action = request.getPathInfo();    //어떤 url을 요청했는가?
        log.info("action : " + action);

        /* 클라이언트가 요청한 URL이 '/board' 또는 '/board/list'일 경우
         * service에서 게시글 목록을 불러오는 메서드를 바인딩 해서 화면으로 보낸다.
         */
        if(action == null || action == "/list") {
            request.setAttribute("boardList", boardService.getList());
            nextPage="list";
        }

        RequestDispatcher dispatcher = request.getRequestDispatcher("/WEB-INF/board/"+nextPage+".jsp");
        dispatcher.forward(request, response);
    }
}

/WEB-INF/board/list.jsp

<%@ 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" %>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>게시판</title>
<style>
.cls1 {
    text-decoration:none;
}

.cls2 {
    text-align:center;
    font-size:30px;
}
</style>
</head>
<body>
<h1 align='center'>자유 게시판</h1>
<table align='center' border='1' width='80%'>
    <tr height='10' align='center' bgcolor='lightgreen'>
        <td>번호</td>
        <td>제목</td>
        <td>작성자</td>
        <td>작성일</td>
    </tr>

    <c:choose>
        <c:when test='${boardList == null}'>
            <td colspan="4">
                <p align="center">
                    <b><span style='font-size=9pt;'>등록된 글이 없습니다.</span></b>
                </p>
            </td>
        </c:when>

        <c:when test="${boardList != null }">
            <c:forEach var="post" items="${boardList }" varStatus="postNum">
            <tr align="center">
                <td width="5%">${postNum.count }</td>
                <td width="10%">${post.id }</td>
                <td align='left' width="35%">
                <span style='padding-right:30px'></span>
            <c:choose>
                <c:when test="${post.level > 1 }">
                    <c:forEach begin="1" end="${post.level }">
                        <span style='padding-left:20px'></span>
                    </c:forEach>
                    <span style='font-size:12px;'>[답변]</span>
                    <a class="cls1" href='/board/post?no=${post.bno }'>${post.title }</a>
                </c:when>

                <c:otherwise>
                    <a class='cls1' href='/board/post?no=${post.bno }'>${post.title }</a>
                </c:otherwise>
            </c:choose>
            </td>
            <td width="10%">
                <fmt:formatDate value="${post.regdate }"/>
            </td>
            </tr>
            </c:forEach>
        </c:when>
    </c:choose>
</table>
<a class='cls1' href='#'><p class='cls2'>글쓰기</a>
</body>
</html>

출력하는 태그를 하나씩 뜯어보면

1. 게시글이 하나도 없을 경우 등록된 글이 없다고 출력한다.

   <c:choose>
   <c:when test='${boardList == null}'>
    <td colspan="4">
      <p align="center">
        <b><span style='font-size=9pt;'>등록된 글이 없습니다.</span></b>
      </p>
    </td>
   </c:when>

2. 게시글이 있을 경우, <c:forEach>를 사용해서 목록을 출력한다.
   • var : 변수 이름
   • itemts : 바인딩된 이름 (request.setAttribute("boardList",list);
   • varStatus : varStatus의 속성에 접근하기 위한 변수명

자세한 JSTL 사용법은 링크

<c:when test="${boardList != null }">
    <c:forEach var="post" items="${boardList }" varStatus="postNum">
        <tr align="center">
            <td width="5%">${postNum.count }</td>
            <td width="10%">${post.id }</td>
            <td align='left' width="35%">
            <span style='padding-right:30px'></span>

2번에는 두 가지 경우가 있다. 답글이 있는 경우와 없는 경우.

• test="${post.level > 1 }" 가 true면 답글이 있는 것이다.
  style속성으로 들여쓰기를 넣어서 [답글]임을 명시한다.

  <c:choose>
   <c:when test="${post.level > 1 }">
       <c:forEach begin="1" end="${post.level }">
           <span style='padding-left:20px'></span>
       </c:forEach>
       <span style='font-size:12px;'>[답변]</span>
       <a class="cls1" href='/board/post?no=${post.bno }'>${post.title }</a>
  </c:when>

답글이 없는 경우는 그냥 출력한다.

<c:otherwise>
<a class='cls1' href='/board/post?no=${post.bno }'>${post.title }</a>
</c:otherwise>

fmt태그로 날짜 출력

<fmt:formatDate value="${post.regdate }"/>

최종