댓글 수 포함하기 및 index 페이지 추가하기

아래의 사진과 같이 게시글 목록에서 댓글 수를 포함하려고 한다.

 

1. 댓글 수 포함하기

먼저 COMMENT 테이블과 MEMBER 테이블의 구성을 살펴보면 아래와 같다.

COMMENT 테이블

MEMBER 테이블

댓글 수를 알기 위해서는 NOTICE 테이블만으로 알 수 없어

COMMENT 테이블과 NOTICE 테이블을 JOIN을 해야 한다.

 

SELECT * FROM NOTICE N
INNER JOIN "COMMENT" C ON N.ID = C.NOTICE_ID
ORDER BY N.REGDATE DESC;

INNER JOIN을 할 때는 자식 테이블이 기준이 되어서 자식 테이블이 필요로 하는

부모 테이블의 컬럼을 가져오도록 한다. 여기서 자식 테이블은 COMMENT 테이블이다.

위에서 보았다싶이 COMMENT 테이블에는 3개의 컬럼이 있으므로 이를 기준으로 해서

연관되어 있는 부모 테이블(NOTICE)의 컬럼을 가져온 것이다.

이때 INNER JOIN은 교집합이기 때문에 3개의 컬럼만 출력되므로 OUTER JOIN을 사용하고자 한다.

 

SELECT * FROM NOTICE N
LEFT JOIN "COMMENT" C ON N.ID = C.NOTICE_ID
ORDER BY N.REGDATE DESC;

하지만 OUTER JOIN의 결과를 보면 ID가 11인 컬럼이 2개가 출력된 것을 볼 수 있다.

이는 NOTICE_ID가 11인 2개의 자식을 가지고 있기 때문에 반복돼서 출력되는 것이다.

우리는 반복되지 않고 하나만 출력되길 원하고 자식의 모든 컬럼들을 가져올 필요가 없기 때문에

아래의 SQL문을 이용하도록 하자.

 

SELECT N.ID, N.TITLE, N.WRITER_ID, N.REGDATE, N.HIT, N.FILES, COUNT(C.ID) CMT_COUNT
FROM NOTICE N
LEFT JOIN "COMMENT" C ON N.ID = C.NOTICE_ID
GROUP BY N.ID, N.TITLE, N.WRITER_ID, N.REGDATE, N.HIT, N.FILES
ORDER BY N.REGDATE DESC;

이제야 비로소 반복되지 않고, 모든 컬럼을 가져오지도 않으며 댓글 수를 알 수 있게 되었다.

참고로 NOTICE 테이블의 CONTENT 자료형은 CLOB(매우 큼)이기 때문에 조인에서는 들어갈 수 없다.

 

목록의 댓글 수를 위해서 아래의 getNoticeList() 메소드 NOTICE가 바로 위의 SQL문으로 받게 된다.

SELECT * FROM (
	SELECT ROWNUM NUM, N.*
	FROM (SELECT * FROM NOTICE WHERE TITLE LIKE '%%' ORDER BY REGDATE DESC) N
)
WHERE NUM BETWEEN 1 AND 10

이를 직접 넣지 않고 간단하게 하기 위해서 VIEW를 생성하도록 한다.

 

CREATE VIEW NOTICE_VIEW
AS
SELECT N.ID, N.TITLE, N.WRITER_ID, N.REGDATE, N.HIT, N.FILES, COUNT(C.ID) CMT_COUNT
FROM NOTICE N
LEFT JOIN "COMMENT" C ON N.ID = C.NOTICE_ID
GROUP BY N.ID, N.TITLE, N.WRITER_ID, N.REGDATE, N.HIT, N.FILES;
--ORDER BY N.REGDATE DESC;

VIEW로 만들 때는 ORDER BY를 빼는 것이 좋다.

VIEW는 테이블을 대신하는 역할로 데이터가 필터링된다거나

정렬하는 것은 VIEW에 포함시키지 않는 것이 바람직하다.

 

이렇게 생성된 NOTICE_VIEW를 NOTICE 대신해서 사용하면 된다.

SELECT * FROM (
	SELECT ROWNUM NUM, N.*
	FROM (SELECT * FROM NOTICE_VIEW WHERE TITLE LIKE '%%' ORDER BY REGDATE DESC) N
)
WHERE NUM BETWEEN 1 AND 10

NoticeService 클래스의 getNoticeList() 메소드 SQL문을

위와 같이 NOTICE에서 NOTICE_VIEW로 변경해준다.

 

이제 Notice를 슈퍼 클래스로 가지고 있는 NoticeView를 아래와 같이 생성하자.

NoticeView.java

package com.web.entity;

import java.util.Date;

public class NoticeView extends Notice {

	private int cmtCount;
	
	public int getCmtCount() {
		return cmtCount;
	}

	public void setCmtCount(int cmtCount) {
		this.cmtCount = cmtCount;
	}

	public NoticeView() {
		// TODO Auto-generated constructor stub
	}
	
	public NoticeView(int id, String title, Date regDate, String writerId, Integer hit, String files, int cmtCount) {
		super(id, title, regDate, writerId, hit, files, "");
		this.cmtCount = cmtCount;
	}

}

 

NoticeService 클래스에서 VIEW의 CONTENT를 삭제했으므로 주석 처리를 해준다.

그리고 아래 코드의 getNoticeList() 메소드뿐만 아니라 getNoticeList()의 반환하는 모든 부분을

Notice에서 NoticeView로 수정해주도록 한다.

이외에도 NoticeListController 클래스에서 반환하는 부분도 Notice에서 NoticeView로 수정하도록 한다.

NoticeService.java

public List<NoticeView> getNoticeList(String field, String query, int page){
		
	List<NoticeView> list = new ArrayList<>();
		
	String sql = "SELECT * FROM (" +
		"SELECT ROWNUM NUM, N.* " + 
		"FROM(SELECT * FROM NOTICE_VIEW WHERE " +field+" LIKE ? ORDER BY REGDATE DESC) N " +
		")" +
		"WHERE NUM BETWEEN ? AND ?";
		
		String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
		String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:xe";
		
		String USER = "system";
		String PASSWORD = "oracle";
		
		try {
			Class.forName(JDBC_DRIVER);
			Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
			PreparedStatement st = conn.prepareStatement(sql);
			st.setString(1, "%" + query + "%");
			st.setInt(2, 1 + (page-1)*10);
			st.setInt(3, page*10);
			ResultSet rs = st.executeQuery();
			
			while(rs.next()){
				int id = rs.getInt("ID");
				String title = rs.getString("TITLE");
				Date regDate = rs.getDate("REGDATE");
				String writerId = rs.getString("WRITER_ID"); 
				Integer hit = rs.getInt("HIT") ;
				String files = rs.getString("FILES");
				//String content = rs.getString("CONTENT");
				int cmtCount = rs.getInt("CMT_COUNT");
			
				NoticeView notice = new NoticeView(
					id, title, regDate, writerId, hit, files, 
					//content, 
					cmtCount
				);
				list.add(notice);
			}
			rs.close();
		    st.close();
		    conn.close(); 
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException se) {
			se.printStackTrace();
		}		
	return list;
}

 

list.jsp

<c:forEach var="n" items="${ list }">
<tr>
	<td>${ n.id }</td>
	td class="title indent text-align-left"><a href="detail?id=${n.id }">${ n.title }</a><span>[${n.cmtCount}]</span></td>
	<td>${ n.writerId }</td>
	<td>
		<fmt:formatDate pattern="yyyy-MM-dd" value="${ n.regDate }"/>	
	</td>
	<td>
		<fmt:formatNumber type="number" pattern="##,####" value="${n.hit}"/>
	</td>
</tr>
</c:forEach>

마지막으로 <span> 태그를 사용해서 EL을 통해 댓글 수를 불러오면 아래와 같이 댓글 수를 알 수 있다.

 

 

2. index 페이지 추가하기

/webapp/WEB-INF/view 폴더 아래에 index.jsp 파일을 위치시켜주고

이를 동작할 수 있게 해주는 Controller를 생성한다.

IndexController.java

package com.web.controller;

import java.io.IOException;

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

@WebServlet("/index")
public class IndexController extends HttpServlet{
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		req.getRequestDispatcher("/WEB-INF/view/index.jsp").forward(req, resp);
	}
	
}