JSP 기초(DB Join) - AWS 풀스택 과정 62일차

목차
1.	DB Join

 

1. DB Join

댓글 작업 : 비동기 처리(AJAX)

- 동기 방식 : 페이지 자체가 변화하는 방식(페이지의 내용을 새로 구성해서 전송)
- 비동기 방식: 정보만 변화하는 방식(데이터만 전송하는 방식)

 

Json-simple 1.1.1 다운로드 및 lib 폴더에 ctrl C, ctrl V

▣ sqlFile.sql

create table board(
  bno int auto_increment,
  title varchar(500) not null,
  writer varchar(100) not null,
  content text,
  regdate datetime default now(),
  moddate datetime default now(),
  primary key(bno));
  
  -- 2024.10.23
  -- 댓글(comment)
  create table comment(
  cno int auto_increment,
  bno int not null,
  content varchar(2000) not null,
  writer varchar(500) default "unknown",
  regdate datetime default now(),
  primary key(cno));

 

▣ mybatis.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/javadb"/>
        <property name="username" value="javaUser"/>
        <property name="password" value="mysql"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="mapper/boardMapper.xml"/>
    <mapper resource="mapper/commentMapper.xml"/>
  </mappers>
</configuration>

 

▣ CommentController.java

package controller;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

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 org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import domain.CommentVO;
import service.CommentService;
import service.CommentServiceImpl;


@WebServlet("/cmt/*")
public class CommentController extends HttpServlet {
	private static final long serialVersionUID = 1L;
    private static final Logger log = LoggerFactory.getLogger(CommentController.class);
    // 동기 통신이 아닌 비동기 통신(데이터만 요청한 곳으로 보내는 방식)
    // RequestDispatcher / destPage가 필요 없음.
    private CommentService csv;

    public CommentController() {
        csv = new CommentServiceImpl();
    }


	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// 실제 처리가 이루어지는 메서드
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		
		// setContentType은 설정할 필요 없음. = 동기에서 설정
		String uri = request.getRequestURI(); // /cmt/insert
		String path = uri.substring(uri.lastIndexOf("/")+1);
		log.info(">>> cmt path > {}", path);
		
		switch(path) {
		case "post" :
			try {
				// js에서 보낸 데이터를 읽어들이는 작업
				// js('Object) -> controller(String) => vommentVO로 변환
				// {"bno": "5","writer": "111", "content" : "11111"}
				StringBuffer sb = new StringBuffer();
				String line = "";
				BufferedReader br = request.getReader(); // request의 body값을 전송
				while((line = br.readLine()) != null) {
					sb.append(line);
					log.info(">>>>> sb > {}", sb.toString());
					
					// CommentVO 객채로 생성
					JSONParser parser = new JSONParser();
					JSONObject jsonObj = (JSONObject)parser.parse(sb.toString());
				
					log.info(">>> jsonObj > {}", jsonObj);
					// key : value 형태로 jsonObj 구성
					int bno = Integer.parseInt(jsonObj.get("bno").toString());
					String writer = jsonObj.get("writer").toString();
					String content = jsonObj.get("content").toString();
					
					CommentVO cvo = new CommentVO(bno, content, writer);
					int isOk = csv.post(cvo);
					log.info(" >>>>> post > {}", (isOk > 0 ? "성공" : "실패"));
					
					// 결과 데이터를 전송 => 화면으로 전송(response 객체의 body에 기록)
					PrintWriter out = response.getWriter();
					out.print(isOk);
				}
			} catch(Exception e) {
				log.info("comment post error!!");
				e.printStackTrace();
			}
			break;
			
		case "list" :
			try {
				int bno = Integer.parseInt(request.getParameter("bno"));
				List<CommentVO> list = csv.getList(bno);
				log.info(">>>>> list > {}", list);
				
				// list를 json 형태로 변환하여 보내기
				// [{...},{...},{...}] : [] JSONArray
				// {...} : JSONObject
				JSONArray jsonArray = new JSONArray();
				JSONObject[] jsonObjArr = new JSONObject[list.size()];
				
				for(int i = 0; i < list.size(); i++) {
					JSONObject json = new JSONObject();
					
//					jsonObjArr[i] = new JSONObject();
//					jsonObjArr[i].put("cno", list.get(i).getCno());
					
					json.put("cno", list.get(i).getCno());
					json.put("bno", list.get(i).getBno());
					json.put("writer", list.get(i).getWriter());
					json.put("content", list.get(i).getContent());
					json.put("regdate", list.get(i).getRegdate());
//					json.put("readCount", list.get(i).getReadCount());
					
					jsonObjArr[i] = json;
					jsonArray.add(jsonObjArr[i]);
					
				}
				// '[{...},{...},{...}]' => jsonArray -> String으로 변환하여 전송
				String jsonData = jsonArray.toJSONString();
				
				// print
				PrintWriter out = response.getWriter();
				out.print(jsonData);
				
			} catch (Exception e) {
				log.info("comment list error!!");
				e.printStackTrace();
			}
			break;
		case "modify" :
			try {
				StringBuffer sb = new StringBuffer();
				String line = "";
				BufferedReader br = request.getReader();
				while((line = br.readLine()) != null) {
					sb.append(line);
				}
					
					JSONParser parser = new JSONParser();
					JSONObject jsonObj = (JSONObject)parser.parse(sb.toString());
					
					int cno = Integer.parseInt(jsonObj.get("cno").toString());
					String content = jsonObj.get("content").toString();
					
					CommentVO cvo = new CommentVO(cno, content);
					int isOk = csv.modify(cvo);
					log.info(">>> comment modify > {}", (isOk > 0 ? "성공" : "실패"));
					
					PrintWriter out = response.getWriter();
					out.print(isOk);
			} catch (Exception e) {
				log.info("comment modify error!!");
				e.printStackTrace();
			}
			break;
		case "remove" :
			try {
				int cno = Integer.parseInt(request.getParameter("cno"));
				int isOk = csv.remove(cno);
				log.info(">>> comment remove > {}", (isOk > 0 ? "성공" : "실패"));
				
				PrintWriter out = response.getWriter();
				out.print(isOk);
			} catch (Exception e) {
				log.info("comment modify error!!");
				e.printStackTrace();
			}
			break;
		}
	}


	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// service에서 처리
		service(request,response);
	}


	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// service에서 처리
		doGet(request, response);
	}

}

 

▣ CommentService.java

package service;

import java.util.List;

import domain.CommentVO;

public interface CommentService {

	int post(CommentVO cvo);

	List<CommentVO> getList(int bno);

	int modify(CommentVO cvo);

	int remove(int cno);

}

 

▣ CommentServiceImpl.java

package service;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import controller.CommentController;
import dao.CommentDAO;
import dao.CommentDAOImpl;
import domain.CommentVO;

public class CommentServiceImpl implements CommentService {
	private static final Logger log = LoggerFactory.getLogger(CommentController.class);
	private CommentDAO cdao;
	
	public CommentServiceImpl() {
		cdao = new CommentDAOImpl();
	}

	@Override
	public int post(CommentVO cvo) {
		return cdao.post(cvo);
	}

	@Override
	public List<CommentVO> getList(int bno) {
		return cdao.getList(bno);
	}

	@Override
	public int modify(CommentVO cvo) {
		// TODO Auto-generated method stub
		return cdao.modify(cvo);
	}

	@Override
	public int remove(int cno) {
		// TODO Auto-generated method stub
		return cdao.delete(cno);
	}
}

 

▣ CommentDAO.java

package dao;

import java.util.List;

import domain.CommentVO;

public interface CommentDAO {

	int post(CommentVO cvo);

	List<CommentVO> getList(int bno);

	int modify(CommentVO cvo);

	int delete(int cno);

}

 

▣ CommentDAOImpl.java

package dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import controller.CommentController;	
import domain.CommentVO;
import orm.DatabaseBuilder;

public class CommentDAOImpl implements CommentDAO {
	private static final Logger log = LoggerFactory.getLogger(CommentController.class);
	private SqlSession sql;
	
	public CommentDAOImpl() {
		new DatabaseBuilder();
		sql = DatabaseBuilder.getFactory().openSession();
	}

	@Override
	public int post(CommentVO cvo) {
		log.info("comment dao post in!!");
		int isOk = sql.insert("CommentMapper.post", cvo);
		if(isOk>0) sql.commit();
		return isOk;
	}

	@Override
	public List<CommentVO> getList(int bno) {
		// TODO Auto-generated method stub
		return sql.selectList("CommentMapper.list", bno);
	}

	@Override
	public int modify(CommentVO cvo) {
		log.info("comment dao update in!!");
		int isOk = sql.update("CommentMapper.up", cvo);
		if(isOk > 0) sql.commit();
		return isOk;
	}

	@Override
	public int delete(int cno) {
		log.info("comment dao delete in!!");
		int isOk = sql.delete("CommentMapper.del", cno);
		if(isOk > 0) sql.commit();
		return isOk;
	}
}

 

▣ commentMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace = "CommentMapper">
  		<insert id="post">
  			insert into comment(bno, writer, content)
  			values(#{bno}, #{writer}, #{content})
  		</insert>
  		<select id="list" resultType="domain.CommentVO">
  			select * from comment where bno = #{bno} order by cno desc
  		</select>
  		<update id="up">
  			update comment set content = #{content}, regdate = now() where cno = #{cno}
  		</update>
  		<delete id="del">
  			delete from comment where cno = #{cno}
  		</delete>
  </mapper>

 

▣ detail.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>Board Detail Page</h1>
	<table>
		<thead>
			<tr>
				<th>no.</th>
				<td>${bvo.bno }</td>
			</tr>
			<tr>
				<th>title</th>
				<td>${bvo.title }</td>
			</tr>
			<tr>
				<th>writer</th>
				<td>${bvo.writer }</td>
			</tr>
			<tr>
				<th>readCount</th>
				<td>${bvo.readCount }</td>
			</tr>
			<tr>
				<th>regdate</th>
				<td>${bvo.regdate }</td>
			</tr>
			<tr>
				<th>moddate</th>
				<td>${bvo.moddate }</td>
			</tr>
			<tr>
				<th>content</th>
				<td>${bvo.content }</td>
			</tr>
		</thead>
	</table>
	<a href="/brd/modify?bno=${bvo.bno }"><button type="submit">update</button></a>
	<a href="/brd/delete?bno=${bvo.bno }"><button type="submit">delete</button></a>
	<a href="/brd/list"><button type="button">list</button></a>
	<hr>
	<!-- comment line -->
	<div>
		<h3>Comment Line</h3>
		<input type="text" id="cmtWriter" placeholder="writer..."><br>
		<input type="text" id="cmtText" placeholder="Add Comment...">
		<button type="button" id="cmtAddBtn">post</button>
	</div>
	<br>
	<hr>
	<!-- comment print line -->
	<div id="commentLine">
		<div>
			<div>cno, bno, writer, regdate</div>
			<div>
				<button>수정</button> <button>삭제</button><br>
				<input type="text" value="content">
			</div>
		</div>
	</div>
	<script type="text/javascript">
		const bnoVal = `<c:out value="${bvo.bno }" />`;
		console.log(bnoVal);
	</script>
	
	<script type="text/javascript" src="/resources/board_detail.js"></script>
	
	<!-- 댓글 리스트 호출 -->
	<script type="text/javascript">
		printList(bnoVal);
	</script>
</body>
</html>

 

▣ board_detail.js

/**
 * 
 */
console.log("board_detail.js in!!");
console.log(bnoVal);

// 등록
// 등록 버튼을 누르면 작성자와 댓글 내용의 값을 객체로 생성 => controller로 전송
document.getElementById("cmtAddBtn").addEventListener('click', () => {
	let cmtText = document.getElementById("cmtText").value;
	let cmtWriter = document.getElementById("cmtWriter").value;
	if(cmtText == null || cmtText == ''){
		alert("댓글을 입력해주세요.");
		return false;
	}
	// 댓글 객체 생성
	let cmtData = {
		bno : bnoVal,
		writer : cmtWriter,
		content : cmtText
	}
	// 댓글을 비동기로 전송
	postCommentToServer(cmtData).then(result => {
		console.log(result); // isOk
		if(result == '1'){
			alert("댓글등록성공!!");	
		}else{
			alert("댓글등록실패!!");
		}
		
		// 댓글 출력
		printList(bnoVal);
	});
	
});

function printList(bnoVal){
	getCommentListFromServer(bnoVal).then(result => {
				console.log(result);
				if(result.length > 0){
					printCommentList(result);					
				}else{
					let div = document.getElementById('commentLine');
					div.innerHTML = `<div> comment가 없습니다. </div>`
				}
			});
}


function printCommentList(result){
	let div = document.getElementById('commentLine');
	div.innerText=''; // 기존에 값이 있다면... 구조 지우기
	for(let i = 0; i < result.length; i++){
		let html = `<div>`;
		html += `<div>${result[i].cno}, ${result[i].bno}, ${result[i].writer}, ${result[i].regdate}</div>`;
		html += `<div>`;
		html += `<button type="button" data-cno="${result[i].cno}" class="cmtModBtn">수정</button>`;
		html += `<button type="button" data-cno="${result[i].cno}" class="cmtDelBtn">삭제</button><br>`;
		html += `<input type="text" class="cmtText" id="${result[i].cno}" value="${result[i].content}">`;
		html += `</div></div><hr>`;
		div.innerHTML += html; // 각 댓글 객체를 누적하여 담기
	}
}

// 화면에서 데이터를 만들어서 보내는 방법 = post
// 데이터를 보낼 때 method=post, headers(Content-Type), body를 작성해서 전송
// 서버에서 데이터를 주는 방법 = get
// Content-Type application/json; charset=utf-8

async function postCommentToServer(cmtData){
	try{
		console.log(cmtData);
		const url = "/cmt/post";
		const config = {
			method: 'post',
			headers:{
				'Content-Type':'application/json; charset=utf-8'
			},
			body: JSON.stringify(cmtData)
		};
		const resp = await fetch(url, config);
		const result = await resp.text(); // isOk 값을 text로 리턴
		return result;
	}catch(error){
		console.log(error);
	}
}

// list 가져오기 : 내 게시글에 달린 댓글만 가져오기
async function getCommentListFromServer(bno){
	try{
		const resp = await fetch("/cmt/list?bno="+bno);
		const result = await resp.json(); // 댓글 리스트 [{...},{...},{...}]
		return result;
	}catch(error){
		console.log(error);
	}
	
};

async function updateCommentToServer(cmtData){
	// 수정 : cno, content 객체를 보내서 isOk return => post
	try{
		const url = "/cmt/modify";
		const config = {
			method : 'post',
			headers : {
				'Content-Type':'application/json; charset=utf-8'
			},
			body: JSON.stringify(cmtData)
		}
		const resp = await fetch(url, config);
		const result = await resp.text();
		return result;
	}catch(error){
		console.log(error);
	}
}

async function removeCommentToServer(cnoVal){
	try{
		const resp = await fetch("/cmt/remove?cno="+cnoVal);
		const result = await resp.text();
		return result;
	}catch(error){
		console.log(error);
	}
}

document.addEventListener('click', (e)=>{
	console.log(e.target);
	console.log(e.target.dataset.cno);
	// 수정
	if(e.target.classList.contains('cmtModBtn')){
		// 수정에 대한 처리
		let cnoVal = e.target.dataset.cno;
		// cno 값을 id로 사용할 경우
		let cmtText = document.getElementById(cnoVal).value;
		console.log(cmtText);
		
		let cmtData = {
			cno : cnoVal,
			content : cmtText
		}
		
		updateCommentToServer(cmtData).then(result =>{
			console.log(result);
			if(result == '1'){
				alert("댓글 수정 성공");
			}else{				
				alert("댓글 수정 실패");
			}
			// 수정 후 수정된 내용 출력
			printList(bnoVal);
		});
		
		// 내 타겟을 기준으로 가장 가까운 div를 찾기 closest('div')
		// let div = e.target.closest('div');
		// console.log(div); // 내 댓글 객체 찾기
		// div.querySelector('.cmtText').value;
		// console.log(cmtText2);
	}
	// 삭제	
	if(e.target.classList.contains('cmtDelBtn')){
		// 삭제에 대한 처리
		let cnoVal = e.target.dataset.cno;
		// 삭제 비동기 함수 호출 result 받아서 alert 띄우기
		removeCommentToServer(cnoVal).then(result => {
			if(result == '1'){
				alert("삭제성공!!");
			}else{
				alert("삭제실패!!");
			}
			printList(bnoVal);
		});
		// 삭제 후 출력 메서드 호출
	}
});

 

▷ 출력

댓글 등록 및 리스트

 

댓글 수정 및 삭제