DB
-- 테이블 명 tbl_qna 생성 create table tbl_qna( qseq number PRIMARY KEY , -- 글번호 qna_seq 시퀀스 객체로 자동 일려번호 부여 subject VARCHAR2(30) , -- 제목 content VARCHAR2(3000), -- 문의 내용 reply VARCHAR2(3000), -- 답변 내용 id VARCHAR2(20), -- 작성자 아이디 member 테이블의 기본키인 id 컬럼 rep char(1) default '1', -- 답변 유무 1:답변 무 2:답변 유 indate date default sysdate )TABLESPACE macaronics ; -- 테이블 명 tbl_qna 시퀀스 생성 CREATE SEQUENCE qna_seq START WITH 1 INCREMENT by 1 NOMAXVALUE NOCYCLE NOCACHE ; -- tbl_qna id 외래키 제약조건 추가 alter TABLE TBL_QNA add CONSTRAINT tbl_qna_id_FK FOREIGN KEY (id) REFERENCES tbl_member (id) on delete CASCADE ;
DTO
QnaVO
package net.macaronics.web.dto; import java.sql.Timestamp; public class QnaVO { private int qseq; //글번호 private String subject; //제목 private String content; //문의 내용 private String reply; //답변 내용 private String id; //작성자 아이디 private String rep; //답변 유무 private Timestamp indate; //작성일 setter, getter
DAO
QnaDAO
package net.macaronics.web.dao; import java.util.ArrayList; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import config.MybatisService; import net.macaronics.web.dto.QnaVO; public class QnaDAO { private Logger logger =LogManager.getLogger(QnaDAO.class); private SqlSession sqlSession; private static QnaDAO instance ; private QnaDAO(){ } public static QnaDAO getInstance(){ if(instance==null){ instance=new QnaDAO(); } return instance; } // Qna 목록 public List<QnaVO> listQna(String id){ List<QnaVO> list =new ArrayList<>(); try{ sqlSession=MybatisService.getFactory().openSession(); list=sqlSession.selectList("qna.listQna", id); }catch(Exception e){ e.printStackTrace(); }finally{ MybatisService.sessionClose(sqlSession); } return list; } //Qna 상세보기 public QnaVO getQna(int seq){ QnaVO qnaVO=new QnaVO(); try{ sqlSession=MybatisService.getFactory().openSession(); qnaVO=sqlSession.selectOne("qna.getQna", seq); }catch (Exception e) { e.printStackTrace(); }finally{ MybatisService.sessionClose(sqlSession); } return qnaVO; } //Qna 쓰기 public void insertQna(QnaVO qnaVO, String session_id){ try{ sqlSession=MybatisService.getFactory().openSession(); qnaVO.setId(session_id); sqlSession.insert("insertQna", qnaVO); }catch(Exception e){ e.printStackTrace(); }finally{ sqlSession.commit(); MybatisService.sessionClose(sqlSession); } } }
Mybatis
qna.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="qna"> <!-- id="태그의 식별자" resultType="sql 명령어의 리턴타입(레코드의 자료형)" 샵{변수} => 입력매개변수 --> <select id="listQna" resultType="net.macaronics.web.dto.QnaVO"> select * from TBL_QNA where id =#{id} order by qseq desc </select> <select id="getQna" resultType="net.macaronics.web.dto.QnaVO"> select * from TBL_QNA where qseq=#{qseq} </select> <insert id="insertQna"> insert INTO TBL_QNA (QSEQ, SUBJECT, CONTENT, ID) VALUES (qna_seq.nextval, #{subject}, #{content}, #{id}) </insert> </mapper>
QnA 목록 보기
QnaListAction
package net.macaronics.web.controll; import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import net.macaronics.web.controll.action.Action; import net.macaronics.web.dao.QnaDAO; import net.macaronics.web.dto.MemberVO; import net.macaronics.web.dto.QnaVO; public class QnaListAction implements Action { @Override public void execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String url ="qna/qnaList.jsp"; HttpSession session=request.getSession(); MemberVO loginUser=(MemberVO)session.getAttribute("loginUser"); if(loginUser==null){ url = "MacaronicsServlet?command=login_form"; }else{ QnaDAO qnaDAO=QnaDAO.getInstance(); List<QnaVO> qnaList=qnaDAO.listQna(loginUser.getId()); request.setAttribute("qnaList", qnaList); } request.getRequestDispatcher(url).forward(request, response); } }
qnaList.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="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %> <!DOCTYPE html> <html> <head> <jsp:include page="../include/Header.jsp" /> </head> <body> <jsp:include page="../include/HeaderMenu.jsp" /> <!-- catg header banner section --> <section id="aa-catg-head-banner"> <img src="/dailyShop/img/slider/4.jpg" alt="fashion img" height="300" style="margin-left: auto; margin-right: auto; display: block;"> <div class="aa-catg-head-banner-area"> <div class="container"> </div> </div> </section> <!-- / catg header banner section --> <!-- product category --> <section id="aa-product-category"> <div class="container"> <div class="row"> <div class="col-lg-9 col-md-9 col-sm-8 col-md-push-3"> <div class="aa-product-catg-content"> <div class="aa-product-catg-body"> <h2>1:1 고객 게시판</h2> <h3>고객님의 질문에 대해서 운영자가 1:1 답변을 드립니다.</h3> <div class="table-responsive"> <table class="table"> <tr> <th>번호</th> <th>제목</th> <th>등록일</th> <th>답변 여부</th> </tr> <c:if test="${empty qnaList }" > <tr> <td colspan="4" class="text-center" style="color:red;">게시글이 없습니다.</td> </tr> </c:if> <c:forEach items="${qnaList}" var="qnaVO"> <tr> <td>${ qnaVO.qseq}</td> <td><a href="MacaronicsServlet?command=qna_view&qseq=${qnaVO.qseq}" >${ qnaVO.subject}</a></td> <td><fmt:formatDate value="${ qnaVO.indate}" type="date" /></td> <td> <c:choose> <c:when test="${ qnaVO.rep==1}"> no </c:when> <c:when test="${ qnaVO.rep==2}"> yes </c:when> </c:choose> </td> </tr> </c:forEach> </table> </div> </div> <div class="text-center"> <input type="button" value="1:1 질문하기" class="btn btn-success" onclick="location.href='MacaronicsServlet?command=qna_write_form'"> <input type="button" value="쇼핑 계속하기" class="btn btn-primary" onclick="location.href='MacaronicsServlet?command=index'" > </div> </div> </div> <jsp:include page="../include/MyLeftMenu.jsp" /> </div> </div> </section> <!-- / product category --> <jsp:include page="../include/Footer.jsp" />
QnA 글 작성하기
QnaWriteFormAction
package net.macaronics.web.controll; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import net.macaronics.web.controll.action.Action; import net.macaronics.web.dto.MemberVO; public class QnaWriteFormAction implements Action{ @Override public void execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String url="qna/qnaWrite.jsp"; HttpSession session =request.getSession(); MemberVO loginUser=(MemberVO)session.getAttribute("loginUser"); if(loginUser ==null){ url = "MacaronicsServlet?command=login_form"; } request.getRequestDispatcher(url).forward(request, response); } }
qnaWrite.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="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %> <%@ taglib uri="http://www.owasp.org/index.php/Category:OWASP_CSRFGuard_Project/Owasp.CsrfGuard.tld" prefix="csrf" %> <!DOCTYPE html> <html> <head> <jsp:include page="../include/Header.jsp" /> <script src="//cdn.ckeditor.com/4.7.3/standard/ckeditor.js"></script> </head> <body> <jsp:include page="../include/HeaderMenu.jsp" /> <!-- catg header banner section --> <section id="aa-catg-head-banner"> <img src="/dailyShop/img/slider/4.jpg" alt="fashion img" height="300" style="margin-left: auto; margin-right: auto; display: block;"> <div class="aa-catg-head-banner-area"> <div class="container"> </div> </div> </section> <!-- / catg header banner section --> <!-- product category --> <section id="aa-product-category"> <div class="container"> <div class="row"> <div class="col-lg-9 col-md-9 col-sm-8 col-md-push-3"> <div class="aa-product-catg-content"> <csrf:form action="MacaronicsServlet?command=qna_write" method="post"> <div class="aa-product-catg-body"> <h2>1:1 고객 게시판</h2> <h3>고객님의 질문에 대해서 운영자가 1:1 답변을 드립니다.</h3> <div class="table-responsive"> <table class="table"> <tr> <th>제목</th> <td><input type="text" name="subject" class="form-control"/></td> </tr> <tr> <th>내용</th> <td><textarea name="content" id="content"></textarea></td> </tr> </table> </div> </div> <div class="text-center"> <input type="submit" value="글쓰기" class="btn btn-success"> <input type="reset" value="취소" class="btn btn-warning"> <input type="button" value="쇼핑 계속하기" class="btn btn-primary" onclick="location.href='MacaronicsServlet?command=index'" > </div> </csrf:form> </div> </div> <jsp:include page="../include/MyLeftMenu.jsp" /> </div> </div> </section> <!-- / product category --> <script type="text/javascript"> //1 첫번째 CKEDITOR.replace( 'content', {//해당 이름으로 된 textarea에 에디터를 적용 <-- 이거 이름 부분입니다. width:'100%', height:'500px' }); </script> <jsp:include page="../include/Footer.jsp" />
QnaWriteAction
package net.macaronics.web.controll; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import net.macaronics.web.controll.action.Action; import net.macaronics.web.dao.QnaDAO; import net.macaronics.web.dto.MemberVO; import net.macaronics.web.dto.QnaVO; public class QnaWriteAction implements Action{ @Override public void execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String url="MacaronicsServlet?command=qna_list"; HttpSession session =request.getSession(); MemberVO loginUser=(MemberVO)session.getAttribute("loginUser"); if(loginUser==null){ url = "MacaronicsServlet?command=login_form"; }else{ QnaVO qnaVO=new QnaVO(); qnaVO.setSubject(request.getParameter("subject")); qnaVO.setContent(request.getParameter("content")); QnaDAO qnaDAO =QnaDAO.getInstance(); qnaDAO.insertQna(qnaVO, loginUser.getId()); } response.sendRedirect(url); } }
QnA 상세보기
QnaViewAction
package net.macaronics.web.controll; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import net.macaronics.web.controll.action.Action; import net.macaronics.web.dao.QnaDAO; import net.macaronics.web.dto.MemberVO; import net.macaronics.web.dto.QnaVO; public class QnaViewAction implements Action{ @Override public void execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String url ="qna/qnaView.jsp"; HttpSession session =request.getSession(); MemberVO loginUser =(MemberVO)session.getAttribute("loginUser"); if(loginUser==null){ url = "MacaronicsServlet?command=login_form"; }else{ int qseq=Integer.parseInt(request.getParameter("qseq")); QnaDAO qnaDAO=QnaDAO.getInstance(); QnaVO qnaVO=qnaDAO.getQna(qseq); String str =qnaVO.getContent(); String content=str.replaceAll("<", "<").replaceAll(">", ">"); qnaVO.setContent(content); request.setAttribute("qnaVO", qnaVO); } request.getRequestDispatcher(url).forward(request, response); } }
qnaView.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="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %> <!DOCTYPE html> <html> <head> <jsp:include page="../include/Header.jsp" /> </head> <body> <jsp:include page="../include/HeaderMenu.jsp" /> <!-- catg header banner section --> <section id="aa-catg-head-banner"> <img src="/dailyShop/img/slider/4.jpg" alt="fashion img" height="300" style="margin-left: auto; margin-right: auto; display: block;"> <div class="aa-catg-head-banner-area"> <div class="container"> </div> </div> </section> <!-- / catg header banner section --> <!-- product category --> <section id="aa-product-category"> <div class="container"> <div class="row"> <div class="col-lg-9 col-md-9 col-sm-8 col-md-push-3"> <div class="aa-product-catg-content"> <div class="aa-product-catg-body"> <h2>1:1 고객 게시판</h2> <h3>고객님의 질문에 대해서 운영자가 1:1 답변을 드립니다.</h3> <div class="table-responsive"> <table class="table"> <tr> <th>제목</th> <td>${qnaVO.subject }</td> </tr> <tr> <th>등록일</th> <td><fmt:formatDate value="${qnaVO.indate }" type="date" /></td> </tr> <tr> <th>질문 내용</th> <td><div>${qnaVO.content}</div></td> </tr> <tr> <th>답변내용</th> <td>${qnaVO.reply }</td> </tr> </table> </div> </div> <div class="text-center"> <input type="button" value="목록보기" class="btn btn-warning" onclick="location.href='MacaronicsServlet?command=qna_list'"> <input type="button" value="쇼핑 계속하기" class="btn btn-primary" onclick="location.href='MacaronicsServlet?command=index'" > </div> </div> </div> <jsp:include page="../include/MyLeftMenu.jsp" /> </div> </div> </section> <!-- / product category --> <jsp:include page="../include/Footer.jsp" />
제작 : macaronics.net - Developer Jun Ho Choi
소스 : https://github.com/braverokmc79/jsp_sin
${request.getContextPath() } 처리를 안한 부분이 있으므로
루트 설정( http://macaronics.net/index.php/m01/jsp/view/1352) 및 server.xml 에서 DB 컨넥션 설정은 필수 설정이다.
댓글 ( 4)
댓글 남기기