[ERMaster] shoppingmall - Q&A, paging

Q&A

Dto

package com.sam.shop.dto;

import java.sql.Timestamp;

public class QnaVO {
	private Integer qseq;
	private String subject;
	private String content;
	private String reply;
	private String id;
	private String rep;
	private Timestamp indate;
	
	public Integer getQseq() {
		return qseq;
	}
	public void setQseq(Integer qseq) {
		this.qseq = qseq;
	}
	public String getSubject() {
		return subject;
	}
	public void setSubject(String subject) {
		this.subject = subject;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getReply() {
		return reply;
	}
	public void setReply(String reply) {
		this.reply = reply;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getRep() {
		return rep;
	}
	public void setRep(String rep) {
		this.rep = rep;
	}
	public Timestamp getIndate() {
		return indate;
	}
	public void setIndate(Timestamp indate) {
		this.indate = indate;
	}
}

 

Dao

package com.sam.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.sam.shop.dto.QnaVO;
import com.sam.shop.util.Dbman;
import com.sam.shop.util.Paging;

public class QnaDao {
	
	private QnaDao() {}
	private static QnaDao ist = new QnaDao();
	public static QnaDao getInstance() { return ist; }
	
	Connection con=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
}

 

 

 

Q&A 페이지

 

else if(command.equals("qnaList")) ac=new QnaListAction(); 

package com.sam.shop.controller.action.qna;

import java.io.IOException;
import java.util.ArrayList;

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

import com.sam.shop.controller.action.Action;
import com.sam.shop.dao.QnaDao;
import com.sam.shop.dto.MemberVO;
import com.sam.shop.dto.QnaVO;
import com.sam.shop.util.Paging;

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 mvo=(MemberVO)session.getAttribute("loginUser");
		if(mvo==null) {
			url="shop.do?command=index";
		} else {
			QnaDao qdao=QnaDao.getInstance()
			ArrayList<QnaVO> list=qdao.listQna(mvo.getId());
			request.setAttribute("qnaList", list);
		}
		request.getRequestDispatcher(url).forward(request, response);
	}
}

 

QnaDao에 listQna 생성

public ArrayList<QnaVO> listQna(String id) {
	ArrayList<QnaVO> list=new ArrayList<QnaVO>();
	String sql="select*from qna where id=? order by qseq desc";
	con=Dbman.getConnection();
	try {
		pstmt=con.prepareStatement(sql);
		pstmt.setString(1, id);
		rs=pstmt.executeQuery();
		while(rs.next()) {
			QnaVO qvo=new QnaVO();
			qvo.setQseq(rs.getInt("qseq"));
			qvo.setSubject(rs.getString("subject"));
			qvo.setContent(rs.getString("content"));
			qvo.setId(rs.getString("id"));
			qvo.setIndate(rs.getTimestamp("indate"));
			qvo.setReply(rs.getString("reply"));
			qvo.setRep(rs.getString("rep"));
			list.add(qvo);
		}
	} catch (SQLException e) {e.printStackTrace();
	} finally {  Dbman.close(con, pstmt, rs);}
	return list;
}

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="../header.jsp" %>
<%@ include file="sub_image_menu.jsp" %>

<article>
<h2>1:1 고객 게시판</h2>
<h3>고객님의 질문에 대해서 운영자가 1:1 답변을 드립니다</h3>
<form name="formm" method="post">
	<table id="cartList">
		<tr><th>번호</th><th>제목</th><th>등록일</th>
		        <th>답변여부</th></tr>
		<c:forEach items="${qnaList }" var="qnaVO">
			<tr>
				<td>${qnaVO.qseq }</td>
				<td>
					<a href="shop.do?command=qnaView&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 class="clear"></div>
	<div id="buttons" style="float:right">
		<input type="button" value="1:1 질문하기" class="submit"
			onClick="location.href='shop.do?command=qnaWriteForm'">
		<input type="button" value="쇼핑 계속하기" class="cancel"
			onClick="loction.href='shop.do?command=index'">
	</div>
</form>
</article>

<%@ include file="../footer.jsp" %>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<meta charset="UTF-8">
<div id="sub_img">
	<img src="images/qna/sub_img.jpg" 
		style="border-radius:20px 20px 20px 20px;">
</div>
<div class="clear"></div>

<nav id="sub_menu">
	<ul>
		<li><a href="shop.do?command=qnaList">
			Q&amp;A 게시글 리스트</a></li>
		<li><a href="shop.do?command=qnaWriteForm">
			Q&amp;A 게시글 글쓰기</a></li>
	</ul>
</nav>

 

 

1:1 질문하기

else if(command.equals("qnaWriteForm")) ac=new QnaWriteFormAction(); 

package com.sam.shop.controller.action.qna;

import java.io.IOException;

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

import com.sam.shop.controller.action.Action;
import com.sam.shop.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 mvo=(MemberVO)session.getAttribute("loginUser");
		if(mvo==null) 
			url="shop.do?command=index";
		request.getRequestDispatcher(url).forward(request,response);
	}
}

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="../header.jsp" %>
<%@ include file="sub_image_menu.jsp" %>

<article>
<h2>1:1 고객 게시판</h2>
<h3>고객님의 질문에 대해서 운영자가 1:1 답변을 드립니다</h3>
<form name="formm" method="post" action="shop.do">
	<input type="hidden" name="command" value="qnaWrite">
	<fieldset>
		<legend>Board Info</legend>
		<label>Title</label>
			<input type="text" name="subject" size="60"><br>
		<label>Content</label>
			<textarea rows="8" cols="65" name="content"></textarea>
	</fieldset>
	<div class="clear"></div>
	<div id="buttons" style="float:right">
		<input type="submit" value="글쓰기" class="submit">
		<input type="reset" value="취소" class="cancel">
		<input type="button" value="쇼핑 계속하기" class="submit"
			onClick="loction.href='shop.do?command=index'">
	</div>
</form>
</article>

<%@ include file="../footer.jsp" %>

 

 

질문 등록하기

else if(command.equals("qnaWrite")) ac=new QnaWriteAction(); 

package com.sam.shop.controller.action.qna;

import java.io.IOException;

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

import com.sam.shop.controller.action.Action;
import com.sam.shop.dao.QnaDao;
import com.sam.shop.dto.MemberVO;
import com.sam.shop.dto.QnaVO;

public class QnaWriteAction implements Action {

	@Override
	public void execute(HttpServletRequest request, 
			HttpServletResponse response) 
			throws ServletException, IOException {
		
		String url="shop.do?command=qnaList"; 
		
		HttpSession session =request.getSession();
		MemberVO mvo=(MemberVO)session.getAttribute("loginUser");
		if(mvo==null) {
			url="shop.do?command=index";
		} else {
			QnaVO qvo=new QnaVO();
			qvo.setSubject(request.getParameter("subject"));
			qvo.setContent(request.getParameter("content"));
			qvo.setId(mvo.getId());
			
			QnaDao qdao=QnaDao.getInstance();
			qdao.insertQna(qvo);
		}
		response.sendRedirect(url);
	}
}

 

QnaDao에 insertQna 생성

public void insertQna(QnaVO qvo) {
	String sql="insert into qna (qseq,subject,content,id) "
			+ "values(qna_seq.nextval,?,?,?)";
	con=Dbman.getConnection();
	try {
		pstmt=con.prepareStatement(sql);
		pstmt.setString(1, qvo.getSubject());
		pstmt.setString(2, qvo.getContent());
		pstmt.setString(3, qvo.getId());
		pstmt.executeUpdate();
	} catch (SQLException e) {e.printStackTrace();
	} finally {  Dbman.close(con, pstmt, rs);}
}

 

 

 

질문 상세보기

else if(command.equals("qnaView")) ac=new QnaViewAction(); 

package com.sam.shop.controller.action.qna;

import java.io.IOException;

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

import com.sam.shop.controller.action.Action;
import com.sam.shop.dao.QnaDao;
import com.sam.shop.dto.MemberVO;
import com.sam.shop.dto.QnaVO;

public class QnaViewAction implements Action {

	@Override
	public void execute(HttpServletRequest request, 
			HttpServletResponse response) 
			throws ServletException, IOException {
		
		String url="qna/qnaView.jsp"; 
		int qseq=Integer.parseInt(request.getParameter("qseq"));
		
		// QnaDao에 추가될 메서드 이름 getQna
		HttpSession session =request.getSession();
		MemberVO mvo=(MemberVO)session.getAttribute("loginUser");
		if(mvo==null) {
			url="shop.do?command=index";
		} else {
			QnaDao qdao=QnaDao.getInstance();
			QnaVO qvo=qdao.getQna(qseq);
			request.setAttribute("qnaVO", qvo);
		}
		request.getRequestDispatcher(url).forward(request, response);
	}
}

 

QnaDao에 getQna에 생성

public QnaVO getQna(int qseq) {
	QnaVO qvo=new QnaVO();
	String sql="select*from qna where qseq=?";
	con=Dbman.getConnection();
	try {
		pstmt=con.prepareStatement(sql);
		pstmt.setInt(1, qseq);
		rs = pstmt.executeQuery();
		if(rs.next()) {
			qvo.setQseq(qseq);
			qvo.setSubject(rs.getString("subject"));
			qvo.setContent(rs.getString("content"));
			qvo.setId(rs.getString("id"));
			qvo.setIndate(rs.getTimestamp("indate"));
			qvo.setReply(rs.getString("reply"));
			qvo.setRep(rs.getString("rep"));
		}
	} catch (SQLException e) {e.printStackTrace();
	} finally {  Dbman.close(con, pstmt, rs);}
	return qvo;
}

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="../header.jsp" %>
<%@ include file="sub_image_menu.jsp" %>

<article>
<h2>1:1 고객 게시판</h2>
<h3>고객님의 질문에 대해서 운영자가 1:1 답변을 드립니다</h3>
<form>
	<table>
		<tr><th>제목</th>
			<td width="500" style="text-align:left;">
				${qnaVO.subject }</td></tr>
		<tr><th>등록일</th>
			<td align="left" style="text-align:left;">
				<fmt:formatDate value="${qnaVO.indate}"
					type="date"/></td></tr>
		<tr><th>질문 내용</th>
			<td align="left" 
				style="text-align:left; font-size:150%;">
			<pre>${qnaVO.content }</pre></td></tr>
		<tr><th>답변 내용</th>
			<td align="left" style="text-align:left; color:white;">
				${qnaVO.reply }</td></tr>
	</table><div class="clear"></div>
	<input type="button" value="목록보기" class="submit"
		onclick="location.href='shop.do?command=qnaList'">
	<input type="button" value="쇼핑 계속하기" class="cancel"
		onClick="loction.href='shop.do?command=index'">
</form>
</article>

<%@ include file="../footer.jsp" %>

---

paging

Dto

package com.sam.shop.util;

public class Paging {
	
	private int page=1;
	private int totalCount;
	private int beginPage;
	private int endPage;
	private int displayRow=5;
	private int displayPage=3;
	private boolean prev;
	private boolean next;
	private int startNum;
	private int endNum;
	
	private void paging() {
		endPage=((int)Math.ceil(page/(double)displayPage))*
				displayPage;
		beginPage=endPage-(displayPage-1);
		int totalPage=(int)Math.ceil(totalCount/(double)displayRow);
		if(totalPage<endPage) {
			endPage=totalPage;
			next=false;
		} else {
			next=true;
		}
		prev=(beginPage==1)?false:true;
		startNum=(page-1)*displayRow+1;
		endNum=page*displayRow;
		
		System.out.println(beginPage+" "+endPage+" "+startNum+" "
				+endNum+" "+totalCount);
	}
	
	public int getPage() {
		return page;
	}
	public void setPage(int page) {
		this.page = page;
	}
	public int getTotalCount() {
		return totalCount;
	}
	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
		paging();
	}
	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;
	}
	public int getDisplayRow() {
		return displayRow;
	}
	public void setDisplayRow(int displayRow) {
		this.displayRow = displayRow;
	}
	public int getDisplayPage() {
		return displayPage;
	}
	public void setDisplayPage(int displayPage) {
		this.displayPage = displayPage;
	}
	public boolean isPrev() {
		return prev;
	}
	public void setPrev(boolean prev) {
		this.prev = prev;
	}
	public boolean isNext() {
		return next;
	}
	public void setNext(boolean next) {
		this.next = next;
	}
	public int getStartNum() {
		return startNum;
	}
	public void setStartNum(int startNum) {
		this.startNum = startNum;
	}
	public int getEndNum() {
		return endNum;
	}
	public void setEndNum(int endNum) {
		this.endNum = endNum;
	}
}

 

qnalist.jsp table 뒷부분에 추가

<div id="paging" style="margin-left:300px; 
		font-size:110%; font-weight:bold">
	<c:url var="action" value="shop.do?command=qnaList" />
	<c:if test="${paging.prev}">
		<a href="${action}&page=${paging.beginPage-1}">
			◀</a>&nbsp;
	</c:if>
	<c:forEach begin="${paging.beginPage}" 
		end="${paging.endPage}" var="index">
		<c:choose>
			<c:when test="${paging.page==index}">
				<span style="color:red">${index}&nbsp;</span>
			</c:when>
			<c:otherwise>
				<a href="${action}&page=${index}">
					${index}&nbsp;</a>
			</c:otherwise>
		</c:choose>
	</c:forEach>
	<c:if test="${paging.next}">
		<a href="${action}&page=${paging.endPage+1}">
			▶</a>&nbsp;
	</c:if>
</div>

 

QnaListAction 수정

package com.sam.shop.controller.action.qna;

import java.io.IOException;
import java.util.ArrayList;

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

import com.sam.shop.controller.action.Action;
import com.sam.shop.dao.QnaDao;
import com.sam.shop.dto.MemberVO;
import com.sam.shop.dto.QnaVO;
import com.sam.shop.util.Paging;

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 mvo=(MemberVO)session.getAttribute("loginUser");
		if(mvo==null) {
			url="shop.do?command=index";
		} else {
			QnaDao qdao=QnaDao.getInstance();
			
			Paging paging=new Paging();
			
			int page=1;
			if(request.getParameter("page")!=null)
				page=Integer.parseInt(request.getParameter("page"));
			
			paging.setPage(page);
			
			int count=qdao.getAllCount(mvo.getId());
			paging.setTotalCount(count);
			
			ArrayList<QnaVO> list=qdao.listQna(mvo.getId(),paging);
			request.setAttribute("qnaList", list);
			request.setAttribute("paging", paging);
		}
		request.getRequestDispatcher(url).forward(request, response);
	}
}

 

QnaDao에 listQna 수정

public ArrayList<QnaVO> listQna(String id, Paging paging) {
		ArrayList<QnaVO> list=new ArrayList<QnaVO>();=
		String sql="select*from("
				+ "select*from("
				+ "select rownum as rn, q.*from "
				+ "((select*from qna where id=? order by qseq desc)q)"
				+ ") where rn>=?"
				+ ") where rn<=?";
		con=Dbman.getConnection();
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setInt(2, paging.getStartNum());
			pstmt.setInt(3, paging.getEndNum());
			rs=pstmt.executeQuery();
			while(rs.next()) {
				QnaVO qvo=new QnaVO();
				qvo.setQseq(rs.getInt("qseq"));
				qvo.setSubject(rs.getString("subject"));
				qvo.setContent(rs.getString("content"));
				qvo.setId(rs.getString("id"));
				qvo.setIndate(rs.getTimestamp("indate"));
				qvo.setReply(rs.getString("reply"));
				qvo.setRep(rs.getString("rep"));
				list.add(qvo);
			}
		} catch (SQLException e) {e.printStackTrace();
		} finally {  Dbman.close(con, pstmt, rs);}
		return list;
	}

 

목록으로 돌아갈 때 접속했던 page로 돌아가기

QnaListAction 내용 수정

int page=1;
if(request.getParameter("page")!=null) {
	// 파라미터 page가 있을 때
	page=Integer.parseInt(request.getParameter("page"));
	session.setAttribute("page", page);		
} else if(session.getAttribute("page")!=null) {
	// 파라미터 page가 없고 session에 page가 있을 때
	page= (Integer)session.getAttribute("page");
} else {
	// 파라미터 page도 없고, session에 page도 없을 때
	// 세션에서 page 삭제
	session.removeAttribute("page");
}

1page로 가지않고 게시글 클릭했던 page로 돌아옴