다이내믹 쿼리

<?xml version="1.0" encoding="UTF-8" ?>
 
 
 
<!DOCTYPE sqlMap

PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"

"http://ibatis.apache.org/dtd/sql-map-2.dtd">
 
 
 
<sqlMap namespace="board">
    <typeAlias alias="boardVO" type="board.vo.BoardVO" />
    <typeAlias alias="commentVO" type="board.vo.CommentVO" />
    
 
    <!-- 리스트 조회시 where절 -->
 
    <sql id="board.where.getList">
        <isNotEmpty property="searchStr">
            <isEqual property="searchSelect" compareValue="title">
                AND title LIKE '%'||#searchStr#||'%'
            </isEqual>
            <isEqual property="searchSelect" compareValue="writer">
                AND writer LIKE '%'||#searchStr#||'%'
            </isEqual>
        </isNotEmpty>
    </sql>
 
    
 
    <!-- 상세정보 조회시 코멘트 같이 조회 -->
 
    <resultMap class="boardVO" id="boardInfoMap">
 
        <result property="no" column="no"/>
        <result property="groupNo" column="group_no"/>
        <result property="title" column="title"/>
        <result property="writer" column="writer"/>
        <result property="contents" column="contents"/>
        <result property="hit" column="hit"/>
        <result property="regdate" column="regdate"/>
        <result property="comments" select="board.getCommentList" column="no"/>
 
    </resultMap>
 
    
 
    <!-- board 시퀀스 구하기 -->
 
    <select id="getSeq" resultClass="int">
 
        SELECT board_seq.nextval
 
        FROM dual
 
    </select>
 
    
 
    <!-- 리스트 목록 조회 -->
 
    <select id="getList" parameterClass="boardVO" resultClass="boardVO">
 
        <![CDATA[

        SELECT     no,

                group_no groupNo,
                depth_no depthNo,
                title,
                writer,
                contents,
                hit,
                type,
                regdate
        FROM (

            SELECT /*+ index_desc(a board_pk)*/rownum rn, no, group_no, order_no, depth_no, title, writer, contents, hit, type, regdate
            FROM board a
            WHERE no > 0 AND rownum <= #end#
        )

        WHERE rn >= #begin# AND type=#type#
 
        ]]>
 
        <include refid="board.where.getList" />
 
        ORDER BY group_no DESC, order_no ASC
 
    </select>
 
    
 
    <!-- 게시글 등록 -->
 
    <insert id="insertBoard" parameterClass="boardVO">
 
        INSERT INTO board (no, group_no, order_no, depth_no, id, title, writer, contents, hit, type, regdate)
 
                    VALUES (#no#, #groupNo#, #orderNo#, #depthNo#, #id#, #title#, #writer#, #contents#, 0, #type#, sysdate)
 
    </insert>
 
    
 
    <!-- 전체게시글 수 구하기 -->
 
    <select id="getTotalRow" parameterClass="boardVO" resultClass="int">
 
        SELECT count(*)
 
        FROM board
 
        WHERE type=#type#
 
        <include refid="board.where.getList" />
 
    </select>
 
    
 
    <!-- 조회수 증가 -->
 
    <update id="updateHit" parameterClass="int">
 
        UPDATE board SET hit=hit+1
 
        WHERE no=#value#
 
    </update>
 
    
 
    <!-- 상세정보 조회 -->
 
    <select id="getInfo" parameterClass="int" resultMap="boardInfoMap">
 
        SELECT no, group_no, title, writer, contents, hit, regdate
 
        FROM board
 
        WHERE no=#value#
 
    </select>
 
    
 
    <!-- 부모글 정보 조회 -->
 
    <select id="getParentInfo" parameterClass="int" resultClass="boardVO">
 
        SELECT     group_no groupNo,
 
                order_no orderNo,
 
                depth_no depthNo
 
        FROM board
 
        WHERE no=#value#
 
    </select>
 
    
 
    <!-- 부모글 순서보다 큰 게시글 업데이트하기 -->
 
    <update id="updateDepthNo" parameterClass="boardVO">
 
        <![CDATA[

        UPDATE board SET order_no=order_no+1

        WHERE group_no=#groupNo# AND order_no > #orderNo#
 
        ]]>
 
    </update>
 
    
 
    <!-- 게시글 수정 -->
 
    <update id="updateBoard" parameterClass="boardVO">
 
        UPDATE board SET
 
                title=#title#,
 
                writer=#writer#,
 
                contents=#contents#
 
        WHERE no=#no#
 
    </update>
 
    
 
    <!-- 게시글 삭제 -->
 
    <delete id="deleteBoard" parameterClass="int">
 
        DELETE FROM board
 
        WHERE no=#value#
 
    </delete>
 
    
 
    <!-- 코멘트 시퀀스 얻기 -->
 
    <select id="getCommentSeq" resultClass="int">
 
        SELECT board_comment_seq.nextval
 
        FROM dual
 
    </select>
 
    
 
    <!-- 코맨트 작성 -->
 
    <insert id="insertComment" parameterClass="commentVO">
 
        INSERT INTO board_comment(no, board_no, id, writer, contents, regdate)
 
                VALUES (#no#, #boardNo#, #id#, #writer#, #contents#, sysdate)
 
    </insert>
 
    
 
    <!-- 코멘트 상세정보 -->
 
    <select id="getCommentInfo" parameterClass="int" resultClass="commentVO">
 
        SELECT     no,
 
                board_no boardNo,
 
                id,
 
                writer,
 
                contents,
 
                regdate
 
        FROM board_comment
 
        WHERE no=#value#
 
    </select>
 
    
 
    <!-- 코멘트 리스트 조회 -->
 
    <select id="getCommentList" parameterClass="int" resultClass="commentVO">
 
        SELECT     no,
 
                board_no boardNo,
 
                id,
 
                writer,
 
                contents,
 
                regdate
 
        FROM board_comment
 
        WHERE board_no=#value#
 
        order by no
 
    </select>
 
    
 
    <!-- 코멘트 수정 -->
 
    <update id="updateComment" parameterClass="commentVO">
 
        UPDATE board_comment SET contents=#contents#
 
        WHERE no=#no#
 
    </update>
 
    
 
    <!-- 코멘트 삭제 -->
 
    <delete id="deleteComment" parameterClass="int">
 
        DELETE FROM board_comment
 
        WHERE no=#value#
 
    </delete>
 
</sqlMap>