게시글 중 페이징네이션과 관련한 글을 실습하다보면, 쿼리 문제에 당면하는 일들이 벌어질 수 있다.
그래서 따로 몇 가지 중요 포인트를 정리해보았다.
1. [JSP] 17. JSP/Servlet MVC2 - 페이징네이션과 검색 그리고 오라클 프로젝트 (1), 2020-09-30
- https://yyman.tistory.com/1428
2. [JSP] 18. JSP/Servlet MVC2 - 페이징네이션과 검색 그리고 오라클 프로젝트 (2), 2020-09-30
- https://yyman.tistory.com/1429
- 사용 프로그램: SQL Developer (Oracle)
- DB: Oracle Databases [11, 19g]
이 글이 어렵게 이해되는 경우를 위해서 아주 친절하게 SQL Developer에서 어떤 작업을 했는지 소개하도록 하겠다.
그림 1. Oracle SQL Developer에서의 작업 모습의 예
그림 1은 쿼리 넣고 태스트를 하는 모습이다. 예를 들면, Java, C#, C++, PHP, .NET 등의 코드를 삽입하기 전에 미리 확인을 해볼 수 있다.
이런 작업을 수 차례 반복해서 해보고 프로그래밍 코드에 적용하면 된다.
더 좋은 전문적인 프로그램이 있으면, 또는 편리한 프로그램이 있으면 다른 프로그램을 사용해도 무방하다.
1. 게시판 페이징 로직
게시글 "[JSP] 18. JSP/Servlet MVC2 - 페이징네이션과 검색 그리고 오라클 프로젝트 (2), 2020-09-30"에 적혀져 있는 SQL 쿼리문이다.
물론 이 쿼리는 오라클 형태로 작성된 쿼리이다.
-- Oracle 11 - 자동번호 생성 테이블 정의
-- Table 생성 (BOARD)
-- NEW.ID (Table의 id를 가리킴)
CREATE TABLE board
(
id NUMBER PRIMARY KEY,
name VARCHAR2(30),
subject VARCHAR2(30),
memo NCLOB,
count NUMBER,
regidate DATE
);
-- Sequence 정의
CREATE SEQUENCE board_sequence
START WITH 1
INCREMENT BY 1;
-- Trigger 생성
-- BEFORE INSERT on '테이블명'
CREATE OR REPLACE TRIGGER board_trigger
BEFORE INSERT
ON board
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT board_sequence.nextval INTO :NEW.ID FROM dual;
END;
/* 데이터 추가 */
INSERT INTO board (name, subject, memo, count, regidate) VALUES ('홍길동', '안녕하세요.', '메모메모', '0', '2020-09-29 11:11:00');
/* 데이터 등록 후 커밋할 것(대량 정보 처리 후) */
COMMIT;
-- 싱글 쿼리 (페이징)
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT * from board order by id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1
-- 특정 싱글 쿼리 SQL
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT * from board where subject like '%야해해%' order by id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1
[파일명: board-tbl-oracle_개선후.sql]
[첨부(Attachments)]
이 쿼리처럼 세상에 있는 문제가 단일로 처리되면 간단하게 구성해도 된다.
하지만, 그렇지 않다는 점이다.
2. 다중 테이블 문제
두 개의 테이블을 두었다.
하나는 메뉴와 가격에 대한 테이블이다.
하나는 가게 정보에 대한 테이블이다.
CREATE TABLE foodmenu_tbl
(
id NUMBER PRIMARY KEY,
name VARCHAR2(30),
price NUMBER,
store_id NUMBER,
cnt NUMBER,
regidate DATE
);
-- Table 생성 (FOODSTORE_TBL)
CREATE TABLE foodstore_tbl
(
id NUMBER PRIMARY KEY,
name VARCHAR2(30),
address VARCHAR2(30),
regidate DATE
);
두 개 테이블을 하나로 합치면, foomenu_tbl의 stord_id와 foodstore_tbl의 id가 참조 관계를 형성하는 것을 알 수 있다.
모델링 프로그램으로 살펴볼 때는 외래키 지정해도 무방하다.
아무튼, 두 가지 이상 테이블을 조인하여 사용했을 때 쿼리에 대한 것이다.
100만 건 이상 넘어갔을 때는 느려지긴 하지만, 성능이 우수한 쿼리이다.
-- 2020-10-07 SQL Paging Upgrade
-- 조회 관련 태스트
select f1.id, f1.name, f1.price, f2.name as storename from foodmenu_tbl f1, foodstore_tbl f2 where f1.store_id = f2.id
select count(*) from foodmenu_tbl f1, foodstore_tbl f2 where f1.store_id = f2.id
-- Oracle 11 - 자동번호 생성 테이블 정의
-- Table 생성 (FOODMENU_TBL)
-- NEW.ID (Table의 id를 가리킴)
CREATE TABLE foodmenu_tbl
(
id NUMBER PRIMARY KEY,
name VARCHAR2(30),
price NUMBER,
store_id NUMBER,
cnt NUMBER,
regidate DATE
);
-- Sequence 정의
CREATE SEQUENCE foodmenu_sequence
START WITH 1
INCREMENT BY 1;
-- Trigger 생성
-- BEFORE INSERT on '테이블명'
CREATE OR REPLACE TRIGGER foodmenu_trigger
BEFORE INSERT
ON foodmenu_tbl
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT foodmenu_sequence.nextval INTO :NEW.ID FROM dual;
END;
----------------------------------------------------------------------
-- Table 생성 (FOODSTORE_TBL)
CREATE TABLE foodstore_tbl
(
id NUMBER PRIMARY KEY,
name VARCHAR2(30),
address VARCHAR2(30),
regidate DATE
);
-- Sequence 정의
CREATE SEQUENCE foodstore_sequence
START WITH 1
INCREMENT BY 1;
-- Trigger 생성
-- BEFORE INSERT on '테이블명'
CREATE OR REPLACE TRIGGER foodstore_trigger
BEFORE INSERT
ON foodstore_tbl
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT foodstore_sequence.nextval INTO :NEW.ID FROM dual;
END;
-- Sequence 정의
CREATE SEQUENCE foodstore_sequence
START WITH 1
INCREMENT BY 1;
-- Sequence 삭제 //
drop sequence foodname_sequence;
-- Trigger 생성
-- BEFORE INSERT on '테이블명'
-- NEW.ID (Table의 id를 가리킴)
CREATE OR REPLACE TRIGGER foodstore_trigger
BEFORE INSERT
ON foodstore_tbl
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT foodstore_sequence.nextval INTO :NEW.ID FROM dual;
END;
-- 뷰 생성하기
create or replace view foodmenu_view as
select f1.id, f1.name, f1.price, f2.name as storename from foodmenu_tbl f1, foodstore_tbl f2 where f1.store_id = f2.id order by f1.id desc;
-- 페이징 SQL(뷰 방식)
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT * FROM FOODMENU_VIEW
) Z WHERE ROWNUM <= 20
) WHERE RNUM >= 11;
-- 페이징 SQL(뷰 원본으로 작성)
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT f1.id, f1.name, f1.price, f2.name as storename from foodmenu_tbl f1, foodstore_tbl f2 where f1.store_id = f2.id order by f1.id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1;
-- 특정 쿼리 페이징 SQL(뷰 원본으로 작성)
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT f1.id, f1.name, f1.price, f2.name as storename from foodmenu_tbl f1, foodstore_tbl f2 where f1.store_id = f2.id and f1.name like '%무봉리%' order by f1.id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1
-- 싱글 쿼리 (페이징)
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT * from foodmenu_tbl order by id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1
-- 특정 싱글 쿼리 SQL
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT * from foodmenu_tbl where name like '%야해해%' order by id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1
-- Sample Insert 문
-- 태스트 코드(foodmenu_tbl)
insert into foodmenu_tbl(name, price, store_id, cnt, regidate) values('사슴1', 1500, 1, 0, '2020-01-01');
insert into foodmenu_tbl(name, price, store_id, cnt, regidate) values('사슴2', 1400, 1, 0, '2020-01-01');
insert into foodmenu_tbl(name, price, store_id, cnt, regidate) values('사슴3', 1300, 1, 0, '2020-01-01');
insert into foodmenu_tbl(name, price, store_id, cnt, regidate) values('사슴4', 1100, 1, 0, '2020-01-01');
insert into foodmenu_tbl(name, price, store_id, cnt, regidate) values('사슴5', 1200, 1, 0, '2020-01-01');
insert into foodmenu_tbl(name, price, store_id, cnt, regidate) values('사슴6', 1300, 1, 0, '2020-01-01');
insert into foodmenu_tbl(name, price, store_id, cnt, regidate) values('사슴7', 1500, 1, 0, '2020-01-01');
-- 상점(foodstore_tbl)
-- 태스트 코드
insert into foodstore_tbl(name, address, regidate) values('치즈집1', '천사1', '2020-01-03');
insert into foodstore_tbl(name, address, regidate) values('치즈집2', '천사2', '2020-01-03');
insert into foodstore_tbl(name, address, regidate) values('치즈집3', '천사3', '2020-01-03');
insert into foodstore_tbl(name, address, regidate) values('치즈집4', '천사4', '2020-01-03');
insert into foodstore_tbl(name, address, regidate) values('치즈집5', '천사5', '2020-01-03');
insert into foodstore_tbl(name, address, regidate) values('치즈집6', '천사6', '2020-01-03');
-- 삭제 관련 Delete Query
delete from foodmenu_tbl;
select * from foodmenu_tbl where name = '야해해';
[파일명: food_tbl_sample_boardQueries.sql]
[첨부(Attachments)]
food_tbl_sample_boardQueries.zip
* 맺음글(conclusion)
게시판 페이징 관련해서 학습 또는 자습할 때, 고민을 하면서 쿼리를 날려보고 하면 좋을 듯 싶다.