JSP - 게시판(MVC패턴 - list.jsp 글 목록 출력 부분 쿼리(limit) 문제 해결 방안)

MY SQL의 limit를 오라클에서 쿼리(질의)로 구현

BoardDBBean.java 파일의 메소드 아래 limit 부분을 오라클에서 구현한다. (인덱스를 사용 안했다.)

public List getArticles(int start, int end) throws Exception {
……
pstmt = conn.prepareStatement(
             "select * from board order by ref desc, re_step asc limit ?,? ");
……
}

쿼리 1안

StringBuffer 를 추가했다.(쿼리를 담을 공간)

public List getArticles(int start, int end) throws Exception {
……
            query.append("SELECT *");
            query.append( " FROM (SELECT board.*, ROWNUM rn");
            query.append(         " FROM (SELECT *");
            query.append(                 " FROM board");
            query.append(                " ORDER BY ref desc, re_step asc) board)");
            query.append(" WHERE rn >= " + start + " and rn < " + (start + end));
……
}

SELECT *
  FROM (SELECT board.*, ROWNUM rn
          FROM (SELECT *
                  FROM board
                 ORDER BY ref desc, re_step asc) board)
 WHERE rn >= " + start + " AND rn < " + (start + end)

쿼리 2안 (채택)

public List getArticles(int start, int end) throws Exception {
……
            query.append("SELECT *");
            query.append( " FROM (SELECT board.*, ROWNUM as rn");
            query.append(         " FROM (SELECT *");
            query.append(                 " FROM board");
            query.append(                " ORDER BY ref desc, re_step asc) board)");
            query.append(" WHERE rn BETWEEN " + start + " AND " + ((start + end) - 1));
……
}

SELECT *
  FROM (SELECT board.*, ROWNUM as rn
          FROM (SELECT *
                  FROM board
                 ORDER BY ref desc, re_step asc) board)
 WHERE rn BETWEEN start  AND ((start + end) - 1)