학원수업_61day [JSP,Servlet(27day) - 게시판 만들기(Pagination, SQL /Inline View )

* SQL

row_number() over()

 : 조회된 row(행)에 대한 순차적인 번호를 제공하는 함수 

Inline View : SQL 문장에서 FROM 절에 사용되는 SubQuery 
- FROM 절에 서브쿼리로 SELECT 되는 조회결과를 테이블처럼 사용  → 하나의 테이블로 인식한다

SELECT 컬럼,컬럼 
  FROM (
   서브쿼리 
  ) 별칭

 

* 테이블 만들기

drop table player;

create table player(
 no number primary key,
 title varchar2(100) not null,
 singer varchar2(30) not null,
 price number not null
)

select * from player;

create sequence mp3_seq;

 

* INSERT values 테이블

insert into player(no,title,singer,price) values(mp3_seq.nextval,'잊어야 한다는 마음으로','아이유',700);
insert into player(no,title,singer,price) values(mp3_seq.nextval,'사람','지코',1000);
insert into player(no,title,singer,price) values(mp3_seq.nextval,'밤편지','아이유',1000);
insert into player(no,title,singer,price) values(mp3_seq.nextval,'도망가자','선우정아',1000);
insert into player(no,title,singer,price) values(mp3_seq.nextval,'그날','박효신',1000);
insert into player(no,title,singer,price) values(mp3_seq.nextval,'좋은밤 좋은꿈','너드커넥션',500);
insert into player(no,title,singer,price) values(mp3_seq.nextval,'사계','태연',600);
insert into player(no,title,singer,price) values(mp3_seq.nextval,'봄눈','루시드폴',1000);
commit
select count(*) from player;

 

테이블 생성

* 조회된 결과 행 번호를 부여하기 위해 ROW_NUMBER() OVER(정렬) 함수를 이용 

select row_number() over(order by no desc) as rnum , no , title from player

* rnum 이 3 이하인 곡들을 조회

- error   ROW_NUMBER 는 조회된 결과행에 대해 번호를 부여하기 때문 

select row_number() over(order by no desc) as rnum , no , title from player where rnum<3

*  Inline View ( FROM 절의 SUBQUERY ) 를 이용하면 된다 

SELECT rnum, no, title, singer, price
FROM (
	SELECT row_number() over(ORDER BY no DESC) AS rnum , no , title, singer , price from player 
) WHERE rnum<=3;

 

* 한 페이지당 3곡을 제공 , 2page에 해당하는 3곡을 보여주고자 한다

* row number 기준으로 4 이상 6 이하의 곡을 조회하면 된다 

SELECT rnum, no, title, singer, price
FROM (
	SELECT row_number() over(ORDER BY no DESC) AS rnum , no , title, singer , price from player 
) WHERE rnum>=4 and rnum<=6;

*  between 을 이용할 수도 있다

SELECT rnum, no, title, singer, price
FROM (
	SELECT row_number() over(ORDER BY no DESC) AS rnum , no , title, singer , price from player 
) WHERE rnum between 4 and 6

 

* Inline View  적용

 

예) 예를들어 Inline view를 적용하지않고 no=3을 삭제했다 가정하면,  no 는 1,2,3,4,5 가 순서대로 되지않고

1,2,4,5 로 빠지게 된다

 

---

DROP TABLE community_member;



DROP TABLE board;

 

* 게시판 테이블 생성

CREATE TABLE board(
	no NUMBER PRIMARY KEY,
	title VARCHAR2(100) NOT NULL,
	content CLOB NOT NULL,
	hits NUMBER DEFAULT 0,
	time_posted DATE NOT NULL,
	id VARCHAR2(100) NOT NULL,
	CONSTRAINT myboard_fk FOREIGN KEY(id) REFERENCES community_member(id)
)

*  Oracle 문자열 varchar2 타입은 최대 4000byte 까지 가능 
      Oracle clob datatype 을 이용하면 많은 문자열을 저장할 수 있음 
      CLOB (character large object) 최대 4GB까지 가능 
      사용법은 varchar2 와 동일함  

DROP SEQUENCE board_seq;
CREATE SEQUENCE board_seq;

INSERT INTO board(no,title,content,time_posted,id) VALUES(board_seq.nextval,'즐공','웹공부중~',sysdate,'java');
INSERT INTO board(no,title,content,time_posted,id) VALUES(board_seq.nextval,'열공','인생공부중~',sysdate,'spring');

 

DROP TABLE community_member;
CREATE TABLE community_member(
	id VARCHAR2(100) PRIMARY KEY,
	password VARCHAR2(100) NOT NULL,
	name VARCHAR2(100) NOT NULL
)
INSERT INTO community_member(id,password,name) VALUES('java','a','아이유');
INSERT INTO community_member(id,password,name) VALUES('spring','a','박보검');

COMMIT

SELECT * FROM community_member;

COMMIT
SELECT * FROM board;

 

* 게시판 리스트 조회 SQL test 
게시물번호, 제목 , 작성자명 , 작성일 , 조회수가 제공되어야 함 
게시물번호 no , 제목 title , 작성일 time_posted , 조회수 hits

→ board table 

작성자명 name

→ community_member table 

community_member 와 board 테이블 결합

→ inner join

 

최근 게시물순으로 정렬 , 작성일 to_char(column,'YYYY.MM.DD')

---

* Oracle  버전

SELECT b.no,b.title,m.name,TO_CHAR(time_posted,'YYYY.MM.DD') as time_posted,b.hits
FROM community_member m, board b
WHERE	m.id=b.id
ORDER BY b.no DESC

* ANSI SQL 버전

SELECT b.no,b.title,m.name,TO_CHAR(time_posted,'YYYY.MM.DD') as time_posted,b.hits
FROM community_member m
INNER JOIN board b ON m.id=b.id
ORDER BY b.no DESC

* 로그인 SQL 

-- id password가 일치하지 않으면 결과조회 x 
SELECT name FROM community_member WHERE id='java' AND password='b';
-- id password 일치할때만 결과조회 o 
SELECT name FROM community_member WHERE id='java' AND password='a';

* 상세 글보기 
board : no,title,content,hits,to_char(time_posted,'YYYY.MM.DD HH24.MI:SS')
community_member : id, name
no 1 의 상세 게시글 조회 sql 

- INNER JOIN 

SELECT	b.no,b.title,b.content,b.hits,to_char(time_posted,'YYYY.MM.DD HH24.MI:SS') as time_posted,m.id,m.name
FROM	board b,community_member m
WHERE	b.id=m.id AND b.no=1;

- ANSI SQL

SELECT	b.no,b.title,b.content,b.hits,to_char(time_posted,'YYYY.MM.DD HH24.MI:SS') as time_posted,m.id,m.name
FROM	board b INNER JOIN community_member m ON  b.id=m.id
WHERE b.no=1;

- 게시글쓰기 sql 

INSERT INTO board(no,title,content,time_posted,id) VALUES(board_seq.nextval,'즐공','웹공부중~',sysdate,'java');

- 조회수 업데이트 sql 
no 1 의 조회수 1씩 증가 

UPDATE board SET hits=hits+1 WHERE no=1

SELECT * FROM board WHERE no=1;

- 게시물 삭제 
 no 7 삭제 sql 

DELETE FROM board WHERE no=7

SELECT * FROM board;

- 게시물 수정 

SELECT title,content FROM board WHERE no=3;
UPDATE board SET title='수정테스트',content='맛점하세요' WHERE no=3;

** 게시판 페이징 SQL  **

SELECT COUNT(*) FROM board;
INSERT INTO board(no,title,content,time_posted,id) VALUES(board_seq.nextval,'열공','웹공부중~',sysdate,'java');
INSERT INTO board(no,title,content,time_posted,id) VALUES(board_seq.nextval,'불금','인생공부중~',sysdate,'spring');
INSERT INTO board(no,title,content,time_posted,id) VALUES(board_seq.nextval,'즐주말','즐거운 프로젝트~',sysdate,'java');

 

- SELECT 한 데이터를 다시 INSERT 하는 구문 : 4번 실행 -> 48개 게시물을 INSERT 하게 된다

INSERT INTO board(no,title,content,time_posted,id)
SELECT board_seq.nextval, title,content,sysdate,id FROM board

 

---

** 게시판 Pagination SQL
step1 : 게시물 리스트 화면에서 사용하는 sql 에 row_number() over() 함수를 적용 

SELECT ROW_NUMBER() OVER(ORDER BY no DESC) AS rnum,no,title,TO_CHAR(time_posted,'YYYY.MM.DD') as time_posted,hits
FROM board

 

** step2 : 게시물 리스트 1page에 해당하는 게시물 리스트를 조회 ( runm 1 이상 5 이하 ) : 한페이지에 5개씩 보여줄 예정
- 한 페이지에 5개씩 보여줄 예정
- inline view를 이용 ( from 절에 사용하는 subquery 를 말함)

SELECT rnum,no,title,time_posted,hits 
FROM (
	SELECT ROW_NUMBER() OVER(ORDER BY no DESC) AS rnum,no,title,TO_CHAR(time_posted,'YYYY.MM.DD') as time_posted,hits
FROM board 
)
WHERE rnum between 1 and 5

 

** step3 : step2 조회 결과에 더해서 게시물 리스트에는 회원명 즉 작성자명이 필요하다
- join을 이용해 id가 일치하는 회원의 회원 name 을 함께 조회되도록 한다

SELECT b.rnum,b.no,b.title,b.time_posted,b.hits,m.name 
FROM (
	SELECT ROW_NUMBER() OVER(ORDER BY no DESC) AS rnum,no,title,TO_CHAR(time_posted,'YYYY.MM.DD') as time_posted,hits,id
	FROM board 
) b INNER JOIN community_member m		ON b.id=m.id
WHERE rnum between 1 and 5
ORDER BY b.no DESC

---

*  (Pagiantion) 

 

 

 

 

 

* 게시판 페이징 처리 로직을 정의한 객체 (PagiantionDemo)

 

 

1. 현재 페이지

private int nowPage=1;

2. 페이지당 게시물 수

private int postCountPerPage=5;

 

3. 페이지 그룹당 페이지수

private int pageCountPerPageGroup=4;

* 1~3 모아둔 것

package org.kosta.myproject.test.pagination;
/**
 * 게시판 페이징 처리 로직을 정의한 객체
 * @author KOSTA
 *
 */
public class PaginationDemo {
	/**
	 * 현재 페이지
	 */
	private int nowPage=1;
	/**
	 * 페이지당 게시물수 
	 */
	private int postCountPerPage=5;
	/**
	 * 페이지 그룹당 페이지수 
	 */
	private int pageCountPerPageGroup=4;

 

 

* 총 게시물 수 ( 데이터베이스에 저장되어 있는 )  

private int totalPostCount;

public PaginationDemo(int totalPostCount) {
this.totalPostCount=totalPostCount;
}
public PaginationDemo(int totalPostCount,int nowPage) { // 예를들어 3페이지로 넘어갔다면 3페이지가 보여지게하는것 
this.totalPostCount=totalPostCount;
this.nowPage=nowPage;
}
public int getNowPage() { // 첫화면 1페이지일때
return nowPage;
}

 

 * 현재 페이지 번호에( nowPage ) 해당하는 게시물 리스트의 시작 row number를 반환
 * 이전 페이지 마지막 번호 + 1 
 2 페이지의 시작번호는 1 페이지의 마지막 번호 5 + 1 이 시작 번호가 된다. 5라는 값은 postCountPerPage
 참고 : 사용자가 페이지번호를 클릭하면 ListController에서 페이지번호를 전달받고 
   -     BoardDAO로부터 총게시물수를 반환받은 후 Pagination 객체를 생성해서
   -    findPostList(Pagination) 에 전달하여 현 페이지에 맞는 게시물 리스트를 반환받을 때 사용하기 위한 메서드

 

●  getStartRowNumber 메서드

	 * @return startRowNumber
	 */
 public int getStartRowNumber() {
		//	이전페이지		페이지당 게시물수 + 1 하면 현 페이지의 시작번호
		return (this.nowPage-1)*this.postCountPerPage+1;  // postCountPerPage -> 페이지당 게시물수 /this.는 인스턴스변수가 존재한다는 
	}

 

return (this.nowPage-1)*this.postCountPerPage+1;

              이전페이지      페이지당 게시물수 

---

*  * 현재 페이지 번호(nowPage) 에 해당하는 게시물 리스트의 게시물 row의 마지막 번호를 반환 
  - nowPage * postCountPerPage 의 연산결과가 게시물의 마지막 번호이나 
  - 만약 totalPostCount(총게시물수) 보다 클 경우에는 totalPostCount(총게시물수)가 
  - 현 페이지의 마지막 게시물 row number가 된다 

 

● getEndRowNumber 메서드

 * @return endRowNumber
	 */
	public int getEndRowNumber() {
		int endRowNumber=this.nowPage*this.postCountPerPage; //
		if(this.totalPostCount<endRowNumber) { //totalPostCount 가 곱한값보다 작으면 //총게시물수보다 크면 큰숫자가 마지막게시물숫자가 된다?
			endRowNumber=this.totalPostCount; //this 생략가능
		}
		return endRowNumber;
	}

 

 

* 총 페이지 수를 반환 
 - totalPostCount / postCountPerPage 연산값의 나머지가 0 이면 나눈값이 총페이지 수 
 - totalPostCount / postCountPerPage 연산값의 나머지가 0 이  아니면  나눈값+1 이 총페이지 수 
 
예)  게시물수 1 2 3 4 5    6 7 8 9 10   11 12 
                        1page       2page       3page  

	 * @return totalPage
	 */
 public int getTotalPage() {
	int totalPage=totalPostCount / postCountPerPage;
	if(totalPostCount % postCountPerPage!=0) // 나누어 떨어지지않으면, -> 나머지가 0이 아니면 총 페이지수는 +1 한 값이다
		totalPage+=1;
	return totalPage;
	}

 

 * 총페이지 그룹수를 반환
 - getTotalPage() 총페이지수  /  pageCountPerPageGroup -> 나머지가 0이면 나눈값이 총그룹수
→ 나머지가 존재하면 나눈값 + 1 한 값이 총그룹수 
 예)   총게시물수 48개 
 -       페이지      1 2 3 4     5 6 7 8      9 10
 - 페이지그룹     1group     2group      3group 

 * @return totalPageGroup
	
 public int getTotalPageGroup() {
	int totalPageGroup=0;
		
	return totalPageGroup; 
	}