[SpringBoot+IntelliJ+Oracle+Thymeleaf+Paging] 웹 게시판 만들기(4) - Mapper , DB 생성

이전글

https://greed-yb.tistory.com/211

[SpringBoot+IntelliJ+Oracle+Thymeleaf+Paging] 웹 게시판 만들기(3) - Service

 

Mapper.java

• mapper -> board -> BoardMapper(interface 로 생성)

package com.example.practice.mapper.board;

import com.example.practice.util.SearchCriteria;
import com.example.practice.vo.BoardVo;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface BoardMapper {

    /**
     * 게시글 리스트
     */
    List<BoardVo> boardSelect(SearchCriteria cri) throws Exception;

    /**
     * 게시글 수
     */
    int totalCount(SearchCriteria cri);

    /**
     * 게시글 생성
     */
    void boardCreate(BoardVo vo) throws Exception;

    /**
     * 게시글 상세 페이지
     */
    BoardVo boardRead(Integer num) throws Exception;

    /**
     * 게시글 삭제 하기
     */
    void boardDelete(Integer num) throws Exception;

    /**
     * 게시글 수정 하기
     */
    void boardUpdate(BoardVo vo) throws Exception;
}

 

 

 

Mapper.xml 

• mappers -> Board -> BoardMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">


<mapper namespace="com.example.practice.mapper.board.BoardMapper">
    <!-- 게시글 리스트 -->
    <select id="boardSelect" resultType="com.example.practice.vo.BoardVo">
        SELECT *
        FROM (
        SELECT ROW_NUMBER() OVER(ORDER by b_num DESC) AS ROW_NUM,
        B_NUM,
        B_NAME,
        B_TITLE,
        TO_CHAR(B_DATE , 'YYYY-MM-DD HH24:SS:MM') AS B_DATE
        FROM BOARD
        WHERE 1 = 1
        <if test="searchType != null and searchType.equals('title')">
            And B_TITLE LIKE '%' || #{keyword} || '%'
        </if>
        <if test="searchType != null and searchType.equals('name')">
            And B_NAME LIKE '%' || #{keyword} || '%'
        </if>
        <if test="searchType != null and searchType.equals('content')">
            And B_CONTENT LIKE '%' || #{keyword} || '%'
        </if>
        ORDER BY B_NUM DESC
        )
        WHERE ROW_NUM BETWEEN #{rowStart} and #{rowEnd} -- 출력하려는 글의 개수
    </select>

    <!-- 게시글 총 개수 -->
    <select id="totalCount" resultType="int">
        SELECT COUNT(*) FROM BOARD WHERE 1 = 1
        <if test="searchType != null and searchType.equals('title')">
            And B_TITLE LIKE '%' || #{keyword} || '%'
        </if>
        <if test="searchType != null and searchType.equals('name')">
            And B_NAME LIKE '%' || #{keyword} || '%'
        </if>
        <if test="searchType != null and searchType.equals('content')">
            And B_CONTENT LIKE '%' || #{keyword} || '%'
        </if>
    </select>

    <!-- 게시글 생성 -->
    <insert id="boardCreate" parameterType="com.example.practice.vo.BoardVo">
        INSERT INTO BOARD(B_NUM,
                          B_TITLE,
                          B_NAME,
                          B_CONTENT)
        VALUES ( BOARD_NUM_SEQ.NEXTVAL -- 게시글 번호 자동 생성 시퀀스
               , #{b_title}
               , #{b_name}
               , #{b_content})
    </insert>
    
    <!-- 게시글 상세 -->
    <select id="boardRead" resultType="com.example.practice.vo.BoardVo">
        SELECT B_NUM,
               B_TITLE,
               B_NAME,
               B_CONTENT,
               TO_CHAR(B_DATE, 'YYYY-MM-DD HH24:SS:MM') AS B_DATE
        FROM BOARD
        WHERE B_NUM = #{b_num}
    </select>
    
    <!-- 게시글 삭제 -->
    <delete id="boardDelete" parameterType="Integer">
        DELETE
        FROM BOARD
        WHERE B_NUM = #{b_num}
    </delete>
    
    <!-- 게시글 수정 -->
    <update id="boardUpdate" parameterType="com.example.practice.vo.BoardVo">
        UPDATE BOARD
        SET B_TITLE   = #{b_title},
            B_CONTENT = #{b_content}
        WHERE B_NUM = #{b_num}
    </update>
</mapper>

 

 

DB Table , Sequence 생성

CREATE TABLE BOARD(
	B_NUM			NUMBER PRIMARY KEY,
	B_NAME			VARCHAR2(20) NOT NULL,
	B_TITLE			VARCHAR2(50) NOT NULL,
	B_CONTENT 		VARCHAR2(2000) NOT NULL,
	B_DATE			DATE	DEFAULT SYSDATE
);

 

게시글 번호인 B_NUM을 PK로 잡았고 글 생성 시 시퀀스를 이용해 게시글 번호를 생성하도록 하였다 

 

CREATE SEQUENCE BOARD_NUM_SEQ 
INCREMENT BY 1 
MINVALUE 1 
MAXVALUE 9999999999999999999999999999 
NOCYCLE 
CACHE 20 
NOORDER