오라클버전 P502 테이블 변경

alter table tbl_board add (replycnt NUMBER(10,0) default 0);


P503

resources/mappers/boardMapper.xml의 일부


<select id="listCri"  resultType="org.zerock.domain.BoardVO">
 <![CDATA[
select
  bno, title, content, writer, viewcnt, regdate, replycnt
from
  (
  select
    /*+INDEX_DESC(tbl_board, pk_board)*/
    rownum rn, bno, title, content, writer, viewcnt, regdate
  from tbl_board
  where rownum <= #{page} * #{perPageNum}
  and bno > 0
  )
where rn > (#{page} -1) * #{perPageNum}
 ]]>
</select>

오라클버전 P418에 필요한 댓글의 페이징 처리 쿼리

<select id="listPage" resultType="ReplyVO">
<![CDATA[
 select
   rno, bno, replytext, replyer, regdate, updatedate
 from
   (
   select /*+INDEX_DESC(tbl_reply pk_reply))*/
     rownum rn, rno, bno, replytext, replyer, regdate, updatedate
   from tbl_reply
   where bno = #{bno}
   and rno > 0
   and rownum <= #{cri.page} * #{cri.perPageNum}
   )
where rn > ( #{cri.page} -1) * #{cri.perPageNum}
]]>
</select>

오라클버전 P373 resources/mappers/replyMapper.xml의 일부

<select id="list" resultType="ReplyVO">
select
*
from
tbl_reply
where bno =
#{bno}
order by rno desc
</select>

<insert id="create">
insert into tbl_reply (rno, bno, replytext, replyer)
values (seq_reply.nextval, #{bno},#{replytext},#{replyer})
</insert>

<update id="update">
update tbl_reply set replytext = #{replytext},
updatedate = sysdate
where rno = #{rno}
</update>

<delete id="delete">
delete from tbl_reply where rno =#{rno}
</delete>

오라클버전 P370 tbl_reply 테이블의 생성 스크립트

create table tbl_reply (
 rno number,
 bno number not null,
 replytext varchar2(2000) not null,
 replyer varchar2(50) not null,
 regdate date default sysdate,
 updatedate date default sysdate);

 --PK
 alter table tbl_reply add constraint pk_reply
 primary key (rno);

 --FK
 alter table tbl_reply add constraint fk_board_reply
 foreign key (bno) references tbl_board(bno);

 --sequence
 create sequence seq_reply;

오라클버전 P327 ~ P330 resources/mappers 밑의 boardMapper.xml의 일부

<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 writer like '%'|| #{keyword}||'%')
   </if>      
   <if test="searchType == 'tcw'.toString()">
     and (   title like '%'|| #{keyword}||'%'
           OR
             content like '%'|| #{keyword}||'%'
           OR
             writer like '%'|| #{keyword}||'%')
   </if>            
 </if>
</sql>

<select id="listSearch"  resultType="org.zerock.domain.BoardVO">
 <![CDATA[
select
  bno, title, content, writer, viewcnt, regdate
from
  (
  select
    /*+INDEX_DESC(tbl_board, pk_board)*/
    rownum rn, bno, title, content, writer, viewcnt, regdate
  from tbl_board
  where 1=1
]]>
  <include refid="search"></include>
<![CDATA[
  and rownum <= #{page} * #{perPageNum}
  and bno > 0
  )
where rn > (#{page} -1) * #{perPageNum}
 ]]>
</select>

<select id="listSearchCount"  resultType="int">
 <![CDATA[
select
  count(bno)
from
  tbl_board
where 1=1
]]>
  <include refid="search"></include>
<![CDATA[
and bno > 0
 ]]>
</select>

오라클버전 P258 쿼리

select
*
from
  (
  select
  /*+INDEX_DESC(tbl_board pk_board) */
  rownum rn, bno, title, content, writer, regdate, viewcnt
  from tbl_board
  where bno > 0
  and rownum <= 40 )
where rn > 20;

오라클버전 P249,P253, P257 /resources/mappers/boardMapper.xml의 일부

<select id="listPage"  resultType="org.zerock.domain.BoardVO">
 <![CDATA[
select
  bno, title, content, writer, viewcnt, regdate
from
  (
  select
    /*+INDEX_DESC(tbl_board, pk_board)*/
    rownum rn, bno, title, content, writer, viewcnt, regdate
  from tbl_board
  where rownum <= #{page} * #{perPageNum}
  and bno > 0
  )
where rn > (#{page} -1) * #{perPageNum}
 ]]>
</select>