[Oracle DB] 게시판 / 서브 쿼리, MP_REPLY, MP_FILE #2

#2 서브 테이블 MP_REPLY와 MP_FILE에 사용한 query문 정리 (replayMapper.xml, boardMapper.xml)

 

---

 

1. MP_REPLY 테이블 생성

// MP_REPLY

CREATE TABLE MP_REPLY (
    BNO NUMBER NOT NULL, // 게시물 번호
    RNO NUMBER NOT NULL, // 댓글 번호
    CONTENT VARCHAR2(1000) NOT NULL, // 댓글 내용
    WRITER VARCHAR2(50) NOT NULL, // 댓글 작성자
    REGDATE DATE DEFAULT SYSDATE, // 댓글 작성일자
    PRIMARYKEY(BNO, RNO) // 기본 키
);

ALTER TABLE MP_REPLY ADD CONSTRAINT MP_REPLY_BNO FOREIGN KEY(BNO)
REFERENCES BOARD(BNO); // 외래 키 설정

CREATE SEQUENCE MP_REPLY_SEQ START WITH 1 MINVALUE 0; // 시퀀스 생성

COMMIT; // 저장

• 테이블에 있는 BNO는 값을 추가하는 것이 아닌 기존 BOARD 테이블의 BNO를 따라간다.
• 기존 BOARD 테이블의 BNO를 따라가기 위해 FOREIGN KEY를 사용한다.
• 외래 키 설정 부분은 BOARD 테이블의 BNO를 참조하여 MP_REPLY 테이블의 BNO를 MP_REPLY_BNO라는 이름의 외래키로 설정한다는 뜻
• 외래 키는 대상 테이블의 컬럼과 참조하는 테이블의 컬럼 값이 존재하는지 확인 후, 값이 없을 경우 에러 발생
• MP_REPLY_SEQ 시퀀스는 숫자 1부터 시작하여 증가하고, 최솟값 0보다 작아서는 안된다.

 

// 댓글 테스트

// 댓글 생성
INSERT INTO MP_REPLY(BNO, RNO, CONTENT, WRITER)
	VALUES(1, MP_REPLY_SEQ.NEXTVAL, '안녕하세요', 'fairycode');
    
// 댓글 조회
SELECT RNO, CONTENT, WRITER, REGDATE
	FROM MP_REPLY WHERE BNO = 1;

 

삽입 결과

 

---

2. replyMapper.xml 생성

2 - 1. 댓글 조회 (게시물 조회와 같은 형태)

// replayMapper.xml

// 댓글 조회
<select id="readReply" resultType="kr.co.vo.ReplyVO">
   	SELECT
	    RNO
          , CONTENT
          , WRITER
          , REGDATE
   	FROM MP_REPLY
    	WHERE BNO = #{bno}
</select>

 

2 - 2. 댓글 작성

// replyMapper.xml

// 댓글 작성
<insert id="writeReply">
	INSERT INTO MP_REPLY(
		  BNO
		, RNO
		, CONTENT
		, WRITER
	) VALUES(    
		  #{bno}
		, MP_REPLY_SEQ.NEXTVAL
		, #{content}
		, #{writer}	)
</insert>

• BNO는 PK의 BNO를 따라가고, RNO는 시퀀스 증가 값으로 매치한다.

 

2 - 3. 선택된 댓글 조회 / 수정 / 삭제 

// replyMapper.xml

// 댓글 수정
<update id="updateReply" parameterType="kr.co.vo.ReplyVO">
	UPDATE MP_REPLY 
    	SET CONTENT = #{content}
	WHERE RNO = #{rno}
</update>

// 댓글 삭제
<delete id="deleteReply" parameterType="kr.co.vo.ReplyVO">
	DELETE 
    	FROM MP_REPLY
	WHERE RNO = #{rno}
</delete>

// 선택된 댓글 조회
<select id="selectReply" resultType="kr.co.vo.ReplyVO">
	SELECT
	      BNO
	    , RNO
	    , CONTENT
  	    , WRITER
  	    , REGDATE
	FROM MP_REPLY
	WHERE RNO = #{rno}
</select>

• 선택된 댓글 조회는 RNO를 파라미터로 받아 RNO에 해당하는 댓글을 조회하여 수정 / 삭제하기 위함

 

---

3. 첨부파일 업로드 / 다운로드

3 - 1. MP_FILE 테이블 생성

// MP_FILE

CREATE TABLE MP_FILE
(
    FILE_NO NUMBER, // 파일 번호
    BNO NUMBER NOT NULL, // 게시물 번호
    ORG_FILE_NAME VARCHAR2(260) NOT NULL, // 원본 파일 이름
    STORED_FILE_NAME VARCHAR2(36) NOT NULL, // 변경된 파일 이름
    FILE_SIZE NUMBER, // 파일 크기
    REGDATE DATE DEFAULT SYSDATE NOT NULL, // 파일 등록일
    DEL_GB VARCHAR2(1) DEFAULT 'N' NOT NULL, // 삭제구분
    PRIMARY KEY(FILE_NO) // 기본 키
);

CREATE SEQUENCE SEQ_MP_FILE_NO // SEQ_MP_FILE_NO 시퀀스 생성
START WITH 1 INCREMENT BY 1 // 1부터 시작해서 1씩 증가 
NOMAXVALUE NOCACHE; // 최댓값 없음

COMMIT; // 저장

• 파일 업로드 시 그 파일은 서버에 저장되는데, 이미 파일이 저장된 상태에서 같은 이름으로 다시 저장하게 되면 저장 중 문제가 발생하거나 파일 이름이 변경될 수 있다.
• 따라서 파일 저장 시 원본 파일(ORG_FILE_NAME) 이름으로 저장, 서버로는 변경된 파일(SRORED_FILE_NAME) 이름으로 저장한다.
• NOCACHE : 시퀀스 값을 메모리에 할당하지 않고 시퀀스를 부여할 때마다 메모리에서 불러와 번호를 부여한다.

 

3 - 2. 첨부파일 업로드 

// boardMapper.xml

// 첨부파일 업로드
<insert id="insertFile" parameterType="hashMap">
	INSERT INTO MP_FILE(
		FILE_NO,
		BNO,
		ORG_FILE_NAME,
		STORED_FILE_NAME,
		FILE_SIZE
	)VALUES(
		SEQ_MP_FILE_NO.NEXTVAL,
		#{BNO},
		#{ORG_FILE_NAME},
		#{STORED_FILE_NAME},
		#{FILE_SIZE}
	)
</insert>

• 게시판에 글을 등록하면 자동으로 시퀀스에 의해 BNO 값이 1씩 증가하게 된다.
• 파일 첨부 insert문의 BNO에 글 작성후 증가된 BNO 값을 받을 수 없다.
• BoardServiceImpl.java 파일의 게시글 작성 메서드를 보면 게시글 작성 후 첨부 파일이 추가되는 것을 볼 수 있다.
• 게시글이 작성되면 BNO 값은 1이 증가된 상태이다.
• 파일 첨부 메서드에 이전 글(3 - 1)의 selectKey를 이용하여 따로 증가된 BNO를 셋팅하여 같은 BNO 값을 가질 수 있다.

 

3 - 3. 첨부파일 조회 / 수정 / 다운로드

// boardMapper.xml

// 첨부파일 조회
<select id="selectFileList" parameterType="int" resultType="hashMap">
	SELECT FILE_NO,
		ORG_FILE_NAME,
		ROUND(FILE_SIZE/1024,1) AS FILE_SIZE
	FROM MP_FILE
		WHERE BNO = #{BNO}
		AND DEL_GB = 'N'
	ORDER BY FILE_NO ASC
</select>
	
// 첨부파일 다운로드
<select id="selectFileInfo" parameterType="hashMap" resultType="hashMap">
	SELECT 
		STORED_FILE_NAME,
		ORG_FILE_NAME
	FROM MP_FILE
		WHERE FILE_NO = #{FILE_NO}
</select>
	
// 첨부파일 업데이트
<update id="updateFile" parameterType="hashMap">
   	UPDATE MP_FILE SET
   	DEL_GB = 'Y'
   	WHERE FILE_NO = #{FILE_NO}
</update>

• 첨부파일 조회는 BNO에 해당하고, DEL_GB(삭제 여부)가 'N'에 해당하는 첨부 파일의 번호와 원래 파일의 이름, 크기를 파일 번호의 오름차순(ASC)으로 조회한다.
• ROUND(FILE_SIZE/1024, 1) 은 파일 크기를 1024로 나눈 소수점을 반올림한 1의 자리 수 까지 조회한다는 뜻
• 파일 다운로드는 FILE_NO에 해당하는 파일의 원본 파일 이름과, 변경된 파일 이름을 SELECT한다.
• 파일 업데이트로 바뀌는 부분은 삭제 여부 밖에 없다.  따라서 DEL_GB(삭제 여부)를 'Y'라고 바꾸어 삭제해준다.

* parametertype과 resulttype의 차이

• parametertype : 해당 파라미터의 자료형을 명시해준다.
• resulttype : select된 데이터를 반환할 그릇을 정해주는 것