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">

<!-- web.mapper.BoardMapper : web프로젝트 mapper패키지 BoardMapper인터페이스명 매칭 --> 

<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">

insert into t_board 

(title,contents,created_datetime,creator_id) 

values 

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

</insert>

<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>