• 현오가 알려준 SQL쿼리문

1. 도메인 = ‘totalReply‘ 추가

@Component("myhomeDomain")
public class MyhomeDomain {

    private int boardMyhomeArticleNo; 		 // 게시글번호
    ....
    private int totalReply;					 // 댓글수
   
	@Override
	public String toString() {
		return "MyhomeDomain [boardMyhomeArticleNo=" + boardMyhomeArticleNo + ", memberId=" + memberId
				+ ", boardMyhomeTitle=" + boardMyhomeTitle + ", boardMyhomeContents=" + boardMyhomeContents
				+ ", boardMyhomeLikes=" + boardMyhomeLikes + ", boardMyhomeViews=" + boardMyhomeViews
				+ ", boardMyhomeUpdated=" + boardMyhomeUpdated + ", boardMyhomeHomeSize=" + boardMyhomeHomeSize
				+ ", boardMyhomeHousingType=" + boardMyhomeHousingType + ", imageFileName=" + imageFileName
				+ ", totalReply=" + totalReply + "]";
	}
	public MyhomeDomain(int boardMyhomeArticleNo, String memberId, String boardMyhomeTitle, String boardMyhomeContents,
			int boardMyhomeLikes, int boardMyhomeViews, Date boardMyhomeUpdated, String boardMyhomeHomeSize,
			String boardMyhomeHousingType, String imageFileName, int totalReply) {
		super();
		this.boardMyhomeArticleNo = boardMyhomeArticleNo;
		this.memberId = memberId;
		this.boardMyhomeTitle = boardMyhomeTitle;
		this.boardMyhomeContents = boardMyhomeContents;
		this.boardMyhomeLikes = boardMyhomeLikes;
		this.boardMyhomeViews = boardMyhomeViews;
		this.boardMyhomeUpdated = boardMyhomeUpdated;
		this.boardMyhomeHomeSize = boardMyhomeHomeSize;
		this.boardMyhomeHousingType = boardMyhomeHousingType;
		this.imageFileName = imageFileName;
		this.totalReply = totalReply;
	}
	public int getTotalReply() {
		return totalReply;
	}
	public void setTotalReply(int totalReply) {
		this.totalReply = totalReply;
	}

1. board.xml = MyhomeResultMap 안에 result property 추가

  <!-- Result Map 정의 -->
    <resultMap id="**MyhomeResultMap**" 
    type="com.spring.gami.board.board_myhome.domain.MyhomeDomain">
        <id property="boardMyhomeArticleNo" column="boardMyhomeArticleNo"/>
        **<result property="totalReply" column="totalReply"/>**
    </resultMap>

1. board.xml = SQL쿼리문 추가

<!-- 각 댓글수 -->
<select id="totalReply" resultMap="MyhomeResultMap">
<![CDATA[
SELECT b.boardMyhomeArticleNo, COUNT(r.replyNo) AS totalReply
FROM boardMyhome b
LEFT OUTER JOIN reply r ON b.boardMyhomeArticleNo = r.boardMyhomeArticleNo
GROUP BY b.boardMyhomeArticleNo
]]>
</select>

1. DAO = 댓글수만 가져오는 메서드 추가

//기존
@Override
public List selectAllArticlesList() throws DataAccessException {
List<MyhomeDomain> myhomeList = sqlSession.selectList("mapper.board.selectAllArticlesList");
System.out.println("DB 조회 결과: " + myhomeList);
return myhomeList;
}
//추가된 댓글수 조회
@Override
public List<MyhomeDomain> getTotalReplys() throws DataAccessException {
return sqlSession.selectList("mapper.board.totalReply");
}

1. Service = List<MyhomeDomain>에 담을 때, 댓글 개수도 함께 조회해서 담음

• 게시글 목록을 가져오고
• • for문으로 각 게시글의 댓글 개수(totalReply)를 추가

// 기존 (삭제)
// @Override
// public List<MyhomeDomain> listArticles() throws Exception {
// return myhomeDAO.selectAllArticlesList();
// }
public List<MyhomeDomain> listArticles() throws Exception {
List<MyhomeDomain> myhomeList = myhomeDAO.selectAllArticlesList(); // 게시글 조회
List<MyhomeDomain> totalReplyList = myhomeDAO.getTotalReplys(); // 각 게시글 댓글개수 조회

for (MyhomeDomain article : myhomeList) { // 모든 게시글 꺼내서
   article.setTotalReply(0); //기본값 0
   for (MyhomeDomain replyData : totalReplyList) {  // 모든 댓글 개수확인
      // 게시글의 넘버와 댓글이 달린 게시글의 넘버가 같은경우
      if (article.getBoardMyhomeArticleNo() == replyData.getBoardMyhomeArticleNo()) {
      // 댓글개수를 'reply의 댓글개수'로 설정
      article.setTotalReply(replyData.getTotalReply());
      break;
      }
   }
}
return myhomeList;
}

1. 컨트롤러 = "myhomeList"라는 이름으로 데이터를 JSP로 전달 (변경없이 그대로)

// 게시글 리스트 출력
@RequestMapping(value = "/myHomeList.do", method = {RequestMethod.GET, RequestMethod.POST})
public ModelAndView myHomelist(
HttpServletRequest request, HttpServletResponse response) throws Exception {

List<MyhomeDomain> myhomeList = myhomeService.listArticles(); // 게시글 조회
        
ModelAndView mav = new ModelAndView("/board/board_myhome/myHomeList");
mav.addObject("myhomeList", myhomeList); // "myhomeList"라는 이름으로 데이터를 JSP로 전달
return mav;
}

1. JSP에서 ${article.totalReply}로 댓글 개수를 출력

<c:choose>
<c:when test="${not empty myhomeList}">
<c:**forEach** var="article" items="${myhomeList}">
...
<!-- 게시글 정보 -->
...
<p>❤️좋아요 ${article.boardMyhomeLikes}
   👀조회수 ${article.boardMyhomeViews}
   💬댓글수 ${article.totalReply} </p>