Service 클래스와 메소드 생성하기위한 SQL문 작성

공지 목록 조회

1. getNoticeList()

사용자는 공지사항의 링크를 클릭하는 것과 같이 페이지 요청을 통해 공지를 볼 수 있다.

공지사항의 목록을 default로 정해진 갯수만큼 보여준다.

 

2. getNoticeList(int page)

페이지 요청을 통해 이전 이후의 페이지들도 볼 수 있다.

특정 페이지의 번호를 인자값으로 받아서 보여준다.

 

3. getNoticeList(String field, String query, int page)

공지를 검색하는 요청을 할 수 있다.

검색할 때 키워드의 분류와 검색 단어뿐만 아니라 검색했을 때 여러 개의 검색이 되었을 떼

페이지 또한 여러 개가 된다. 따라서 검색된 결과물에서 페이지를 클릭할 수 있어야 한다.

 

4. getNoticeCount() 

5. getNoticeCount(String field, String query) 

검색 요청한 페이지의 수와 전체 페이지의 수를 알 수 있다. (페이징 요청, 검색 요청)

 

공지 상세 조회

글 목록에서 항목을 클릭했을 때 페이지 요청을 하는데

목록에서 어떤 항목을 클릭해서 요청한 것인지 알기 위해서 id를 넘겨 받는다.

1. getNotice(id)

현재 공지사항을 요청할 수 있다.

 

2. getNextNotice(id)

현재 id를 가지고 다음 공지 사항을 요청할 수 있다.

 

3. getPrevNotice(id)

이전 공지 사항을 요청할 수 있다.

 

Service 클래스 구현하기

이 Service를 구현한다면 Controller에서는 이 기능을 가져다 쓰기만 하면 된다.

위에서 알아본 메소드들 중에서 이름이 같은 메소드들을 전부 구현하면 코드 변경시

전부 고쳐야하는 상황이 생길 수도 있다, 이는 유지보수하는데 불편해질 수도 있다.

따라서 이름이 같은 메소드를 하나만 구현하고 나머지는 구현한 하나를 재호출하는 방법으로 구현하면 된다.

이때 인자가 제일 많은 메소드를 구현해야 나머지 메소드들이 가져가 사용할 수 있다.

 

getNoticeList(String field, String query, int page) 메소드 구현하기

이 메소드를 구현하기 위해서 SQL을 작성해보자.

공지 사항 목록은 등록일자를 기준으로 역정렬이 되어야 한다. (최신순으로 정렬되어야 한다는 뜻)

페이징(원하는 갯수만큼 끊어서 읽기)을 하기 위해서는 기준열이 있어야 한다.

하지만 연속되는 번호가 없기 때문에 기준열이 없다. 그래서 이때 사용하는 것이 ROWNUM이다.

ROWNUM을 사용해서 기준열을 만드는 것이다.

그래서 아래와 같이 코드를 작성하면 ROWNUM이 뒤죽 박죽인 것을 확인할 수 있다.

SELECT ROWNUM,NOTICE.* FROM NOTICE ORDER BY REGDATE DESC;

이유는 정렬(ORDER BY)이 이루어지기 전에 ROWNUM이 먼저 만들어지고 정렬이 되기 때문이다.

정렬이 된 후에 ROWNUM을 얻기 위해서 사용하는 방법이 서브쿼리이다.

 

아래와 같이 ROWNUM이 1부터 5까지는 잘 출력되는 것을 확인할 수 있다.

-- 더이상 () 부분이 NOTICE가 아니기 때문에 NOTICE.*로 출력할 수 없다. -> 별칭 주기(N)
SELECT ROWNUM, N.* 
SELECT ROWNUM, N.* 
FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC) N
WHERE ROWNUM BETWEEN 1 AND 5;

 

 

하지만 6부터 10까지와 같이 그 뒤의 ROWNUM이 출력되지 않는 것을 확인할 수 있다.

그 이유는 WHERE절이 구현되면서 ROWNUM이 만들어지는 것이기 때문에 검색이 되지 않는 것이다.

따라서 서브쿼리를 한 번 더 사용해줘야 한다.

-- ROWNUM이 2개가 되므로 별칭 필요
SELECT * FROM (
SELECT ROWNUM NUM, N.* 
FROM(SELECT * FROM NOTICE ORDER BY REGDATE DESC) N
)
WHERE NUM BETWEEN 6 AND 10;

이제 6부터 10까지의 ROWNUM도 출력이 되는 것을 확인할 수 있다.

 

참고로 ROWNUM을 뽑아내는 함수인 ROW_NUMBER()를 사용하면 아래와 같이 코드를 짤 수 있다.

SELECT * FROM(
SELECT ROW_NUMBER() OVER (ORDER BY REGDATE DESC) NUM,
	NOTICE.* FROM NOTICE
)
WHERE NUM BETWEEN 6 AND 10;

 

getNextNotice(int id) 메소드 구현하기

이 메소드는 현재 Notice의 id를 주면서 다음 공지글을 달라는 메소드이다,

근데 현재 공지글의 id가 3일 때 다음 공지글을 불러 오려고 하면 +1을 해서 id가 4인 공지글을

구할 수 있지 않을까라는 생각을 할 수도 있다.

하지만 실제로 글은 삭제될 수 있기 때문에 이는 적합하지 못한 방법이다.

일단 예를 들어서 id가 3인 글의 다음글을 보려고 한다.

현재 id가 3인 다음글은 id가 6인 것을 확인할 수 있다.

REGDATE 컬럼을 이용해서 다음 글을 구할 수 있는데

먼저 id가 3인 REGDATE보다 큰 id를 구하는 SQL문을 작성하면 아래와 같다.

 SELECT ID FROM NOTICE
 WHERE REGDATE>(SELECT REGDATE FROM NOTICE WHERE ID = 3);

위의 데이터베이스 전체 결과에서 확인했던 것과 같이 결과가 같게 나오는 것을 볼 수 있다.

그렇다면 이제 다음글 하나를 구해야하기 때문에 첫 번째 ROWNUM을 구하면 된다.

SELECT * FROM NOTICE
WHERE ID = (
    SELECT ID FROM NOTICE
    WHERE REGDATE>(SELECT REGDATE FROM NOTICE WHERE ID = 3)
    AND ROWNUM=1
);

id가 6인 다음글을 불러온 것을 확인할 수 있다.

 

getPrevNotice(int id) 메소드 구현하기

이번엔 현재 id를 이용해서 이전글을 구하는 메소드이다.

마찬가지로 id가 3인 글의 이전글을 보려고 한다면 id가 2인 글을 가져와야 한다.

SELECT ID FROM NOTICE
WHERE REGDATE<(SELECT REGDATE FROM NOTICE WHERE ID = 3);

근데 id가 2가 처음에 위치해있는 것이 아니므로 ROWNUM = 1을 이용하지 못한다.

따라서 역순으로 정렬해줄 필요가 있다. ORDER BY DESC를 이용해서 아래와 같이 정렬해주도록 한다.

 

SELECT ID FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC)
WHERE REGDATE<(SELECT REGDATE FROM NOTICE WHERE ID = 3);

역순으로 정렬을 했다면 아까와 같이 ROWNUM이 첫 번째인 id의 글을 불러오면 된다.

 

SELECT * FROM NOTICE
WHERE ID = (
    SELECT ID FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC)
    WHERE REGDATE<(SELECT REGDATE FROM NOTICE WHERE ID = 3)
    AND ROWNUM=1
);

id가 2인 글을 불러온 것을 확인할 수 있다.