오늘이라도
[Spring] 15. 미니 프로젝트 : Q&A 게시판 만들기 / 방명록 만들기 본문
반응형
https://github.com/upcake/Class_Examples
교육 중에 작성한 예제들은 깃허브에 올려두고 있습니다.
gif 파일은 클릭해서 보는 것이 정확합니다.
- Q&A 게시판 만들기 -
package com.hanul.iot;
import java.io.File;
import java.util.HashMap;
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.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import common.CommonService;
import member.MemberServiceImpl;
import member.MemberVO;
import qna.QnaPage;
import qna.QnaServiceImpl;
import qna.QnaVO;
@Controller
public class QnaController {
@Autowired private QnaServiceImpl service;
@Autowired private CommonService common;
@Autowired private MemberServiceImpl member;
@Autowired private QnaPage page;
//글 목록
@RequestMapping("/list.qna")
public String list(Model model, HttpSession session, @RequestParam(defaultValue = "1") int curPage, String search, String keyword) {
//QNA 클릭 하면 admin으로 자동 로그인
HashMap<String, String> map = new HashMap<String, String>();
//HashMap : 데이터를 담을 자료 구조
map.put("id", "admin");
map.put("pw", "1234");
session.setAttribute("login_info", member.member_login(map));
session.setAttribute("category", "qna");
//DB에서 글 목록 조회해와 화면에 출력
page.setCurPage(curPage);
page.setSearch(search);
page.setKeyword(keyword);
model.addAttribute("page", service.qna_list(page));
return "qna/list";
}
//신규 글 작성 화면 요청=========================================================
@RequestMapping("/new.qna")
public String qna() {
return "qna/new";
}
//신규 글 저장 처리 요청
@RequestMapping("/insert.qna")
public String insert(MultipartFile file, QnaVO vo, HttpSession session) {
//첨부한 파일을 서버 시스템에 업로드하는 처리
if(!file.isEmpty()) {
vo.setFilepath(common.upload("qna", file, session));
vo.setFilename(file.getOriginalFilename());
}
vo.setWriter( ((MemberVO) session.getAttribute("login_info")).getId() );
//화면에서 입력한 정보를 DB에 저장한 후
service.qna_insert(vo);
//목록 화면으로 연결
return "redirect:list.qna";
}
//QNA 글 상세 화면 요청
@RequestMapping("/detail.qna")
public String detail(int id, Model model) {
//선택한 QNA 글에 대한 조회수 증가 처리
service.qna_read(id);
//선택한 QNA 글 정보를 DB에 조회해와 상세 화면에 출력
model.addAttribute("vo", service.qna_detail(id));
model.addAttribute("crlf", "\r\n");
model.addAttribute("page", page);
return "qna/detail";
} //detail()
//첨부 파일 다운로드 요청
@ResponseBody @RequestMapping("/download.qna")
public void download(int id, HttpSession session, HttpServletResponse response) {
QnaVO vo = service.qna_detail(id);
common.download(vo.getFilename(), vo.getFilepath(), session, response);
} // download()
//QNA 글 삭제 처리 요청
@RequestMapping("/delete.qna")
public String delete(int id, HttpSession session) {
//선택한 QNA 글에 첨부한 파일이 있다면 서버의 물리적 영역에서 해당 파일도 삭제한다
QnaVO vo = service.qna_detail(id);
if(vo.getFilepath() != null) {
File file = new File(session.getServletContext().getRealPath("resources") + vo.getFilepath());
if( file.exists() ) { file.delete(); }
}
//선택한 QNA 글을 DB에서 삭제한 후 목록 화면으로 연결
service.qna_delete(id);
return "redirect:list.qna";
} //delete()
//QNA 글 수정 화면 요청
@RequestMapping("/modify.qna")
public String modify(int id, Model model) {
//선택한 QNA 글 정보를 DB에서 조회해와 수정 화면에 출력
model.addAttribute("vo", service.qna_detail(id));
return "qna/modify";
} //modify()
//QNA 글 수정 처리 요청
@RequestMapping("/update.qna")
public String update(QnaVO vo, MultipartFile file, HttpSession session, String attach) {
//원래 글의 첨부 파일 관련 정보를 조회
QnaVO qna = service.qna_detail(vo.getId());
String uuid = session.getServletContext().getRealPath("resources") + qna.getFilepath();
//파일을 첨부한 경우 - 없었는데 첨부 / 있던 파일을 바꿔서 첨부
if(!file.isEmpty()) {
vo.setFilename(file.getOriginalFilename());
vo.setFilepath(common.upload("qna", file, session));
//원래 있던 첨부 파일은 서버에서 삭제
if(qna.getFilename() != null) {
File f = new File(uuid);
if (f.exists()) { f.delete(); }
}
} else {
//원래 있던 첨부 파일을 삭제됐거나 원래부터 첨부 파일이 없었던 경우
if(attach.isEmpty()) {
//원래 있던 첨부 파일은 서버에서 삭제
if(qna.getFilename() != null) {
File f = new File(uuid);
if (f.exists()) { f.delete(); }
}
} else { //원래 있던 첨부 파일을 그대로 사용하는 경우
vo.setFilename(qna.getFilename());
vo.setFilepath(qna.getFilepath());
}
}
//화면에서 변경한 정보를 DB에 저장한 후 상세 화면으로 연결
service.qna_update(vo);
return "redirect:detail.qna?id=" + vo.getId();
} //update()
//답글 쓰기 화면 요청==================================================================
@RequestMapping("/reply.qna")
public String reply(Model model, int id) {
//원글의 정보를 답글 쓰기 화면에서 알 수 있도록 한다.
model.addAttribute("vo", service.qna_detail(id));
return "qna/reply";
} //reply()
//신규 답글 저장 처리 요청==============================================================
@RequestMapping("/reply_insert.qna")
public String reply_insert(QnaVO vo, HttpSession session, MultipartFile file) {
if(!file.isEmpty()) {
vo.setFilename(file.getOriginalFilename());
vo.setFilepath(common.upload("qna", file, session));
}
vo.setWriter(((MemberVO) session.getAttribute("login_info")).getId());
//화면에서 입력한 정보를 DB에 저장한 후 목록 화면으로 연결
service.qna_reply_insert(vo);
return "redirect:list.qna";
} //reply_insert()
}
▲QnAController.java
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="core" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>qna list JSP</title>
</head>
<body>
<h3>Q&A</h3>
<form method="post" action="list.qna" id="list">
<input type="hidden" name="curPage" value="1" />
<div id="list-top">
<div>
<ul>
<li>
<select name="search" class="w-px80">
<option value="all" ${page.search eq 'all' ? 'selected' : '' }>전체</option>
<option value="title" ${page.search eq 'title' ? 'selected' : '' }>제목</option>
<option value="content" ${page.search eq 'content' ? 'selected' : '' }>내용</option>
<option value="writer" ${page.search eq 'writer' ? 'selected' : '' }>작성자</option>
</select>
</li>
<li><input value="${page.keyword }" type="text" name="keyword" class="w-px300" /></li>
<li><a class="btn-fill" onclick="$('form').submit()">검색</a></li>
</ul>
<ul>
<core:if test="${login_info.admin eq 'Y' }">
<li><a class="btn-fill" href="new.qna">글쓰기</a></li>
</core:if>
</ul>
</div>
</div>
</form>
<table>
<tr>
<th class="w-px60">번호</th>
<th>제목</th>
<th class="w-px100">작성자</th>
<th class="w-px120">작성일자</th>
<th class="w-px60">첨부파일</th>
</tr>
<core:forEach items="${page.list }" var="vo">
<tr>
<td>${vo.no }</td>
<td class="left">
<core:forEach var="i" begin="1" end="${vo.indent }">
${i eq vo.indent ? "<img src='img/re.gif' />" : " " }
</core:forEach>
<a href="detail.qna?id=${vo.id }" >${vo.title }</a>
</td>
<td>${vo.writer }</td>
<td>${vo.writedate }</td>
<td>
<core:if test="${!empty vo.filename }">
<a href="download.qna?id=${vo.id }">
<img title="${vo.filename }" class="file-img" src="img/attach.png" />
</a>
</core:if>
</td>
</tr>
</core:forEach>
</table>
<div class="btnSet">
<jsp:include page="/WEB-INF/views/include/page.jsp"/>
</div>
</body>
</html>▲list.jsp
package qna;
import java.util.List;
public interface QnaService {
//CRUD
void qna_insert(QnaVO vo); //글 저장
List<QnaVO> qna_list(); //목록 조회
QnaPage qna_list(QnaPage page); //페이지 처리 된 공지글 목록 조회
QnaVO qna_detail(int id); //상세 조회
void qna_update(QnaVO vo); //글 수정
void qna_delete(int id); //글 삭제
void qna_read(int id); //조회수 증가 처리
void qna_reply_insert(QnaVO vo);//답글 저장
}
▲QnaService.java
package qna;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class QnaServiceImpl implements QnaService {
@Autowired private QnaDAO dao;
@Override
public void qna_insert(QnaVO vo) {
dao.qna_insert(vo);
}
@Override
public List<QnaVO> qna_list() {
return dao.qna_list();
}
@Override
public QnaVO qna_detail(int id) {
return dao.qna_detail(id);
}
@Override
public void qna_update(QnaVO vo) {
dao.qna_update(vo);
}
@Override
public void qna_delete(int id) {
dao.qna_delete(id);
}
@Override
public void qna_read(int id) {
dao.qna_read(id);
}
@Override
public void qna_reply_insert(QnaVO vo) {
dao.qna_reply_insert(vo);
}
@Override
public QnaPage qna_list(QnaPage page) {
return dao.qna_list(page);
}
}
▲QnaServiceImpl.java
package qna;
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 QnaDAO implements QnaService {
@Autowired private SqlSession sql;
@Override
public void qna_insert(QnaVO vo) {
sql.insert("qna.mapper.insert", vo);
}
@Override
public List<QnaVO> qna_list() {
return sql.selectList("qna.mapper.list");
}
@Override
public QnaVO qna_detail(int id) {
return sql.selectOne("qna.mapper.detail", id);
}
@Override
public void qna_update(QnaVO vo) {
sql.update("qna.mapper.update", vo);
}
@Override
public void qna_delete(int id) {
sql.delete("qna.mapper.delete", id);
}
@Override
public void qna_read(int id) {
sql.update("qna.mapper.read", id);
}
@Override
public void qna_reply_insert(QnaVO vo) {
sql.insert("qna.mapper.reply_insert", vo);
}
@Override
public QnaPage qna_list(QnaPage page) {
page.setTotalList((Integer) sql.selectOne("qna.mapper.totalList", page));
page.setList(sql.selectList("qna.mapper.list", page));
return page;
}
}
▲QnaDAO.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="qna.mapper" >
<insert id="insert">
INSERT INTO qna (id, root, title, content, writer, filename, filepath)
VALUES (seq_qna.NEXTVAL, seq_qna.CURRVAL, #{title }, #{content }, #{writer }, #{filename, jdbcType=VARCHAR }, #{filepath, jdbcType=VARCHAR })
</insert>
<select id="detail" resultType="qna.QnaVO">
SELECT q.*, (SELECT name FROM member m WHERE m.id = q.writer) name
FROM qna q
WHERE id=#{id }
</select>
<update id="read">
UPDATE qna SET readcnt = readcnt + 1 WHERE id=#{id }
</update>
<delete id="delete">
DELETE FROM qna WHERE id=#{id }
</delete>
<update id="update">
UPDATE qna SET title = #{title }, content = #{content }, filename = #{filename, jdbcType=VARCHAR }, filepath = #{filepath, jdbcType=VARCHAR }
WHERE id = #{id }
</update>
<select id="list" resultType="qna.QnaVO">
SELECT *
FROM (SELECT ROWNUM no, q.*, (SELECT name FROM member m WHERE m.id = writer) name
FROM (SELECT * FROM qna <include refid="search_where" /> ORDER BY root, step DESC) q
ORDER BY no DESC) q
WHERE no BETWEEN #{beginList } and #{endList }
</select>
<select id="totalList" resultType="integer">
SELECT COUNT(*) FROM qna <include refid="search_where"/>
</select>
<insert id="reply_insert">
<!-- 원글의 step보다 더 큰 step을 가진 글이 있다면 그 글들의 step을 먼저 +1 한다. -->
<![CDATA[
{CALL DECLARE BEGIN
UPDATE qna SET step = step + 1
WHERE root = #{root } AND step > #{step };
INSERT INTO qna (id, root, title, content, writer, step, indent, filename, filepath)
VALUES (seq_qna.NEXTVAL, #{root }, #{title }, #{content }, #{writer }, #{step } + 1, #{indent } + 1, #{filename, jdbcType=VARCHAR }, #{filepath, jdbcType=VARCHAR });
END}
]]>
</insert>
<sql id="search_where">
<if test="search == 'title' or search == 'content'">
WHERE ${search } LIKE '%' || #{keyword } || '%'
</if>
<if test="search == 'writer'">
WHERE <include refid="search_writer" />
</if>
<if test="search == 'all'">
WHERE title LIKE '%' || #{keyword } || '%'
OR content LIKE '%' || #{keyword } || '%'
OR <include refid="search_writer" />
</if>
</sql>
<sql id="search_writer">
writer IN (SELECT id FROM member WHERE name LIKE '%' || #{keyword } || '%')
</sql>
</mapper>▲qna-mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd">
<!-- Root Context: defines shared resources visible to all other web components -->
<import resource="classpath:data/*.xml" />
<!-- context에 member를 적음으로써 Autowired의 스캔 범위에 member가 추가된다. -->
<!-- 20/07/09 common 추가 -->
<!-- 20/07/10 notice 추가 -->
<!-- 20/07/20 qna 추가 -->
<context:component-scan base-package="qna ,notice, common, member, customer"/>
</beans>
▲root-context.xml
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>qna new jsp</title>
</head>
<body>
<h3>신규 QNA</h3>
<!--
파일 첨부 시 form 태그의 필요 속성
1. 반드시 method가 post
2. enctype을 지정 ▶ enctype='multipart/form-data'
-->
<form action="insert.qna" method="post" enctype="multipart/form-data">
<table>
<tr>
<th class="w-px160">제목</th>
<td><input type="text" name="title" class="need" /></td>
</tr>
<tr>
<th>작성자</th>
<td>${login_info.name }</td>
</tr>
<tr>
<th>내용</th>
<td><textarea name="content" class="need"></textarea></td>
</tr>
<tr>
<th>파일 첨부</th>
<td class="left">
<label>
<input type="file" name="file" id="attach-file" />
<img src="img/select.png" class="file-img" />
</label>
<span id="file-name"></span>
<span id="delete-file" style="color: red; margin-lefT: 20px;"><i class="fas fa-times font-img" ></i></span>
</td>
</tr>
</table>
</form>
<div class="btnSet">
<a class="btn-fill" onclick="if(necessary()) $('form').submit()">저장</a>
<a class="btn-empty" href="list.qna">취소</a>
</div>
<script type="text/javascript" src="js/need_check.js?v=<%=new java.util.Date().getTime() %>"></script>
<script type="text/javascript" src="js/file_attach.js"></script>
</body>
</html>▲new.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="core" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h3>QNA글 조회</h3>
<table>
<tr>
<th class="w-px160">제목</th>
<td colspan="5" class="left">${vo.title }</td>
</tr>
<tr>
<th>작성자</th>
<td>${vo.name }</td>
<th class="w-px120">작성일자</th>
<td class="w-px120">${vo.writedate }</td>
<th class="w-px80">조회수</th>
<td class="w-px80">${vo.readcnt }</td>
</tr>
<tr>
<th>내용</th>
<td colspan="5" class="left">${fn:replace(vo.content, crlf, '<br>') }</td>
</tr>
<tr>
<th>첨부 파일</th>
<td colspan="5" class="left">
${vo.filename }
<core:if test="${!empty vo.filename }">
<a href="download.qna?id=${vo.id }" style="margin-left: 15px"><i class="fas fa-download font-img"></i></a>
</core:if>
</td>
</tr>
</table>
<div class="btnSet">
<a class="btn-fill" href="list.qna">목록으로</a>
<!-- 관리자인 경우 수정 삭제 가능 -->
<core:if test="${login_info.admin eq 'Y' }">
<a class="btn-fill" href="modify.qna?id=${vo.id }">수정</a>
<a class="btn-fill" onclick="if(confirm('정말 삭제하시겠습니까?')) { href='delete.qna?id=${vo.id }' }">삭제</a>
</core:if>
<!-- 로그인이 된 경우 답글 쓰기 가능 -->
<core:if test="${!empty login_info }">
<a class="btn-fill" href="reply.qna?id=${vo.id }">답글 쓰기</a>
</core:if>
</div>
</body>
</html>▲detail.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>qna modify jsp</title>
</head>
<body>
<h3>QNA 수정</h3>
<!--
파일 첨부 시 form 태그의 필수 속성
1. method="post"
2. enctype="multipart/form-data"
-->
<form action="update.qna" method="post" enctype="multipart/form-data">
<input type="hidden" name="id" value="${vo.id }"/>
<input type="hidden" name="attach" />
<table>
<tr>
<th class="w-px160">제목</th>
<td><input type="text" class="need" name="title" value="${vo.title }"/></td>
</tr>
<tr>
<th>내용</th>
<td><textarea class="need" name="content">${vo.content }</textarea></td>
</tr>
<tr>
<th>첨부 파일</th>
<td class="left">
<label>
<input id="attach-file" type="file" name="file" />
<img src="img/select.png" class="file-img" />
</label>
<span id="file-name">${vo.filename }</span>
<span id="delete-file" style="display:${empty vo.filename ? 'none' : 'inline'}; color:red; margin-left:20px;"><i class="fas fa-times font-img"></i></span>
</td>
</tr>
</table>
</form>
<div class="btnSet">
<a class="btn-fill" onclick="if( necessary() ) { $('[name=attach]').val($('#file-name').text()); $('form').submit(); }">저장</a>
<a class="btn-empty" href="detail.qna?id=${vo.id }">취소</a>
</div>
<script type="text/javascript" src="js/need_check.js?v=<%=new java.util.Date().getTime() %>"></script>
<script type="text/javascript" src="js/file_attach.js"></script>
</body>
</html>▲modify.jsp
package qna;
import java.sql.Date;
public class QnaVO {
private int id, readcnt, no, root, step, indent;
private String title, content, writer, filename, filepath, name;
private Date writedate;
public int getId() {
return id;
}
public int getNo() {
return no;
}
public void setNo(int no) {
this.no = no;
}
public Date getWritedate() {
return writedate;
}
public void setWritedate(Date writedate) {
this.writedate = writedate;
}
public void setId(int id) {
this.id = id;
}
public int getReadcnt() {
return readcnt;
}
public void setReadcnt(int readcnt) {
this.readcnt = readcnt;
}
public int getRoot() {
return root;
}
public void setRoot(int root) {
this.root = root;
}
public int getStep() {
return step;
}
public void setStep(int step) {
this.step = step;
}
public int getIndent() {
return indent;
}
public void setIndent(int indent) {
this.indent = indent;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public String getFilename() {
return filename;
}
public void setFilename(String filename) {
this.filename = filename;
}
public String getFilepath() {
return filepath;
}
public void setFilepath(String filepath) {
this.filepath = filepath;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}▲QnaVO.java
package qna;
import java.util.List;
import org.springframework.stereotype.Component;
import common.PageVO;
@Component
public class QnaPage extends PageVO {
private List<QnaVO> list;
public List<QnaVO> getList() {
return list;
}
public void setList(List<QnaVO> list) {
this.list = list;
}
}▲QnaPage.java
--테이블 생성
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;▲table.sql
package common;
public class PageVO {
private int pageList = 10; //페이지당 목록수
private int blockPage = 10; //블럭당 페이지수
private int totalList; //총목록수
private int totalPage; //총페이지수
//157 페이지 = 총목록수/페이지당 목록수 + 나머지가 있으면+1
private int totalBlock; //총블럭수
//16 블럭 = 총페이지수/블럭당 페이지수 + 나머지가 있으면+1
private int curPage; //현재페이지번호
private int beginList, endList; //현재페이지의 시작/끝 목록번호
private int beginPage, endPage; //현재블럭의 시작/끝 페이지번호
private String search, keyword; //검색기준, 검색어
public String getSearch() {
return search;
}
public void setSearch(String search) {
this.search = search;
}
public String getKeyword() {
return keyword;
}
public void setKeyword(String keyword) {
this.keyword = keyword;
}
public int getPageList() {
return pageList;
}
public void setPageList(int pageList) {
this.pageList = pageList;
}
public int getBlockPage() {
return blockPage;
}
public void setBlockPage(int blockPage) {
this.blockPage = blockPage;
}
public int getTotalList() {
return totalList;
}
public void setTotalList(int totalList) {
this.totalList = totalList;
//총페이지수=총목록수/페이지당보여질목록수
//576/10 --> 57 ..6 -> 58페이지
totalPage = totalList / pageList;
if( totalList % pageList >0 ) ++totalPage;
//총블럭수=총페이지수/블럭당보여질페이지수
//58/10 --> 5..8 -> 6블럭
totalBlock = totalPage / blockPage;
if( totalPage % blockPage > 0 ) ++totalBlock;
//시작/끝 목록번호
//끝목록번호: 576, 566, 556,
endList = totalList - (curPage-1) * pageList;
//시작목록번호: 567, 557, 547,
//= 끝목록번호 - (페이지당보여질목록수-1)
beginList = endList - (pageList-1);
//현재 블럭번호
curBlock = curPage / blockPage;
if( curPage % blockPage > 0 ) ++curBlock;
//시작/끝 페이지번호
//끝페이지번호: 10, 20, 30, ...
endPage = curBlock * blockPage;
//시작페이지번호 : 1, 11, 21, ...
beginPage = endPage - (blockPage-1);
//2048건 ▶ 1페이지 : 2048 ~ 2039, 1 ~ 10
// 205페이지 : 8 ~ 1, 51 ~ 58
//끝 페이지 번호가 총 페이지 번호보다 크면 총 페이지 번호가 끝 페이지 번호이다.
if(endPage > totalPage) {endPage = totalPage; }
}
private int curBlock; //현재블럭번호
public int getCurBlock() {
return curBlock;
}
public void setCurBlock(int curBlock) {
this.curBlock = curBlock;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getTotalBlock() {
return totalBlock;
}
public void setTotalBlock(int totalBlock) {
this.totalBlock = totalBlock;
}
public int getCurPage() {
return curPage;
}
public void setCurPage(int curPage) {
this.curPage = curPage;
}
public int getBeginList() {
return beginList;
}
public void setBeginList(int beginList) {
this.beginList = beginList;
}
public int getEndList() {
return endList;
}
public void setEndList(int endList) {
this.endList = endList;
}
public int getBeginPage() {
return beginPage;
}
public void setBeginPage(int beginPage) {
this.beginPage = beginPage;
}
public int getEndPage() {
return endPage;
}
public void setEndPage(int endPage) {
this.endPage = endPage;
}
}▲PageVO.java
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>qna reply jsp</title>
</head>
<body>
<h3>답글 쓰기</h3>
<!--
파일 첨부 시 form 태그가 반드시 가져야 할 속성
method="post"
enctype="multipart/form-data"
-->
<form action="reply_insert.qna" method="post" enctype="multipart/form-data">
<input type="hidden" name="root" value="${vo.root }" />
<input type="hidden" name="step" value="${vo.step }" />
<input type="hidden" name="indent" value="${vo.indent }" />
<table>
<tr>
<th class="w-px160">제목</th>
<td><input type="text" name="title" class="need" /></td>
</tr>
<tr>
<th>작성자</th>
<td>${login_info.name }</td>
</tr>
<tr>
<th>내용</th>
<td><textarea name="content" class="need"></textarea></td>
</tr>
<tr>
<th>파일 첨부</th>
<td class="left">
<label>
<input type="file" name="file" id="attach-file" />
<img src="img/select.png" class="file-img" />
</label>
<span id="file-name"></span>
<span id="delete-file" style="color: red; margin-left: 20px;"><i class="fas fa-times font-img"/></i></span>
</td>
</tr>
</table>
</form>
<div class="btnSet">
<a class="btn-fill" onclick="if(necessary()) $('form').submit()">저장</a>
<a class="btn-empty" href="list.qna">취소</a>
</div>
<!-- 실시간 갱신을 위해 getTime을 붙여준다 -->
<script type="text/javascript" src="js/need_check.js?v=<%=new java.util.Date().getTime() %>"></script>
<script type="text/javascript" src="js/file_attach.js"></script>
</body>
</html>▲reply.jsp
- 방명록 게시판 만들기 ① : 화면 요청까지 -
package com.hanul.iot;
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.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import board.BoardPage;
import board.BoardServiceImpl;
@Controller
public class BoardController {
@Autowired private BoardServiceImpl service;
@Autowired private BoardPage page;
//방명록 목록 화면 요청================================================================
@RequestMapping("/list.bo")
public String list(HttpSession session, Model model, @RequestParam(defaultValue = "1") int curPage, String search, String keyword) {
//DB에서 방명록 정보를 조회해와 목록 화면에 출력
session.setAttribute("category", "bo");
page.setCurPage(curPage);
page.setSearch(search);
page.setKeyword(keyword);
model.addAttribute("page", service.board_list(page));
return "board/list";
} //list()
} //class▲BoardController.java
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>board list jsp</title>
</head>
<body>
<h3>방명록</h3>
</body>
</html>▲list.jsp
package board;
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);
}▲BoardService.java
package board;
import java.sql.Date;
public class BoardVO {
private int id, readcnt, no;
private String title, content, writer, name, filename, filepath;
private Date writedate;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getReadcnt() {
return readcnt;
}
public void setReadcnt(int readcnt) {
this.readcnt = readcnt;
}
public int getNo() {
return no;
}
public void setNo(int no) {
this.no = no;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
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 getFilename() {
return filename;
}
public void setFilename(String filename) {
this.filename = filename;
}
public String getFilepath() {
return filepath;
}
public void setFilepath(String filepath) {
this.filepath = filepath;
}
public Date getWritedate() {
return writedate;
}
public void setWritedate(Date writedate) {
this.writedate = writedate;
}
}
▲BoardVO.java
--테이블 생성
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;▲table.sql
package board;
import java.util.List;
import org.springframework.stereotype.Component;
@Component
public class BoardPage extends common.PageVO {
private List<BoardVO> list;
public List<BoardVO> getList() {
return list;
}
public void setList(List<BoardVO> list) {
this.list = list;
}
}▲BoardPage.java
package board;
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) {
// TODO Auto-generated method stub
return 0;
}
@Override
public BoardPage board_list(BoardPage page) {
return dao.board_list(page);
}
@Override
public BoardVO board_detail(int id) {
// TODO Auto-generated method stub
return null;
}
@Override
public void board_read(int id) {
// TODO Auto-generated method stub
}
@Override
public int board_update(BoardVO vo) {
// TODO Auto-generated method stub
return 0;
}
@Override
public int board_delete(int id) {
// TODO Auto-generated method stub
return 0;
}
}
▲BoardServiceImpl.java
package board;
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) {
// TODO Auto-generated method stub
return 0;
}
@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) {
// TODO Auto-generated method stub
return null;
}
@Override
public void board_read(int id) {
// TODO Auto-generated method stub
}
@Override
public int board_update(BoardVO vo) {
// TODO Auto-generated method stub
return 0;
}
@Override
public int board_delete(int id) {
// TODO Auto-generated method stub
return 0;
}
}
▲BoardDAO.java
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd">
<!-- Root Context: defines shared resources visible to all other web components -->
<import resource="classpath:data/*.xml" />
<!-- context에 member를 적음으로써 Autowired의 스캔 범위에 member가 추가된다. -->
<!-- 20/07/09 common 추가 -->
<!-- 20/07/10 notice 추가 -->
<!-- 20/07/20 qna 추가 -->
<!-- 20/07/21 board 추가 -->
<context:component-scan base-package="board, qna ,notice, common, member, customer"/>
</beans>
▲root-context.xml
<?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>
</mapper>▲board-mapper.xml
반응형
'취업성공패키지 SW 개발자 교육 > Spring' 카테고리의 다른 글
| [Spring] 17. 방명록 만들기 ③ : 글 상세 조회, 수정 (0) | 2020.07.23 |
|---|---|
| [Spring] 16. 방명록 만들기 ② : 첨부파일 미리보기, 게시글 표시 갯수, 게시판 표시 형태 (0) | 2020.07.22 |
| [Spring] 14. 웹사이트 만들기 ⑬ : 답글 처리, 검색 기능 (0) | 2020.07.17 |
| [Spring] 13. 웹사이트 만들기 ⑫ : 페이징, 답글 작성 (0) | 2020.07.16 |
| [Spring] 12. 웹사이트 만들기 ⑪ : 공지글 수정, 목록에서 첨부파일 다운로드, 페이징 밑작업 (0) | 2020.07.15 |
'취업성공패키지 SW 개발자 교육/Spring' Related Articles
more
