[20121212] 21일차 (상담게시판)

Java/JSP & Servlet 2012. 12. 18. 15:55

share this post

----------------------------------------

상담 게시판 댓글 쓰기 (로그인한 사용자만 댓글 쓰기 가능)

//consultView.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<%@ page import="com.test.*" %>

ConsultationDTO dto

= (ConsultationDTO)request.getAttribute("dto");

String sid = dto.getSid();

String name = dto.getName();

String title = dto.getTitle();

String wdate = dto.getWdate();

String content = dto.getContent();

//----------------------------------

//출력 오류 처리 필요

//내용에 HTML 태그가 포함된 경우

//HTML 태그가 태그로 보이는게 아니라 실행 코드가 되버린다.

//태그를 문자열로 변경시켜야 한다.

name = name.replaceAll("<", "<").replaceAll(">", ">");

title = title.replaceAll("<", "<").replaceAll(">", ">");

content = content.replaceAll("<", "<").replaceAll(">", ">");

//공백, 줄바꿈문자, 탭에 대한 특수문자 처리 필요

content = content.replaceAll(" ", " ");

content = content.replaceAll("\n", "<br>");

content = content.replaceAll("\t", "      ");

//----------------------------------

//-----------------------------------

//답변글 출력 준비 추가

ConsultationDTO rdto

= (ConsultationDTO)request.getAttribute("rdto");

String rname = "";

String rtitle = "";

String rwdate = "";

String rcontent = "";

if (rdto != null) {

rname = rdto.getName();

rtitle = rdto.getTitle();

rwdate = rdto.getWdate();

rcontent = rdto.getContent();

rname = rname.replaceAll("<", "<").replaceAll(">", ">");

rtitle = rtitle.replaceAll("<", "<").replaceAll(">", ">");

rcontent = rcontent.replaceAll("<", "<").replaceAll(">", ">");

rcontent = rcontent.replaceAll(" ", " ");

rcontent = rcontent.replaceAll("\n", "<br>");

rcontent = rcontent.replaceAll("\t", "      ");

}

//----------------------------------

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

</head>

<body>

<div>

<%-- 메인메뉴를 import 구문으로 대체 --%>

<c:import url="mainMenu.jsp"></c:import>

<div>

<h3>[상담게시판_내용보기]</h3>

<tr>

</td>

</tr>

<tr>

<%=content%>

<c:if test="${!empty rdto}">

<tr>

</td>

</tr>

<tr>

<%=rcontent%>

</td>

</tr>

</table>

</div>

</c:if>

</td>

</tr>

</table>

1 개의 댓글이 있습니다.

</div>

<c:if test="${!empty sessionScope.id}">

글쓴이 '<%=(String)session.getAttribute("name")%>' 이름으로

(200자 이내)

</form>

</c:if>

<tr>

</tr>

<tr>

<td>참고합니다.</td>

</tr>

</table>

</div>

<%-- 관리자 전용 메뉴 출력 부분 --%>

<c:if test="${sessionScope.grade == '1'}">

<a href="adminReplyForm.con?sid=<%=sid%>">[*답변글쓰기]</a>

</c:if>

</div>

</body>

</html>

//consultation.js

function consultInsertSubmit() {

//데이터 검사

//에러 메시지 출력

//데이터 전송

var obj = document.getElementById("consultInsertForm");

var name = document.getElementById("name");

var pw = document.getElementById("pw");

var title = document.getElementById("title");

var nameMsg = document.getElementById("nameMsg");

nameMsg.style.display = "none";

var pwMsg = document.getElementById("pwMsg");

pwMsg.style.display = "none";

var titleMsg = document.getElementById("titleMsg");

titleMsg.style.display = "none";

if (name.value == "" || name.value.length > 20) {

nameMsg.style.display = "inline";

return;

}

if (pw.value == "" || pw.value.length > 20) {

pwMsg.style.display = "inline";

return;

}

if (title.value == "" || title.value.length > 100) {

titleMsg.style.display = "inline";

return;

}

obj.submit();

}

function pwFormSubmit() {

//데이터 검사

//에러 메시지 출력

//데이터 전송

var obj = document.getElementById("pwForm");

var name = document.getElementById("name");

var pw = document.getElementById("pw");

var nameMsg = document.getElementById("nameMsg");

nameMsg.style.display = "none";

var pwMsg = document.getElementById("pwMsg");

pwMsg.style.display = "none";

if (name.value == "" || name.value.length > 20) {

nameMsg.style.display = "inline";

return;

}

if (pw.value == "" || pw.value.length > 20) {

pwMsg.style.display = "inline";

return;

}

obj.submit();

}

function consultModifySubmit(){

var obj = document.getElementById("consultModifyForm");

var title = document.getElementById("title");

var titleMsg = document.getElementById("titleMsg");

titleMsg.style.display="none";

if(title.value=="" || title.value.length>100){

titleMsg.style.display="inline";

return;

}

obj.submit();

}

function consultSearchSubmit() {

//데이터 검사

//에러 메시지 출력

//데이터 전송

var obj = document.getElementById("consultSearchForm");

var svalue = document.getElementById("svalue");

var searchMsg = document.getElementById("searchMsg");

searchMsg.style.display = "none";

if (svalue.value == "") {

searchMsg.style.display = "inline";

return;

}

obj.submit();

}

function loginFormSubmit() {

var obj = document.getElementById("loginForm");

var id = document.getElementById("id");

var pw = document.getElementById("pw");

var idMsg = document.getElementById("idMsg");

idMsg.style.display = "none";

var pwMsg = document.getElementById("pwMsg");

pwMsg.style.display = "none";

if (id.value == "" || id.value.length > 20) {

idMsg.style.display = "inline";

return;

}

if (pw.value == "" || pw.value.length > 20) {

pwMsg.style.display = "inline";

return;

}

obj.submit();

}

//댓글 펼치기, 감추기 호출 함수 추가

function commentDivPanel() {

var commentMsg = document.getElementById("commentMsg");

var commentDiv = document.getElementById("commentDiv");

if (commentMsg.innerHTML == "[댓글 펼치기]") {

commentMsg.innerHTML = "[댓글 감추기]";

commentDiv.style.display = "block";

} else {

commentMsg.innerHTML = "[댓글 펼치기]";

commentDiv.style.display = "none";

}

function commentInsertSubmit() {

var commentInsertForm = document.getElementById("commentInsertForm");

//데이터 검사 과정 추가

var title = document.getElementById("title");

var commentErrMsg = document.getElementById("commentErrMsg");

commentErrMsg.style.display="none";

if(title.value=="" || title.value.length>200){

commentErrMsg.style.display="inline";

return;

}

commentInsertForm.submit();

}

//ConsultationServlet.java

package com.test;

import java.io.IOException;

import java.sql.SQLException;

import java.util.ArrayList;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

public class ConsultationServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

@Override

protected void doGet(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

doGetPost(req, resp);

}

@Override

protected void doPost(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

doGetPost(req, resp);

}

protected void doGetPost(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//서블릿 주소 분석

String uri = req.getRequestURI();

if (uri.indexOf("consultList.con") != -1) {

consultList(req, resp);

}

if (uri.indexOf("consultInsertForm.con") != -1) {

consultInsertForm(req, resp);

}

if (uri.indexOf("consultInsert.con") != -1) {

consultInsert(req, resp);

}

if (uri.indexOf("consultView.con") != -1) {

consultView(req, resp);

}

if (uri.indexOf("consultModifyForm.con") != -1) {

consultModifyForm(req, resp);

}

if (uri.indexOf("consultModify.con") != -1) {

consultModify(req, resp);

}

if (uri.indexOf("consultRemoveForm.con") != -1) {

consultRemoveForm(req, resp);

}

if (uri.indexOf("consultSearch.con") != -1) {

consultSearch(req, resp);

}

if (uri.indexOf("adminReplyForm.con") != -1) {

adminReplyForm(req, resp);

}

if (uri.indexOf("adminReply.con") != -1) {

adminReply(req, resp);

}

if (uri.indexOf("commentInsert.con") != -1) {

commentInsert(req, resp);

}

private void consultList(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

String pageUrl = "[1][2][3][4]...";

//--------------------------------

//페이지 처리 후

//페이지 번호 수신

//한 페이지당 게시물 숫자 지정

//총 게시물 수 확인

//총 페이지수 계산

//예를 들어, 한 페이지당 10개씩 계산

//게시물 21개 있다면

//총 페이지는 3페이지

//특정 페이지의 start, end 값 계산

String pn = req.getParameter("pageNum");

if (pn == null) {

pn = "1";

}

int recordCountPerPage = 10;

int start = (Integer.parseInt(pn) - 1)

* recordCountPerPage + 1;

int end = Integer.parseInt(pn) * recordCountPerPage;

int recordCount = 0;

//--------------------------------

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

//페이지 처리 전

//arrayList = dao.lists();

//--------------------------

//페이지 처리 후

recordCount = dao.count();

arrayList = dao.lists(start, end);

//--------------------------

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

//---------------------------

//페이지 처리 후

MyUtil myUtil = new MyUtil();

pageUrl = myUtil.pageIndexList(

Integer.parseInt(pn)

, myUtil.getPageCount(recordCountPerPage, recordCount)

, "consultList.con");

//---------------------------

req.setAttribute("arrayList", arrayList);

req.setAttribute("title", "글목록");

req.setAttribute("pageUrl", pageUrl);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultList.jsp");

dispatcher.forward(req, resp);

}

private void consultInsertForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultInsertForm.jsp");

dispatcher.forward(req, resp);

}

private void consultInsert(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT 쿼리 메소드 호출

//consultList.con 으로 페이지 이동

req.setCharacterEncoding("euc-kr");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

String title = req.getParameter("title");

String content = req.getParameter("content");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

name = name.replaceAll("'", "''");

pw = pw.replaceAll("'", "''");

title = title.replaceAll("'", "''");

content = content.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setName(name);

dto.setPw(pw);

dto.setTitle(title);

dto.setContent(content);

dao.add(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultList.con");

resp.sendRedirect(url);

}

private void consultView(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//데이터베이스 연결

//글번호에 해당하는 글 정보 읽어온다.

//글 정보(ConsultationDTO)를 출력 페이지로 전달

//답변글번호에 해당하는 글 정보 읽어온다.

//답변글 정보를 출력 페이지로 전달

//글 정보 출력 페이지(consultView.jsp)로 이동

String sid = req.getParameter("sid");

ConsultationDTO dto = new ConsultationDTO();

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO rdto = null;

try {

dao.connect();

dto = dao.searchBySid(sid);

//----------------------------------

//답변글 읽어오는 부분 추가

if (dto.getRid() != null) {

rdto = dao.searchByRid(dto.getRid());

}

//----------------------------------

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

req.setAttribute("dto", dto);

req.setAttribute("rdto", rdto);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultView.jsp");

dispatcher.forward(req, resp);

}

private void consultModifyForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//작성자, 패스워드 수신

//처음에는 패스워드 검사 페이지로 이동-> consultPW.jsp

//작성자, 패스워드가 수신된 경우는 패스워드 검사 과정 수행

//패스워드 검사 결과에 따라서

//맞는 경우는 수정 페이지로 이동 -> consultModifyForm.jsp

//틀린 경우는 에러 메시지 출력-> consultPW.jsp

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

if (name == null && pw == null) {

//consultView.jsp -> consultPW.jsp

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

} else {

//consultPW.jsp -> consultPW.jsp

//패스워드 검사 과정

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO dto = null;

try {

dao.connect();

ConsultationDTO sdto = new ConsultationDTO();

sdto.setSid(sid);

sdto.setName(name);

sdto.setPw(pw);

dto = dao.searchByPw(sdto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

if (dto != null) {

req.setAttribute("dto", dto);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultModifyForm.jsp");

dispatcher.forward(req, resp);

} else {

req.setAttribute("error", "true");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

}

private void consultModify(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String title = req.getParameter("title");

String content = req.getParameter("content");

ConsultationDAO dao = new ConsultationDAO();

try{

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(sid);

dto.setTitle(title);

dto.setContent(content);

dao.modify(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try{

dao.close();

}catch(Exception e){

System.out.println(e.toString());

}

String url = String.format("consultView.con?sid=%s", sid);

resp.sendRedirect(url);

}

private void consultRemoveForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

if (name == null && pw == null) {

//consultView.jsp -> consultPW.jsp

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

} else {

//consultPW.jsp -> consultPW.jsp

//패스워드 검사 과정

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO dto = null;

try {

dao.connect();

ConsultationDTO sdto = new ConsultationDTO();

sdto.setSid(sid);

sdto.setName(name);

sdto.setPw(pw);

dto = dao.searchByPw(sdto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

if (dto != null) {

//삭제 과정 추가 -> sid 필요

try {

dao.connect();

dao.remove(sid);

} catch(Exception e){

System.out.println(e.toString());

} finally {

try {

dao.close();

} catch (SQLException e) {

}

//목록 페이지로 이동

String url = String.format("consultList.con");

resp.sendRedirect(url);

} else {

req.setAttribute("error", "true");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

}

private void consultSearch(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//한글 인코딩 처리

//데이터 수신(skey, svalue)

//검색 실행 -> searchLists(skey, svalue)

//검색 결과(ArrayList<ConsultationDTO>)를

//출력 페이지(consultList.jsp)로 전달

req.setCharacterEncoding("euc-kr");

String skey = req.getParameter("skey");

String svalue = req.getParameter("svalue");

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

arrayList = dao.searchLists(skey, svalue);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

req.setAttribute("arrayList", arrayList);

req.setAttribute("title", "글검색");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultList.jsp");

dispatcher.forward(req, resp);

}

private void adminReplyForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//데이터베이스 연결

//글번호에 해당하는 글 정보 읽어온다.

//글 정보(ConsultationDTO)를 출력 페이지로 전달

//답변 글쓰기 페이지(adminReplyForm.jsp)로 이동

String sid = req.getParameter("sid");

ConsultationDTO dto = new ConsultationDTO();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

dto = dao.searchBySid(sid);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

req.setAttribute("dto", dto);

RequestDispatcher dispatcher

= req.getRequestDispatcher("adminReplyForm.jsp");

dispatcher.forward(req, resp);

}

private void adminReply(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT, UPDATE 쿼리 메소드 호출

//consultList.con 으로 페이지 이동

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String title = req.getParameter("title");

String content = req.getParameter("content");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

name = name.replaceAll("'", "''");

title = title.replaceAll("'", "''");

content = content.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(sid);

dto.setName(name);

dto.setTitle(title);

dto.setContent(content);

dao.adminReply(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultList.con");

resp.sendRedirect(url);

}

private void commentInsert(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT, UPDATE 쿼리 메소드 호출

//consultView.con 으로 페이지 이동

HttpSession session = req.getSession();

req.setCharacterEncoding("euc-kr");

String id = (String)session.getAttribute("id");

String name = (String)session.getAttribute("name");

String title = req.getParameter("title");

String sid = req.getParameter("sid");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

title = title.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setId(id);

dto.setName(name);

dto.setTitle(title);

dto.setSid(sid);

dao.commentAdd(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultView.con?sid=%s", sid);

resp.sendRedirect(url);

}

//ConsultationDTO.java

package com.test;

public class ConsultationDTO {

private String sid, name, pw, title, content, wdate;

//-----------------------------

//답변글 읽어오는 전용 변수 추가

private String rid;

public String getRid() {

return rid;

}

public void setRid(String rid) {

this.rid = rid;

}

//-----------------------------

//-------------------------------

//댓글 처리를 위한 id 변수 추가

private String id;

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

//-------------------------------

public String getSid() {

return sid;

}

public void setSid(String sid) {

this.sid = sid;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getPw() {

return pw;

}

public void setPw(String pw) {

this.pw = pw;

}

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 getWdate() {

return wdate;

}

public void setWdate(String wdate) {

this.wdate = wdate;

}

//ConsultationDAO.java

package com.test;

import java.sql.*;

import java.util.*;

public class ConsultationDAO {

private Connection conn;

public void connect()

throws ClassNotFoundException, SQLException {

conn = DBConn.getConnection();

}

public void close()

throws SQLException {

DBConn.close();

}

//상담 게시판 글목록 출력 (페이지 처리 전, 답변글 번호 처리 전)

public ArrayList<ConsultationDTO> lists()

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate FROM consultation ORDER BY sid DESC");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//상담 게시판 글 입력

public int add(ConsultationDTO dto)

throws SQLException {

int result = 0;

String sql = String.format("INSERT INTO consultation (sid, name, pw, title, content, wdate) VALUES (consultationSeq.nextVal,'%s',encrypt('%s', '%s'),'%s','%s',SYSDATE)", dto.getName(), dto.getPw(), dto.getName(), dto.getTitle(), dto.getContent());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

public ConsultationDTO searchBySid(String sid)

throws SQLException {

ConsultationDTO dto = new ConsultationDTO();

String sql = String.format("SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate, content, rid FROM consultation WHERE sid=%s", sid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setContent(rs.getString("content"));

dto.setWdate(rs.getString("wdate"));

//답변글 번호 읽어오는 부분

dto.setRid(rs.getString("rid"));

}

rs.close();

return dto;

}

public ConsultationDTO searchByPw(ConsultationDTO dto)

throws SQLException {

ConsultationDTO sdto = null;

String sql = String.format("SELECT sid, title, content FROM consultation WHERE sid=%s AND name='%s' AND pw=encrypt('%s', '%s')", dto.getSid(), dto.getName(), dto.getPw(), dto.getName());

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

sdto = new ConsultationDTO();

sdto.setSid(rs.getString("sid"));

sdto.setTitle(rs.getString("title"));

sdto.setContent(rs.getString("content"));

}

rs.close();

return sdto;

}

//상담 게시판 글 수정

public int modify(ConsultationDTO dto) throws SQLException {

int result =0;

String sql = String.format("UPDATE consultation SET title='%s', CONTENT='%s', wdate=SYSDATE WHERE SID=%s"

, dto.getTitle()

, dto.getContent()

, dto.getSid());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

public int remove(String sid)

throws SQLException {

int result = 0;

String sql = String.format("DELETE FROM consultation WHERE sid=%s", sid);

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

//상담 게시판 글검색 출력

public ArrayList<ConsultationDTO> searchLists(String skey, String svalue)

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate, rid FROM consultation WHERE LOWER(%s) LIKE '%%'||LOWER('%s')||'%%' ORDER BY sid DESC", skey, svalue);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

//답변글 번호 읽어오는 부분 추가

dto.setRid(rs.getString("rid"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//-------------------------------------------

//페이지 처리 후

//상담 게시판 글목록 출력 (페이지 처리 후, 답변글 처리 후)

public ArrayList<ConsultationDTO> lists(int start, int end)

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT * FROM pageListView WHERE rnum>=%d AND rnum<=%d", start, end);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

//답변글 읽어오는 부분 추가

dto.setRid(rs.getString("rid"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

public int count()

throws SQLException {

int result = 0;

String sql = String.format("SELECT COUNT(*) AS count FROM consultation");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

result = rs.getInt("count");

}

rs.close();

return result;

}

//-------------------------------------------

public int adminReply(ConsultationDTO dto)

throws SQLException {

int result = 0;

//답변글 쓰기

String sql = String.format("INSERT INTO consultReply (rid, name, title, content, wdate) VALUES (consultReplySeq.nextval, '%s', '%s','%s', SYSDATE)", dto.getName(), dto.getTitle(), dto.getContent());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

//부모글에 답변글 연결 지정

String sql2 = String.format("UPDATE consultation SET rid=consultReplySeq.currval WHERE sid=%s", dto.getSid());

Statement stmt2 = conn.createStatement();

result = stmt2.executeUpdate(sql2);

return result;

}

//-------------------------------

//답변글 읽어오는 전용 메소드 추가

public ConsultationDTO searchByRid(String rid)

throws SQLException {

ConsultationDTO dto = new ConsultationDTO();

String sql = String.format("SELECT rid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate, content FROM consultReply WHERE rid=%s", rid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

dto.setRid(rs.getString("rid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setContent(rs.getString("content"));

dto.setWdate(rs.getString("wdate"));

}

rs.close();

return dto;

}

//-------------------------------

//댓글 입력 메소드 추가

public int commentAdd(ConsultationDTO dto)

throws SQLException {

int result = 0;

//댓글 쓰기

String sql = String.format("INSERT INTO consultComment (cid, id, name, title, wdate, sid) VALUES (consultCommentSeq.nextval, '%s', '%s', '%s', SYSDATE, %s)", dto.getId(), dto.getName(), dto.getTitle(), dto.getSid());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

//부모글에 댓글 갯수 증가

String sql2 = String.format("UPDATE consultation SET commentCount = commentCount + 1 WHERE sid=%s", dto.getSid());

Statement stmt2 = conn.createStatement();

result = stmt2.executeUpdate(sql2);

return result;

}

//-------------------------------

}

--------------------------------------------------------

상담 게시판 댓글 출력

//ConsultationDTO.java

package com.test;

public class ConsultationDTO {

private String sid, name, pw, title, content, wdate;

//-----------------------------

//답변글 읽어오는 전용 변수 추가

private String rid;

public String getRid() {

return rid;

}

public void setRid(String rid) {

this.rid = rid;

}

//-----------------------------

//-------------------------------

//댓글 처리를 위한 id 변수 추가

private String id;

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

//댓글 처리를 위한 commentCount 변수 추가

private int commentCount;

public int getCommentCount() {

return commentCount;

}

public void setCommentCount(int commentCount) {

this.commentCount = commentCount;

}

//댓글 처리를 위한 cid 변수 추가

private String cid;

public String getCid() {

return cid;

}

public void setCid(String cid) {

this.cid = cid;

}

//-------------------------------

public String getSid() {

return sid;

}

public void setSid(String sid) {

this.sid = sid;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getPw() {

return pw;

}

public void setPw(String pw) {

this.pw = pw;

}

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 getWdate() {

return wdate;

}

public void setWdate(String wdate) {

this.wdate = wdate;

}

//ConsultationDAO.java

package com.test;

import java.sql.*;

import java.util.*;

public class ConsultationDAO {

private Connection conn;

public void connect()

throws ClassNotFoundException, SQLException {

conn = DBConn.getConnection();

}

public void close()

throws SQLException {

DBConn.close();

}

//상담 게시판 글목록 출력 (페이지 처리 전, 답변글 번호 처리 전, 댓글 처리 전)

public ArrayList<ConsultationDTO> lists()

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate FROM consultation ORDER BY sid DESC");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//상담 게시판 글 입력

public int add(ConsultationDTO dto)

throws SQLException {

int result = 0;

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

public ConsultationDTO searchBySid(String sid)

throws SQLException {

ConsultationDTO dto = new ConsultationDTO();

String sql = String.format("SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate, content, rid, commentCount FROM consultation WHERE sid=%s", sid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setContent(rs.getString("content"));

dto.setWdate(rs.getString("wdate"));

dto.setCommentCount(rs.getInt("commentCount"));

//답변글 번호 읽어오는 부분

dto.setRid(rs.getString("rid"));

}

rs.close();

return dto;

}

public ConsultationDTO searchByPw(ConsultationDTO dto)

throws SQLException {

ConsultationDTO sdto = null;

String sql = String.format("SELECT sid, title, content FROM consultation WHERE sid=%s AND name='%s' AND pw=encrypt('%s', '%s')", dto.getSid(), dto.getName(), dto.getPw(), dto.getName());

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

sdto = new ConsultationDTO();

sdto.setSid(rs.getString("sid"));

sdto.setTitle(rs.getString("title"));

sdto.setContent(rs.getString("content"));

}

rs.close();

return sdto;

}

//상담 게시판 글 수정

public int modify(ConsultationDTO dto) throws SQLException {

int result =0;

String sql = String.format("UPDATE consultation SET title='%s', CONTENT='%s', wdate=SYSDATE WHERE SID=%s"

, dto.getTitle()

, dto.getContent()

, dto.getSid());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

public int remove(String sid)

throws SQLException {

int result = 0;

String sql = String.format("DELETE FROM consultation WHERE sid=%s", sid);

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

//상담 게시판 글검색 출력

public ArrayList<ConsultationDTO> searchLists(String skey, String svalue)

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate, rid, commentCount FROM consultation WHERE LOWER(%s) LIKE '%%'||LOWER('%s')||'%%' ORDER BY sid DESC", skey, svalue);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

dto.setCommentCount(rs.getInt("commentCount"));

//답변글 번호 읽어오는 부분 추가

dto.setRid(rs.getString("rid"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//-------------------------------------------

//페이지 처리 후

//상담 게시판 글목록 출력 (페이지 처리 후, 답변글 처리 후, 댓글 처리 후)

public ArrayList<ConsultationDTO> lists(int start, int end)

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT * FROM pageListView WHERE rnum>=%d AND rnum<=%d", start, end);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

dto.setCommentCount(rs.getInt("commentCount"));

//답변글 읽어오는 부분 추가

dto.setRid(rs.getString("rid"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

public int count()

throws SQLException {

int result = 0;

String sql = String.format("SELECT COUNT(*) AS count FROM consultation");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

result = rs.getInt("count");

}

rs.close();

return result;

}

//-------------------------------------------

public int adminReply(ConsultationDTO dto)

throws SQLException {

int result = 0;

//답변글 쓰기

String sql = String.format("INSERT INTO consultReply (rid, name, title, content, wdate) VALUES (consultReplySeq.nextval, '%s', '%s','%s', SYSDATE)", dto.getName(), dto.getTitle(), dto.getContent());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

//부모글에 답변글 연결 지정

String sql2 = String.format("UPDATE consultation SET rid=consultReplySeq.currval WHERE sid=%s", dto.getSid());

Statement stmt2 = conn.createStatement();

result = stmt2.executeUpdate(sql2);

return result;

}

//-------------------------------

//답변글 읽어오는 전용 메소드 추가

public ConsultationDTO searchByRid(String rid)

throws SQLException {

ConsultationDTO dto = new ConsultationDTO();

String sql = String.format("SELECT rid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate, content FROM consultReply WHERE rid=%s", rid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

dto.setRid(rs.getString("rid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setContent(rs.getString("content"));

dto.setWdate(rs.getString("wdate"));

}

rs.close();

return dto;

}

//-------------------------------

//댓글 입력 메소드 추가

public int commentAdd(ConsultationDTO dto)

throws SQLException {

int result = 0;

//댓글 쓰기

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

//부모글에 댓글 갯수 증가

String sql2 = String.format("UPDATE consultation SET commentCount = commentCount + 1 WHERE sid=%s", dto.getSid());

Statement stmt2 = conn.createStatement();

result = stmt2.executeUpdate(sql2);

return result;

}

//-------------------------------

//--------------------------------

//댓글 목록 읽어오는 전용 메소드 추가

public ArrayList<ConsultationDTO> commentLists(String sid)

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT cid, id, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate FROM consultComment WHERE sid=%s ORDER BY cid ASC", sid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setCid(rs.getString("cid"));

dto.setId(rs.getString("id"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//---------------------------------

}

//ConsultationServlet.java

package com.test;

import java.io.IOException;

import java.sql.SQLException;

import java.util.ArrayList;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

public class ConsultationServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

@Override

protected void doGet(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

doGetPost(req, resp);

}

@Override

protected void doPost(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

doGetPost(req, resp);

}

protected void doGetPost(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//서블릿 주소 분석

String uri = req.getRequestURI();

if (uri.indexOf("consultList.con") != -1) {

consultList(req, resp);

}

if (uri.indexOf("consultInsertForm.con") != -1) {

consultInsertForm(req, resp);

}

if (uri.indexOf("consultInsert.con") != -1) {

consultInsert(req, resp);

}

if (uri.indexOf("consultView.con") != -1) {

consultView(req, resp);

}

if (uri.indexOf("consultModifyForm.con") != -1) {

consultModifyForm(req, resp);

}

if (uri.indexOf("consultModify.con") != -1) {

consultModify(req, resp);

}

if (uri.indexOf("consultRemoveForm.con") != -1) {

consultRemoveForm(req, resp);

}

if (uri.indexOf("consultSearch.con") != -1) {

consultSearch(req, resp);

}

if (uri.indexOf("adminReplyForm.con") != -1) {

adminReplyForm(req, resp);

}

if (uri.indexOf("adminReply.con") != -1) {

adminReply(req, resp);

}

if (uri.indexOf("commentInsert.con") != -1) {

commentInsert(req, resp);

}

private void consultList(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

String pageUrl = "[1][2][3][4]...";

//--------------------------------

//페이지 처리 후

//페이지 번호 수신

//한 페이지당 게시물 숫자 지정

//총 게시물 수 확인

//총 페이지수 계산

//예를 들어, 한 페이지당 10개씩 계산

//게시물 21개 있다면

//총 페이지는 3페이지

//특정 페이지의 start, end 값 계산

String pn = req.getParameter("pageNum");

if (pn == null) {

pn = "1";

}

int recordCountPerPage = 10;

int start = (Integer.parseInt(pn) - 1)

* recordCountPerPage + 1;

int end = Integer.parseInt(pn) * recordCountPerPage;

int recordCount = 0;

//--------------------------------

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

//페이지 처리 전

//arrayList = dao.lists();

//--------------------------

//페이지 처리 후

recordCount = dao.count();

arrayList = dao.lists(start, end);

//--------------------------

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

//---------------------------

//페이지 처리 후

MyUtil myUtil = new MyUtil();

pageUrl = myUtil.pageIndexList(

Integer.parseInt(pn)

, myUtil.getPageCount(recordCountPerPage, recordCount)

, "consultList.con");

//---------------------------

req.setAttribute("arrayList", arrayList);

req.setAttribute("title", "글목록");

req.setAttribute("pageUrl", pageUrl);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultList.jsp");

dispatcher.forward(req, resp);

}

private void consultInsertForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultInsertForm.jsp");

dispatcher.forward(req, resp);

}

private void consultInsert(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT 쿼리 메소드 호출

//consultList.con 으로 페이지 이동

req.setCharacterEncoding("euc-kr");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

String title = req.getParameter("title");

String content = req.getParameter("content");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

name = name.replaceAll("'", "''");

pw = pw.replaceAll("'", "''");

title = title.replaceAll("'", "''");

content = content.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setName(name);

dto.setPw(pw);

dto.setTitle(title);

dto.setContent(content);

dao.add(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultList.con");

resp.sendRedirect(url);

}

private void consultView(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//데이터베이스 연결

//글번호에 해당하는 글 정보 읽어온다.

//글 정보(ConsultationDTO)를 출력 페이지로 전달

//답변글번호에 해당하는 글 정보 읽어온다.

//답변글 정보를 출력 페이지로 전달

//특정 부모글에 대한 댓글 리스트를 읽어온다.

//댓글 리스트를 출력 페이지로 전달

//글 정보 출력 페이지(consultView.jsp)로 이동

String sid = req.getParameter("sid");

ConsultationDTO dto = new ConsultationDTO();

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO rdto = null;

ArrayList<ConsultationDTO> commentLists

= new ArrayList<ConsultationDTO>();

try {

dao.connect();

dto = dao.searchBySid(sid);

//----------------------------------

//답변글 읽어오는 부분 추가

if (dto.getRid() != null) {

rdto = dao.searchByRid(dto.getRid());

}

//----------------------------------

//댓글 목록 읽어오는 부분 추가

if (dto.getCommentCount() > 0) {

commentLists = dao.commentLists(sid);

}

//----------------------------------

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

req.setAttribute("dto", dto);

req.setAttribute("rdto", rdto);

req.setAttribute("commentLists", commentLists);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultView.jsp");

dispatcher.forward(req, resp);

}

private void consultModifyForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//작성자, 패스워드 수신

//처음에는 패스워드 검사 페이지로 이동-> consultPW.jsp

//작성자, 패스워드가 수신된 경우는 패스워드 검사 과정 수행

//패스워드 검사 결과에 따라서

//맞는 경우는 수정 페이지로 이동 -> consultModifyForm.jsp

//틀린 경우는 에러 메시지 출력-> consultPW.jsp

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

if (name == null && pw == null) {

//consultView.jsp -> consultPW.jsp

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

} else {

//consultPW.jsp -> consultPW.jsp

//패스워드 검사 과정

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO dto = null;

try {

dao.connect();

ConsultationDTO sdto = new ConsultationDTO();

sdto.setSid(sid);

sdto.setName(name);

sdto.setPw(pw);

dto = dao.searchByPw(sdto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

if (dto != null) {

req.setAttribute("dto", dto);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultModifyForm.jsp");

dispatcher.forward(req, resp);

} else {

req.setAttribute("error", "true");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

}

private void consultModify(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String title = req.getParameter("title");

String content = req.getParameter("content");

ConsultationDAO dao = new ConsultationDAO();

try{

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(sid);

dto.setTitle(title);

dto.setContent(content);

dao.modify(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try{

dao.close();

}catch(Exception e){

System.out.println(e.toString());

}

String url = String.format("consultView.con?sid=%s", sid);

resp.sendRedirect(url);

}

private void consultRemoveForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

if (name == null && pw == null) {

//consultView.jsp -> consultPW.jsp

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

} else {

//consultPW.jsp -> consultPW.jsp

//패스워드 검사 과정

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO dto = null;

try {

dao.connect();

ConsultationDTO sdto = new ConsultationDTO();

sdto.setSid(sid);

sdto.setName(name);

sdto.setPw(pw);

dto = dao.searchByPw(sdto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

if (dto != null) {

//삭제 과정 추가 -> sid 필요

try {

dao.connect();

dao.remove(sid);

} catch(Exception e){

System.out.println(e.toString());

} finally {

try {

dao.close();

} catch (SQLException e) {

}

//목록 페이지로 이동

String url = String.format("consultList.con");

resp.sendRedirect(url);

} else {

req.setAttribute("error", "true");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

}

private void consultSearch(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//한글 인코딩 처리

//데이터 수신(skey, svalue)

//검색 실행 -> searchLists(skey, svalue)

//검색 결과(ArrayList<ConsultationDTO>)를

//출력 페이지(consultList.jsp)로 전달

req.setCharacterEncoding("euc-kr");

String skey = req.getParameter("skey");

String svalue = req.getParameter("svalue");

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

arrayList = dao.searchLists(skey, svalue);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

req.setAttribute("arrayList", arrayList);

req.setAttribute("title", "글검색");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultList.jsp");

dispatcher.forward(req, resp);

}

private void adminReplyForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//데이터베이스 연결

//글번호에 해당하는 글 정보 읽어온다.

//글 정보(ConsultationDTO)를 출력 페이지로 전달

//답변 글쓰기 페이지(adminReplyForm.jsp)로 이동

String sid = req.getParameter("sid");

ConsultationDTO dto = new ConsultationDTO();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

dto = dao.searchBySid(sid);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

req.setAttribute("dto", dto);

RequestDispatcher dispatcher

= req.getRequestDispatcher("adminReplyForm.jsp");

dispatcher.forward(req, resp);

}

private void adminReply(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT, UPDATE 쿼리 메소드 호출

//consultList.con 으로 페이지 이동

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String title = req.getParameter("title");

String content = req.getParameter("content");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

name = name.replaceAll("'", "''");

title = title.replaceAll("'", "''");

content = content.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(sid);

dto.setName(name);

dto.setTitle(title);

dto.setContent(content);

dao.adminReply(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultList.con");

resp.sendRedirect(url);

}

private void commentInsert(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT, UPDATE 쿼리 메소드 호출

//consultView.con 으로 페이지 이동

HttpSession session = req.getSession();

req.setCharacterEncoding("euc-kr");

String id = (String)session.getAttribute("id");

String name = (String)session.getAttribute("name");

String title = req.getParameter("title");

String sid = req.getParameter("sid");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

title = title.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setId(id);

dto.setName(name);

dto.setTitle(title);

dto.setSid(sid);

dao.commentAdd(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultView.con?sid=%s", sid);

resp.sendRedirect(url);

}

//consultList.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<%@ page import="java.util.*" %>

<%@ page import="com.test.*" %>

//검색 기준, 검색 단어 수신

String skey = request.getParameter("skey");

if (skey == null) {

skey = "";

}

String svalue = request.getParameter("svalue");

if (svalue == null) {

svalue = "";

}

//제목 글자 수신

String title = (String)request.getAttribute("title");

//페이징 처리 수신

String pageUrl = (String)request.getAttribute("pageUrl");;

if (pageUrl == null) {

pageUrl = "<br>";

}

@SuppressWarnings("unchecked")

ArrayList<ConsultationDTO> arrayList

= (ArrayList<ConsultationDTO>)request.getAttribute("arrayList");

StringBuilder str = new StringBuilder();

for (ConsultationDTO dto : arrayList) {

str.append("<tr>");

str.append(String.format("<td class=\"bDot\">%s</td>", dto.getSid()));

str.append(String.format("<td class=\"bTitle\"><a href=\"consultView.con?sid=%s\">%s</a>", dto.getSid(), dto.getTitle()));

//댓글 갯수 출력 부분

if (dto.getCommentCount() > 0) {

str.append(String.format("<span style=\"color:red; font-size:10pt;\">[%d]</span>", dto.getCommentCount()));

}

//답변글 출력 부분

if (dto.getRid() != null) {

str.append("<span style=\"color:blue; font-size:10pt;\">[답변있음]</span>");

}

str.append("</td>");

str.append(String.format("<td class=\"bDot\">%s</td>", dto.getName()));

str.append(String.format("<td class=\"bDot\">%s</td>", dto.getWdate()));

str.append("</tr>");

}

StringBuilder options = new StringBuilder();

if (skey.equals("title")) {

options.append("<option value=\"title\" selected=\"selected\">제목</option>");

} else {

options.append("<option value=\"title\">제목</option>");

}

if (skey.equals("content")) {

options.append("<option value=\"content\" selected=\"selected\">내용</option>");

} else {

options.append("<option value=\"content\">내용</option>");

}

if (skey.equals("name")) {

options.append("<option value=\"name\" selected=\"selected\">글작성자</option>");

} else {

options.append("<option value=\"name\">글작성자</option>");

}

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

</head>

<body>

<div>

<%-- 메인메뉴를 import 구문으로 대체 --%>

<c:import url="mainMenu.jsp"></c:import>

<div>

<h3>[상담게시판_<%=title%>]</h3>

<tr>

</tr>

<!-- <tr>

<td class="bTitle"><a href="consultView.con?sid=1">상담 게시판이 오픈되었습니다.</a></td>

</tr> -->

<%=str%>

</table>

<tr>

</tr>

</table>

<tr>

<td>

</td>

</td>

</tr>

</table>

</form>

</div>

</body>

</html>

//consultView.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<%@ page import="com.test.*" %>

<%@ page import="java.util.*" %>

ConsultationDTO dto

= (ConsultationDTO)request.getAttribute("dto");

String sid = dto.getSid();

String name = dto.getName();

String title = dto.getTitle();

String wdate = dto.getWdate();

String content = dto.getContent();

int commentCount = dto.getCommentCount();

//----------------------------------

//출력 오류 처리 필요

//내용에 HTML 태그가 포함된 경우

//HTML 태그가 태그로 보이는게 아니라 실행 코드가 되버린다.

//태그를 문자열로 변경시켜야 한다.

name = name.replaceAll("<", "<").replaceAll(">", ">");

title = title.replaceAll("<", "<").replaceAll(">", ">");

content = content.replaceAll("<", "<").replaceAll(">", ">");

//공백, 줄바꿈문자, 탭에 대한 특수문자 처리 필요

content = content.replaceAll(" ", " ");

content = content.replaceAll("\n", "<br>");

content = content.replaceAll("\t", "      ");

//----------------------------------

//-----------------------------------

//답변글 출력 준비 추가

ConsultationDTO rdto

= (ConsultationDTO)request.getAttribute("rdto");

String rname = "";

String rtitle = "";

String rwdate = "";

String rcontent = "";

if (rdto != null) {

rname = rdto.getName();

rtitle = rdto.getTitle();

rwdate = rdto.getWdate();

rcontent = rdto.getContent();

rname = rname.replaceAll("<", "<").replaceAll(">", ">");

rtitle = rtitle.replaceAll("<", "<").replaceAll(">", ">");

rcontent = rcontent.replaceAll("<", "<").replaceAll(">", ">");

rcontent = rcontent.replaceAll(" ", " ");

rcontent = rcontent.replaceAll("\n", "<br>");

rcontent = rcontent.replaceAll("\t", "      ");

}

//----------------------------------

//-----------------------------------

//댓글 목록 출력 준비 추가

@SuppressWarnings("unchecked")

ArrayList<ConsultationDTO> commentLists

= (ArrayList<ConsultationDTO>)request.getAttribute("commentLists");

StringBuilder comments = new StringBuilder();

for (ConsultationDTO cdto : commentLists) {

comments.append("<tr>");

comments.append(String.format("<td>%s</td>", cdto.getName()));

comments.append(String.format("<td>%s</td>", cdto.getTitle()));

comments.append(String.format("<td>%s</td>", cdto.getWdate()));

comments.append("</tr>");

}

//-----------------------------------

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

</head>

<body>

<div>

<%-- 메인메뉴를 import 구문으로 대체 --%>

<c:import url="mainMenu.jsp"></c:import>

<div>

<h3>[상담게시판_내용보기]</h3>

<tr>

</td>

</tr>

<tr>

<%=content%>

<c:if test="${!empty rdto}">

<tr>

</td>

</tr>

<tr>

<%=rcontent%>

</td>

</tr>

</table>

</div>

</c:if>

</td>

</tr>

</table>

<%=commentCount%> 개의 댓글이 있습니다.

</div>

<c:if test="${!empty sessionScope.id}">

글쓴이 '<%=(String)session.getAttribute("name")%>' 이름으로

(200자 이내)

</form>

</c:if>

<tr>

</tr>

<!-- <tr>

<td>참고합니다.</td>

</tr> -->

<%=comments%>

</table>

</div>

<%-- 관리자 전용 메뉴 출력 부분 --%>

<c:if test="${sessionScope.grade == '1'}">

<a href="adminReplyForm.con?sid=<%=sid%>">[*답변글쓰기]</a>

</c:if>

</div>

</body>

</html>

//요청주소

http://localhost:8090/ConsultationServlet_20121207/consultList.con

--------------------------------------------------------

문제) 상담 게시판 댓글 삭제 (로그인한 사용자가 본인이 쓴 댓글만 삭제 가능)

--------------------------------------------------------

상담 게시판 회원 글쓰기(로그인한 사용자인 경우)

//consultList.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<%@ page import="java.util.*" %>

<%@ page import="com.test.*" %>

//검색 기준, 검색 단어 수신

String skey = request.getParameter("skey");

if (skey == null) {

skey = "";

}

String svalue = request.getParameter("svalue");

if (svalue == null) {

svalue = "";

}

//제목 글자 수신

String title = (String)request.getAttribute("title");

//페이징 처리 수신

String pageUrl = (String)request.getAttribute("pageUrl");;

if (pageUrl == null) {

pageUrl = "<br>";

}

@SuppressWarnings("unchecked")

ArrayList<ConsultationDTO> arrayList

= (ArrayList<ConsultationDTO>)request.getAttribute("arrayList");

StringBuilder str = new StringBuilder();

for (ConsultationDTO dto : arrayList) {

str.append("<tr>");

str.append(String.format("<td class=\"bDot\">%s</td>", dto.getSid()));

str.append(String.format("<td class=\"bTitle\"><a href=\"consultView.con?sid=%s\">%s</a>", dto.getSid(), dto.getTitle()));

//댓글 갯수 출력 부분

if (dto.getCommentCount() > 0) {

str.append(String.format("<span style=\"color:red; font-size:10pt;\">[%d]</span>", dto.getCommentCount()));

}

//답변글 출력 부분

if (dto.getRid() != null) {

str.append("<span style=\"color:blue; font-size:10pt;\">[답변있음]</span>");

}

str.append("</td>");

str.append(String.format("<td class=\"bDot\">%s</td>", dto.getName()));

str.append(String.format("<td class=\"bDot\">%s</td>", dto.getWdate()));

str.append("</tr>");

}

StringBuilder options = new StringBuilder();

if (skey.equals("title")) {

options.append("<option value=\"title\" selected=\"selected\">제목</option>");

} else {

options.append("<option value=\"title\">제목</option>");

}

if (skey.equals("content")) {

options.append("<option value=\"content\" selected=\"selected\">내용</option>");

} else {

options.append("<option value=\"content\">내용</option>");

}

if (skey.equals("name")) {

options.append("<option value=\"name\" selected=\"selected\">글작성자</option>");

} else {

options.append("<option value=\"name\">글작성자</option>");

}

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

</head>

<body>

<div>

<%-- 메인메뉴를 import 구문으로 대체 --%>

<c:import url="mainMenu.jsp"></c:import>

<div>

<h3>[상담게시판_<%=title%>]</h3>

<tr>

</tr>

<!-- <tr>

<td class="bTitle"><a href="consultView.con?sid=1">상담 게시판이 오픈되었습니다.</a></td>

</tr> -->

<%=str%>

</table>

<tr>

</tr>

</table>

<tr>

<td>

</td>

<%-- 회원 로그인 여부에 따라서 다른 새글쓰기 메뉴 출력됨 --%>

<c:choose>

<c:when test="${empty sessionScope.id}"><a href="consultInsertForm.con">[새글쓰기]</a></c:when>

<c:otherwise><a href="consultMemberInsertForm.con">[새글쓰기]</a></c:otherwise>

</c:choose>

</td>

</tr>

</table>

</form>

</div>

</body>

</html>

//ConsultationServlet.java

package com.test;

import java.io.IOException;

import java.sql.SQLException;

import java.util.ArrayList;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

public class ConsultationServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

@Override

protected void doGet(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

doGetPost(req, resp);

}

@Override

protected void doPost(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

doGetPost(req, resp);

}

protected void doGetPost(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//서블릿 주소 분석

String uri = req.getRequestURI();

if (uri.indexOf("consultList.con") != -1) {

consultList(req, resp);

}

if (uri.indexOf("consultInsertForm.con") != -1) {

consultInsertForm(req, resp);

}

if (uri.indexOf("consultInsert.con") != -1) {

consultInsert(req, resp);

}

if (uri.indexOf("consultView.con") != -1) {

consultView(req, resp);

}

if (uri.indexOf("consultModifyForm.con") != -1) {

consultModifyForm(req, resp);

}

if (uri.indexOf("consultModify.con") != -1) {

consultModify(req, resp);

}

if (uri.indexOf("consultRemoveForm.con") != -1) {

consultRemoveForm(req, resp);

}

if (uri.indexOf("consultSearch.con") != -1) {

consultSearch(req, resp);

}

if (uri.indexOf("adminReplyForm.con") != -1) {

adminReplyForm(req, resp);

}

if (uri.indexOf("adminReply.con") != -1) {

adminReply(req, resp);

}

if (uri.indexOf("commentInsert.con") != -1) {

commentInsert(req, resp);

}

if (uri.indexOf("consultMemberInsertForm.con") != -1) {

consultMemberInsertForm(req, resp);

}

if (uri.indexOf("consultMemberInsert.con") != -1) {

consultMemberInsert(req, resp);

}

private void consultList(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

String pageUrl = "[1][2][3][4]...";

//--------------------------------

//페이지 처리 후

//페이지 번호 수신

//한 페이지당 게시물 숫자 지정

//총 게시물 수 확인

//총 페이지수 계산

//예를 들어, 한 페이지당 10개씩 계산

//게시물 21개 있다면

//총 페이지는 3페이지

//특정 페이지의 start, end 값 계산

String pn = req.getParameter("pageNum");

if (pn == null) {

pn = "1";

}

int recordCountPerPage = 10;

int start = (Integer.parseInt(pn) - 1)

* recordCountPerPage + 1;

int end = Integer.parseInt(pn) * recordCountPerPage;

int recordCount = 0;

//--------------------------------

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

//페이지 처리 전

//arrayList = dao.lists();

//--------------------------

//페이지 처리 후

recordCount = dao.count();

arrayList = dao.lists(start, end);

//--------------------------

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

//---------------------------

//페이지 처리 후

MyUtil myUtil = new MyUtil();

pageUrl = myUtil.pageIndexList(

Integer.parseInt(pn)

, myUtil.getPageCount(recordCountPerPage, recordCount)

, "consultList.con");

//---------------------------

req.setAttribute("arrayList", arrayList);

req.setAttribute("title", "글목록");

req.setAttribute("pageUrl", pageUrl);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultList.jsp");

dispatcher.forward(req, resp);

}

private void consultInsertForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultInsertForm.jsp");

dispatcher.forward(req, resp);

}

private void consultInsert(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT 쿼리 메소드 호출

//consultList.con 으로 페이지 이동

req.setCharacterEncoding("euc-kr");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

String title = req.getParameter("title");

String content = req.getParameter("content");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

name = name.replaceAll("'", "''");

pw = pw.replaceAll("'", "''");

title = title.replaceAll("'", "''");

content = content.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setName(name);

dto.setPw(pw);

dto.setTitle(title);

dto.setContent(content);

dao.add(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultList.con");

resp.sendRedirect(url);

}

private void consultView(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//데이터베이스 연결

//글번호에 해당하는 글 정보 읽어온다.

//글 정보(ConsultationDTO)를 출력 페이지로 전달

//답변글번호에 해당하는 글 정보 읽어온다.

//답변글 정보를 출력 페이지로 전달

//특정 부모글에 대한 댓글 리스트를 읽어온다.

//댓글 리스트를 출력 페이지로 전달

//글 정보 출력 페이지(consultView.jsp)로 이동

String sid = req.getParameter("sid");

ConsultationDTO dto = new ConsultationDTO();

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO rdto = null;

ArrayList<ConsultationDTO> commentLists

= new ArrayList<ConsultationDTO>();

try {

dao.connect();

dto = dao.searchBySid(sid);

//----------------------------------

//답변글 읽어오는 부분 추가

if (dto.getRid() != null) {

rdto = dao.searchByRid(dto.getRid());

}

//----------------------------------

//댓글 목록 읽어오는 부분 추가

if (dto.getCommentCount() > 0) {

commentLists = dao.commentLists(sid);

}

//----------------------------------

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

req.setAttribute("dto", dto);

req.setAttribute("rdto", rdto);

req.setAttribute("commentLists", commentLists);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultView.jsp");

dispatcher.forward(req, resp);

}

private void consultModifyForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//작성자, 패스워드 수신

//처음에는 패스워드 검사 페이지로 이동-> consultPW.jsp

//작성자, 패스워드가 수신된 경우는 패스워드 검사 과정 수행

//패스워드 검사 결과에 따라서

//맞는 경우는 수정 페이지로 이동 -> consultModifyForm.jsp

//틀린 경우는 에러 메시지 출력-> consultPW.jsp

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

if (name == null && pw == null) {

//consultView.jsp -> consultPW.jsp

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

} else {

//consultPW.jsp -> consultPW.jsp

//패스워드 검사 과정

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO dto = null;

try {

dao.connect();

ConsultationDTO sdto = new ConsultationDTO();

sdto.setSid(sid);

sdto.setName(name);

sdto.setPw(pw);

dto = dao.searchByPw(sdto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

if (dto != null) {

req.setAttribute("dto", dto);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultModifyForm.jsp");

dispatcher.forward(req, resp);

} else {

req.setAttribute("error", "true");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

}

private void consultModify(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String title = req.getParameter("title");

String content = req.getParameter("content");

ConsultationDAO dao = new ConsultationDAO();

try{

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(sid);

dto.setTitle(title);

dto.setContent(content);

dao.modify(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try{

dao.close();

}catch(Exception e){

System.out.println(e.toString());

}

String url = String.format("consultView.con?sid=%s", sid);

resp.sendRedirect(url);

}

private void consultRemoveForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

if (name == null && pw == null) {

//consultView.jsp -> consultPW.jsp

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

} else {

//consultPW.jsp -> consultPW.jsp

//패스워드 검사 과정

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO dto = null;

try {

dao.connect();

ConsultationDTO sdto = new ConsultationDTO();

sdto.setSid(sid);

sdto.setName(name);

sdto.setPw(pw);

dto = dao.searchByPw(sdto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

if (dto != null) {

//삭제 과정 추가 -> sid 필요

try {

dao.connect();

dao.remove(sid);

} catch(Exception e){

System.out.println(e.toString());

} finally {

try {

dao.close();

} catch (SQLException e) {

}

//목록 페이지로 이동

String url = String.format("consultList.con");

resp.sendRedirect(url);

} else {

req.setAttribute("error", "true");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

}

private void consultSearch(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//한글 인코딩 처리

//데이터 수신(skey, svalue)

//검색 실행 -> searchLists(skey, svalue)

//검색 결과(ArrayList<ConsultationDTO>)를

//출력 페이지(consultList.jsp)로 전달

req.setCharacterEncoding("euc-kr");

String skey = req.getParameter("skey");

String svalue = req.getParameter("svalue");

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

arrayList = dao.searchLists(skey, svalue);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

req.setAttribute("arrayList", arrayList);

req.setAttribute("title", "글검색");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultList.jsp");

dispatcher.forward(req, resp);

}

private void adminReplyForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//데이터베이스 연결

//글번호에 해당하는 글 정보 읽어온다.

//글 정보(ConsultationDTO)를 출력 페이지로 전달

//답변 글쓰기 페이지(adminReplyForm.jsp)로 이동

String sid = req.getParameter("sid");

ConsultationDTO dto = new ConsultationDTO();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

dto = dao.searchBySid(sid);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

req.setAttribute("dto", dto);

RequestDispatcher dispatcher

= req.getRequestDispatcher("adminReplyForm.jsp");

dispatcher.forward(req, resp);

}

private void adminReply(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT, UPDATE 쿼리 메소드 호출

//consultList.con 으로 페이지 이동

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String title = req.getParameter("title");

String content = req.getParameter("content");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

name = name.replaceAll("'", "''");

title = title.replaceAll("'", "''");

content = content.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(sid);

dto.setName(name);

dto.setTitle(title);

dto.setContent(content);

dao.adminReply(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultList.con");

resp.sendRedirect(url);

}

private void commentInsert(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT, UPDATE 쿼리 메소드 호출

//consultView.con 으로 페이지 이동

HttpSession session = req.getSession();

req.setCharacterEncoding("euc-kr");

String id = (String)session.getAttribute("id");

String name = (String)session.getAttribute("name");

String title = req.getParameter("title");

String sid = req.getParameter("sid");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

title = title.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setId(id);

dto.setName(name);

dto.setTitle(title);

dto.setSid(sid);

dao.commentAdd(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultView.con?sid=%s", sid);

resp.sendRedirect(url);

}

private void consultMemberInsertForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultMemberInsertForm.jsp");

dispatcher.forward(req, resp);

}

private void consultMemberInsert(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT 쿼리 메소드 호출

//consultList.con 으로 페이지 이동

//회원 정보를 세션을 이용해서 얻어 온다

//name, pw 를 채운다.

HttpSession session = req.getSession();

req.setCharacterEncoding("euc-kr");

String name = (String)session.getAttribute("name");

String pw = (String)session.getAttribute("id");

String title = req.getParameter("title");

String content = req.getParameter("content");

String id = (String)session.getAttribute("id");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

title = title.replaceAll("'", "''");

content = content.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setName(name);

dto.setPw(pw);

dto.setTitle(title);

dto.setContent(content);

dto.setId(id);

dao.memberAdd(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultList.con");

resp.sendRedirect(url);

}

//consultMemberInsertForm.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

</head>

<body>

<div>

<%-- 메인메뉴를 import 구문으로 대체 --%>

<c:import url="mainMenu.jsp"></c:import>

<div>

<h3>[상담게시판_글쓰기]</h3>

<tr>

</tr>

<tr>

</tr>

</table>

<table>

<tr>

<td>

</td>

</tr>

</table>

</form>

</div>

</body>

</html>

//consultation.js

function consultInsertSubmit() {

//데이터 검사

//에러 메시지 출력

//데이터 전송

var obj = document.getElementById("consultInsertForm");

var name = document.getElementById("name");

var pw = document.getElementById("pw");

var title = document.getElementById("title");

var nameMsg = document.getElementById("nameMsg");

nameMsg.style.display = "none";

var pwMsg = document.getElementById("pwMsg");

pwMsg.style.display = "none";

var titleMsg = document.getElementById("titleMsg");

titleMsg.style.display = "none";

if (name.value == "" || name.value.length > 20) {

nameMsg.style.display = "inline";

return;

}

if (pw.value == "" || pw.value.length > 20) {

pwMsg.style.display = "inline";

return;

}

if (title.value == "" || title.value.length > 100) {

titleMsg.style.display = "inline";

return;

}

obj.submit();

}

function pwFormSubmit() {

//데이터 검사

//에러 메시지 출력

//데이터 전송

var obj = document.getElementById("pwForm");

var name = document.getElementById("name");

var pw = document.getElementById("pw");

var nameMsg = document.getElementById("nameMsg");

nameMsg.style.display = "none";

var pwMsg = document.getElementById("pwMsg");

pwMsg.style.display = "none";

if (name.value == "" || name.value.length > 20) {

nameMsg.style.display = "inline";

return;

}

if (pw.value == "" || pw.value.length > 20) {

pwMsg.style.display = "inline";

return;

}

obj.submit();

}

function consultModifySubmit(){

var obj = document.getElementById("consultModifyForm");

var title = document.getElementById("title");

var titleMsg = document.getElementById("titleMsg");

titleMsg.style.display="none";

if(title.value=="" || title.value.length>100){

titleMsg.style.display="inline";

return;

}

obj.submit();

}

function consultSearchSubmit() {

//데이터 검사

//에러 메시지 출력

//데이터 전송

var obj = document.getElementById("consultSearchForm");

var svalue = document.getElementById("svalue");

var searchMsg = document.getElementById("searchMsg");

searchMsg.style.display = "none";

if (svalue.value == "") {

searchMsg.style.display = "inline";

return;

}

obj.submit();

}

function loginFormSubmit() {

var obj = document.getElementById("loginForm");

var id = document.getElementById("id");

var pw = document.getElementById("pw");

var idMsg = document.getElementById("idMsg");

idMsg.style.display = "none";

var pwMsg = document.getElementById("pwMsg");

pwMsg.style.display = "none";

if (id.value == "" || id.value.length > 20) {

idMsg.style.display = "inline";

return;

}

if (pw.value == "" || pw.value.length > 20) {

pwMsg.style.display = "inline";

return;

}

obj.submit();

}

//댓글 펼치기, 감추기 호출 함수 추가

function commentDivPanel() {

var commentMsg = document.getElementById("commentMsg");

var commentDiv = document.getElementById("commentDiv");

if (commentMsg.innerHTML == "[댓글 펼치기]") {

commentMsg.innerHTML = "[댓글 감추기]";

commentDiv.style.display = "block";

} else {

commentMsg.innerHTML = "[댓글 펼치기]";

commentDiv.style.display = "none";

}

function commentInsertSubmit() {

var commentInsertForm = document.getElementById("commentInsertForm");

//데이터 검사 과정 추가

var title = document.getElementById("title");

var commentErrMsg = document.getElementById("commentErrMsg");

commentErrMsg.style.display="none";

if(title.value=="" || title.value.length>200){

commentErrMsg.style.display="inline";

return;

}

commentInsertForm.submit();

}

function consultMemberInsertSubmit() {

//데이터 검사

//에러 메시지 출력

//데이터 전송

var obj = document.getElementById("consultInsertForm");

var title = document.getElementById("title");

var titleMsg = document.getElementById("titleMsg");

titleMsg.style.display = "none";

if (title.value == "" || title.value.length > 100) {

titleMsg.style.display = "inline";

return;

}

obj.submit();

}

//ConsultationDAO.java

package com.test;

import java.sql.*;

import java.util.*;

public class ConsultationDAO {

private Connection conn;

public void connect()

throws ClassNotFoundException, SQLException {

conn = DBConn.getConnection();

}

public void close()

throws SQLException {

DBConn.close();

}

//상담 게시판 글목록 출력 (페이지 처리 전, 답변글 번호 처리 전, 댓글 처리 전)

public ArrayList<ConsultationDTO> lists()

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate FROM consultation ORDER BY sid DESC");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//상담 게시판 글 입력 (비회원 글쓰기)

public int add(ConsultationDTO dto)

throws SQLException {

int result = 0;

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

public ConsultationDTO searchBySid(String sid)

throws SQLException {

ConsultationDTO dto = new ConsultationDTO();

String sql = String.format("SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate, content, rid, commentCount FROM consultation WHERE sid=%s", sid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setContent(rs.getString("content"));

dto.setWdate(rs.getString("wdate"));

dto.setCommentCount(rs.getInt("commentCount"));

//답변글 번호 읽어오는 부분

dto.setRid(rs.getString("rid"));

}

rs.close();

return dto;

}

public ConsultationDTO searchByPw(ConsultationDTO dto)

throws SQLException {

ConsultationDTO sdto = null;

String sql = String.format("SELECT sid, title, content FROM consultation WHERE sid=%s AND name='%s' AND pw=encrypt('%s', '%s')", dto.getSid(), dto.getName(), dto.getPw(), dto.getName());

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

sdto = new ConsultationDTO();

sdto.setSid(rs.getString("sid"));

sdto.setTitle(rs.getString("title"));

sdto.setContent(rs.getString("content"));

}

rs.close();

return sdto;

}

//상담 게시판 글 수정

public int modify(ConsultationDTO dto) throws SQLException {

int result =0;

String sql = String.format("UPDATE consultation SET title='%s', CONTENT='%s', wdate=SYSDATE WHERE SID=%s"

, dto.getTitle()

, dto.getContent()

, dto.getSid());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

public int remove(String sid)

throws SQLException {

int result = 0;

String sql = String.format("DELETE FROM consultation WHERE sid=%s", sid);

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

//상담 게시판 글검색 출력

public ArrayList<ConsultationDTO> searchLists(String skey, String svalue)

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

dto.setCommentCount(rs.getInt("commentCount"));

//답변글 번호 읽어오는 부분 추가

dto.setRid(rs.getString("rid"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//-------------------------------------------

//페이지 처리 후

//상담 게시판 글목록 출력 (페이지 처리 후, 답변글 처리 후, 댓글 처리 후)

public ArrayList<ConsultationDTO> lists(int start, int end)

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT * FROM pageListView WHERE rnum>=%d AND rnum<=%d", start, end);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

dto.setCommentCount(rs.getInt("commentCount"));

//답변글 읽어오는 부분 추가

dto.setRid(rs.getString("rid"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

public int count()

throws SQLException {

int result = 0;

String sql = String.format("SELECT COUNT(*) AS count FROM consultation");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

result = rs.getInt("count");

}

rs.close();

return result;

}

//-------------------------------------------

public int adminReply(ConsultationDTO dto)

throws SQLException {

int result = 0;

//답변글 쓰기

String sql = String.format("INSERT INTO consultReply (rid, name, title, content, wdate) VALUES (consultReplySeq.nextval, '%s', '%s','%s', SYSDATE)", dto.getName(), dto.getTitle(), dto.getContent());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

//부모글에 답변글 연결 지정

String sql2 = String.format("UPDATE consultation SET rid=consultReplySeq.currval WHERE sid=%s", dto.getSid());

Statement stmt2 = conn.createStatement();

result = stmt2.executeUpdate(sql2);

return result;

}

//-------------------------------

//답변글 읽어오는 전용 메소드 추가

public ConsultationDTO searchByRid(String rid)

throws SQLException {

ConsultationDTO dto = new ConsultationDTO();

String sql = String.format("SELECT rid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate, content FROM consultReply WHERE rid=%s", rid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

dto.setRid(rs.getString("rid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setContent(rs.getString("content"));

dto.setWdate(rs.getString("wdate"));

}

rs.close();

return dto;

}

//-------------------------------

//댓글 입력 메소드 추가

public int commentAdd(ConsultationDTO dto)

throws SQLException {

int result = 0;

//댓글 쓰기

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

//부모글에 댓글 갯수 증가

String sql2 = String.format("UPDATE consultation SET commentCount = commentCount + 1 WHERE sid=%s", dto.getSid());

Statement stmt2 = conn.createStatement();

result = stmt2.executeUpdate(sql2);

return result;

}

//-------------------------------

//--------------------------------

//댓글 목록 읽어오는 전용 메소드 추가

public ArrayList<ConsultationDTO> commentLists(String sid)

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT cid, id, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate FROM consultComment WHERE sid=%s ORDER BY cid ASC", sid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setCid(rs.getString("cid"));

dto.setId(rs.getString("id"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//---------------------------------

//---------------------------------------

//상담 게시판 글 입력 (회원 글쓰기)

public int memberAdd(ConsultationDTO dto)

throws SQLException {

int result = 0;

String sql = String.format("INSERT INTO consultation (sid, name, pw, title, content, wdate, id) VALUES (consultationSeq.nextVal,'%s',encrypt('%s', '%s'),'%s','%s',SYSDATE, '%s')", dto.getName(), dto.getPw(), dto.getName(), dto.getTitle(), dto.getContent(), dto.getId());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

//---------------------------------------

}

문제) 상담 게시판에서 회원 전용 수정, 삭제 과정 추가할 것.

//consultView.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<%@ page import="com.test.*" %>

<%@ page import="java.util.*" %>

ConsultationDTO dto

= (ConsultationDTO)request.getAttribute("dto");

String sid = dto.getSid();

String name = dto.getName();

String title = dto.getTitle();

String wdate = dto.getWdate();

String content = dto.getContent();

int commentCount = dto.getCommentCount();

//---------------------------------

//회원 전용 수정, 삭제 부분 추가

//데이터베이스에서 회원 아이디 정보를 읽어오는 부분 추가

String id = dto.getId();

if (id == null) {

id = "비회원";

}

//회원 아이디 정보를 EL 표현에서 사용할 수 있도록 추가한 부분

request.setAttribute("id", id);

//---------------------------------

//----------------------------------

//출력 오류 처리 필요

//내용에 HTML 태그가 포함된 경우

//HTML 태그가 태그로 보이는게 아니라 실행 코드가 되버린다.

//태그를 문자열로 변경시켜야 한다.

name = name.replaceAll("<", "<").replaceAll(">", ">");

title = title.replaceAll("<", "<").replaceAll(">", ">");

content = content.replaceAll("<", "<").replaceAll(">", ">");

//공백, 줄바꿈문자, 탭에 대한 특수문자 처리 필요

content = content.replaceAll(" ", " ");

content = content.replaceAll("\n", "<br>");

content = content.replaceAll("\t", "      ");

//----------------------------------

//-----------------------------------

//답변글 출력 준비 추가

ConsultationDTO rdto

= (ConsultationDTO)request.getAttribute("rdto");

String rname = "";

String rtitle = "";

String rwdate = "";

String rcontent = "";

if (rdto != null) {

rname = rdto.getName();

rtitle = rdto.getTitle();

rwdate = rdto.getWdate();

rcontent = rdto.getContent();

rname = rname.replaceAll("<", "<").replaceAll(">", ">");

rtitle = rtitle.replaceAll("<", "<").replaceAll(">", ">");

rcontent = rcontent.replaceAll("<", "<").replaceAll(">", ">");

rcontent = rcontent.replaceAll(" ", " ");

rcontent = rcontent.replaceAll("\n", "<br>");

rcontent = rcontent.replaceAll("\t", "      ");

}

//----------------------------------

//-----------------------------------

//댓글 목록 출력 준비 추가

@SuppressWarnings("unchecked")

ArrayList<ConsultationDTO> commentLists

= (ArrayList<ConsultationDTO>)request.getAttribute("commentLists");

StringBuilder comments = new StringBuilder();

for (ConsultationDTO cdto : commentLists) {

comments.append("<tr>");

comments.append(String.format("<td>%s</td>", cdto.getName()));

comments.append(String.format("<td>%s</td>", cdto.getTitle()));

comments.append(String.format("<td>%s</td>", cdto.getWdate()));

//댓글에 저장된 아이디와 로그인한 아이디가 같은 경우

//삭제 메뉴 출력

if (session.getAttribute("id") != null

&& cdto.getId().equals((String)session.getAttribute("id"))) {

comments.append("<td><a href=\"\">[삭제]</a></td>");

} else {

comments.append("<td></td>");

}

comments.append("</tr>");

}

//-----------------------------------

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

</head>

<body>

<div>

<%-- 메인메뉴를 import 구문으로 대체 --%>

<c:import url="mainMenu.jsp"></c:import>

<div>

<h3>[상담게시판_내용보기]</h3>

<tr>

</td>

</tr>

<tr>

<%=content%>

<c:if test="${!empty rdto}">

<tr>

</td>

</tr>

<tr>

<%=rcontent%>

</td>

</tr>

</table>

</div>

</c:if>

</td>

</tr>

</table>

<%=commentCount%> 개의 댓글이 있습니다.

</div>

<c:if test="${!empty sessionScope.id}">

글쓴이 '<%=(String)session.getAttribute("name")%>' 이름으로

(200자 이내)

</form>

</c:if>

<tr>

</tr>

<!-- <tr>

<td>참고합니다.</td>

</tr> -->

<%=comments%>

</table>

</div>

<%-- 관리자 전용 메뉴 출력 부분 --%>

<c:if test="${sessionScope.grade == '1'}">

<a href="adminReplyForm.con?sid=<%=sid%>">[*답변글쓰기]</a>

</c:if>

<%-- ------------------------------- --%>

<%-- 회원 전용 수정, 삭제 추가한 부분 --%>

<%-- 회원 로그인한 경우와 비회원인 경우를 구분해서 수정, 삭제 진행 --%>

<c:choose>

<c:when test="${empty sessionScope.id && id == '비회원'}">

</c:when>

<c:when test="${!empty sessionScope.id && sessionScope.id == id}">

</c:when>

<c:otherwise>

</c:otherwise>

</c:choose>

<%-- ------------------------------- --%>

</div>

</body>

</html>

//ConsultationServlet.java

package com.test;

import java.io.IOException;

import java.sql.SQLException;

import java.util.ArrayList;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

public class ConsultationServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

@Override

protected void doGet(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

doGetPost(req, resp);

}

@Override

protected void doPost(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

doGetPost(req, resp);

}

protected void doGetPost(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//서블릿 주소 분석

String uri = req.getRequestURI();

if (uri.indexOf("consultList.con") != -1) {

consultList(req, resp);

}

if (uri.indexOf("consultInsertForm.con") != -1) {

consultInsertForm(req, resp);

}

if (uri.indexOf("consultInsert.con") != -1) {

consultInsert(req, resp);

}

if (uri.indexOf("consultView.con") != -1) {

consultView(req, resp);

}

if (uri.indexOf("consultModifyForm.con") != -1) {

consultModifyForm(req, resp);

}

if (uri.indexOf("consultModify.con") != -1) {

consultModify(req, resp);

}

if (uri.indexOf("consultRemoveForm.con") != -1) {

consultRemoveForm(req, resp);

}

if (uri.indexOf("consultSearch.con") != -1) {

consultSearch(req, resp);

}

if (uri.indexOf("adminReplyForm.con") != -1) {

adminReplyForm(req, resp);

}

if (uri.indexOf("adminReply.con") != -1) {

adminReply(req, resp);

}

if (uri.indexOf("commentInsert.con") != -1) {

commentInsert(req, resp);

}

if (uri.indexOf("consultMemberInsertForm.con") != -1) {

consultMemberInsertForm(req, resp);

}

if (uri.indexOf("consultMemberInsert.con") != -1) {

consultMemberInsert(req, resp);

}

if (uri.indexOf("consultMemberModifyForm.con") != -1) {

consultMemberModifyForm(req, resp);

}

if (uri.indexOf("consultMemberDelete.con") != -1) {

consultMemberDelete(req, resp);

}

private void consultList(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

String pageUrl = "[1][2][3][4]...";

//--------------------------------

//페이지 처리 후

//페이지 번호 수신

//한 페이지당 게시물 숫자 지정

//총 게시물 수 확인

//총 페이지수 계산

//예를 들어, 한 페이지당 10개씩 계산

//게시물 21개 있다면

//총 페이지는 3페이지

//특정 페이지의 start, end 값 계산

String pn = req.getParameter("pageNum");

if (pn == null) {

pn = "1";

}

int recordCountPerPage = 10;

int start = (Integer.parseInt(pn) - 1)

* recordCountPerPage + 1;

int end = Integer.parseInt(pn) * recordCountPerPage;

int recordCount = 0;

//--------------------------------

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

//페이지 처리 전

//arrayList = dao.lists();

//--------------------------

//페이지 처리 후

recordCount = dao.count();

arrayList = dao.lists(start, end);

//--------------------------

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

//---------------------------

//페이지 처리 후

MyUtil myUtil = new MyUtil();

pageUrl = myUtil.pageIndexList(

Integer.parseInt(pn)

, myUtil.getPageCount(recordCountPerPage, recordCount)

, "consultList.con");

//---------------------------

req.setAttribute("arrayList", arrayList);

req.setAttribute("title", "글목록");

req.setAttribute("pageUrl", pageUrl);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultList.jsp");

dispatcher.forward(req, resp);

}

private void consultInsertForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultInsertForm.jsp");

dispatcher.forward(req, resp);

}

private void consultInsert(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT 쿼리 메소드 호출

//consultList.con 으로 페이지 이동

req.setCharacterEncoding("euc-kr");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

String title = req.getParameter("title");

String content = req.getParameter("content");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

name = name.replaceAll("'", "''");

pw = pw.replaceAll("'", "''");

title = title.replaceAll("'", "''");

content = content.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setName(name);

dto.setPw(pw);

dto.setTitle(title);

dto.setContent(content);

dao.add(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultList.con");

resp.sendRedirect(url);

}

private void consultView(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//데이터베이스 연결

//글번호에 해당하는 글 정보 읽어온다.

//글 정보(ConsultationDTO)를 출력 페이지로 전달

//답변글번호에 해당하는 글 정보 읽어온다.

//답변글 정보를 출력 페이지로 전달

//특정 부모글에 대한 댓글 리스트를 읽어온다.

//댓글 리스트를 출력 페이지로 전달

//글 정보 출력 페이지(consultView.jsp)로 이동

String sid = req.getParameter("sid");

ConsultationDTO dto = new ConsultationDTO();

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO rdto = null;

ArrayList<ConsultationDTO> commentLists

= new ArrayList<ConsultationDTO>();

try {

dao.connect();

dto = dao.searchBySid(sid);

//----------------------------------

//답변글 읽어오는 부분 추가

if (dto.getRid() != null) {

rdto = dao.searchByRid(dto.getRid());

}

//----------------------------------

//댓글 목록 읽어오는 부분 추가

if (dto.getCommentCount() > 0) {

commentLists = dao.commentLists(sid);

}

//----------------------------------

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

req.setAttribute("dto", dto);

req.setAttribute("rdto", rdto);

req.setAttribute("commentLists", commentLists);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultView.jsp");

dispatcher.forward(req, resp);

}

private void consultModifyForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//작성자, 패스워드 수신

//처음에는 패스워드 검사 페이지로 이동-> consultPW.jsp

//작성자, 패스워드가 수신된 경우는 패스워드 검사 과정 수행

//패스워드 검사 결과에 따라서

//맞는 경우는 수정 페이지로 이동 -> consultModifyForm.jsp

//틀린 경우는 에러 메시지 출력-> consultPW.jsp

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

if (name == null && pw == null) {

//consultView.jsp -> consultPW.jsp

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

} else {

//consultPW.jsp -> consultPW.jsp

//패스워드 검사 과정

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO dto = null;

try {

dao.connect();

ConsultationDTO sdto = new ConsultationDTO();

sdto.setSid(sid);

sdto.setName(name);

sdto.setPw(pw);

dto = dao.searchByPw(sdto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

if (dto != null) {

req.setAttribute("dto", dto);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultModifyForm.jsp");

dispatcher.forward(req, resp);

} else {

req.setAttribute("error", "true");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

}

private void consultModify(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String title = req.getParameter("title");

String content = req.getParameter("content");

ConsultationDAO dao = new ConsultationDAO();

try{

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(sid);

dto.setTitle(title);

dto.setContent(content);

dao.modify(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try{

dao.close();

}catch(Exception e){

System.out.println(e.toString());

}

String url = String.format("consultView.con?sid=%s", sid);

resp.sendRedirect(url);

}

private void consultRemoveForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String pw = req.getParameter("pw");

if (name == null && pw == null) {

//consultView.jsp -> consultPW.jsp

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

} else {

//consultPW.jsp -> consultPW.jsp

//패스워드 검사 과정

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO dto = null;

try {

dao.connect();

ConsultationDTO sdto = new ConsultationDTO();

sdto.setSid(sid);

sdto.setName(name);

sdto.setPw(pw);

dto = dao.searchByPw(sdto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

if (dto != null) {

//삭제 과정 추가 -> sid 필요

try {

dao.connect();

dao.remove(sid);

} catch(Exception e){

System.out.println(e.toString());

} finally {

try {

dao.close();

} catch (SQLException e) {

}

//목록 페이지로 이동

String url = String.format("consultList.con");

resp.sendRedirect(url);

} else {

req.setAttribute("error", "true");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultPW.jsp");

dispatcher.forward(req, resp);

}

private void consultSearch(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//한글 인코딩 처리

//데이터 수신(skey, svalue)

//검색 실행 -> searchLists(skey, svalue)

//검색 결과(ArrayList<ConsultationDTO>)를

//출력 페이지(consultList.jsp)로 전달

req.setCharacterEncoding("euc-kr");

String skey = req.getParameter("skey");

String svalue = req.getParameter("svalue");

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

arrayList = dao.searchLists(skey, svalue);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

req.setAttribute("arrayList", arrayList);

req.setAttribute("title", "글검색");

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultList.jsp");

dispatcher.forward(req, resp);

}

private void adminReplyForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//데이터베이스 연결

//글번호에 해당하는 글 정보 읽어온다.

//글 정보(ConsultationDTO)를 출력 페이지로 전달

//답변 글쓰기 페이지(adminReplyForm.jsp)로 이동

String sid = req.getParameter("sid");

ConsultationDTO dto = new ConsultationDTO();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

dto = dao.searchBySid(sid);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

req.setAttribute("dto", dto);

RequestDispatcher dispatcher

= req.getRequestDispatcher("adminReplyForm.jsp");

dispatcher.forward(req, resp);

}

private void adminReply(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT, UPDATE 쿼리 메소드 호출

//consultList.con 으로 페이지 이동

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

String name = req.getParameter("name");

String title = req.getParameter("title");

String content = req.getParameter("content");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

name = name.replaceAll("'", "''");

title = title.replaceAll("'", "''");

content = content.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(sid);

dto.setName(name);

dto.setTitle(title);

dto.setContent(content);

dao.adminReply(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultList.con");

resp.sendRedirect(url);

}

private void commentInsert(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT, UPDATE 쿼리 메소드 호출

//consultView.con 으로 페이지 이동

HttpSession session = req.getSession();

req.setCharacterEncoding("euc-kr");

String id = (String)session.getAttribute("id");

String name = (String)session.getAttribute("name");

String title = req.getParameter("title");

String sid = req.getParameter("sid");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

title = title.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setId(id);

dto.setName(name);

dto.setTitle(title);

dto.setSid(sid);

dao.commentAdd(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultView.con?sid=%s", sid);

resp.sendRedirect(url);

}

private void consultMemberInsertForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultMemberInsertForm.jsp");

dispatcher.forward(req, resp);

}

private void consultMemberInsert(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//INSERT 쿼리 메소드 호출

//consultList.con 으로 페이지 이동

//회원 정보를 세션을 이용해서 얻어 온다

//name, pw 를 채운다.

HttpSession session = req.getSession();

req.setCharacterEncoding("euc-kr");

String name = (String)session.getAttribute("name");

String pw = (String)session.getAttribute("id");

String title = req.getParameter("title");

String content = req.getParameter("content");

String id = (String)session.getAttribute("id");

//-----------------------------

//입력 오류 처리 필요

//오라클에서는 입력 데이터에

//작은따옴표(')가 있으면

//입력 오류 발생됨

//작은따옴표(')를 두 번 입력('')하면 해결됨.

title = title.replaceAll("'", "''");

content = content.replaceAll("'", "''");

//-----------------------------

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setName(name);

dto.setPw(pw);

dto.setTitle(title);

dto.setContent(content);

dto.setId(id);

dao.memberAdd(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultList.con");

resp.sendRedirect(url);

}

//-----------------------------------

//회원 전용 수정, 삭제 기능 추가

//회원 전용 수정 메소드 추가

private void consultMemberModifyForm(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//글번호 수신

//수정 페이지로 이동 -> consultMemberModifyForm.jsp

req.setCharacterEncoding("euc-kr");

String sid = req.getParameter("sid");

ConsultationDAO dao = new ConsultationDAO();

ConsultationDTO dto = null;

try {

dao.connect();

dto = dao.searchBySid(sid);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

}catch(Exception e){

}

req.setAttribute("dto", dto);

RequestDispatcher dispatcher

= req.getRequestDispatcher("consultModifyForm.jsp");

dispatcher.forward(req, resp);

}

//회원 전용 삭제 메소드 추가

private void consultMemberDelete(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//데이터 수신

//데이터베이스 연결

//DELETE 쿼리 메소드 호출

//consultList.con 으로 페이지 이동

//회원 정보를 세션을 이용해서 얻어 온다

//name, pw 를 채운다.

HttpSession session = req.getSession();

String sid = req.getParameter("sid");

String id = (String)session.getAttribute("id");

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(sid);

dto.setId(id);

dao.memberRemove(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

try {

dao.close();

} catch (SQLException e) {

}

String url = String.format("consultList.con");

resp.sendRedirect(url);

}

//--------------------------------------------

}

//ConsultationDAO.java

package com.test;

import java.sql.*;

import java.util.*;

public class ConsultationDAO {

private Connection conn;

public void connect()

throws ClassNotFoundException, SQLException {

conn = DBConn.getConnection();

}

public void close()

throws SQLException {

DBConn.close();

}

//상담 게시판 글목록 출력 (페이지 처리 전, 답변글 번호 처리 전, 댓글 처리 전, 회원 전용 글쓰기 처리 후)

public ArrayList<ConsultationDTO> lists()

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate FROM consultation ORDER BY sid DESC");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//상담 게시판 글 입력 (비회원 글쓰기)

public int add(ConsultationDTO dto)

throws SQLException {

int result = 0;

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

public ConsultationDTO searchBySid(String sid)

throws SQLException {

ConsultationDTO dto = new ConsultationDTO();

String sql = String.format("SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate, content, rid, commentCount, id FROM consultation WHERE sid=%s", sid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setContent(rs.getString("content"));

dto.setWdate(rs.getString("wdate"));

//답변글 번호 읽어오는 부분

dto.setRid(rs.getString("rid"));

//댓글 갯수 읽어오는 부분 추가

dto.setCommentCount(rs.getInt("commentCount"));

//회원 전용 글쓰기 처리 부분 추가

dto.setId(rs.getString("id"));

}

rs.close();

return dto;

}

public ConsultationDTO searchByPw(ConsultationDTO dto)

throws SQLException {

ConsultationDTO sdto = null;

String sql = String.format("SELECT sid, title, content FROM consultation WHERE sid=%s AND name='%s' AND pw=encrypt('%s', '%s')", dto.getSid(), dto.getName(), dto.getPw(), dto.getName());

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

sdto = new ConsultationDTO();

sdto.setSid(rs.getString("sid"));

sdto.setTitle(rs.getString("title"));

sdto.setContent(rs.getString("content"));

}

rs.close();

return sdto;

}

//상담 게시판 글 수정

public int modify(ConsultationDTO dto) throws SQLException {

int result =0;

String sql = String.format("UPDATE consultation SET title='%s', CONTENT='%s', wdate=SYSDATE WHERE SID=%s"

, dto.getTitle()

, dto.getContent()

, dto.getSid());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

public int remove(String sid)

throws SQLException {

int result = 0;

String sql = String.format("DELETE FROM consultation WHERE sid=%s", sid);

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

//상담 게시판 글검색 출력

public ArrayList<ConsultationDTO> searchLists(String skey, String svalue)

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate, rid, commentCount, id FROM consultation WHERE LOWER(%s) LIKE '%%'||LOWER('%s')||'%%' ORDER BY sid DESC", skey, svalue);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

//답변글 번호 읽어오는 부분 추가

dto.setRid(rs.getString("rid"));

//댓글 갯수 읽어오는 부분 추가

dto.setCommentCount(rs.getInt("commentCount"));

//회원 전용 글쓰기 처리 부분 추가

dto.setId(rs.getString("id"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//-------------------------------------------

//페이지 처리 후

//상담 게시판 글목록 출력 (페이지 처리 후, 답변글 처리 후, 댓글 처리 후, 회원 전용 글쓰기 처리 후)

public ArrayList<ConsultationDTO> lists(int start, int end)

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT * FROM pageListView WHERE rnum>=%d AND rnum<=%d", start, end);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setSid(rs.getString("sid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

//답변글 읽어오는 부분 추가

dto.setRid(rs.getString("rid"));

//댓글 갯수 읽어오는 부분 추가

dto.setCommentCount(rs.getInt("commentCount"));

//회원 전용 글쓰기 처리 부분 추가

dto.setId(rs.getString("id"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

public int count()

throws SQLException {

int result = 0;

String sql = String.format("SELECT COUNT(*) AS count FROM consultation");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

result = rs.getInt("count");

}

rs.close();

return result;

}

//-------------------------------------------

public int adminReply(ConsultationDTO dto)

throws SQLException {

int result = 0;

//답변글 쓰기

String sql = String.format("INSERT INTO consultReply (rid, name, title, content, wdate) VALUES (consultReplySeq.nextval, '%s', '%s','%s', SYSDATE)", dto.getName(), dto.getTitle(), dto.getContent());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

//부모글에 답변글 연결 지정

String sql2 = String.format("UPDATE consultation SET rid=consultReplySeq.currval WHERE sid=%s", dto.getSid());

Statement stmt2 = conn.createStatement();

result = stmt2.executeUpdate(sql2);

return result;

}

//-------------------------------

//답변글 읽어오는 전용 메소드 추가

public ConsultationDTO searchByRid(String rid)

throws SQLException {

ConsultationDTO dto = new ConsultationDTO();

String sql = String.format("SELECT rid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate, content FROM consultReply WHERE rid=%s", rid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

dto.setRid(rs.getString("rid"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setContent(rs.getString("content"));

dto.setWdate(rs.getString("wdate"));

}

rs.close();

return dto;

}

//-------------------------------

//댓글 입력 메소드 추가

public int commentAdd(ConsultationDTO dto)

throws SQLException {

int result = 0;

//댓글 쓰기

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

//부모글에 댓글 갯수 증가

String sql2 = String.format("UPDATE consultation SET commentCount = commentCount + 1 WHERE sid=%s", dto.getSid());

Statement stmt2 = conn.createStatement();

result = stmt2.executeUpdate(sql2);

return result;

}

//-------------------------------

//--------------------------------

//댓글 목록 읽어오는 전용 메소드 추가

public ArrayList<ConsultationDTO> commentLists(String sid)

throws SQLException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

String sql = String.format("SELECT cid, id, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate FROM consultComment WHERE sid=%s ORDER BY cid ASC", sid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

ConsultationDTO dto = new ConsultationDTO();

dto.setCid(rs.getString("cid"));

dto.setId(rs.getString("id"));

dto.setName(rs.getString("name"));

dto.setTitle(rs.getString("title"));

dto.setWdate(rs.getString("wdate"));

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//---------------------------------

//---------------------------------------

//상담 게시판 글 입력 (회원 글쓰기)

public int memberAdd(ConsultationDTO dto)

throws SQLException {

int result = 0;

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

//---------------------------------------

//--------------------------------

//회원 전용 삭제 메소드 추가

public int memberRemove(ConsultationDTO dto)

throws SQLException {

int result = 0;

String sql = String.format("DELETE FROM consultation WHERE sid=%s AND id='%s'", dto.getSid(), dto.getId());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

//--------------------------------

}

//consultation.js

function consultInsertSubmit() {

//데이터 검사

//에러 메시지 출력

//데이터 전송

var obj = document.getElementById("consultInsertForm");

var name = document.getElementById("name");

var pw = document.getElementById("pw");

var title = document.getElementById("title");

var nameMsg = document.getElementById("nameMsg");

nameMsg.style.display = "none";

var pwMsg = document.getElementById("pwMsg");

pwMsg.style.display = "none";

var titleMsg = document.getElementById("titleMsg");

titleMsg.style.display = "none";

if (name.value == "" || name.value.length > 20) {

nameMsg.style.display = "inline";

return;

}

if (pw.value == "" || pw.value.length > 20) {

pwMsg.style.display = "inline";

return;

}

if (title.value == "" || title.value.length > 100) {

titleMsg.style.display = "inline";

return;

}

obj.submit();

}

function pwFormSubmit() {

//데이터 검사

//에러 메시지 출력

//데이터 전송

var obj = document.getElementById("pwForm");

var name = document.getElementById("name");

var pw = document.getElementById("pw");

var nameMsg = document.getElementById("nameMsg");

nameMsg.style.display = "none";

var pwMsg = document.getElementById("pwMsg");

pwMsg.style.display = "none";

if (name.value == "" || name.value.length > 20) {

nameMsg.style.display = "inline";

return;

}

if (pw.value == "" || pw.value.length > 20) {

pwMsg.style.display = "inline";

return;

}

obj.submit();

}

function consultModifySubmit(){

var obj = document.getElementById("consultModifyForm");

var title = document.getElementById("title");

var titleMsg = document.getElementById("titleMsg");

titleMsg.style.display="none";

if(title.value=="" || title.value.length>100){

titleMsg.style.display="inline";

return;

}

obj.submit();

}

function consultSearchSubmit() {

//데이터 검사

//에러 메시지 출력

//데이터 전송

var obj = document.getElementById("consultSearchForm");

var svalue = document.getElementById("svalue");

var searchMsg = document.getElementById("searchMsg");

searchMsg.style.display = "none";

if (svalue.value == "") {

searchMsg.style.display = "inline";

return;

}

obj.submit();

}

function loginFormSubmit() {

var obj = document.getElementById("loginForm");

var id = document.getElementById("id");

var pw = document.getElementById("pw");

var idMsg = document.getElementById("idMsg");

idMsg.style.display = "none";

var pwMsg = document.getElementById("pwMsg");

pwMsg.style.display = "none";

if (id.value == "" || id.value.length > 20) {

idMsg.style.display = "inline";

return;

}

if (pw.value == "" || pw.value.length > 20) {

pwMsg.style.display = "inline";

return;

}

obj.submit();

}

//댓글 펼치기, 감추기 호출 함수 추가

function commentDivPanel() {

var commentMsg = document.getElementById("commentMsg");

var commentDiv = document.getElementById("commentDiv");

if (commentMsg.innerHTML == "[댓글 펼치기]") {

commentMsg.innerHTML = "[댓글 감추기]";

commentDiv.style.display = "block";

} else {

commentMsg.innerHTML = "[댓글 펼치기]";

commentDiv.style.display = "none";

}

function commentInsertSubmit() {

var commentInsertForm = document.getElementById("commentInsertForm");

//데이터 검사 과정 추가

var title = document.getElementById("title");

var commentErrMsg = document.getElementById("commentErrMsg");

commentErrMsg.style.display="none";

if(title.value=="" || title.value.length>200){

commentErrMsg.style.display="inline";

return;

}

commentInsertForm.submit();

}

function consultMemberInsertSubmit() {

//데이터 검사

//에러 메시지 출력

//데이터 전송

var obj = document.getElementById("consultInsertForm");

var title = document.getElementById("title");

var titleMsg = document.getElementById("titleMsg");

titleMsg.style.display = "none";

if (title.value == "" || title.value.length > 100) {

titleMsg.style.display = "inline";

return;

}

obj.submit();

}

//---------------------------------

//회원 전용 삭제 함수 추가

function consultMemberDelete(sid) {

if (confirm("현재 자료를 삭제하시겠습니까?")) {

window.location.href="consultMemberDelete.con?sid="+sid;

}

//---------------------------------

//요청주소

http://localhost:8090/ConsultationServlet_20121207/consultList.con

-------------------------------------------------------

'Java > JSP & Servlet' 카테고리의 다른 글

[20121214] 23일차(상담게시판, JSTL & EL) (0)	2012.12.18
[20121213] 22일차 (상담게시판) (0)	2012.12.18
[20121211] 20일차 (상담게시판) (0)	2012.12.18
[20121210] 19일차 (상담게시판) (0)	2012.12.18
[20121207] 18일차 (상담게시판) (0)	2012.12.18

WRITTEN BY

: 빨강꼬마

[20121212] 21일차 (상담게시판)

'Java > JSP & Servlet' 카테고리의 다른 글

티스토리툴바