JDBC 제어 oracleDB SQL 게시판용 몇개 #9

 

리스트(목록)용

최신 내림차순 게시글 리스트
"select * from ("
+ "select rownum NUM, N.* "
+ "from (select * from notice where "+field+" like ? order by regdate desc) N "
+ ") "
+ "where NUM between ? and ?";

게시글 수 가져오기
"select COUNT(ID) COUNT from ("
+ "select rownum NUM, N.* from (select * from notice where "+field+" like ? order by regdate desc) N "
+ ")";

 

게시글(내부)용

다음 게시글 가져오기
"select * from notice where id = ( "
+ "select id from notice where regdate > "
+ "(select regdate from notice where id=?)"
+ " and rownum=1 "
+ ")";

이전 게시글 가져오기
"select * from notice where id = ("
+ " select * from (select * from notice order by regdate desc)"
+ " where regdate < (select regdate from notice where id=?) and rownum=1"
+ " )";

 

 

게시글 목록 테이블 SQL과 댓글 테이블 SQL 합치기

집계함수(count) 등에 해당되는 select 주체에는 테이블.* 같은 *은 쓸 수 없다.

DB의 데이터 타입 중 CONTENT 등에 사용되는 CLOB 데이터 타입의 경우 몇 GB 까지 들어가는 데이터형이므로 JOIN 문장에서는 사용할 수 없다.
(JOIN해서 사용할 일이 집계가 대부분이므로 굳이 그렇게 모든 field를 가져올 필요도 없다)
(사용하려면 variable 캐릭터 형식으로 형변환 후 사용할 수 있으나 4000자 내로 잘리게 된다)
(잘리지 않게 하려면 개별적으로 요청한다)


위 내용을 토대로 게시글(notice) 목록 마다 댓글(comment) 수 집계해서 가져오기

select N.id, N.title, N.writer_id, N.regdate, N.hit, N.files, count(C.id) CMT_COUNT
from notice N
left join "COMMENT" C on N.id = C.notice_id
group by N.id, N.title, N.writer_id, N.regdate, N.hit, N.files
order by N.regdate desc;


기존에 게시글 목록 가져오던 SQL

select * from (
select rownum NUM, N.* 
from (select * from NOTICE where "+field+" like ? order by regdate desc) N 
) 
where NUM between ? and ?;


둘을 합쳐야 한다 (굵게 표시된 곳에 넣어야 한다)


1) 댓글 집계수 컬럼 join한 테이블에서 order by절을 빼고 view로 만든다 
(view는 컬럼 추가정도만. 필터나 정렬은 빼는게 써먹기 좋음)

create view NOTICE_VIEW
as
select N.id, N.title, N.writer_id, N.regdate, N.hit, N.files, count(C.id) CMT_COUNT
from notice N
left join "COMMENT" C on N.id = C.notice_id
group by N.id, N.title, N.writer_id, N.regdate, N.hit, N.files
--order by N.regdate desc;


2) 리스트를 뽑을 때 가장 기준이 되는 테이블에 넣는다
select * from (
select rownum NUM, N.* 
from (select * from NOTICE_VIEW where "+field+" like ? order by regdate desc) N 
) 
where NUM between ? and ?;


3) 기존에 만들어둔 서비스클래스로 가서 쿼리를 NOTICE_VIEW로만 고쳐주면 적용된다

 

4) 추가로 만든 notice_view 테이블에 관련된 서비스는 새로이 notice_view 테이블용 entity class를 따로 만들어 superclass로 notice를 상속받아 사용한다
(content는 db 데이터타입 문제로 지웠으니 빈문자열로 기본값을 넣어 사용)
(어차피 게시글 리스트에는 본문이 보이지 않는다. 필요한 것만 구현하자)
(기존 list 서비스용 객체 생성 notice_view로 전부 바꾸고 새로운 entity import)