[JSP] Comment

* 게시판 댓글 기능 (JSON + AJAX 사용)
JSON resopnse + AJAX 를 사용한 댓글

 

* 구현할 댓글 기능
- 댓글 목록 
- 댓글 작성
- 댓글 삭제 : 삭제는 한번 confirm 하고 진행

 

* ERD, DDL 작성

* 라이브러리 준비

* select 쿼리문 준비
# 특정글 의 (댓글 + 사용자) 정보

필요한 쿼리문 작성
D.java

	// ★ 댓글
	// 댓글과 작성자 SELECT
	public static final String SQL_COMMENT_SELECT = 
			"SELECT c.uid \"uid\", c.content \"content\", c.regdate \"regdate\",\r\n"
			+ "	u.uid \"user_uid\", u.username \"user_username\", u.password \"user_password\", u.name \"user_name\", u.authorities \"user_authorities\", u.regdate \"user_regdate\"\r\n"
			+ "FROM t2_comment c, t2_user u\r\n"
			+ "WHERE c.user_uid = u.uid "
			;
	
	// '특정 글'의 댓글과 작성자 정보 SELECT
	public static final String SQL_COMMENTS_BY_WRITE =
			SQL_COMMENT_SELECT
			+ "AND c.write_uid = ?\r\n"
			+ "ORDER BY c.uid DESC"
			;
	
	// 특정글에 댓글 추가
	public static final String SQL_COMMENT_INSERT = 
			"INSERT INTO t2_comment(user_uid, write_uid, content) VALUES"
			+ "(?, ?, ?)";	
	
	// 특정 댓글 삭제
	public static final String SQL_COMMENT_DELETE_BY_UID = 
			"DELETE FROM t2_comment WHERE uid = ?";

* CommentDTO 생성

package domain;

import java.time.LocalDateTime;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class CommentDTO {
	private int uid;   // uid
	private String content;
	// java.time.* 객체 변환을 위한 annotation
	@JsonDeserialize(using = LocalDateTimeDeserializer.class)
	@JsonSerialize(using = LocalDateTimeSerializer.class)
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Seoul")
	@JsonProperty("regdate")
	private LocalDateTime regDate;
	private UserDTO user;    // user_uid (FK) 작성자
	@JsonIgnore
	private WriteDTO write;  // write_uid (FK) 글
}

---

* CommentDAO 생성

package domain;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import common.D;

public class CommentDAO {
	Connection conn;
	PreparedStatement pstmt;
	Statement stmt;
	ResultSet rs;
	
	// DAO 객체가 생성될때 Connection 도 생성된다
	public CommentDAO(){
		try {
			Class.forName(D.DRIVER);
			conn = DriverManager.getConnection(D.URL, D.USERID, D.USERPW);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} // end try
	}// 생성자
	
	// DB 자원 반납 메소드, 만들어놓으면 편함..
	public void close() throws SQLException{
		if(rs != null) rs.close();
		if(pstmt != null) pstmt.close();
		if(stmt != null) stmt.close();
		if(conn != null) conn.close();
	} // end close();
	
	// 댓글목록읽기
	// ResultSet --> List<DTO> 로 리턴
	public List<CommentDTO> buildList(ResultSet rs) throws SQLException{
		List<CommentDTO> list = new ArrayList<>();
		
		while(rs.next()) {
			int uid = rs.getInt("uid");
			String content = rs.getString("content");
			if(content == null) content = "";
			LocalDateTime regDate = rs.getObject("regdate", LocalDateTime.class); 			
			
			// user_id 의 User 정보 가져오기
			UserDTO user = UserDTO.builder()
					.uid(rs.getInt("user_uid"))
					.username(rs.getString("user_username"))
					.password(rs.getString("user_password"))
					.name(rs.getString("user_name"))
					.authorities(rs.getString("user_authorities"))
					.regdate(rs.getObject("user_regdate", LocalDateTime.class))
					.build()
					;
			
			CommentDTO dto = CommentDTO.builder()
					.uid(uid)
					.content(content)
					.regDate(regDate)
					.user(user)
					.build()
					;
			
			list.add(dto);
			
		} // end while
		
		return list;
	}
	
	
	// 특정글의 댓글들 SELECT
	public List<CommentDTO> selectByWrite(int write_uid) throws SQLException {
		List<CommentDTO> list = null;
		try{
			pstmt = conn.prepareStatement(D.SQL_COMMENTS_BY_WRITE);
			pstmt.setInt(1, write_uid);
			rs = pstmt.executeQuery();
			list = buildList(rs);
		}finally{
			close();
		} // end try
		return list;		
	} // end selectByWrite()
	
	
	// 댓글 작성 <-- DTO
	public int insert(CommentDTO dto) throws SQLException {
		int cnt = 0;
		
		String content = dto.getContent();
		UserDTO user = dto.getUser();  // 댓글 작성자 (로그인 한 사용자)
		WriteDTO write = dto.getWrite();  // 댓글이 달린 글 
		
		
		int uid;  // auto-generated keys 값
		// auto-generated 컬럼
		String [] generatedCols = {"uid"};  

		try{	
			pstmt = conn.prepareStatement(D.SQL_COMMENT_INSERT, generatedCols);
			pstmt.setInt(1, user.getUid());
			pstmt.setInt(2, write.getUid());
			pstmt.setString(3, content);
			cnt = pstmt.executeUpdate();
			
			if(cnt > 0){
				// auto-generated keys 값 뽑아오기
				rs = pstmt.getGeneratedKeys();
				if(rs.next()) {
					uid = rs.getInt(1);
					dto.setUid(uid);  // DTO 에 set
				}
			}
			
		}finally{
			close();
		} // end try
		
		
		return cnt;
	} // end insert()
	
	
	// 특정 댓글 삭제하기 <-- uid
	public int deleteByUid(int uid) throws SQLException {
		int cnt = 0;
		
		try{
			pstmt=conn.prepareStatement(D.SQL_COMMENT_DELETE_BY_UID);
			pstmt.setInt(1, uid);
			cnt = pstmt.executeUpdate();
		}finally{
			close();
		} // end try
		
		return cnt;
	} // end deleteByUid()
	
	
	
} // end DAO

* command.comment
CmdListCommand.java
CmdWriteCommand.java
CmdDeleteCommand.java

 

* CommentController 생성
이번에는 페이지를 response 하는게 아니다. (viewPage 가 필요없다)
걍  각 Command 를 execute() 만 해주면 된다.

package 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;

import command.Command;
import command.comment.CmtDeleteCommand;
import command.comment.CmtListCommand;
import command.comment.CmtWriteCommand;

@WebServlet("/comment/*")
public class CommentController extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public CommentController() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doAction(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		doAction(request, response);
	}

	protected void doAction(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		System.out.println("CommentController.actionDo() 호출");
		
		String method = request.getMethod();
				
		// URL로부터 URI, ContextPath, Command 분리
		String uri = request.getRequestURI();
		String conPath = request.getContextPath() + "/comment";
		String com = uri.substring(conPath.length());
		
		// 테스트 출력
		System.out.println("uri: " + uri);  
		System.out.println("conPath: " + conPath);  
		System.out.println("com: " + com);

		Command command = null;   // 어떠한 로직을 수행할지

		switch(com) {
		case "/list":
			new CmtListCommand().execute(request, response);
			break;
		case "/write":
			if(method.equals("POST")) {
				new CmtWriteCommand().execute(request, response);
			}
			break;
		case "/delete":
			if(method.equals("POST")) {
				new CmtDeleteCommand().execute(request, response);
			}
			break;
		} // end switch
	
	
	} // end doAction()
	
	

} // end Controller

---

* Response 할 객체 준비 (공통)
일단 QryResult.java 객체
'공통적'으로  response 할 내용들을 필드로 담아서 정의
(@Data, @NoArgsConstructor  두개만)

package domain;

import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
public class QryResult {
	int count;  // 결괏값 
	String status;  // 결과 메세지
}

---

* Response 할 객체 준비 ( 댓글 List  Response)

package domain;

import java.util.List;

import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
public class QryCommentList extends QryResult {
	List<CommentDTO> list;
}

---

댓글 목록
특정글의 댓글 목록 가져와서 response 하기
CmtListCommand.java 작성

package command.comment;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.databind.ObjectMapper;

import command.Command;
import domain.CommentDAO;
import domain.CommentDTO;
import domain.QryCommentList;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

public class CmtListCommand implements Command {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) throws IOException {
		
//		AjaxWriteResult obj = AjaxWriteResult.builder()
//				.num(177)
//				.name("홍길동")
//				.arr(new int[] {10, 20, 30})
//				.cities(Arrays.asList("서울", "인천", "부산"))
//				.item2(new AjaxItem(10, "사과"))
//				.build()				
//				;
		
		int uid = Integer.parseInt(request.getParameter("uid"));
		
		QryCommentList obj = new QryCommentList();
		ObjectMapper mapper = new ObjectMapper();   // JSON 매핑할 Mapper 객체
		
		
		try {
			List<CommentDTO> list = new CommentDAO().selectByWrite(uid);
			obj.setCount(list.size());
			obj.setStatus("OK");
			obj.setList(list);
		} catch (SQLException e) {
			e.printStackTrace();
			obj.setStatus("댓글목록 ERROR: " + e.getMessage());
		}
		
		String output = mapper.writeValueAsString(obj);  // JSON String <- Java Object
		System.out.println("output : " + output);
		response.setContentType("application/json; charset=utf-8");  // MIME 설정
		response.getWriter().write(output);

	}

}

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
class AjaxWriteResult {
	private int num;   // -> number 로
	
	@JsonProperty("Name")  // JSON 으로 출력되는 이름지정
	private String name; // -> string
	private int[] arr;  // ->  array 로
	@JsonIgnore   // JSON 변환에서 제외
	private List<String> cities; // -> array 로 변환
	
	public int getAge() {
		return 43;
	}
	
	// -> object 로 변환
	AjaxItem item1;
	AjaxItem item2;
	
}

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
class AjaxItem {
	private int stock;
	private String product;
}

java.time.* 처리해주기
여기서 CommentDTO List 를 넣으면 response 할때 에러 난다.
1. Java8 의 Date/Time 객체를 위한 Jackson 의 추가 모듈 설치 +
2. java.time.* 에 대한 annotation  설정이  필요.하다
일단 server stop 하고 라이브러리 설치

 

* Jackson + java.time.*     annotaion 설정하기

* 불필요한 필드에 @JsonIgnore 처리하기

 

* 일단 하단에 댓글 html 기본양식 붙이기
board/comment.jsp 생성,  댓글 기본 양식
그리고 board/view.jsp  에 include 시키기 그리고 결과 확인 

 

* ROLE_MEMBER 가진 사용자만 댓글 작성 가능케 하기
comment.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>   
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %> 

<script src="https://kit.fontawesome.com/51772bd9bd.js" crossorigin="anonymous"></script>
    
<div class="container my-3 border rounded">
    <div class="mb-3 mt-3">
        <label>댓글: <span id="cmt_cnt"></span> 개</label>
        
        <%-- ROLE_MEMBER 권한 가진 사용자만 댓글 작성 가능 --%>
        <c:if test="${fn:contains(sessionScope.PRINCIPAL.authorities, 'ROLE_MEMBER') }">
        <div class="input-group my-2">
            <input type="text" class="form-control" id="input_comment">
            <button type="button" class="btn btn-outline-primary" id="btn_comment">작성</button>
        </div>
        </c:if>

        <table class="table table-hover mt-3" id="cmt_table">
            <thead>
              <tr>
                <th style="width: 16.66%">작성자</th>
                <th>내용</th>
                <th style="width: 16.66%">작성일</th>
              </tr>
            </thead>
            <tbody id="cmt_list">

            </tbody>
        </table>
    </div>
</div>    

 

* 확인 후에는 일부 내용 삭제하기
댓글개수, <tbody> 안의 내용

 

* js/view.js 파일 생성
* board/view.jsp 에는
 jQuery 와 함께 js 파일 포함
contextPath 와 현재 로그인한 user 의 uid 정보가 js 에 넘겨져야 하므로 jsp 파링레 설정
view.js 을 include 

 

* view.js  작성
ajax() request - response 하기
댓글 목록 렌더링 해주기
작성자 본인인 경우에만 삭제 버튼 보여주기
댓글 작성 input 은 ROLE_MEMBER 권한을 가진이에게만 보여주기

 

---

* 댓글 작성
다음의 항목들이 서버로 전달되어야 한다
1. 어느글에 대한 댓글인지?
2. 어느 사용자가 작성한 댓글인지?
3. 댓글 내용은 무엇인지?
서버에서 INSERT 트랜잭션후 다시 댓글 list 를 불러와서 update 를 해야 한다.

 

* 댓글 작성 버튼 누를시 동작
view.js 의 document.ready() 에 등록
1. 입력한 값 검증
2. 전달할 parameter 준비
3. ajax request - response
4. response 후 댓글 목록 업데이트

 

* CmtWriteCommand 작성
CmtWriteCommand.java 작성

 

package command.comment;

import java.io.IOException;
import java.sql.SQLException;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.fasterxml.jackson.databind.ObjectMapper;

import command.Command;
import domain.CommentDAO;
import domain.CommentDTO;
import domain.QryResult;
import domain.UserDTO;
import domain.WriteDTO;

public class CmtWriteCommand implements Command {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) throws IOException {
		int writeUid = Integer.parseInt(request.getParameter("write_uid"));  // 어느글에 대한 댓글?
		int userUid = Integer.parseInt(request.getParameter("user_uid"));  // 누가 쓰는 댓글?
		String content = request.getParameter("content");
		
		WriteDTO write = new WriteDTO();
		write.setUid(writeUid);
		UserDTO user = new UserDTO();
		user.setUid(userUid);

		CommentDTO dto = CommentDTO.builder()
				.write(write)
				.user(user)
				.content(content)
				.build();
		
		QryResult obj = new QryResult();  // response 할 Java 객체
		ObjectMapper mapper = new ObjectMapper();  // JSON 으로 매핑할 Mapper 객체
		
		try {
			int cnt = new CommentDAO().insert(dto);
			obj.setCount(cnt);
			obj.setStatus("OK");
		} catch (SQLException e) {
			e.printStackTrace();
			obj.setStatus("댓글 작성 ERROR: " + e.getMessage());
		}
		
		String output = mapper.writeValueAsString(obj);  // JSON 문자열 변환
		response.setContentType("application/json; charset=utf-8"); // MIME 설정
		response.getWriter().write(output);  // 전송
		
	}

}

---

* 댓글 삭제

1. 몇번(uid) 댓글이 삭제되는 것인가?
2. 삭제하기 전에 한번 confirm 시키기
3. 댓글 삭제 버튼
명심! : 댓글목록을 불러온 후에, 삭제 버튼에 대한 이벤트 리스터를 동작시켜야 한다.

 

view.js 에 addDelete() 함수

 

* CmtDeleteCommand 작성

package command.comment;

import java.io.IOException;
import java.sql.SQLException;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.fasterxml.jackson.databind.ObjectMapper;

import command.Command;
import domain.CommentDAO;
import domain.QryResult;

public class CmtDeleteCommand implements Command {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) throws IOException {
		int uid = Integer.parseInt(request.getParameter("uid"));
		
		QryResult obj = new QryResult();
		ObjectMapper mapper = new ObjectMapper();
		
		try {
			int cnt = new CommentDAO().deleteByUid(uid);
			obj.setCount(cnt);
			obj.setStatus("OK");
		} catch (SQLException e) {
			e.printStackTrace();
			obj.setStatus("댓글 삭제 ERROR: " + e.getMessage());
		}
		
		String output = mapper.writeValueAsString(obj);  // JSON 문자열 변환
		response.setContentType("application/json; charset=utf-8"); // MIME 설정
		response.getWriter().write(output);  // 전송

	}

}