기능
UserNo를 전달받아 유저별 작성글/댓글 조회
느낀점
모든 게시판을 통합해서 조회해야 했는데, 나중에 추가하기로 한 기능이라 테이블이 통합조회에 적합하지 않은 상태였다.
즉, 게시판용 통합 테이블 1개 - 게시판카테고리(6개) 컬럼 이런 형태로 작성을 했어야 했는데, 각 게시판 카테고리(6개)마다 테이블이 따로 구성되어 있다보니 통합조회 할 때 저 6개의 테이블에 대한 쿼리문을 몽땅 작성해야 했다.
노가다(?)로 구현은 해냈지만 너무 비효율적이라고 느꼈다. 다음부터는 좀 더 멀리 내다보고 구현이 당장 안되더라도 더 넓은 경우의 수까지 상정해서 개발을 해야겠다고 생각했다.
구현
dao.java
/**
* 작성댓글 조회
* @param userNo
* @return
* @throws SQLException
*/
public List<Hit> getMyComment(int userNo) throws SQLException{
String sql = "select * "
+ "from ( "
+ " select * "
+ " from ( "
+ " select * from tb_diablo_board_comments "
+ " union "
+ " select * from tb_animal_board_comments "
+ " union "
+ " select * from tb_stock_board_comments "
+ " union "
+ " select * from tb_soccer_board_comments "
+ " union "
+ " select * from tb_coin_board_comments "
+ " union "
+ " select * from tb_hotplace_board_comments "
+ " ) "
+ " )c, tb_boards_type t, tb_comm_users u "
+ "where c.comment_writer_no = u.user_no "
+ "and c.board_type_code = t.board_type_code "
+ "and c.comment_deleted = 'N' "
+ "and user_no = ? ";
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, userNo);
ResultSet rs = pstmt.executeQuery();
List<Hit> hitList = new ArrayList<>();
while(rs.next()) {
Hit hit = new Hit();
Board board = new Board();
BoardType boardType = new BoardType();
board.setContent(rs.getString("comment_content"));
board.setCreatedDate(rs.getDate("comment_created_date"));
board.setType(rs.getInt("board_type_code"));
boardType.setName(rs.getString("board_type_name"));
hit.setBoard(board);
hit.setBoardType(boardType);
hitList.add(hit);
}
rs.close();
pstmt.close();
connection.close();
return hitList;
}
/**
* 작성글 조회
* @param userNo
* @return
* @throws SQLException
*/
public List<Hit> getMyPosting(int userNo) throws SQLException{
String sql = "select * "
+ "from ( "
+ " select * "
+ " from ( "
+ " select * from tb_diablo_boards"
+ " union "
+ " select * from tb_animal_boards "
+ " union "
+ " select * from tb_stock_boards "
+ " union "
+ " select * from tb_soccer_boards "
+ " union "
+ " select * from tb_coin_boards "
+ " union "
+ " select * from tb_hotplace_boards "
+ " )A "
+ " ) b, tb_boards_type t, tb_comm_users u "
+ "where b.board_type_code = t.board_type_code "
+ "and b.board_writer_no = u.user_no "
+ "and user_no = ?";
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, userNo);
ResultSet rs = pstmt.executeQuery();
List<Hit> hitList = new ArrayList<>();
while(rs.next()) {
Hit hit = new Hit();
Board board = new Board();
BoardType boardType = new BoardType();
User user = new User();
board.setType(rs.getInt("board_type_code"));
board.setTitle(rs.getString("board_title"));
board.setNo(rs.getInt("board_no"));
board.setCreatedDate(rs.getTimestamp("board_created_date"));
board.setLikeCount(rs.getInt("board_like_count"));
board.setViewCount(rs.getInt("board_view_count"));
board.setCommentCount(rs.getInt("board_comment_count"));
boardType.setName(rs.getString("board_type_name"));
user.setName(rs.getString("user_name"));
hit.setBoard(board);
hit.setBoardType(boardType);
hit.setUser(user);
hitList.add(hit);
}
rs.close();
pstmt.close();
connection.close();
return hitList;
}myposting.jsp
<%
User loginUserInfo = (User)session.getAttribute("LOGIN_USER_INFO");
if(loginUserInfo == null){
response.sendRedirect("../loginform.jsp?error=noLogin");
return;
}
DiabloBoardDao boardDao = DiabloBoardDao.getInstance();
BoardDao boardDao2 = BoardDao.getInstance();
List<Hit> posting = boardDao2.getMyPosting(loginUserInfo.getNo());
List<Hit> comments = boardDao2.getMyComment(loginUserInfo.getNo());
%>
<div class="dcwrap">
<%@include file="../common/navbar.jsp" %>
<div class="wrap_inner">
<main class="dc_container">
<section class="left_content">
<!-- 작성글 -->
<div class="row mt-2 mb-2">
<div class="col">
<div class="border-bottom border-secondary border-2 p-1 fw-bold text-primary fs-6">작성글</div>
<table class="table table-sm mt-3">
<tbody>
<tr class="fw-bold">
<td class="col-1">갤러리</td>
<td class="col-6 text-center">제목</td>
<td class="col-2 text-center">작성일</td>
<td class="col-1">조회</td>
<td class="col-1">추천</td>
</tr>
<%
if (posting.isEmpty()) {
%>
<tr class="border-bottom mt-1 p-2 text-center align-middle" style="height: 180px;">
<td></td>
<td class="align-top text-end"> 작성한 게시글이 없습니다.</td>
</tr>
<%
}
for (Hit post : posting) {
%>
<tr>
<td class="col-1" style="font-size:13px;"><%=post.getBoardType().getName()%></td>
<td class="col-6">
<a href="../<%=post.getBoard().getType()%>/detail.jsp?no=<%=post.getBoard().getNo()%>">
<%=post.getBoard().getTitle()%></a>
(<%=post.getBoard().getCommentCount()%>)
</td>
<td class="col-2" style="font-size:13px;"><%=DateUtils.dateToString(post.getBoard().getCreatedDate())%></td>
<td class="col-1"><%=post.getBoard().getViewCount()%></td>
<td class="col-1"><%=post.getBoard().getLikeCount()%></td>
</tr>
<%
}
%>
</tbody>
</table>
</div>
</div>
<!-- 작성댓글 -->
<div class="row mt-2 mb-2">
<div class="col">
<div class="border-bottom border-secondary border-2 p-1 fw-bold text-primary fs-6">작성댓글</div>
<table class="table table-sm mt-3">
<tbody>
<tr class="fw-bold">
<td class="col-2">갤러리</td>
<td class="col-7 text-center">내용</td>
<td class="col-2">작성일</td>
<td class="col-1"></td>
</tr>
<%
if (comments.isEmpty()) {
%>
<tr class="border-bottom mt-1 p-2 text-center align-middle" style="height: 180px;">
<td></td>
<td class="align-top text-end"> 작성한 댓글이 없습니다.</td>
</tr>
<%
}
for (Hit comment : comments) {
%>
<tr>
<td class="col-2" style="font-size:13px;"><%=comment.getBoardType().getName() %></td>
<td class="col-7">
<%=comment.getBoard().getContent()%>
</td>
<td class="col-2" style="font-size:13px;"><%=DateUtils.dateToString(comment.getBoard().getCreatedDate())%></td>
<td class="col-1"></td>
</tr>
<%
}
%>
</tbody>
</table>
</div>
</div>