● 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);
}
'웹 개발 > Spring' 카테고리의 다른 글
[Web_Spring] 16 (0) | 2022.06.28 |
---|---|
[Web_Spring] 15 (0) | 2022.06.27 |
[Web_Spring] 13 (0) | 2022.06.25 |
[Web_Spring] 12 (0) | 2022.06.24 |
[Web_Spring] 11 (0) | 2022.06.23 |