[20121207] 18일차 (상담게시판)

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

상담 게시판 (JSP, Servlet, JDBC 버전)

1. 웹 상에서 글쓰기, 글보기를 할 수 있는 프로그램.

2. 회원, 비회원 글쓰기 가능. 회원 댓글 쓰기. 회원 가입. 회원 로그인, 로그아웃.

3. 관리자 관리 기능. 관리자 답변글 쓰기.

4. 프로젝트 생성 (ConsultationServlet_20121207)

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

--상담게시판 테이블 (글번호, 글쓴이이름, 패스워드, 제목, 내용, 작성일)

CREATE TABLE consultation (

sid NUMBER  --PK 지정. 시퀀스 지원.

,name NVARCHAR2(20)

,pw VARCHAR2(20) --암호화 지원

,title NVARCHAR2(100)

,content NVARCHAR2(2000)

,wdate DATE --자동 입력. 시스템 현재 날짜.

);

ALTER TABLE consultation

ADD CONSTRAINT consultation_sid_pk PRIMARY KEY(sid);

CREATE SEQUENCE consultationSeq;

INSERT INTO consultation (sid, name, pw, title, content, wdate)

VALUES (consultationSeq.nextVal

,'관리자'

,encrypt('1234', '관리자')  --사용자 정의 암호화 함수

,'상담 게시판이 오픈되었습니다'

,'많은 이용 바랍니다'

,SYSDATE);

COMMIT;

SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

FROM consultation 

ORDER BY sid DESC;

--SID 기준 검색 쿼리

SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

, content

FROM consultation 

WHERE sid=1;

SELECT sid, title, content

FROM consultation 

WHERE sid=1 AND name='관리자' AND pw=encrypt('1234', '관리자');

--이름 기준 검색 (부분 검색 가능하도록 할 것. 대소문자 구분 하지 않도록 할 것)

SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

FROM consultation 

WHERE LOWER(name) LIKE '%'||LOWER('홍길동')||'%'

ORDER BY sid DESC;

--제목 기준 검색 (부분 검색 가능하도록 할 것. 대소문자 구분 하지 않도록 할 것)

SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

FROM consultation 

WHERE LOWER(title) LIKE '%'||LOWER('html')||'%'

ORDER BY sid DESC;

--내용 기준 검색 (부분 검색 가능하도록 할 것. 대소문자 구분 하지 않도록 할 것)

SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

FROM consultation 

WHERE LOWER(content) LIKE '%'||LOWER('html')||'%'

ORDER BY sid DESC;

--페이지 처리 쿼리문 (총 게시물 카운트)

SELECT COUNT(*) AS count FROM consultation;

--페이지 처리 쿼리문 (특정 페이지 게시물 읽어오기. 시작번호, 끝번호 필요)

--> 한 페이지당 10개의 게시물 처리

--> 1페이지 게시물은 시작번호 1 ~ 끝번호 10

--> 2페이지 게시물은 시작번호 11 ~ 끝번호 20

SELECT *

FROM (SELECT ROWNUM rnum, data.*

FROM (SELECT sid, name, title

,TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

FROM consultation

ORDER BY sid DESC) data)

WHERE rnum>=1 AND rnum<=10;

CREATE VIEW pageListView

AS

SELECT *

FROM (SELECT ROWNUM rnum, data.*

FROM (SELECT sid, name, title

,TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

FROM consultation

ORDER BY sid DESC) data);

SELECT * 

FROM pageListView

WHERE rnum>=1 AND rnum<=10;

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

--관리자 답변글 전용 테이블

CREATE TABLE consultReply (

rid NUMBER  --PK

,name NVARCHAR2(20) --관리자 전용

--,pw VARCHAR2(20)  --관리자 전용이므로 패스워드 저장 안함

,title NVARCHAR2(100)

,content NVARCHAR2(2000)

,wdate DATE

);

ALTER TABLE consultReply

ADD CONSTRAINT consultReply_rid_pk PRIMARY KEY(rid);

CREATE SEQUENCE consultReplySeq;

--답변글과 부모글 연관성 지정 필요

--> 부모글 테이블에 답변글 번호를 저장하는 컬럼 추가 및 FK 지정

ALTER TABLE consultation

ADD rid NUMBER;

ALTER TABLE consultation

ADD CONSTRAINT consultation_rid_fk FOREIGN KEY (rid)

REFERENCES consultReply(rid);

--답변글 추가 쿼리

INSERT INTO consultReply (rid, name, title, content, wdate)

VALUES (consultReplySeq.nextval, '관리자', '예약 확인했습니다.'

,'내일 오전 중으로 연락 드리겠습니다.', SYSDATE);

COMMIT;

--특정 부모글에 답변글 번호를 연결하는 쿼리

UPDATE consultation

SET rid=consultReplySeq.currval

WHERE sid=49;

COMMIT;

--답변글 번호도 읽어올 수 있도록 SELECT 쿼리문 변경(검색 쿼리)

SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

,rid  --답변글 번호 추가

FROM consultation 

WHERE LOWER(name) LIKE '%'||LOWER('홍길동')||'%'

ORDER BY sid DESC;

SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

, content

,rid  --답변글 번호 추가

FROM consultation 

WHERE sid=1;

--답변글 번호도 읽어올 수 있도록 SELECT 쿼리문 변경(전체 출력 쿼리)

CREATE OR REPLACE VIEW pageListView

AS

SELECT *

FROM (SELECT ROWNUM rnum, data.*

FROM (SELECT sid, name, title

,TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

,rid  --답변글 번호 추가

FROM consultation

ORDER BY sid DESC) data);

--답변글 읽어오는 쿼리(RID 필요)

SELECT rid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

, content

FROM consultReply

WHERE rid=1;

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

회원 가입, 로그인, 로그아웃 쿼리 작성

--회원 테이블 작성

CREATE TABLE jmember (

id VARCHAR2(20) --PK

,pw VARCHAR2(20) --암호화 지원

,name NVARCHAR2(20)

,email VARCHAR2(100)

,tel VARCHAR2(30)

,wdate DATE

,grade NUMBER DEFAULT 4 --관리자 1, 직원 2, 학생 3, 회원가입 4

);

ALTER TABLE jmember

ADD CONSTRAINT jmember_id_pk PRIMARY KEY(id);

--관리자 전용 입력 쿼리

INSERT INTO jmember (id, pw, name, email, tel, wdate, grade)

VALUES ('admin', encrypt('1234', 'admin'),'관리자', 'admin@test.com', '02-123-1234', SYSDATE, 1);

COMMIT;

--사용자 입력 쿼리 (회원등급이 자동으로 회원가입(4) 등급이 된다)

INSERT INTO jmember (id, pw, name, email, tel, wdate)

VALUES ('test', encrypt('1234', 'test'),'테스트'

, 'test@test.com', '02-111-1111', SYSDATE);

COMMIT;

--회원 확인용 쿼리 (id, pw 동시 검사)

SELECT id, name, email, tel

, TO_CHAR(wdate, 'YYYY-MM-DD HH24:MI') AS wdate

, grade

FROM jmember

WHERE id='admin' AND pw=encrypt('1234', 'admin');

--회원 확인용 쿼리 (id만 검사)

SELECT id, name, email, tel

, TO_CHAR(wdate, 'YYYY-MM-DD HH24:MI') AS wdate

, grade

FROM jmember

WHERE id='admin';

--패스워드 수정 쿼리

UPDATE jmember 

SET pw=encrypt('새로운패스워드', '아이디')

WHERE pw=encrypt('기존패스워드', '아이디')

AND id = '아이디';

--회원정보 수정 쿼리

UPDATE jmember

SET name='', email='', tel=''

WHERE pw=encrypt('기존패스워드', '아이디')

AND id = '아이디';

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

--댓글 저장용 테이블 생성

CREATE TABLE consultComment (

cid NUMBER --PK. 댓글 번호

,id VARCHAR2(20) -- 글쓴이의 아이디

,name NVARCHAR2(20) --글쓴이의 이름

,title NVARCHAR2(200)

,wdate DATE

,sid NUMBER --FK. 부모 글번호

);

ALTER TABLE consultComment

ADD CONSTRAINT consultComment_cid_pk PRIMARY KEY(cid);

ALTER TABLE consultComment

ADD CONSTRAINT consultComment_sid_fk FOREIGN KEY (sid)

REFERENCES consultation(sid);

--댓글 추가 여부를 확인할 수 있는 컬럼을 부모 테이블에 추가

ALTER TABLE consultation

ADD commentCount NUMBER DEFAULT 0;

CREATE SEQUENCE consultCommentSeq;

--댓글 입력 (댓글 테이블에 댓글 내용 추가)

INSERT INTO consultComment (cid, id, name, title, wdate, sid)

VALUES (consultCommentSeq.nextval, 'hong', '홍길동', '댓글 테스트', SYSDATE, 1);

COMMIT;

--댓글 입력 (부모 테이블에 댓글 갯수 증가)

UPDATE consultation

SET commentCount = commentCount + 1

WHERE sid=1;

COMMIT;

--댓글 출력(부모글 기준)

SELECT cid, id, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

FROM consultComment

WHERE sid=1

ORDER BY cid ASC;

--댓글 갯수도 읽어올 수 있도록 SELECT 쿼리문 변경(검색 쿼리)

SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

,rid

,commentCount --댓글 갯수

FROM consultation 

WHERE LOWER(name) LIKE '%'||LOWER('홍길동')||'%'

ORDER BY sid DESC;

SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

, content

,rid

,commentCount --댓글 갯수

FROM consultation 

WHERE sid=1;

--댓글 갯수도 읽어올 수 있도록 SELECT 쿼리문 변경(전체 출력 쿼리)

CREATE OR REPLACE VIEW pageListView

AS

SELECT *

FROM (SELECT ROWNUM rnum, data.*

FROM (SELECT sid, name, title

,TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

,rid

,commentCount --댓글 갯수

FROM consultation

ORDER BY sid DESC) data);

--댓글 삭제 쿼리

DELETE 

FROM consultComment

WHERE cid=1 AND id='hong';

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

회원 전용 글쓰기 과정 추가

--회원 전용 글쓰기를 위해서 consultation 테이블에 id 컬럼 추가

ALTER TABLE consultation

ADD id VARCHAR2(20);

--글쓰기 내용을 입력하는 과정에서 ID 추가

INSERT INTO consultation (sid, name, pw, title, content, wdate, id)

VALUES (consultationSeq.nextVal

,'관리자'

,encrypt('1234', '관리자')  --사용자 정의 암호화 함수

,'상담 게시판이 오픈되었습니다'

,'많은 이용 바랍니다'

,SYSDATE

, 'hong');

COMMIT;

--아이디도 읽어올 수 있도록 SELECT 쿼리문 변경(검색 쿼리)

SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

,rid

,commentCount

,id --아이디

FROM consultation 

WHERE LOWER(name) LIKE '%'||LOWER('홍길동')||'%'

ORDER BY sid DESC;

SELECT sid, name, title, TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

, content

,rid

,commentCount

,id --아이디

FROM consultation 

WHERE sid=1;

--아이디도 읽어올 수 있도록 SELECT 쿼리문 변경(전체 출력 쿼리)

CREATE OR REPLACE VIEW pageListView

AS

SELECT *

FROM (SELECT ROWNUM rnum, data.*

FROM (SELECT sid, name, title

,TO_CHAR(wdate, 'YYYY-MM-DD') AS wdate

,rid

,commentCount

,id --아이디

FROM consultation

ORDER BY sid DESC) data);

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

//web.xml -> 서블릿 주소 등록

//ConsultationServlet.java -> 서블릿 클래스. 서블릿 주소 분석. 액션 처리.

//ConsultationDTO.java

//ConsultationDAO.java

//common.css, table.css -> CSS 전용 파일.

//consultList.jsp -> 글목록 페이지

//consultInsertForm.jsp -> 글 입력 페이지

//consultation.js -> 자바스크립트 전용 파일.

//consultView.jsp -> 글 내용 보기 페이지

//consultPW.jsp -> 패스워드 검사 페이지

//consultModifyForm.jsp -> 글 수정 페이지

//MyUtil.java -> 페이징 처리 전용 메소드 

//adminReplyForm.jsp -> 관리자 답변글 쓰기 전용 페이지

//MemberDTO.java

//MemberDAO.java

//MemberServlet.java -> 회원 관리 전용 서블릿

//login.jsp -> 로그인 화면용 페이지

//memberInfo.jsp -> 회원 정보 출력용 페이지

//mainMenu.jsp -> 주메뉴 전용 페이지

//consultMemberInsertForm.jsp -> 회원 전용 글쓰기 페이지

//memberInsertForm.jsp -> 회원 가입 화면용 페이지

//memberIDCheck.jsp -> Ajax를 이용한 아이디 중복 체크 페이지

//memberInsertOK.jsp -> 회원 가입 완료 메시지 페이지

//memberInsertCancel.jsp -> 회원 가입 실패 메시지 페이지

//memberList.jsp -> 회원 명단 출력 페이지 (관리자, 직원 전용)

//adminUpdateForm.jsp -> 회원 수정 페이지 (관리자 전용)

//요청주소

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

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

상담게시판 글목록 페이지 작성

//web.xml -> 서블릿 주소 등록

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">

  <display-name>ConsultationServlet_20121207</display-name>

  <welcome-file-list>

    <welcome-file>index.html</welcome-file>

    <welcome-file>index.htm</welcome-file>

    <welcome-file>index.jsp</welcome-file>

    <welcome-file>default.html</welcome-file>

    <welcome-file>default.htm</welcome-file>

    <welcome-file>default.jsp</welcome-file>

  </welcome-file-list>

  

<servlet>

<servlet-name>consultationServlet</servlet-name>

<servlet-class>com.test.ConsultationServlet</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>consultationServlet</servlet-name>

<url-pattern>*.con</url-pattern>

</servlet-mapping>

 

</web-app>

//ConsultationDTO.java

package com.test;

public class ConsultationDTO {

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

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;

}

}

//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;

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);

}

}

private void consultList(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

arrayList = dao.lists();

}catch(Exception e){

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

}finally{

try {

dao.close();

} catch (SQLException e) {

}

}

req.setAttribute("arrayList", arrayList);

RequestDispatcher dispatcher

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

dispatcher.forward(req, resp);

}

}

//consultList.jsp

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

    pageEncoding="EUC-KR"%>

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

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

<%

@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\">%s</td>", dto.getTitle()));

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>");

}

%>    

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

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title>Java 전문 교육센터</title>

<link rel="stylesheet" type="text/css" href="common.css">

<link rel="stylesheet" type="text/css" href="table.css">

</head>

<body>

<div>

<div>

<h1>Java 전문 교육센터</h1>

<a href="javascript:">[교육원소개]</a>

<a href="javascript:">[교육과정안내]</a>

<a href="javascript:">[취업지원센터]</a>

<a href="consultList.con">[상담게시판]</a>

<a href="member.mem">[회원가입]</a>

<a href="login.mem">[로그인]</a>

<hr>

</div>

<div>

<h3>[상담게시판_글목록]</h3>

<table cellpadding="5" class="style01">

<tr>

<td class="tName" width="60">번호</td>

<td class="tName">제목</td>

<td class="tName" width="160">작성자</td>

<td class="tName" width="160">작성일</td>

</tr>

<!-- <tr>

<td class="bDot">1</td>

<td class="bTitle">상담 게시판이 오픈되었습니다.</td>

<td class="bDot">관리자</td>

<td class="bDot">2012-03-13</td>

</tr> -->

<%=str%>

</table>

<table class="style01">

<tr>

<td class="bDot">[1][2][3][4]...</td>

</tr>

</table>

<form>

<table class="style01">

<tr>

<td>

<select><option>제목</option></select>

<input type="text">

<a href="">[검색]</a>

</td>

<td style="text-align:right;">

<a href="">[새글쓰기]</a>

</td>

</tr>

</table>

</form>

</div>

</div>

</body>

</html>

//요청주소

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

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

상담 게시판 비회원 글쓰기 작성 (패스워드 필요)

//consultList.jsp -> 새글쓰기 메뉴에 서블릿 주소 지정

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

    pageEncoding="EUC-KR"%>

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

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

<%

@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\">%s</td>", dto.getTitle()));

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>");

}

%>    

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

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title>Java 전문 교육센터</title>

<link rel="stylesheet" type="text/css" href="common.css">

<link rel="stylesheet" type="text/css" href="table.css">

</head>

<body>

<div>

<div>

<h1>Java 전문 교육센터</h1>

<a href="javascript:">[교육원소개]</a>

<a href="javascript:">[교육과정안내]</a>

<a href="javascript:">[취업지원센터]</a>

<a href="consultList.con">[상담게시판]</a>

<a href="member.mem">[회원가입]</a>

<a href="login.mem">[로그인]</a>

<hr>

</div>

<div>

<h3>[상담게시판_글목록]</h3>

<table cellpadding="5" class="style01">

<tr>

<td class="tName" width="60">번호</td>

<td class="tName">제목</td>

<td class="tName" width="160">작성자</td>

<td class="tName" width="160">작성일</td>

</tr>

<!-- <tr>

<td class="bDot">1</td>

<td class="bTitle">상담 게시판이 오픈되었습니다.</td>

<td class="bDot">관리자</td>

<td class="bDot">2012-03-13</td>

</tr> -->

<%=str%>

</table>

<table class="style01">

<tr>

<td class="bDot">[1][2][3][4]...</td>

</tr>

</table>

<form>

<table class="style01">

<tr>

<td>

<select><option>제목</option></select>

<input type="text">

<a href="">[검색]</a>

</td>

<td style="text-align:right;">

<a href="consultInsertForm.con">[새글쓰기]</a>

</td>

</tr>

</table>

</form>

</div>

</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;

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);

}

}

private void consultList(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

arrayList = dao.lists();

}catch(Exception e){

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

}finally{

try {

dao.close();

} catch (SQLException e) {

}

}

req.setAttribute("arrayList", arrayList);

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);

}

}

//consultInsertForm.jsp

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

    pageEncoding="EUC-KR"%>

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

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title>Java 전문 교육센터</title>

<link rel="stylesheet" type="text/css" href="common.css">

<link rel="stylesheet" type="text/css" href="table.css">

<script type="text/javascript" src="consultation.js"></script>

</head>

<body>

<div>

<div>

<h1>Java 전문 교육센터</h1>

<a href="javascript:">[교육원소개]</a>

<a href="javascript:">[교육과정안내]</a>

<a href="javascript:">[취업지원센터]</a>

<a href="consultList.con">[상담게시판]</a>

<a href="member.mem">[회원가입]</a>

<a href="login.mem">[로그인]</a>

<hr>

</div>

<div>

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

<form action="consultInsert.con" method="post" id="consultInsertForm">

<table cellpadding="5" class="style01">

<tr>

<td class="tName" width="100">작성자*</td>

<td class="bTitle"><input type="text" id="name" name="name"><span id="nameMsg" style="color:red; display:none;">1~20자 이름 입력</span></td>

</tr>

<tr>

<td class="tName" width="100">패스워드*</td>

<td class="bTitle"><input type="password" id="pw" name="pw"><span id="pwMsg" style="color:red; display:none;">1~20자 패스워드 입력</span></td>

</tr>

<tr>

<td class="tName" width="100">제목*</td>

<td class="bTitle"><input type="text" style="width:600px;" id="title" name="title"><span id="titleMsg" style="color:red; display:none;">1~100자 제목 입력</span></td>

</tr>

<tr>

<td class="tName" width="100">내용</td>

<td class="bTitle"><textarea style="width:600px;height:200px;" id="content" name="content"></textarea></td>

</tr>

</table>

<table>

<tr>

<td>

<a href="javascript:consultInsertSubmit()">[글쓰기]</a>

<a href="javascript:consultInsertReset()">[새로작성]</a>

<a href="consultList.con">[목록보기]</a>

</td>

</tr>

</table>

</form>

</div>

</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();

}

//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;

}

}

//요청주소

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

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

상담 게시판 글 내용 보기

//consultList.jsp -> 제목 부분에 링크 추가

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

    pageEncoding="EUC-KR"%>

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

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

<%

@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></td>", dto.getSid(), dto.getTitle()));

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>");

}

%>    

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

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title>Java 전문 교육센터</title>

<link rel="stylesheet" type="text/css" href="common.css">

<link rel="stylesheet" type="text/css" href="table.css">

</head>

<body>

<div>

<div>

<h1>Java 전문 교육센터</h1>

<a href="javascript:">[교육원소개]</a>

<a href="javascript:">[교육과정안내]</a>

<a href="javascript:">[취업지원센터]</a>

<a href="consultList.con">[상담게시판]</a>

<a href="member.mem">[회원가입]</a>

<a href="login.mem">[로그인]</a>

<hr>

</div>

<div>

<h3>[상담게시판_글목록]</h3>

<table cellpadding="5" class="style01">

<tr>

<td class="tName" width="60">번호</td>

<td class="tName">제목</td>

<td class="tName" width="160">작성자</td>

<td class="tName" width="160">작성일</td>

</tr>

<!-- <tr>

<td class="bDot">1</td>

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

<td class="bDot">관리자</td>

<td class="bDot">2012-03-13</td>

</tr> -->

<%=str%>

</table>

<table class="style01">

<tr>

<td class="bDot">[1][2][3][4]...</td>

</tr>

</table>

<form>

<table class="style01">

<tr>

<td>

<select><option>제목</option></select>

<input type="text">

<a href="">[검색]</a>

</td>

<td style="text-align:right;">

<a href="consultInsertForm.con">[새글쓰기]</a>

</td>

</tr>

</table>

</form>

</div>

</div>

</body>

</html>

//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 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"));

}

rs.close();

return dto;

}

}

//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;

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);

}

}

private void consultList(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

ArrayList<ConsultationDTO> arrayList

= new ArrayList<ConsultationDTO>();

ConsultationDAO dao = new ConsultationDAO();

try {

dao.connect();

arrayList = dao.lists();

}catch(Exception e){

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

}finally{

try {

dao.close();

} catch (SQLException e) {

}

}

req.setAttribute("arrayList", arrayList);

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();

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("consultView.jsp");

dispatcher.forward(req, resp);

}

}

//consultView.jsp

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

    pageEncoding="EUC-KR"%>

<%@ 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("<", "&lt;").replaceAll(">", "&gt;");

title = title.replaceAll("<", "&lt;").replaceAll(">", "&gt;");

content = content.replaceAll("<", "&lt;").replaceAll(">", "&gt;");

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

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

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

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

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

%>    

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

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title>Java 전문 교육센터</title>

<link rel="stylesheet" type="text/css" href="common.css">

<link rel="stylesheet" type="text/css" href="table.css">

</head>

<body>

<div>

<div>

<h1>Java 전문 교육센터</h1>

<a href="javascript:">[교육원소개]</a>

<a href="javascript:">[교육과정안내]</a>

<a href="javascript:">[취업지원센터]</a>

<a href="consultList.con">[상담게시판]</a>

<a href="member.mem">[회원가입]</a>

<a href="login.mem">[로그인]</a>

<hr>

</div>

<div>

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

<table cellpadding="5" class="style01 borderTop">

<tr>

<td class="bTitle">

<h3><!-- 상담 게시판을 오픈합니다. --><%=title%></h3>

<span><!-- 관리자 --><%=name%></span>

<span><!-- 2010-01-01 --><%=wdate%></span>

</td>

</tr>

<tr>

<td class="bTitle" style="padding-top:20px;padding-bottom:20px;"><!-- 많은 이용 바랍니다. --><%=content%></td>

</tr>

</table>

<div style="margin-top:10px;">

<a href="consultList.con">[목록보기]</a>

<a href="">[답글쓰기]</a>

<a href="">[글 수정]</a>

<a href="">[글 삭제]</a>

</div>

</div>

</div>

</body>

</html>

//요청주소

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

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

상담 게시판 글 수정 (패스워드 검사 과정 추가)

//consultView.jsp -> [글수정] 메뉴에 링크 추가

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

    pageEncoding="EUC-KR"%>

<%@ 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("<", "&lt;").replaceAll(">", "&gt;");

title = title.replaceAll("<", "&lt;").replaceAll(">", "&gt;");

content = content.replaceAll("<", "&lt;").replaceAll(">", "&gt;");

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

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

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

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

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

%>    

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

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title>Java 전문 교육센터</title>

<link rel="stylesheet" type="text/css" href="common.css">

<link rel="stylesheet" type="text/css" href="table.css">

</head>

<body>

<div>

<div>

<h1>Java 전문 교육센터</h1>

<a href="javascript:">[교육원소개]</a>

<a href="javascript:">[교육과정안내]</a>

<a href="javascript:">[취업지원센터]</a>

<a href="consultList.con">[상담게시판]</a>

<a href="member.mem">[회원가입]</a>

<a href="login.mem">[로그인]</a>

<hr>

</div>

<div>

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

<table cellpadding="5" class="style01 borderTop">

<tr>

<td class="bTitle">

<h3><!-- 상담 게시판을 오픈합니다. --><%=title%></h3>

<span><!-- 관리자 --><%=name%></span>

<span><!-- 2010-01-01 --><%=wdate%></span>

</td>

</tr>

<tr>

<td class="bTitle" style="padding-top:20px;padding-bottom:20px;"><!-- 많은 이용 바랍니다. --><%=content%></td>

</tr>

</table>

<div style="margin-top:10px;">

<a href="consultList.con">[목록보기]</a>

<a href="">[답글쓰기]</a>

<a href="consultModify.con?sid=<%=sid%>">[글 수정]</a>

<a href="">[글 삭제]</a>

</div>

</div>

</div>

</body>

</html>

//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;

}

}

//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;

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);

}

}

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);

}

}

//consultModifyForm.jsp

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

    pageEncoding="EUC-KR"%>

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

<%

ConsultationDTO dto

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

String sid = dto.getSid();

String title = dto.getTitle();

String content = dto.getContent();

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

//수정 오류 처리 필요

//수정 폼 화면에서 제목 부분에 큰 따옴표(") 포함시

//제목 일부가 나타나지 않는 문제 발생

//-> 큰따옴표(")를 특수문자(&quot;)로 처리함.

title = title.replaceAll("\"", "&quot;");

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

%>    

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

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title>Java 전문 교육센터</title>

<link rel="stylesheet" type="text/css" href="common.css">

<link rel="stylesheet" type="text/css" href="table.css">

<script type="text/javascript" src="consultation.js"></script>

</head>

<body>

<div>

<div>

<h1>Java 전문 교육센터</h1>

<a href="javascript:">[교육원소개]</a>

<a href="javascript:">[교육과정안내]</a>

<a href="javascript:">[취업지원센터]</a>

<a href="consultList.con">[상담게시판]</a>

<a href="member.mem">[회원가입]</a>

<a href="login.mem">[로그인]</a>

<hr>

</div>

<div>

<h3>[상담게시판_글수정]</h3>

<form action="consultModify.con" method="post" id="consultModifyForm">

<%--수정을 위해서 sid 값 재전송 필요 --%>

<input type="hidden" name = "sid" value="<%=sid%>">

<table cellpadding="5" class="style01 borderTop borderBottom">

<!--

<tr>

<td class="tName" width="100">작성자*</td>

<td class="bTitle"><input type="text" id="uname" name="uname" value=""><span id="unameMsg"></span></td>

</tr>

<tr>

<td class="tName" width="100">패스워드*</td>

<td class="bTitle"><input type="password" id="pw" name="pw" value=""><span id="pwMsg"></span></td>

</tr>

-->

<tr>

<td class="tName" width="100">제목*</td>

<td class="bTitle"><input type="text" style="width:600px;" id="title" name="title" value="<%=title%>"><span id="titleMsg"></span></td>

</tr>

<tr>

<td class="tName" width="100">내용</td>

<td class="bTitle"><textarea style="width:600px;height:200px;" id="content" name="content"><%=content%></textarea></td>

</tr>

</table>

<table>

<tr>

<td>

<a href="javascript:consultModifySubmit()">[글수정]</a>

<a href="javascript:consultModifyReset()">[새로작성]</a>

<a href="">[취소]</a>

<a href="consultList.con">[목록보기]</a>

</td>

</tr>

</table>

</form>

</div>

</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();

}

//요청주소

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

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