[Spring] 18. 방명록 만들기 ④ : 사진 미리보기, 덧글 기능

Recent Posts

Today

Total

관리 메뉴

오늘이라도

[Spring] 18. 방명록 만들기 ④ : 사진 미리보기, 덧글 기능 본문

취업성공패키지 SW 개발자 교육/Spring

[Spring] 18. 방명록 만들기 ④ : 사진 미리보기, 덧글 기능

upcake_ 2020. 7. 24. 09:33

https://github.com/upcake/Class_Examples

교육 중에 작성한 예제들은 깃허브에 올려두고 있습니다.

gif 파일은 클릭해서 보는 것이 정확합니다.

- 방명록 만들기 ④ : 사진 미리보기, 덧글 기능 -

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>board detail jsp</title>
<style type="text/css">
table td { word-break:break-all; } /* 영문으로'만' 이루어진 글 줄바꿈 되게끔 처리 */

#popup { 
	width: 350px;
 	height: 350px; 
 	position:absolute; 
 	left:50%; 
 	top:15%; 
 	transform:translate(-50%);
 	border: 3px solid #666;
 	border-radius: 50%;
 	z-index: 9999999999;
 	display: none;
}


#popup-background {
	position: absolute;
	left: 0;
	top: 0;
	width: 100%;
	height: 100%;
	background-color: #000;
	opacity: 0.3;
	display: none;
}

.popup{ width: 100%; height: 100%;}
#comment_regist span{ width:50%; float:left; }
</style>
</head>
<body>
<h3>방명록 상세 조회</h3>
<table>
	<tr>
		<th class="w-px160">제목</th>
		<td class="left" colspan="5" class="left">${vo.title }</td>
	</tr>
	<tr>
		<th>작성자</th>
		<td>${vo.name }</td>
		<th class="w-px100">작성일자</th>
		<td class="w-px100">${vo.writedate }</td>
		<th class="w-px80">조회수</th>
		<td class="w-px60">${vo.readcnt }</td>
	</tr>
	<tr>
		<th>내용</th>
		<td class="left" colspan="5">${fn:replace(vo.content, crlf, '<br>') }</td>
	</tr>
	<tr>
		<th>첨부 파일</th>
		<td class="left" colspan="5">
			<core:if test="${!empty vo.filename }">
				${vo.filename }
				<span id="preview"></span>
				<a href="download.bo?id=${vo.id }"><i class="fas fa-download font-img"></i></a>
			</core:if>
		</td>
	</tr>
</table>
<div class="btnSet">
	<a class="btn-fill" onclick="go_list()">목록으로</a>
	<!-- 작성자로 로그인한 경우만 수정/삭제 가능, 관리자는 삭제 가능 -->
	<core:if test="${login_info.id eq vo.writer}"> 
		<a class="btn-fill" onclick="$('form').attr('action', 'modify.bo'); $('form').submit()">수정</a>
	</core:if>
	<core:if test="${login_info.id eq vo.writer or login_info.admin eq 'Y' }"> 
		<a class="btn-fill" onclick="if( confirm('정말 삭제?') ) { $('form').attr('action', 'delete.bo'); $('form').submit(); } ">삭제</a>
	</core:if>
</div>

<div style="margin:0 auto; padding-top:20px; width:500px;">
	<div id="comment_regist">
		<span class="left">댓글 작성</span>
		<span class="right"><a class="btn-fill-s" onclick="comment_regist()">등록</a></span>
		<textarea id="comment" style="width:99%; height:60px; margin-top:5px; resize:none;"></textarea>
	</div>
	<div id="comment_list" style="text-align:left"></div>
</div>


<form method="post" action="list.bo">
	<input type="hidden" name="id" value="${vo.id }" />
	<input type="hidden" name="curPage" value="${page.curPage }" />
	<input type="hidden" name="search" value="${page.search }" />
	<input type="hidden" name="keyword" value="${page.keyword }" />
	<input type="hidden" name="viewType" value="${page.viewType }" />
	<input type="hidden" name="pageList" value="${page.pageList }" />
</form>
<div id="popup" onclick ="$('#popup, #popup-background').css('display', 'none')"></div>
<div id="popup-background"></div>
<script type="text/javascript">
function go_list() {
	$('form').submit();
}

function showAttachImage(id) {
	//첨부된 파일이 이미지인 경우 보여지게
	var filename = '${vo.filename}';
	var ext = filename.substring( filename.lastIndexOf('.') + 1 ).toLowerCase(); //확장자
	var imgs = [ 'gif', 'jpg', 'jpeg', 'png', 'bmg' ];
	if( imgs.indexOf(ext) > -1 ) {
		var img = '<img src="' + '${vo.filepath}'.substring(1) + '" '
			+ 'id="preview-img" ' 
			+ 'class="' + (id == '#popup' ? 'popup' : 'file-img') + '" '
			+ 'style="border-radius: 50%"/>';
		$(id).html(img);
	}
}

if( ${!empty vo.filename} ) {
	showAttachImage('#preview');
}

$('#preview-img').click(function() {
	$('#popup, #popup-background').css('display', 'block');
	showAttachImage('#popup');
});

function comment_regist() {
	if(${empty login_info}) {
		alert("댓글을 등록하려면 로그인하세요!");
		return;	
	}
	
	if( $("#comment").val() == "" ) {
		alert("댓글을 입력하세요!");
		$("#comment").focus();
		return;
	}

	$.ajax({
		url: "board/comment/insert",
		data: { pid:${vo.id}, content: $("#comment").val() },
		success: function(data) {
				if(data) {
					$("#comment").val('');
					comment_list();
				}
		},
		error: function(req, text) {
			alert(text + " : " + req.status)
		}
	});
}

function comment_list() {
	$.ajax({
		url: 'board/comment/${vo.id}',
		success: function(data) {
			$("#comment_list").html(data);
		},
		error: function(req, text) {
			alert(text + ' : ' + req.status);
		}
		
	});
}
comment_list();
</script>
</body>
</html>

▲detail.jsp

package com.hanul.iot;

import java.io.File;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import board.BoardCommentVO;
import board.BoardPage;
import board.BoardServiceImpl;
import board.BoardVO;
import common.CommonService;
import member.MemberVO;

@Controller
public class BoardController {
	@Autowired private BoardServiceImpl service;
	@Autowired private BoardPage page;
	@Autowired private CommonService common;
	
	//방명록 목록 화면 요청================================================================
	@RequestMapping("/list.bo")
	public String list(HttpSession session, Model model, @RequestParam(defaultValue = "1") int curPage,
			String search, String keyword, @RequestParam(defaultValue = "10") int pageList, 
			@RequestParam(defaultValue = "list") String viewType) {
		//DB에서 방명록 정보를 조회해와 목록 화면에 출력
		session.setAttribute("category", "bo");
		page.setCurPage(curPage);
		page.setSearch(search);
		page.setKeyword(keyword);
		page.setPageList(pageList);
		page.setViewType(viewType);
		
		model.addAttribute("page", service.board_list(page));
		
		return "board/list";
	} //list()
	
	//방명록 신규 화면 요청================================================================
	@RequestMapping("/new.bo")
	public String board() {
		//방명록 글쓰기 화면으로 연결
		return "board/new";
	} // board()
	
	//신규 방명록 저장 처리 요청================================================================
	@RequestMapping("/insert.bo")
	public String insert(BoardVO vo, MultipartFile file, HttpSession session) {
		//화면에서 입력한 정보를 DB에 저장한 후 목록 화면으로 연결
		if( !file.isEmpty() ) {
			vo.setFilename( file.getOriginalFilename() );
			vo.setFilepath(common.upload("board", file, session));
		}
		vo.setWriter( ((MemberVO) session.getAttribute("login_info")).getId() );
		service.board_insert(vo);
		return "redirect:list.bo";
	} //insert()
	
	//방명록 상세 화면 요청====================================================================
	@RequestMapping("/detail.bo")
	public String detail(int id, Model model) {
		//선택한 방명록 글을 DB에서 조회해와 상세 화면에 출력
		service.board_read(id);
		model.addAttribute("vo", service.board_detail(id));
		model.addAttribute("page", page);
		model.addAttribute("crlf", "\r\n");
		
		return "board/detail";
	} //detail()
	
	//방명록 상세 화면 요청====================================================================
	@ResponseBody @RequestMapping("/download.bo")
	public void download(int id, HttpSession session, HttpServletResponse response) {
		//해당 글의 첨부 파일 정보를 조회해와 다운로드한다.
		BoardVO vo = service.board_detail(id);
		common.download(vo.getFilename(), vo.getFilepath(), session, response);
	} //download()
	
	//방명록 수정 화면 요청====================================================================
	@RequestMapping("/modify.bo")
	public String modify(int id, Model model) {
		//선택한 방명록 글의 정보를 DB에서 조회해와 수정 화면에 출력
		model.addAttribute("vo", service.board_detail(id));
		return "board/modify";
	} //modify()
	
	//방명록 수정 화면 요청====================================================================
	@RequestMapping("/update.bo")
	public String update(BoardVO vo, MultipartFile file, HttpSession session, String attach, Model model) {
		//화면에서 입력한 정보를 DB에 변경, 저장한 후 상세 화면으로 연결
		BoardVO board = service.board_detail(vo.getId());
		String uuid = session.getServletContext().getRealPath("resources") + board.getFilepath();
		
		//파일을 첨부한 경우 - 없었는데 새로 첨부, 있었는데 바꿔 첨부
		if( !file.isEmpty() ) {
			vo.setFilename(file.getOriginalFilename());
			vo.setFilepath(common.upload("board", file, session));
			
			if( board.getFilename() != null ) {
				File f = new File(uuid);
				if( (f.exists()) ) { f.delete(); }
			}
		} else {
			//파일 첨부가 없는 경우 - if 없었고, else 있었는데 그대로 사용하는 경우
			if( attach.isEmpty() ) {  
				File f = new File(uuid);
				if( (f.exists()) ) { f.delete(); }
			} else {
				vo.setFilename(board.getFilename());
				vo.setFilepath(board.getFilepath());
			}
		}
		service.board_update(vo);
		
		//기존 방법
		//return "redirect:detail.bo?id=" + vo.getId();
		
		//다른 방법
		model.addAttribute("url", "detail.bo");
		model.addAttribute("id", vo.getId());
		return "board/redirect";
	} //update()
	
	//방명록 수정 화면 요청====================================================================
	@RequestMapping("/delete.bo")
	public String delete(int id, Model model) {
		//선택한 글을 DB에서 삭젷나 후 목록 화면으로 연결
		service.board_delete(id);
		model.addAttribute("url", "list.bo");
		model.addAttribute("id", id);
		model.addAttribute("page", page);
		
		return "board/redirect";
	} //delete()
	
	//댓글 저장 처리 요청====================================================================
	@ResponseBody @RequestMapping ("/board/comment/insert")
	public boolean comment_insert(BoardCommentVO vo, HttpSession session) {
		//화면에서 입력한 정보를 DB에 저장한다.
		vo.setWriter( ((MemberVO) session.getAttribute("login_info")).getId());
		return service.board_comment_insert(vo) > 0 ? true : false;
	} //comment_insert()
	
	//댓글 목록 조회 요청====================================================================
	@RequestMapping("/board/comment/{pid}")
	public String comment_list(@PathVariable int pid, Model model) {
		//DB에서 댓글 목록을 조회해와 댓글 목록 화면에 출력
		model.addAttribute("list", service.board_comment_list(pid));
		
		return "board/comment/list";
	} //comment_list()
} //class

▲BoardController.java

package board;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class BoardServiceImpl implements BoardService {
	@Autowired private BoardDAO dao;

	@Override
	public int board_insert(BoardVO vo) {
		return dao.board_insert(vo);
	}

	@Override
	public BoardPage board_list(BoardPage page) {
		return dao.board_list(page);
	}

	@Override
	public BoardVO board_detail(int id) {
		return dao.board_detail(id);
	}

	@Override
	public void board_read(int id) {
		dao.board_read(id);
	}

	@Override
	public int board_update(BoardVO vo) {
		return dao.board_update(vo);
	}

	@Override
	public int board_delete(int id) {
		return dao.board_delete(id);
	}

	@Override
	public int board_comment_insert(BoardCommentVO vo) {
		return dao.board_comment_insert(vo);
	}

	@Override
	public List<BoardCommentVO> board_comment_list(int pid) {
		return dao.board_comment_list(pid);
	}

	@Override
	public int board_comment_update(BoardCommentVO vo) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int board_comment_delete(int id) {
		// TODO Auto-generated method stub
		return 0;
	}

}

▲BoardServiceImpl.java

package board;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class BoardDAO implements BoardService {
	@Autowired private SqlSession sql;
	
	@Override
	public int board_insert(BoardVO vo) {
		return sql.insert("board.mapper.insert", vo);
	}

	@Override
	public BoardPage board_list(BoardPage page) {
		page.setTotalList((Integer) sql.selectOne("board.mapper.total", page));
		page.setList(sql.selectList("board.mapper.list", page));
		return page;
	}

	@Override
	public BoardVO board_detail(int id) {
		return sql.selectOne("board.mapper.detail", id);
	}

	@Override
	public void board_read(int id) {
		sql.update("board.mapper.read", id);
	}

	@Override
	public int board_update(BoardVO vo) {
		return sql.update("board.mapper.update", vo);
	}

	@Override
	public int board_delete(int id) {
		return sql.delete("board.mapper.delete", id);
	}

	@Override
	public int board_comment_insert(BoardCommentVO vo) {
		return sql.insert("board.mapper.comment_insert", vo);
	}

	@Override
	public List<BoardCommentVO> board_comment_list(int pid) {
		return sql.selectList("board.mapper.comment_list", pid);
	}

	@Override
	public int board_comment_update(BoardCommentVO vo) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int board_comment_delete(int id) {
		// TODO Auto-generated method stub
		return 0;
	}
}

▲BoardDAO.java

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="board.mapper">
	<select id="total" resultType="integer">
		SELECT COUNT(*) FROM board <include refid="search"/>
	</select>
	
	<select id="list" resultType="board.BoardVO">
		SELECT n.*, (SELECT name FROM member WHERE member.id=writer) name
		FROM (SELECT b.*, ROWNUM no
	   	 	  FROM (SELECT * FROM board <include refid="search"/> ORDER by id) b
			  ORDER BY no DESC) n
		WHERE no BETWEEN #{beginList } AND #{endList }
					
	</select>
	
	<sql id="search">
		<if test="search == 'title' or search == 'content'" >
			WHERE ${search } LIKE '%' || #{keyword } || '%'
		</if>
		<if test="search == 'writer'" >
			WHERE <include refid="writer" />
		</if>
		<if test="search == 'all'">
			WHERE TITLE LIKE '%' || #{keyword } || '%'
			OR content LIKE '%' || #{keyword } || '%'
			OR <include refid="writer" />
		</if>
	</sql>
	
	<sql id="writer">
		writer IN (SELECT id FROM member WHERE name LIKE '%' || #{keyword } || '%')
	</sql>
	
	<insert id="insert">
		INSERT INTO board(title, content, writer, filename, filepath)
		VALUES (#{title }, #{content}, #{writer}, #{filename, jdbcType=VARCHAR}, #{filepath, jdbcType=VARCHAR} )
	</insert>
	
	<update id="read">
		UPDATE BOARD SET readcnt= readcnt + 1 WHERE id= #{id }
	</update>
	
	<select id="detail" resultType="board.BoardVO">
		SELECT b.*, (SELECT name FROM member m WHERE m.id= b.writer) name
		FROM board b 
		WHERE id=#{id }
	</select>
	
	<update id="update">
		UPDATE board SET title=#{title }, content=#{content }, filename=#{filename, jdbcType=VARCHAR}, filepath=#{filepath, jdbcType=VARCHAR}
		WHERE id=#{id }
	</update>
	
	<delete id="delete">
		DELETE FROM board WHERE id=#{id}
	</delete>
	
	<insert id="comment_insert">
		INSERT INTO board_comment(content, pid, writer)
		VALUES (#{content}, #{pid}, #{writer})
	</insert>
	
	<select id="comment_list" resultType="board.BoardCommentVO">
		SELECT c.*, (SELECT name FROM member m WHERE m.id=writer) name,
				TO_CHAR(writedate, 'yyyy-mm-dd hh24:mi:ss') writedate
		FROM board_comment c
		WHERE pid=#{pid}
		ORDER BY id
	</select>
</mapper>

▲board-mapper.xml

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<form method="post" action="${url }">
<input type="text" name="id"  value="${id }"/>
<input type="text" name="curPage" value="${page.curPage }" />
<input type="text" name="search" value="${page.search }" />
<input type="text" name="keyword" value="${page.keyword }" />
<input type="text" name="viewType" value="${page.viewType }" />
<input type="text" name="pageList" value="${page.pageList }" />
</form>
<script>
$('form').submit();
</script>

▲redirect.jsp

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:beans="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd
		http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">

	<!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
	
	<!-- Enables the Spring MVC @Controller programming model -->
	<annotation-driven />

	<!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->
	<resources mapping="/resources/**" location="/resources/" />
	
	
	<!-- resources를 따로 지정하지 않아도 바로 css폴더로 연결되게끔 지정 -->
	<!-- 경로지정만 해도 css폴더의 하위 파일들을 모두 적용되게끔 지정 -->
	<resources location="/resources/css/" mapping="/css/**" />
	
	<!-- js폴더, 파일 맵핑 -->
	<resources location="/resources/js/" mapping="/js/**" />

	<!-- images 폴더 맵핑 -->
	<resources location="/resources/images/" mapping="/img/**" />
	
	<resources location="/resources/upload/" mapping="/upload/**" />
	
	<!-- 화면을 어떻게 연결할 것인지 선언하는 부분 1순위 -->
	<beans:bean class="org.springframework.web.servlet.view.tiles3.TilesViewResolver">
		<beans:property name="order" value="0" />
	</beans:bean>
	
	<!-- 만들어둔 layout과 tiles를 쓰기 위해 선언하는 부분 -->
	<beans:bean class="org.springframework.web.servlet.view.tiles3.TilesConfigurer">
		<beans:property name="definitions" value="/WEB-INF/views/tiles/tiles.xml" />
	</beans:bean>
	
	<!-- 화면을 어떻게 연결할 것인지 선언하는 부분 2순위 -->
	<!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->
	<beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<beans:property name="prefix" value="/WEB-INF/views/" />
		<beans:property name="suffix" value=".jsp" />
		<beans:property name="order" value="1" />
	</beans:bean>
	
	<context:component-scan base-package="com.hanul.iot" />
	
</beans:beans>

▲servlet-context.xml

--테이블 생성
CREATE TABLE customer (
	id		NUMBER CONSTRAINT customer_id_pk PRIMARY KEY,
	name	VARCHAR2(50) NOT NULL,
	gender	VARCHAR2(3) NOT NULL,
	email	VARCHAR2(50),
	phone	VARCHAR2(13)
);

--시퀀스 생성
CREATE SEQUENCE seq_customer START WITH 1 INCREMENT BY 1;

--레코드 삽입
INSERT INTO customer(id, name, gender)
VALUES (seq_customer.NEXTVAL, '홍길동', '남');

INSERT INTO customer(name, gender)
VALUES ('심청', '여');

--트리거(trigger) 설정
CREATE OR REPLACE TRIGGER trg_customer
    BEFORE INSERT ON customer   --커스터머 테이블에 인서트가 되기전에
    FOR EACH ROW    --모든 행에 대하여
BEGIN   --시작한다
    SELECT seq_customer.NEXTVAL INTO :new.id FROM dual;  --시퀀스의 데이터를 담고있는 테이블은 없으므로 더미 테이블(dual)에서 조회한다.
END;    
/       --끝 슬래쉬까지 써줘야한다


drop trigger trg_customer;
CREATE OR REPLACE TRIGGER trg_customer
    BEFORE INSERT ON customer 
    FOR EACH ROW 
BEGIN 
    SELECT seq_customer.NEXTVAL INTO :new.id FROM dual; 
END;    
/

--조회
SELECT * FROM customer;

--커밋
COMMIT;



--20/07/02==================================================================
--회원 관리 테이블
CREATE TABLE member(
    irum    VARCHAR2(20)    NOT NULL,
    id      VARCHAR2(20)    CONSTRAINT member_id_pk PRIMARY KEY,
    pw      VARCHAR2(20)    NOT NULL,
    age     NUMBER,
    gender  VARCHAR2(3)     NOT NULL,
    birth   DATE,
    post    VARCHAR2(7),
    addr    VARCHAR2(50),
    email   VARCHAR2(50)    NOT NULL,   --유니크가 들어간다 생각하고 NOT NULL만 지정
    tel     VARCHAR2(20),
    admin   VARCHAR2(1)     DEFAULT 'N'
);

--기존에 있던 member 테이블 수정
ALTER TABLE member
ADD(
    gender  VARCHAR2(3) DEFAULT '남' NOT NULL,
    birth   DATE,
    post    VARCHAR2(7),
    email   VARCHAR2(50),
    admin   VARCHAR2(1) default 'N'
);

UPDATE MEMBER SET email = id || '@naver.com';

ALTER TABLE member
MODIFY (irum NOT NULL, pw NOT NULL, email NOT NULL);

ALTER TABLE member RENAME COLUMN irum TO name;

ALTER TABLE member ADD CONSTRAINT member_id_pk PRIMARY KEY(id);

--관리자 회원 정보 저장
INSERT INTO member(name, id, pw, age, gender, email, admin)
VALUES ('관리자', 'admin', '1234', 25, '남', 'admin@admin.com', 'Y');

SELECT * FROM member;

--20/07/10======================================================================
CREATE TABLE notice(
    id          NUMBER CONSTRAINT notice_id_pk PRIMARY KEY,
    title       VARCHAR2(300) NOT NULL,
    content     VARCHAR2(4000) NOT NULL,
    writer      VARCHAR2(20) NOT NULL,
    writedate   DATE DEFAULT SYSDATE,
    readcnt     NUMBER DEFAULT 0,
    filename    VARCHAR2(300),
    filepath    VARCHAR2(300)
);

CREATE SEQUENCE seq_notice
START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trg_notice
    BEFORE INSERT ON notice
    FOR EACH ROW
BEGIN
    SELECT seq_notice.NEXTVAL INTO:NEW.id FROM dual;
END;
/

SELECT * FROM notice;

INSERT INTO notice(title, content, writer)
VALUES ('공지 글 테스트', '테스트 공지 글 입니다.', 'admin');

COMMIT;

--20/07/15================================================
--테이블에 공지글 항목 추가
INSERT INTO notice(title, content ,writer, writedate, filepath, filename)
SELECT title, content, writer, writedate, filepath, filename FROM notice;

COMMIT;

--20/07/16================================================
--notice 테이블에 칼럼 추가
CREATE TABLE notice(
    id          NUMBER CONSTRAINT notice_id_pk PRIMARY KEY,
    title       VARCHAR2(300) NOT NULL,
    content     VARCHAR2(4000) NOT NULL,
    writer      VARCHAR2(20) NOT NULL,
    writedate   DATE DEFAULT SYSDATE,
    readcnt     NUMBER DEFAULT 0,
    filename    VARCHAR2(300),
    filepath    VARCHAR2(300)
    root        NUMBER,
    step        NUMBER default 0,
    indent      NUMBER default 0 
);

ALTER TABLE notice
ADD(root NUMBER, step NUMBER DEFAULT 0, indent NUMBER DEFAULT 0);

UPDATE notice SET root = id;

--root : 원글의 root
--step : 원글 step + 1 / 원글의 step보다 더 큰 step을 가진 글이 있다면 그 글들의 step을 먼저 +1 한다.
--indent : 원글 indent + 1

ALTER TRIGGER trg_notice DISABLE;

SELECT * FROM notice;

COMMIT;

--20/07/20======================================================================================================================
INSERT INTO member(id, pw, gender, email, name, admin)
VALUES ('admin2', '1234', '남', 'admin2@admin@.com', '운영자', 'Y');

UPDATE notice SET writer='admin2'
WHERE mod(id, 3) = 0;

COMMIT;

--qna 테이블 생성
CREATE TABLE qna(
    id          NUMBER CONSTRAINT qna_id_pk PRIMARY KEY,
    title       VARCHAR2(300) NOT NULL,
    content     VARCHAR2(4000) NOT NULL,
    writer      VARCHAR2(20) NOT NULL,
    writedate   DATE DEFAULT SYSDATE,
    readcnt     NUMBER DEFAULT 0,
    filename    VARCHAR2(300),
    filepath    VARCHAR2(300),
    root        NUMBER,
    step        NUMBER default 0,
    indent      NUMBER default 0 
);

CREATE SEQUENCE seq_qna
START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trg_qna
    BEFORE INSERT ON qna
    FOR EACH ROW
BEGIN
    SELECT seq_qna.NEXTVAL INTO:NEW.id FROM dual;
END;
/

INSERT INTO qna (id, title, content, writer)
VALUES (1, '첫 글 테스트', 'ㅁㄴㅇㄻㄴㅇㄹ', '관리자');

--20/07/21======================================================================================================================
INSERT INTO qna(title, content, writer, writedate, filepath, filename)
SELECT title, content, writer, writedate, filepath, filename FROM qna;

UPDATE qna SET root = id;

ALTER TRIGGER trg_qna DISABLE

UPDATE qna SET writer='admin2'
WHERE mod(id, 3) = 0;

COMMIT;

--방명록 관리
CREATE TABLE board(
	id			NUMBER			CONSTRAINT board_id_pk PRIMARY KEY,
	title		VARCHAR2(300)	NOT NULL,
	content		VARCHAR2(4000)	NOT NULL,
	writer		VARCHAR2(100)	NOT NULL,
	writedate	DATE DEFAULT SYSDATE,
	readcnt		NUMBER DEFAULT 0,
	filename	VARCHAR2(300),
	filepath	VARCHAR2(300)
);

CREATE SEQUENCE seq_board START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trg_board
	BEFORE INSERT ON board
	FOR EACH ROW
BEGIN
	SELECT seq_board.NEXTVAL INTO :new.id FROM dual;
END;
/

COMMIT;

--20/07/22========================================================
INSERT INTO board (title, content, writer, filename, filepath)
SELECT title, content, writer, filename, filepath
FROM board;

--20/07/24========================================================
CREATE TABLE board_comment (
	id NUMBER constraint board_comment_id_pk PRIMARY KEY,
	pid NUMBER NOT NULL, /* 원글의 아이디 */
	writer VARCHAR2(20) NOT NULL, /* 댓글 작성자 아이디 */
	content VARCHAR2(4000) NOT NULL,
    writedate DATE DEFAULT SYSDATE,
    CONSTRAINT board_comment_pid_fk FOREIGN KEY(pid) REFERENCES board(id) ON DELETE CASCADE,
    CONSTRAINT board_comment_writer_fk FOREIGN KEY(writer) REFERENCES member(id) ON DELETE CASCADE
);

CREATE SEQUENCE seq_board_comment
START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trg_board_comment
    BEFORE INSERT ON board_comment
    FOR EACH ROW
BEGIN
    SELECT seq_board_comment.NEXTVAL INTO :NEW.id FROM dual;
END;
/

COMMIT;

▲table.sql

package board;

import java.util.List;

public interface BoardService {
	//CRUD
	int board_insert(BoardVO vo);
	BoardPage board_list(BoardPage page);
	BoardVO board_detail(int id);
	void board_read(int id);
	int board_update(BoardVO vo);
	int board_delete(int id);
	
	int board_comment_insert(BoardCommentVO vo);
	List<BoardCommentVO> board_comment_list(int pid);
	int board_comment_update(BoardCommentVO vo);
	int board_comment_delete(int id);
}

▲BoardService.java

package board;

public class BoardCommentVO {
	private int id, pid;
	private String writer, name, content;
	private String writedate;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public int getPid() {
		return pid;
	}
	public void setPid(int pid) {
		this.pid = pid;
	}
	public String getWriter() {
		return writer;
	}
	public void setWriter(String writer) {
		this.writer = writer;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getWritedate() {
		return writedate;
	}
	public void setWritedate(String writedate) {
		this.writedate = writedate;
	}
}

▲BoardCommentVO.java

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="core" %>
<core:forEach items="${list }" var="vo" varStatus="status"> 
	<!-- varStatus 인덱스값 찾아갈때 사용하는 속성 -->
	${status.index eq 0 ? '<hr>' : '' }
	<div>
		${vo.name } [${vo.writedate }]
		<div>
			${vo.content }
		</div>
	</div>
	<hr>
</core:forEach>

▲list.jsp

저작자표시 비영리 동일조건

'취업성공패키지 SW 개발자 교육 > Spring' 카테고리의 다른 글

[Spring] 20. 공공 데이터 약국 API 출력, 구글 지도 API로 구글 지도 활용 (2)	2020.07.28
[Spring] 19. 방명록 만들기 ⑤ : 덧글 수정, 삭제 / 공공 데이터 API 가져오기 (0)	2020.07.27
[Spring] 17. 방명록 만들기 ③ : 글 상세 조회, 수정 (0)	2020.07.23
[Spring] 16. 방명록 만들기 ② : 첨부파일 미리보기, 게시글 표시 갯수, 게시판 표시 형태 (0)	2020.07.22
[Spring] 15. 미니 프로젝트 : Q&A 게시판 만들기 / 방명록 만들기 (1)	2020.07.21

'취업성공패키지 SW 개발자 교육/Spring' Related Articles

오늘이라도

[Spring] 18. 방명록 만들기 ④ : 사진 미리보기, 덧글 기능 본문

[Spring] 18. 방명록 만들기 ④ : 사진 미리보기, 덧글 기능

- 방명록 만들기 ④ : 사진 미리보기, 덧글 기능 -

'취업성공패키지 SW 개발자 교육 > Spring' 카테고리의 다른 글

티스토리툴바