기능
게시글/댓글 조회
추천
- 중복추천 불가
- 본인 작성글 추천 불가
- 로그인 유저만 가능
댓글
- 로그인 유저만 작성 가능
- 대댓글 가능
- 댓글/대댓글 삭제, 수정 가능
느낀점
댓글에 대댓글 기능이 있어서 내가 직접 알고리즘을 짜봤는데 너무 재밌는 경험이었다.
악필이 부끄럽지만 메모 했던 내용을 올려본다.
구현
dao.java
/**
* 추천 정보 입력
* @param boardNo
* @param userNo
* @throws SQLException
*/
public void insertBoardLiker(BoardLiker boardLiker) throws SQLException{
String sql ="insert into tb_like_users(like_board_no, like_user_no) "
+ "values (?, ?) ";
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, boardLiker.getBoardNo());
pstmt.setInt(2, boardLiker.getUserNo());
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
/**
* 추천 여부 조회
* @param boardNo
* @param userNo
* @return
* @throws SQLException
*/
public BoardLiker getBoardLiker(int boardNo, int userNo) throws SQLException{
String sql ="select like_board_no, like_user_no "
+ "from tb_like_users "
+ "where like_board_no = ? "
+ "and like_user_no = ? ";
BoardLiker boardLiker = null;
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, boardNo);
pstmt.setInt(2, userNo);
ResultSet rs = pstmt.executeQuery();
if(rs.next()) {
boardLiker = new BoardLiker();
boardLiker.setBoardNo(boardNo);
boardLiker.setUserNo(userNo);
}
rs.close();
pstmt.close();
connection.close();
return boardLiker;
}
/**
* 게시글 별 추천한 사용자 조회
* @param boardNo
* @return
* @throws SQLException
*/
public List<User> getLikeUsers(int boardNo) throws SQLException {
String sql = "select u.user_no, u.user_id, u.user_name "
+ "from tb_like_users l, tb_comm_users u "
+ "where l.like_user_no = u.user_no "
+ "and l.like_board_no = ? ";
List<User> userList = new ArrayList<>();
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, boardNo);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
User user = new User();
user.setNo(rs.getInt("user_no"));
user.setId(rs.getString("user_id"));
user.setName(rs.getString("user_name"));
userList.add(user);
}
rs.close();
pstmt.close();
connection.close();
return userList;
}
/**
* 대댓글 등록
* @param comment
* @throws SQLException
*/
public void insertCommetReply(Comment comment) throws SQLException {
String sql = "insert into tb_diablo_board_comments(comment_no, diablo_board_no, comment_writer_no, comment_content, comment_order, comment_group) "
+ "values (comm_comment_seq.nextval, ?, ?, ?, ?, ?) ";
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, comment.getBoard().getNo());
pstmt.setInt(2, comment.getWriter().getNo());
pstmt.setString(3, comment.getContent());
pstmt.setInt(4, comment.getOrder());
pstmt.setInt(5, comment.getGroup());
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
/**
* 덧글번호 전달받아 덧글 삭제('삭제된 댓글입니다' 남기기)
* @param no
* @throws SQLException
*/
public void deleteComment(int no) throws SQLException{
String sql = "update tb_diablo_board_comments "
+ "set comment_deleted = 'Y' "
+ "where comment_no = ? ";
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, no);
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
/**
* 댓글 컬럼 완전 삭제
* @param no
* @throws SQLException
*/
public void deleteCommentColumn(int commentNo) throws SQLException{
String sql ="delete from tb_diablo_board_comments "
+ "where comment_no = ? ";
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, commentNo);
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
/**
* 댓글 그룹 전부 삭제
* @param groupNo
* @throws SQLException
*/
public void deleteCommentGroup(int groupNo) throws SQLException{
String sql ="delete from tb_diablo_board_comments "
+ "where comment_group = ? ";
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, groupNo);
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
/**
* 댓글번호를 받아 group 갯수 반환
* (대댓없음 = 1 / 대댓있음 = 2이상)
* @param no
* @throws SQLException
*/
public int getGroupCount(int no) throws SQLException{
String sql ="select count(*) cnt "
+ "from tb_diablo_board_comments "
+ "where comment_group = ? ";
int groupCount = 0;
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, no);
pstmt.executeQuery();
ResultSet rs = pstmt.executeQuery();
rs.next();
groupCount = rs.getInt("cnt");
rs.close();
pstmt.close();
connection.close();
return groupCount;
}
/**
* 댓글 번호로 댓글 정보 조회
* @param no
* @return
* @throws SQLException
*/
public Comment getCommentInfo(int no) throws SQLException {
String sql = "select * "
+ "from tb_diablo_board_comments "
+ "where comment_no = ? ";
Comment comment = new Comment();
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, no);
ResultSet rs = pstmt.executeQuery();
if(rs.next()) {
comment.setNo(rs.getInt("comment_no"));
comment.setContent(rs.getString("comment_content"));
comment.setDeleted(rs.getString("comment_deleted"));
comment.setCreatedDate(rs.getDate("comment_created_date"));
comment.setOrder(rs.getInt("comment_order"));
comment.setGroup(rs.getInt("comment_group"));
}
rs.close();
pstmt.close();
connection.close();
return comment;
}
/**
* 댓글 등록(대댓글 아님)
* @param no
* @throws SQLException
*/
public void insertComment(Comment comment) throws SQLException{
String sql = "insert into tb_diablo_board_comments(comment_no, diablo_board_no, comment_writer_no, comment_content, comment_group) "
+ "values(comm_comment_seq.nextval, ?, ?, ?, comm_commentgroup_seq.nextval) ";
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, comment.getBoard().getNo());
pstmt.setInt(2, comment.getWriter().getNo());
pstmt.setString(3, comment.getContent());
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
/**
* 글번호 별 댓글 조회
* @param no
* @return
* @throws SQLException
*/
public List<Comment> getAllComment(int no) throws SQLException {
String sql = "select comment_no, comment_content, comment_deleted, comment_created_date, comment_order, "
+ " comment_group, board_no, user_no, user_name "
+ "from (select * "
+ " from tb_diablo_boards b, tb_diablo_board_comments c "
+ " where b.board_no = c.diablo_board_no ) board, tb_comm_users u "
+ "where board.comment_writer_no = u.user_no "
+ "and board.board_no = ? "
+ "order by board.comment_group, board.comment_no, board.comment_created_date";
Comment comment = null;
List<Comment> commentList = new ArrayList<>();
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, no);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
comment = new Comment();
User user = new User();
Board board = new Board();
comment.setNo(rs.getInt("comment_no"));
comment.setContent(rs.getString("comment_content"));
comment.setDeleted(rs.getString("comment_deleted"));
comment.setCreatedDate(rs.getDate("comment_created_date"));
comment.setOrder(rs.getInt("comment_order"));
comment.setGroup(rs.getInt("comment_group"));
board.setNo(rs.getInt("board_no"));
user.setNo(rs.getInt("user_no"));
user.setName(rs.getString("user_name"));
comment.setBoard(board);
comment.setWriter(user);
commentList.add(comment);
}
rs.close();
pstmt.close();
connection.close();
return commentList;
}
/**
* 게시글 번호 전달받아 게시글 삭제
* (deleted-y로 변경)
* @param no
* @throws SQLException
*/
public void deleteBoard(int no) throws SQLException {
String sql ="update tb_diablo_boards "
+ "set"
+ " board_deleted = 'Y' "
+ "where board_no = ? ";
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, no);
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
/**
* 수정
* @param board
* @throws SQLException
*/
public void updateBoard(Board board) throws SQLException{
String sql ="update tb_diablo_boards "
+ "set "
+ " board_title = ?, "
+ " board_content = ?, "
+ " board_like_count = ?, "
+ " board_view_count = ?, "
+ " board_comment_count = ? "
+ "where board_no = ?";
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, board.getTitle());
pstmt.setString(2, board.getContent());
pstmt.setInt(3, board.getLikeCount());
pstmt.setInt(4, board.getViewCount());
pstmt.setInt(5, board.getCommentCount());
pstmt.setInt(6, board.getNo());
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
/**
* 게시글번호로 내용 조회
* @param no
* @throws SQLException
*/
public Board getBoardDetail(int no) throws SQLException{
String sql = "select B.board_no, B.board_title, U.user_no, U.user_id, U.user_name, B.board_content, B.board_comment_count, "
+ " B.board_view_count, B.board_like_count, B.board_deleted, B.board_created_date "
+ "from tb_diablo_boards B, tb_comm_users U "
+ "where B.board_writer_no = U.user_no "
+ "and B.board_no = ? ";
Board board = null;
Connection connection = getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, no);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
board = new Board();
User user = new User();
board.setNo(rs.getInt("board_no"));
board.setTitle(rs.getString("board_title"));
board.setContent(rs.getString("board_content"));
board.setLikeCount(rs.getInt("board_like_count"));
board.setViewCount(rs.getInt("board_view_count"));
board.setDeleted(rs.getString("board_deleted"));
board.setCreatedDate(rs.getDate("board_created_date"));
board.setCommentCount(rs.getInt("board_comment_count"));
user.setNo(rs.getInt("user_no"));
user.setId(rs.getString("user_id"));
user.setName(rs.getString("user_name"));
board.setWriter(user);
}
rs.close();
pstmt.close();
connection.close();
return board;
}detail.jsp
<%
int no = Integer.parseInt(request.getParameter("no"));
String pageNo = request.getParameter("pageNo");
String error = request.getParameter("error");
DiabloBoardDao boardDao = DiabloBoardDao.getInstance();
Board board = boardDao.getBoardDetail(no);
board.setViewCount(board.getViewCount()+1);
boardDao.updateBoard(board);
List<Comment> commentList = boardDao.getAllComment(no);
User loginUserInfo = (User)session.getAttribute("LOGIN_USER_INFO");
%>
<!-- 게시판 제목 -->
<div class="row">
<div class="col mb-4 mt-2 border-bottom">
<div class="coinTitle col">
<h2 class="fw-bold"><a href="list.jsp">디아블로 갤러리</a></h2>
</div>
</div>
</div>
<!-- 게시글 제목/작성자 정보 등 -->
<div class="row mb-1">
<div class="col mb-3">
<!-- 간격(spacing): https://getbootstrap.kr/docs/5.1/utilities/spacing/ -->
<div class="border-top border-bottom">
<div class="fs-4 pt-2"><%=board.getTitle()%></div>
<div>
<small><%=board.getWriter().getName()%></small>
<small class="ml-3 ps-2 border-start"><%=board.getCreatedDate()%></small>
</div>
<div class="text-end border-bottom">
<small>조회 <%=board.getViewCount()%></small>
<small class="ml-3 ps-2 border-start">추천
<%
if(board.getLikeCount() > 0){
%>
<a class="p-2" data-bs-toggle="modal" data-bs-target="<%=board.getLikeCount() > 0 ? "#liker": ""%>">
<%
}
%>
<%=board.getLikeCount()%></a></small>
</div>
<div class="py-3"><%=board.getContent()%></div>
<!-- 중복추천 경고창 -->
<%
if ("likeLogin".equals(error)){
%>
<div class="alert alert-danger" role="alert">
본인의 글에는 불가능한 기능입니다.
</div>
<%
}
%>
<%
if ("alreadyLike".equals(error)){
%>
<div class="alert alert-danger" role="alert">
이미 추천한 글입니다.
</div>
<%
}
%>
<!-- 추천/즐찾 버튼 -->
<div class="row d-flex justify-content-center">
<div class="col-3 mt-5 mb-4">
<div>
<a class="p-2" data-bs-toggle="modal" data-bs-target="<%=board.getLikeCount() > 0 ? "#liker": ""%>">
<strong><%=board.getLikeCount()%></strong>
</a>
<!-- 추천아이콘 -->
<%
boolean canLike = false;
if (loginUserInfo != null) {
if (loginUserInfo.getNo() != board.getWriter().getNo()) {
BoardLiker boardLiker = boardDao.getBoardLiker(no, loginUserInfo.getNo());
if (boardLiker == null) {
canLike = true;
}
}
}
/*
로그인 한 경우 : 작성자인 경우 비활성화
작성자가 아닌 경우 추천한 경우 비활성화
추천안한 경우 활성화
*/
%>
<a href="like.jsp?no=<%=board.getNo()%>&pageNo=<%=pageNo%>" <%=canLike ? "" : "disabled"%>>
<img class="m-1" src="../resources/images/like.png">
</a>
</div>
</div>
</div>
</div>
</div>
</div>
<!-- 하단 버튼 -->
<div class="row">
<div class="col">
<!-- 플렉스(flex): https://getbootstrap.kr/docs/5.1/utilities/flex/ -->
<!-- div 기준으로 양옆으로 배치 -->
<div class="col d-flex justify-content-between">
<div>
<a href="list.jsp?pageNo=1" class="btn btn-primary">전체글</a>
<a href="" class="btn btn-light">개념글</a>
</div>
<%
if(loginUserInfo != null && loginUserInfo.getNo() == board.getWriter().getNo()){
%>
<div>
<a href="updateform.jsp?no=<%=board.getNo()%>&pageNo=<%=pageNo%>" class="btn btn-secondary">수정</a>
<a class="btn btn-secondary" data-bs-toggle="modal" data-bs-target="#deleteConfirm">삭제</a>
</div>
<%
}
%>
</div>
</div>
</div>
<!-- 댓글part 시작(댓글위 라인부터 끝까지) -->
<div class="row mt-5 mb-3">
<div class="col pt-1 pb-1">
<div class="border-top">
<!-- 경고창 -->
<%
if ("comcont".equals(error)){
%>
<div class="alert alert-danger" role="alert">
내용을 입력하세요.
</div>
<%
}
%>
<%
if ("commmentId".equals(error)){
%>
<div class="alert alert-danger" role="alert">
로그인이 필요합니다.
</div>
<%
}
%>
<!-- 댓글창시작 -->
<%
if(!commentList.isEmpty()){
for(Comment comments : commentList){
%>
<div class="row p-2">
<div class="col <%=comments.getOrder() != 0 ? "ps-5" : ""%>">
<div class="row <%=comments.getOrder() != 0 ? "border pt-2 pb-2" : ""%>">
<%
if (comments.getDeleted().equals("Y")) {
%>
<div class="col-12">삭제된 댓글입니다.</div>
<%
} else {
%>
<div class="col-2" style="font-size:15px"><%=comments.getWriter().getName()%></div>
<div class="col-7"><%=comments.getContent()%></div>
<div class="col-2" style="font-size:11px;"><%=DateUtils.dateToString(comments.getCreatedDate())%></div>
<div class="col-1">
<!-- 답글달기 아이콘 -->
<%
if(loginUserInfo!=null && comments.getOrder() == 0) {
%>
<i class="fa fa-commenting-o" aria-hidden="true" onclick="toggleform('form-<%=comments.getNo()%>')"></i>
<%
}
// 댓글 삭제 아이콘
if(loginUserInfo != null && loginUserInfo.getNo() == comments.getWriter().getNo()) {
%>
<a href="../comment/delete.jsp?boardNo=<%=board.getNo()%>&writerNo=<%=comments.getWriter().getNo()%>&commentNo=<%=comments.getNo()%>&order=<%=comments.getOrder()%>&groupNo=<%=comments.getGroup()%>">
<i class="fa fa-trash" aria-hidden="true"></i></a>
<%
}
%>
</div>
<%
}
%>
<!-- 대댓글 달기 창 -->
<%
if (comments.getOrder() == 0){
%>
<div class="row my-3" id="form-<%=comments.getNo()%>" style="display: none;">
<div class="col">
<!-- comment_no, diablo_board_no, comment_writer_no, comment_content, comment_order, comment_group -->
<form method="get" action="../comment/reply.jsp" class="border bg-light p-2">
<input type="hidden" name="boardNo" value="<%=board.getNo()%>" />
<input type="hidden" name="order" value="<%=comments.getNo()%>" />
<input type="hidden" name="group" value="<%=comments.getGroup()%>" />
<div class="row g-3">
<div class="col-11">
<textarea placeholder="타인의 권리를 침해하거나 명예를 훼손하는 댓글은 운영원칙 및 관련법률에 제재를 받을 수 있습니다." rows="2" name="content" class="form-control" style="resize: none;"></textarea>
</div>
<div class="col-1">
<button type="submit" class="btn btn-primary ">등록</button>
</div>
</div>
</form>
</div>
</div>
<%
}
%>
</div>
</div>
</div>
<%
}
%>
<%
} else {
%>
<div class="col ps-5">
<div class="row px-3 py-2">
작성된 댓글이 없습니다.
</div>
</div>
<%
}
%>
<%
if (loginUserInfo == null) {
%>
<div class="row mb-3 px-3 py-2">
<div class="col">
<div class="alert alert-secondary py-2">
로그인 작성자만 댓글을 작성 할 수 있습니다.
</div>
</div>
</div>
<%
} else {
%>
<!-- 댓글 달기 창 -->
<div class="row mb-3 px-3 py-2">
<div class="col">
<form method="post" action="../comment/comment.jsp" class="border bg-light p-2">
<input type="hidden" name="boardNo" value="<%=board.getNo()%>" />
<div class="row g-3">
<div class="col-10">
<textarea placeholder="타인의 권리를 침해하거나 명예를 훼손하는 댓글은 운영원칙 및 관련법률에 제재를 받을 수 있습니다." rows="5" name="content" class="form-control" style="resize: none;"></textarea>
</div>
<div class="col-2">
<button type="submit" class="btn btn-primary ">등록</button>
</div>
</div>
</form>
</div>
</div>
<%
}
%>
</div>
</div>
</div>
<!-- 추천인 표시 모달창 -->
<div class="modal fade" id="liker" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">추천인</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<ul>
<%
List<User> likerList = boardDao.getLikeUsers(no);
for (User liker : likerList){
%>
<li><%=liker.getName() %></li>
<% } %>
</ul>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
<!-- 삭제 재확인 모달창 -->
<div class="modal fade" id="deleteConfirm" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog modal-dialog-centered">
<div class="modal-content">
<div class="modal-header">
<h6 class="modal-title" id="exampleModalLabel">삭제</h6>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
게시글을 삭제하시겠습니까?
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">취소</button>
<a class="btn btn-primary" href="delete.jsp?no=<%=board.getNo() %>&pageNo=<%=pageNo %>">확인</a>
</div>
</div>
</div>
</div>
</section>
<section class="right_content">
<%@include file="/common/right_section.jsp" %>
</section>
</main>
</div>
<%@include file="/common/footer.jsp" %>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
<script type="text/javascript">
function toggleform (id) {
var el = document.getElementById(id);
var value = el.style.display;
if (value == '') {
el.style.display = 'none';
} else {
el.style.display = '';
}
}
function loginPlease(){
alert("로그인이 필요합니다.");
}
function writerLike(){
alert("본인의 글에는 불가능합니다.");
}
</script>