데이터 분석가 블로그

쿼리를 등록하면 DAO에서 자동으로 쿼리를 서치해서 사용하는 *Mapper.xml 자료이다.

여기서는 Oracle을 기준으로 작성했고  기본적인 게시판 쿼리가 있다.

때문에 totalCount, searhType, keyword와 같이 페이징처리, 검색어를 이용한 검색 내용도 있다.

위치는 src/java/resoures/ mappers/boardMapper.xml이다.

======================== src/java/resources/mappers/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="org.zerock.mapper.BoardMapper">

<!-- 게시판 리스트 : select -->

<!-- <select id="list" resultType="org.zerock.board.dto.BoardDTO"></select> -->

<select id="list" resultType="boardDTO">

select *

from(

select rownum rnum, no, title, writer, writedate, hit 

from(

select no, title, writer, writedate, hit

from board

where no > 0

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

order by no desc

)

)

where rnum between #{startRow} and #{endRow}

</select>

<!-- 게시판 글보기 : select => view, update form -->

<select id="view" resultType="boardDTO">

select no, title, content, writer,

writedate, hit

from board

where no = #{no}

</select>

<!-- 조회수 증가 : increase -->

<update id="increase">

update board

set hit = hit + 1

where no = #{no}

</update>

<!-- 게시판 글쓰기 : write -->

<insert id="write">

insert into board(no, title, content, writer)

values(board_seq.nextval, #{title}, #{content}, #{writer})

</insert>

<!-- 게시판 글수정 : update -->

<update id="update">

update board

set title = #{title}, content = #{content}, writer = #{writer}

where no = #{no}

</update>

<!-- 게시판 글삭제 : delete -->

<delete id="delete">

delete from board where no = #{no}

</delete>

<!-- 전체 데이터의 갯수를 세는 처리 -->

<select id="totalCount" resultType="Integer">

select count(*) 

from board

where no > 0

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

</select>

<!-- 검색을 위한 조건 처리문 : list, totalCount에서 각각 사용한다. -->

<sql id="search">

<if test="searchType != null">

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

and ( title like '%' || #{keyword} || '%' )

</if>

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

and ( content like '%' || #{keyword} || '%' ) 

</if>

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

and ( writer like '%' || #{keyword} || '%' ) 

</if>

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

and ( title like '%' || #{keyword} || '%' 

or content like '%' || #{keyword} || '%' ) 

</if>

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

and ( content like '%' || #{keyword} || '%'  

or and writer like '%' || #{keyword} || '%' ) 

</if>

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

and ( title like '%' || #{keyword} || '%' 

or content like '%' || #{keyword} || '%' 

or and writer like '%' || #{keyword} || '%' ) 

</if>

</if>

</sql>

</mapper>

======================== src/java/resources/mappers/boardMapper.xml =======================