BoardMapper.xml > SPRING1

• 목록

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

<select id="getList" resultType="com.pkt.model.BoardVO">

<!-- <![CDATA[ ]] 쓰는 이유 -->

<!-- sql 구문안에 비교 연산자가 들어갈 경우(특수문자) 문자열로 인식하게 하기 위해서 ... -->

<![CDATA[

select * from tbl_board where bno > 0

]]>

</select>

<select id="listPage" resultType="com.pkt.model.BoardVO">

<![CDATA[

select * from tbl_board where bno > 0 order by bno desc limit #{page}, 5

]]>

</select>

<select id="listCriteria" resultType="com.pkt.model.BoardVO">

  <![CDATA[

select * from tbl_board where bno > 0 order by bno desc limit #{pageStart}, #{perPageNum}

  ]]>

</select>

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

  <![CDATA[

select count(bno) from tbl_board where bno > 0

  ]]>

</select>

<insert id="create">

insert into tbl_board (title, content, writer) values (#{title},#{content}, #{writer})

</insert>

<select id="read" resultType="com.pkt.model.BoardVO">

select * from tbl_board where bno = #{bno}

</select>

<update id="update">

update tbl_board set title =#{title}, content =#{content} where bno = #{bno}

</update>

<update id="updateViewCnt">

update tbl_board set viewcnt = viewcnt + 1 where bno = #{bno}

</update>

<delete id="delete">

delete from tbl_board where bno = #{bno}

</delete>

<!-- search -->

<select id="listSearch" resultType="com.pkt.model.BoardVO">

  <![CDATA[

select *,(select count(*) from tbl_reply where bno = tbl_board.bno) as replycount from tbl_board where bno > 0 

  ]]>

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

  <![CDATA[

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

  ]]>

</select>

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

  <![CDATA[

select count(bno) from tbl_board where bno > 0 

  ]]>

  <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 content like CONCAT('%', #{keyword}, '%')

</if>

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

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

</if>

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

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

</if>

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

and ( content like CONCAT('%', #{keyword}, '%') OR writer like CONCAT('%', #{keyword}, '%'))

</if>

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

and 

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

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

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

</if>

</if>

</sql>

</mapper>