뉴렉처[자바 JDBC 프로그래밍] 강의 복습 13강~25강

17강 - 페이징을 위한 쿼리 만들기

(참고영상: www.youtube.com/watch?v=-YjsGGCAtik&list=PLq8wAnVUcTFWxwoc41CqmwnO-ZyRDL0og&index=18 )

 

※DB 결과의 rownum(행 번호)를 이용하는 것이 핵심!!

 

① 테이블 결과에 대한 일련 번호(행 번호) 출력하기

select rownum, notice.* from notice;

==> notice 테이블에 데이터의 rownum와 notice 테이블의 전체 데이터를 출력

 

② 1부터 1까지의 rownum 가져오기

select rownum, notice.* from notice

where rownum between 1 and 10;

위와 같이 쿼리식을 작성하여 수행했을때는 rownum이 1부터 10까지인 데이터를 잘 가져온다.

그런데! rownum이 2부터 10 또는 5부터 10까지의 데이터를 가져오게 명령했을때는 안나온다.

select rownum, notice.* from notice               select rownum, notice.* from notice
where rownum between 2 and 10;        또는    where rownum between 5 and 10;

왜 안될까? 그 이유는

⑴rownum은 테이블이 만들어지고, ⑵그 위에서 부터 rownum 번호를 설정해서 ⑶where로 뽑아오는 방식이 아닌 ⑴where절로 데이터를 테이블로 만든 다음에 ⑵rownum이 매겨지는 방식이기 때문이다.

 

③ 전체 테이블에 rownum을 매긴 것을 서브쿼리 사용함으로써 대안 가능하다.

==> 먼저 ⑴rownum은 테이블이 만들어지고 그 위에서 부터 rownum 번호를 설정해서 ⑵where로 뽑아오는 방식

select notice.* from ( select rownum num, notice.* from notice )

where rownum between 2 and 10;

 

④ 2부터 10까지 나오는 문제는 해결됬지만 최신글 부터 정렬해서 나오기 위해서

⑴데이터를 뽑을때 정렬을 먼저하고 ⑵rownum을 붙여준다.

select notice.* from ( select rownum num, notice.* from notice order by regdate desc)

where rownum between 2 and 10;

 

⑤ 서브쿼리 "select row num, notice.* from notice order by regdate desc" 의 문제 발생

 

아래 사진과 같이 최신 글에 대한 10개가 나와야하는데....

④ 단계에서 작성한 쿼리식을 호출하면 의도와 다르게 나온다.

왜 그럴까? 

서브쿼리 select rownum num, notice.* from notice order by regdate desc 를 한번 출력해보자

출력 결과를 보니 ⑴데이터 전체에 대해서 rownum이 붙여진 다음에 ⑵regdate에 대해서 정렬이 일어난다.

==> 우리는 최신글로 정렬이 된 후에 rownum이 붙길 원한다.

 

⑥ 최신글로 정렬 후 rownum을 붙여주기 위해서 또 하나의 서브쿼리를 만들어 준다.

select notice.* from (

      select rownum num, N.* from ( -- 최신글로 정렬된 테이블의 rownum을 num으로 별칭해줌으로써 

          select * from notcie order by regdate desc

      ) N -- 최신글로 정렬한 테이블을 별칭 N

)

where num between 2 and 10; -- 최근글의 rownum num에 대해서 2 and 10

 

(※ 주의!!!만약 별칭을 안붙여주면 최신글로 정렬된 rownum이 아닌 최종 결과 테이블에대한 rownum으로 조건을 붙여주게 된다.)

 

※ 우선 순위 정리

⑴ where 절 ⑵rownum 붙는것 ⑶order by

③에서 where 절 실행하기 전에 rownum을 붙여 주었고,

⑥에서 rownum을 붙이기 전에 order  by를 해주었다.

 

18강 - 페이징 쿼리 이용하기

public List<Notice> getList(int page) throws ClassNotFoundException, SQLException {

int start = 1 + (page-1)*10 //1, 11, 21, 31

int end = page*10 //10, 20, 30, 40

String sql = "select * from (" +

                        " select rownum num, N.* from (" +

                                 " select * from notice notice order by regdate desc" +

                        " ) N" +

               ")" +

                "where num between ? and ?"

 

Class.forName(dirver)

Connection con = DriverManager.getConnection(url, uid, upw)

PreparedStatement st = con.prepareStatement(sql)

st.setInt(1, start)

st.setInt(2, end)

ResultSet rs = st.executeQuery()

 

List<Notice> list = new ArrayList<>()

while (rs.next()) {

 

int id = rs.getInt("ID")

String title = rs.getString("TITLE")

String writerId = rs.getString("WRITER_ID")

Date regDate = rs.getDate("REGDATE")

String content = rs.getString("CONTENT")

int hit = rs.getInt("HIT")

String files = rs.getNString("FILES")

 

Notice notice = new Notice(id, title, writerId, regDate, content, hit, files)

list.add(notice)

}

 

rs.close()

st.close()

con.close()

 

return list

}

 

19강 - 목록을 위한 View 생성하기

[뷰 생성]

 

create view notice_view

as 

select * from (
    select rownum num, N.* from (
        select * from notice notice order by regdate desc
    ) N
);

[뷰 활용]

 

select notice_view

where num between 11 and 20;

 

24강 - 검색 서비스 추가하기

getList()함수의 매개변수를 추가한다. field는 검색조건 (title/content/writerId), query는 검색내용

 

public List<Notice> getList(int page, String field, String query) throws ClassNotFoundException, SQLException {
   int start = 1 + (page-1)*10 //1, 11, 21, 31
   int end = page*10 //10, 20, 30, 40
   String sql = "select * from notice_view where title like '%A%' and num between ? and ?"
   ....
}

 

여기서 String sql 변수에 sql문을 넣어야할때

String sql = "select * from notice_view where title like '%A%' and num between ? and ?" 을

"select * from notice_view where ? like ? and num between ? and ?" 과 같이 물음표로 값을 세팅하는 것처럼 넣으면

==>  "select * from notice_view where 'title' like '%A%' and num between ? and ?" 으로 들어간다.

- title을 넣을때는 String 값으로 인식해서 ' ' 이 붙어서 들어가는데 title은 속성이라서 '' 이 들어가면 안된다.

따라서 title의 경우에는 물음표(값으로)로 넣으면 안된다.

- '%A%' 의 경우에는 값으로 인식해도 됨으로 물음표로 해서 넣어도 된다.

 

따라서 String sql = "select * from notice_view where "+ field +" like ? and num between ? and ?"; 해주어야 한다.

 

st.setString(1, "%"+ query +"%");

st.setInt(2, start);

st.setInt(3, end);

으로 나머지는 값으로 세팅하면 된다.

 

25강 - 트랜잭션 처리란

트랜잭션이란, 하나의 단위로 수행되길 바라는 쿼리의 묶음(= 업무 수행단위, 논리적 수행단위)

트랜잭션 처리란, ACID를 유지하는 것이다.

 

* ACID란, 데이터베이스 트랜잭션들이 안정적으로 수행된다는 것을 보장하기 위한 성질을 가르키기 위한 약어

Automaticity: 원자성

=> 트랜잭션과 간련된 작업들이 모두 수행되었는지 아니면, 모두 실행이 안되었는지를 보장하는 능력

ex. 자금 이체는 성공할 수도 실패할 수도 있지만, 중간 단계까지 실행되고 실패하는 일은 없도록 하는 것

Consistency: 일관성

=> 트랜잭션이 실행을 성공적으로 완려하면 언제나 일관성 있는 데이터베이스 상태로 유지하는 것

ex. 무결성 제약이 모든 계좌는 잔고가 있어야 한다면 , 이를 위반하는 트랜잭션은 중단된다.

Isolation: 고립성

=> 트랜잭션을 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하는 것

즉, 트랜잭션 밖에 있는 어떤 연산도 중간 단계의 데이트를 볼 수 없음을 의미한다.

ex. 은행 관리자는 이체 작업을 하는 도중에 쿼리를 실행하더라도 특정 계좌간 이체하는 양쪽을 볼 수 없더.

Durability: 지속성

=> DBMS가 사용자에게 트랜잭션 커밋(Commit) 응답을 했을 경우, 설사 데이터베이스 객체에 대한 해당 변경 사항이 디스크에 반영(flush)되기 전에 시스템 장애가 발생하였더라도 해당 트랜잭션의 커밋은 보장 되어야 한다는 속성.

DBMS는 트랜잭션의 지속성을 제공하기 위해 로그(log)라고 하는 즉, 데이터베이스 객체의 갱신 작업에 대한 기록을

관리한다.

커밋된 트랜잭션에 의해 갱신된 내용이 디스크에 미처 반영되기 전에 시스템 장애가 발생하면, 시스템 재구동 동시에 로그를 판독하여 변경된 내용을 복구하게 된다.

 

 

 

 

[출처]

뉴렉처

www.youtube.com/watch?v=c0s7g7iVtwc&list=PLq8wAnVUcTFWxwoc41CqmwnO-ZyRDL0og

ACID 출처

m.blog.naver.com/PostView.nhn?blogId=sophie_yeom&logNo=220245191398&proxyReferer=https:%2F%2Fwww.google.com%2F