[20121228] 8일차 (SpringMVC Annotation, iBatis)

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

상담 게시판 (Spring, iBatis 버전)

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

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

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

4. 프로젝트 생성 (ConsultationSpring_20121228)

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

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

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

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

//ConsultationController.java -> 컨트롤러 클래스.

//ConsultationDTO.java

//consultationIbatis.xml

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

//consultList.jsp -> 글목록 페이지. JSTL, EL 표현 사용.

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

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

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

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

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

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

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

//MemberDTO.java

//memberIbatis.xml

//MemberController.java -> 회원 관리 전용 컨트롤러 클래스

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

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

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

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

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

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

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

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

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

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

//memberPWModifyForm.jsp -> 패스워드 수정 페이지(사용자)

//memberModifyForm.jsp -> 회원 수정 페이지 (사용자)

//sqlMapConfig.xml -> iBatis XML 파일 등록

//요청주소

http://localhost:8090/ConsultationSpring_20121228/consultList.mvc

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