[Web_Spring] 14

● MyBatis의 동적 태그들

- if

- choose(when, otherwise)

- where

- trim

- foreach

 

 

 

● if

- 검색 조건이 'T' : 제목이 키워드인 항목을 검색

<if test="type == 'T'.toString()">

    (TITLE LIKE '%'||#{keyword}||'%')

</if>

 

 

 

● choose

<choose>

    <when test="type == 'T'.toString()">

        (TITLE LIKE '%'||#{keyword}||'%')

    </when>

    <otherwise>

        (CONTENT LIKE '%'||#{keyword}||'%')

    </otherwise>

</choose>

 

 

 

● where

SELECT * FROM TBL_BOARD

<where>

    <if test="boardNumber != null">

        boardNumber = #{boardNumber}

    </if>

</where>

 

※ boardNumber가 null일 경우

SELECT * FROM TBL_BOARD

 

※ boardNumber가 null이 아닐 경우

SELECT * FROM TBL_BOARD

WHERE boardNumber = #{boardNumber}

 

 

 

● trim

SELECT * FROM TBL_BOARD

<where>

    <if test="boardNumber != null">

        boardNumber = #{boardNumber}

    </if>

    <trim prefixOverrides="AND">

        ROWNUM = 1

    </trim>

</where>

 

※ boardNumber가 null일 경우

SELECT * FROM TBL_BOARD WHERE ROWNUM = 1

 

※ boardNumber가 null이 아닐 경우

SELECT * FROM TBL_BOARD

WHERE boardNumber = #{boardNumber}

AND ROWNUM = 1

 

 

 

● foreach

Map<String, String> searchMap = new HashMap<>();

map.put("T", "제목");

map.put("C", "내용");

 

SELECT * FROM TBL_BOARD WHERE

<trim prefixOverrides="OR">

    <foreach item="val" index="key" collection="searchMap">

        <trim prefix="OR">

            <choose>

                <when test="key=='T'.toString()">

                    (TITLE = #{val})

                </when>

                <when test="key=='C'.toString()">

                    (CONTENT = #{val})

                </when>

                <when test="key=='W'.toString()">

                    (WRITER = #{val})

                </when>

            </choose>

        </trim>

    </foreach>

</trim>

 

※ 결과

SELECT * FROM TBL_BOARD WHERE

(TITLE = #{val}) OR (CONTENT = #{val})

 

 

 

---

 

실습(동적쿼리_검색)

- [Web_Spring] 13 이어서

 

 

1. src/main/java/com.example.board/domain/vo/Criteria.java​

package com.example.board.domain.vo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Component;

@Component
@Data
//@RequiredArgsConstructor //기본 생성자보다 우선순위가 낮다.
@AllArgsConstructor
public class Criteria {
    private Integer pageNum;
    private Integer amount;
    private String type;
    private String keyword;

    public Criteria() {
        this(1, 10);
    }

    public Criteria(Integer pageNum, Integer amount) {
        this.pageNum = pageNum;
        this.amount = amount;
    }

    public String[] getTypes(){
        return type == null ? new String[]{} : type.split("");
    }
}

 

 

 

2. src/main/resource/mapper/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.board.mapper.BoardMapper">

    <sql id="search">
        <if test="type != null and keyword != null">
            <trim prefix="(" suffix=") AND " prefixOverrides="OR">
                <foreach item="type" collection="types">
                    <trim prefix="OR">
                        <choose>
                            <when test="type == 'T'.toString()">
                                (BOARD_TITLE LIKE '%'||#{keyword}||'%')
                            </when>
                            <when test="type == 'C'.toString()">
                                (BOARD_CONTENT LIKE '%'||#{keyword}||'%')
                            </when>
                            <when test="type == 'W'.toString()">
                                (BOARD_WRITER LIKE '%'||#{keyword}||'%')
                            </when>
                        </choose>
                    </trim> 
                </foreach>
            </trim>
        </if>
    </sql>

    <select id="getList" resultType="boardVO">
        SELECT BOARD_NUMBER, BOARD_TITLE, BOARD_CONTENT, BOARD_WRITER, BOARD_REGISTER_DATE, BOARD_UPDATE_DATE
        FROM
        (
        SELECT /*+ INDEX_DESC(TBL_BOARD PK_BOARD) */
        ROWNUM RN, BOARD_NUMBER, BOARD_TITLE, BOARD_CONTENT, BOARD_WRITER, BOARD_REGISTER_DATE, BOARD_UPDATE_DATE
        FROM TBL_BOARD
        WHERE
        <include refid="search"/>
        <![CDATA[
                ROWNUM <= #{pageNum} * #{amount}
            )
            WHERE RN > (#{pageNum} - 1) * #{amount}
        ]]>
    </select>

    <insert id="insert">
        <selectKey keyProperty="boardNumber" order="BEFORE" resultType="long">
            SELECT SEQ_BOARD.NEXTVAL FROM DUAL
        </selectKey>
        INSERT INTO TBL_BOARD (BOARD_NUMBER, BOARD_TITLE, BOARD_CONTENT, BOARD_WRITER)
        VALUES (#{boardNumber}, #{boardTitle}, #{boardContent}, #{boardWriter})
    </insert>

    <select id="select" resultType="boardVO">
        SELECT BOARD_NUMBER, BOARD_TITLE, BOARD_CONTENT, BOARD_WRITER, BOARD_REGISTER_DATE, BOARD_UPDATE_DATE
        FROM TBL_BOARD
        WHERE BOARD_NUMBER = #{boardNumber}
    </select>

    <update id="update">
        UPDATE TBL_BOARD
        SET BOARD_TITLE = #{boardTitle}, BOARD_CONTENT = #{boardContent}, BOARD_UPDATE_DATE = SYSDATE
        WHERE BOARD_NUMBER = #{boardNumber}
    </update>

    <delete id="delete">
        DELETE FROM TBL_BOARD
        WHERE BOARD_NUMBER = #{boardNumber}
    </delete>

    <select id="getTotal" resultType="_int">
        SELECT COUNT(BOARD_NUMBER) FROM TBL_BOARD
        <where>
            <trim suffixOverrides="AND">
                <include refid="search"></include>
            </trim>
        </where>
    </select>

</mapper>

 

 

​

3. src/main/java/com.example.board/domain/vo/PageDTO.java​

package com.example.board.domain.vo;

import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.ibatis.annotations.Param;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.RequestParam;

@Component
@Data
@NoArgsConstructor
public class PageDTO {
    private Integer startPage;
    private Integer endPage;
    private Integer realEnd;
    private boolean next, prev;
    private Integer total; 
    private Criteria criteria;

    public PageDTO(Criteria criteria, Integer total){
        this.criteria = criteria;
        this.total = total;

        this.endPage = (int)(Math.ceil(criteria.getPageNum() / (double)criteria.getAmount())) * criteria.getAmount();
        this.startPage = this.endPage - 9;
        this.realEnd = (int)Math.ceil(total / (double)criteria.getAmount());

        if(realEnd < this.endPage){
            this.endPage = realEnd;
        }

        this.prev = this.startPage > 1;
        this.next = this.endPage < realEnd;
    }

}

 

 

 

4. src/main/java/com.example.board/mapper/BoardMapper.java

package com.example.board.mapper;

import com.example.board.domain.vo.BoardVO;
import com.example.board.domain.vo.Criteria;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface BoardMapper {
    //    게시글 목록
    public List<BoardVO> getList(Criteria criteria);
    //    게시글 추가
    public void insert(BoardVO boardVO);
    //    게시글 한 개 가져오기
    public BoardVO select(Long boardNumber);
    //    게시글 수정
    public int update(BoardVO boardVO);
    //    게시글 삭제
    public int delete(Long boardNumber);
    //    게시글 전체 개수
    public int getTotal(Criteria criteria);
}