[Oracle DB] 게시판 / 메인 쿼리, BOARD #1

#1 메인 테이블 BOARD에 사용한 query문 정리 (boardMapper.xml)

 

---

 

총 4개의 테이블 제작

 

1. 게시판 테이블 BOARD, SEQUENCE 생성

// BOARD 테이블

CREATE TABLE BOARD(
    BNO NUMBER NOT NULL, // 게시물 번호
    TITLE VARCHAR2(100)     NOT NULL, // 게시물 제목
    CONTENT VARCHAR2(2000)  NOT NULL, // 게시물 내용
    WRITER VARCHAR2(100)    NOT NULL, // 작성자
    REGDATE DATE            DEFAULT SYSDATE, // 게시물 등록일
    PRIMARY KEY(BNO) // 기본 키
);

CREATE SEQUENCE BOARD_SEQ // BOARD_SEQ 라는 시퀀스 생성
START WITH 1 // 시퀀스를 지정한 컬럼의 번호를 
INCREMENT BY 1; // 숫자 1부터 1씩 증가 시킴

• BOARD 라는 메인 테이블에 BNO(PK), TITLE, CONTENT, WRITER, REGDATE라는 컬럼들을 생성
• BOARD_SEQ 라는 자동 증가 시퀀스를 생성해 이후 게시물 번호인 BNO를 1씩 증가 시키는 데에 활용

 

// BOARD 테이블에 임의의 값 삽입
// 시퀀스 BOARD_SEQ에 .NEXTVAL을 사용하여 BNO에 일련 번호를 생성함

INSERT INTO BOARD(BNO, TITLE, CONTENT, WRITER)
VALUES (BOARD_SEQ.NEXTVAL, '제목', '내용', 'fairycode'); 
     
// 값 생성 후 

COMMIT;
SELECT * FROM BOARD;

 

삽입 결과

 

---

2. 페이징 테스트를 위한 더미용 데이터 생성

INSERT INTO BOARD(BNO, TITLE, CONTENT, WRITER)
SELECT BOARD_SEQ.NEXTVAL, TITLE, CONTENT, WRITER FROM BOARD;

// 저장
COMMIT;

• 생성되어 있던 데이터들을 SELECT 하여 그 값들을 INSERT 함으로써, 명령을 실행할 때 마다 데이터가 2배씩 증가함

 

---

boardMapper.xml / QUERY 문 설명

3 - 1. 게시물 작성

// boardMapper.xml

// 게시물 작성
<insert id="insert" parameterType="kr.co.vo.BoardVO" useGeneratedKeys="true" keyProperty="bno">
    <selectKey keyProperty="bno" resultType="int" order="BEFORE">
    	SELECT BOARD_SEQ.NEXTVAL FROM DUAL
    </selectKey>
    INSERT INTO BOARD(    BNO 
			, TITLE 
			, CONTENT 
			, WRITER  )
                VALUES(    BOARD_SEQ.NEXTVAL
			, #{title}
			, #{content}
			, #{writer}  )
</insert>

• userGeneratedKeys : 자동적으로 증가되는 키를 받는 getGenereateKeys() 를 사용할 수 있게 true로 설정
• keyProperty : selecyKey에 의해 선택 된 키가 무엇인지 설정
• order = "BEFORE" : insert를 실행하기 전에 selectKey문을 실행한다는 뜻
• FROM DUAL의 'DUAL'은 SYS 소유의 테이블. FROM에 담을 것이 없을 때 사용하는 용도

• 실행 순서 // 참고 (3 - 2)

1. insert문 실행 전, selectKey문 안의 쿼리 문 실행
2. 실행한 쿼리에서 가져올 값 bno를 int형으로 가져옴
3. insert문 안에 있는 keyProperty에 의해 <selectKey>에 있는 keyProperty값 bno를 가져옴
4. bno 값을 파라미터로 넣어줌
5. 게시물이 새로 작성된 후 bno값은 selectKey문에 의해 1이 증가된 상태가 됨

 

3 - 2. 게시물 조회

// boardMapper.xml

// 게시물 조회 
<select id="read" parameterType="int" resultType="kr.co.vo.BoardVO">
	SELECT	BNO
		  , TITLE
		  , CONTENT
		  , WRITER
		  , REGDATE
	 FROM MP_BOARD
	 WHERE BNO = #{bno}
</select>

• WHERE BNO = #{bno} : where에 bno 값을 넣어주어 제목을 클릭했을 때 그 번호에 해당하는 게시물을 조회함

 

3 - 3. 게시물 수정 / 삭제

// boardMapper.xml

// 게시물 수정
<update id="update" parameterType="kr.co.vo.BoardVO">
	UPDATE BOARD
	SET TITLE = #{title}
    	  , CONTENT = #{content}
	WHERE BNO = #{bno}
</update>

// 게시물 삭제
<delete id="delete" parameterType="int">
	DELETE 
	FROM BOARD
	WHERE BNO = #{bno}
</delete>

 

• 게시물 수정 시 bno 값에 해당하는 게시물의 제목, 내용을 업데이트함
• 게시물 삭제 시 bno 값에 해당하는 게시물을 BOARD 테이블에서 삭제함

 

3 - 4. 게시물 총 갯수

// boardMapper.xml

// 게시물 총 갯수
<select id="listCount" parameterType="kr.co.vo.SearchCriteria" resultType="int">
	SELECT COUNT(BNO)
	   FROM BOARD
	   WHERE 1=1
	<include refid="search"></include>	
	   AND BNO > 0
</select>

• WHERE 1=1 은 '참(true)' 를 뜻하며, 조회하려는 데이터들에 조건을 거는 문법 // 참고
• <include refid="search"> 는 boardMapper.xml 내에 선언한 search 쿼리문을 사용한다는 뜻 // 3 - 5 참조
• WHERE 1=1 AND BNO > 0 는 bno가 0 이상인 것들의 bno 갯수를 카운트함

 

3 - 5. 게시판 페이징

INSERT INTO BOARD(BNO, TITLE, CONTENT, WRITER)
SELECT BOARD_SEQ.NEXTVAL, TITLE, CONTENT, WRITER FROM BOARD;

// 저장
COMMIT;

• 생성되어 있던 데이터들을 SELECT 하여 그 값들을 INSERT 함으로써, 명령을 실행할 때 마다 데이터가 2배씩 증가함

 

// boardMapper.xml

<select id="listPage" resultType="kr.co.vo.BoardVO" parameterType="kr.co.vo.SearchCriteria">
	SELECT  BNO, 
	        TITLE, 
	        CONTENT,
	        WRITER, 
	        REGDATE,
	        HIT // 조회수
	 FROM ( 
	        SELECT BNO, 
	               TITLE, 
	               CONTENT, 
	               WRITER, 
	               REGDATE, 
	               HIT,
	               ROW_NUMBER() OVER(ORDER BY BNO DESC) AS RNUM
	         FROM BOARD
	         WHERE 1=1 
	         	<include refid="search"></include>
	                       ) MP
	WHERE RNUM BETWEEN #{rowStart} AND #{rowEnd}
	ORDER BY BNO DESC
</select>

• 첫 번째 FROM() 절에서 RNUM으로 별칭한 ROW_NUMBER() 에 번호를 내림차순(DESC)으로 매김
• WHERE 절에서 RNUM이 #{rowStrat} 부터 #{rowEnd} 까지 내림차순으로 조회

 

3 - 6. 게시판 검색

// boardMapper.xml
   
<select id="listPage" resultType="kr.co.vo.BoardVO" parameterType="kr.co.vo.SearchCriteria">
	...
    FROM BOARD
	WHERE 1=1 
		<include refid="search"></include>
		) MP
	WHERE RNUM BETWEEN #{rowStart} AND #{rowEnd}
	ORDER BY BNO DESC
</select>
	
<select id="listCount" parameterType="kr.co.vo.SearchCriteria" resultType="int">
	...
	WHERE 1=1
	<include refid="search"></include>	
	   AND BNO > 0
</select>

<sql id="search">
	<if test="searchType != null">
		<if test="searchType == 't'.toString()">AND TITLE LIKE '%' || #{keyword} || '%'</if>
		<if test="searchType == 'c'.toString()">AND CONTENT LIKE '%' || #{keyword} || '%'</if>
		<if test="searchType == 'w'.toString()">AND WRITER LIKE '%' || #{keyword} || '%'</if>
		<if test="searchType == 'tc'.toString()">AND (TITLE LIKE '%' || #{keyword} || '%') or (CONTENT LIKE '%' || #{keyword} || '%')</if>
	</if>
</sql>

• <sql> 태그는 다른 구문에서 재사용 가능한 SQL 구문을 정의할 때 사용
• 동적 쿼리 sql 문 작성, searchType이 null이 아닐 경우 검색 유형을 식별함
• 검색 유형 별로 || #{keyword} || 내부의 내용을 검색함

 

• 정적 쿼리 : 고정된 SQL 형태, 변경 X. Parsing 되면 메모리에 상주하며 공유됨
• 동적 쿼리 : 입력 값이나 변경 사항을 추가해 실행할 쿼리문을 문자열로 SQL 변수에 작성함. 실행 될 때마다 Parsing 함