마카로닉스

1374 No

jsp qna 고객 게시판 목록 보기, 글작성, 상세 보기 쇼핑몰(Shoes) 프로젝트 - CJH - 26

2017-10-10 01:06:25 By 라이트 0 1642 댓글수 4 포스트 하기

4월 2024
일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

-- 테이블 명 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("&lt;", "<").replaceAll("&gt;", ">");
			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 컨넥션 설정은 필수 설정이다.