기능
인기글 게시
- 추천수 5 이상인 게시글 자동으로 출력
- CRUD 기능은 없으며 클릭 시 해당 게시판-게시글로 자동 이동
구현
dao.java
/**
* 힛갤 게시글 갯수
* @return
* @throws SQLException
*/
public int getHitRecords() throws SQLException {
String sql = "select count(*) cnt "
+ "from ( "
+ " select A.* "
+ " 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 "
+ " where a.board_like_count >= 5 "
+ " ) 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";
int hitRecords = 0;
Connection connection = getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
rs.next();
hitRecords = rs.getInt("cnt");
rs.close();
pstmt.close();
connection.close();
return hitRecords;
}
/**
* Hit 게시글 출력용
* @return
* @throws SQLException
*/
public List<Hit> getHitPost(int begin, int end) throws SQLException {
String sql = "select * "
+ "from ( "
+ " select row_number() over (order by board_created_date desc) rn, A.* "
+ " 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 "
+ " where a.board_like_count >= 5 "
+ " ) 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 rn >= ? and rn <= ? " ;
List<Hit> hitList = new ArrayList<>();
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, begin);
pstmt.setInt(2, end);
ResultSet rs = pstmt.executeQuery();
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;
}
hit.jsp
<body>
<%
String pageNo = request.getParameter("pageNo");
DiabloBoardDao boardDao = DiabloBoardDao.getInstance();
BoardDao boardDao2 = BoardDao.getInstance();
int hitRecords = boardDao2.getHitRecords();
Pagination pagination = new Pagination(pageNo, hitRecords);
List<Hit> hitList = boardDao2.getHitPost(pagination.getBegin(), pagination.getEnd());
%>
<div class="dcwrap">
<%@include file="/common/navbar.jsp" %>
<div class="wrap_inner">
<main class="dc_container">
<section class="left_content">
<div class="row">
<div class="col mb-4 mt-2 border-bottom">
<div class="col-3"><h2 class="fw-bold"><a href="hit.jsp">Hit 갤러리</a></h2></div>
</div>
</div>
<div class="row">
<div class="col">
<div>
<a href="hit.jsp?pageNo=1" class="btn btn-primary">전체글</a>
</div>
</div>
</div>
<div class="row mb-1">
<div class="col border-top border-bottom border-primary border-2 mt-2 mb-2">
<table class="table table-sm mt-3">
<thead>
<tr>
<th class="col-2">갤러리</th>
<th class="col-5 text-center" style="width: 90px;">제목</th>
<th class="col-1">글쓴이</th>
<th class="col-2 text-center">작성일</th>
<th class="col-1">조회</th>
<th class="col-1">추천</th>
</tr>
</thead>
<tbody>
<%
if (hitList.isEmpty()) {
%>
<tr>
<td class="text-center"> 게시글이 없습니다.</td>
</tr>
<%
}
for (Hit hit : hitList) {
%>
<tr>
<td class="col-2" style="font-size:13px;"><%=hit.getBoardType().getName()%></td>
<td class="col-5">
<a href="<%=hit.getBoard().getType()%>/detail.jsp?no=<%=hit.getBoard().getNo()%>">
<%=hit.getBoard().getTitle()%></a>
(<%=hit.getBoard().getCommentCount()%>)
</td>
<td class="col-1"><%=hit.getUser().getName()%></td>
<td class="col-2" style="font-size:13px;"><%=DateUtils.dateToString(hit.getBoard().getCreatedDate())%></td>
<td class="col-1"><%=hit.getBoard().getViewCount()%></td>
<td class="col-1"><%=hit.getBoard().getLikeCount()%></td>
</tr>
<%
}
%>
</tbody>
</table>
</div>
</div>
<div class="row">
<div class="col">
<div>
<a href="hit.jsp?pageNo=1" class="btn btn-primary">전체글</a>
</div>
</div>
</div>
<div class="row">
<div class="col">
<nav aria-label="Page navigation example">
<ul class="pagination justify-content-center">
<li class="page-item">
<a class="page-link" href="hit.jsp?pageNo=1" aria-label="Previous">
<span aria-hidden="true">«</span>
</a>
</li>
<li class="page-item <%=!pagination.isExistPrev() ? "disabled" : ""%>"><a class="page-link" href="hit.jsp?pageNo=<%=pagination.getPrevPage()%>" >이전</a></li>
<%
for (int num = pagination.getBeginPage(); num <= pagination.getEndPage(); num++) {
%>
<li class="page-item <%=pagination.getPageNo() == num ? "active" : ""%>"><a class="page-link" href="hit.jsp?pageNo=<%=num%>"><%=num%></a></li>
<%
}
%>
<li class="page-item <%=!pagination.isExistNext() ? "disabled" :""%>"><a class="page-link" href="hit.jsp?pageNo=<%=pagination.getNextPage()%>" >다음</a></li>
<li class="page-item">
<a class="page-link" href="hit.jsp?pageNo=<%=pagination.getEnd()%>" aria-label="Next">
<span aria-hidden="true">»</span>
</a>
</li>
</ul>
</nav>
</div>
</div>
