sql-board.xml > SPRINGBOOT

• 목록

<?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="web.mapper.BoardMapper"> 

<select id="selectBoardList" resultType="web.dto.BoardDto">

<![CDATA[

select * from t_board where deleted_yn = 'N' order by board_idx desc

]]>

</select>

<insert id="insertBoard" parameterType="web.dto.BoardDto" useGeneratedKeys="true" keyProperty="boardIdx">

insert into t_board 

(title,contents,created_datetime,creator_id) 

values 

(#{title},#{contents},now(),'admin')

</insert>

<insert id="insertBoardFileList" parameterType="web.dto.BoardFileDto">

<![CDATA[

INSERT INTO t_file

( 

board_idx, 

original_file_name, 

stored_file_path, 

file_size

)

VALUES

]]>

<foreach collection="list" item="item" separator=",">

(

#{item.boardIdx},

#{item.originalFileName},

#{item.storedFilePath},

#{item.fileSize}

)

</foreach>

</insert>

<select id="selectBoardFileList" parameterType="int" resultType="web.dto.BoardFileDto">

<![CDATA[

SELECT

idx, board_idx, original_file_name, stored_file_path, 

FORMAT(ROUND(file_size / 1024), 0) AS file_size

FROM

t_file

WHERE

board_idx = #{boardIdx}

]]>

</select>

<select id="selectBoardDetail" resultType="web.dto.BoardDto">

<![CDATA[

select * from t_board where deleted_yn='N' and board_idx=#{boardIdx}

]]>

</select>

<update id="updateHitCount">

update t_board set hit_cnt = hit_cnt + 1 where board_idx = #{boardIdx}

</update>

<update id="updateBoard" parameterType="web.dto.BoardDto">

update t_board set title = #{title}, contents = #{contents} where board_idx = #{boardIdx}

</update>

<update id="deleteBoard" parameterType="int">

update t_board set 

deleted_yn='Y' ,

updated_datetime = now(),

updater_id = 'admin'

where board_idx = #{boardIdx}

</update>

<select id="selectCriList" resultType="web.dto.BoardDto">

<![CDATA[

select * from t_board where deleted_yn='N' order by board_idx desc limit #{pageStart},#{perPageNum}

]]>

</select>

<select id="selectPageList" resultType="web.dto.BoardDto">

<![CDATA[

select * from t_board where deleted_yn='N' order by board_idx desc limit #{pageStart},#{perPageNum}

]]>

</select>

<select id="listCount" resultType="int">

<![CDATA[

select count(*) from t_board where deleted_yn='N'

]]>

</select>

<!-- search -->

<select id="selectSearchList" resultType="web.dto.BoardDto">

  <![CDATA[

select * from t_board where deleted_yn='N' 

  ]]>

<include refid="search"></include> 

  <![CDATA[

order by board_idx desc limit #{pageStart},#{perPageNum}

  ]]>

</select>

<select id="listSearchCount" resultType="int">

  <![CDATA[

select count(*) from t_board where deleted_yn='N' 

  ]]>

  <include refid="search"></include>

</select>

<sql id="search">

<if test="searchType != null" >

<!-- 

MyBatis에서는 변수가 자바객체처럼 사용되므로 

( OGNL (Object Graph Navigation Language)를 사용하여 속성 처리를 하고 있기 때문에 )

자바에서 사용되는 문자열 비교 메소드를 이용하면 된다.

단 비교할 문자를 먼저 쓴 경우도 에러 발생.

<if test="searchType == 't'.toString()"> (o)

<if test="searchType eq 't'.toString()"> (o)

<if test="searchType.toString() == 't'.toString()"> (o)

<if test="searchType.equalsIgnoreCase('t')"> (o)

<if test='searchType == "t"'> (o)

// 작은, 큰 따옴표 순서

<if test='"t".equals(searchType)'> (o)

<if test="'t'.equals(searchType)"> (x)

<if test='searchType.equals("t")'> (o)

<if test="searchType.equals('t')"> (x)

-->

<if test="searchType == 't'.toString()">

and title like CONCAT('%', #{keyword}, '%')

</if>

<if test="searchType == 'c'.toString()">

and contents like CONCAT('%', #{keyword}, '%')

</if>

<if test="searchType == 'w'.toString()">

and creator_id like CONCAT('%', #{keyword}, '%')

</if>

<if test="searchType == 'tc'.toString()">

and ( title like CONCAT('%', #{keyword}, '%') OR contents like CONCAT('%', #{keyword}, '%'))

</if>

<if test="searchType == 'cw'.toString()">

and ( contents like CONCAT('%', #{keyword}, '%') OR creator_id like CONCAT('%', #{keyword}, '%'))

</if>

<if test="searchType == 'tcw'.toString()">

and 

( title like CONCAT('%', #{keyword}, '%') 

OR contents like CONCAT('%', #{keyword}, '%') 

OR creator_id like CONCAT('%', #{keyword}, '%'))

</if>

</if>

</sql>

</mapper>