Zerockcode's Note

2016년 1월 6일 수요일

오라클버전 P244- 오라클 페이징 기법

오라클의 페이징은 MySQL과 같이 간단하게 처리되지 못하고 rownum을 활용해 주어야 합니다.

쿼리를 실행할 때 primary key와 관련된 조건을 준다면 이를 이용하는 '실행 계획(Execution Plan)'을 생성하게 됩니다.

예를 들어 아래와 같은 SQL문을 실행해 sqldeveloper에서 실행해 봅니다.

select * from tbl_board
where bno > 0
order by bno desc;

sqldevloper의 실행 계획을 보면 아래와 같이 만들어 지는 것을 볼 수 있습니다.

실행 계획을 보면 내부적으로 primary key로 지정한 PK_BOARD를 이용해서 TBL_BOARD를 접근하는 것을 볼 수 있습니다.

이 때 DESCENDING이 적용되면서 접근하기 때문에 결과는 최근 데이터가 가장 먼저 나오게 됩니다.

order by의 경우 join등을 할 때 내부적으로 최적화된 방법을 찾기 때문에 항상 게시물의 역순으로 데이터를 검색한다는 보장은 없습니다.

이를 위해서 오라클에서는 개발자가 직접 실행계획을 유도하는 '힌트(Hint)'라는 것을 이용합니다.

select
/*+INDEX_DESC(tbl_board pk_board) */
*
from tbl_board
where bno > 0;

위의 SQL은 오라클의 힌트를 이용해서 강제로 primary key의 역순으로 데이터를 검색하도록 합니다. 실행 결과는 위의 그림과 동일하게 게시물의 역순으로 출력되게 됩니다.

실행된 결과물을 페이징 처리하기 위해서 필요한 또 다른 개념은 'ROWNUM'입니다.

ROWNUM은 쿼리의 처리가 끝나고 최종적으로 출력될 때 각 레코드에 붙는 일종의 일련 번호 입니다.

아래의 쿼리를 실행해 봅니다.

select
/*+INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title, content, writer, regdate, viewcnt
from tbl_board
where bno > 0;

위의 쿼리를 실행하면 출력되면서 번호가 붙는 것을 확인할 수 있습니다.

오라클의 페이징은 이렇게 실행 결과의 ROWNUM 값을 이용해서 페이지에 해당하는 데이터를 걸러내는 방식을 이용합니다.

아래의 쿼리를 실행해 봅니다.

select
/*+INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title, content, writer, regdate, viewcnt
from tbl_board
where bno > 0
and rownum <= 10;

rownum 이 10보다 작다는 조건은 마지막에 처리되는데, 이를 이용하면 아래와 같이 최신 데이터 10개를 가져올 수 있습니다.

여기서 주의할 점은 ROWNUM 조건은 반드시 1이 포함되어야 한다는 점입니다. ROWNUM은 마지막에 데이터가 출력되면서 붙는 번호이기 때문에 where 조건에 1이 포함되지 않으면 매번 새로운 데이터가 1번이 되면서 아무런 데이터가 출력되지 않는 문제가 생깁니다.

예를 들어 아래의 쿼리는 기대와 달리 아무런 결과가 만들어지지 않습니다.

select
/*+INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title, content, writer, regdate, viewcnt
from tbl_board
where bno > 0
and rownum between 11 and 20;

이 문제를 해결하기 위해서는 'in-line view'기법을 이용해야 합니다.

in-line view는 쉽게 말해서 실행된 SQL문을 하나의 테이블처럼 간주해서 사용하는 방식입니다.

만일 10개씩 화면에 내용을 출력하는 경우 2페이지의 데이터는 우선 20개의 레코드를 가져온 후에 다시 rownum으로 생성된 번호를 이용해서 최신 10개의 데이터를 배제하는 방식입니다.

위의 설명을 쿼리로 만들면 아래와 같습니다.

select
*
from
(
select
/*+INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title, content, writer, regdate, viewcnt
from tbl_board
where bno > 0
and rownum <= 20 )
where rn > 10;

쿼리의 실행 결과는 아래와 같습니다.

실행된 결과를 보면 RN 컬럼이 ROWNUM번호를 의미하고 있고, BNO가 역순으로 정렬된 것을 볼 수 있습니다.

이 때 발생하는 실행 계획은 다음과 같습니다.

실행 계획을 보면 우선 ROWNUM <= 20 조건을 먼저 실행하고, 실행된 결과 내에서 RN > 10 조건을 이용하므로 2페이지의 데이터를 출력하게 됩니다.

오라클버전 P163 tbl_board 테이블 생성 SQL

create table tbl_board (

bno number,

title varchar2(200) not null,

content varchar2(4000) not null,

writer varchar2(50) not null,

regdate date default sysdate,

viewcnt number default 0

);

--Primary key 추가

alter table tbl_board add constraint pk_board

primary key(bno);

-- 시퀀스 추가

create sequence seq_board;

P164 새로운 게시물의 등록에 사용하는 SQL

새로운 게시물의 추가

insert into tbl_board (bno, title,content,writer)

values (seq_board.nextval,'제목','내용','user00');

게시물의 수정

update tbl_board set title= '수정된 제목',content = '수정된 내용', writer='user01'

where bno = 1;

게시물의 삭제

delete tbl_board where bno = 1;

테이블의 자가 복사를 이용한 많은 양의 데이터 넣는 SQL

여러번 실행하면 현재 테이블의 기하급수적인 데이터가 생성

insert into tbl_board (bno, title,content,writer)

(select seq_board.nextval, title, content, writer from tbl_board);

MySQL과 달리 오라클은 기본이 Auto Commit이 아니므로 반드시 작업후 커밋을 할것

commit;

오라클버전 P139 root-context.xml의 수정

오라클 데이터베이스와 Log4jdbc-log4j2를 적용하기 위해서 가장 중요한 것은 적절한 JDBC드라이버를 사용하는 것입니다.

테스트 결과 ojdbc14.jar는 제대로 동작하지 않으므로 주의하시기 바랍니다.

<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>


<property name="url" value="jdbc:log4jdbc:oracle:thin:@localhost:1521:XE"></property>
<property name="username" value="zerock"></property>
<property name="password" value="zerock"></property>
</bean>

2016년 1월 4일 월요일

오라클버전 P131 resources/mappers의 memberMapper.xml파일

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.zerock.mapper.MemberMapper">

<select id="getTime" resultType="string">
select sysdate from dual
</select>

<insert id="insertMember">

insert into tbl_member (userid, userpw, username, email)
values (#{userid}, #{userpw}, #{username}, #{email})

</insert>

</mapper>

오라클버전 P126 tbl_member테이블의 SQL 스크립트

create table tbl_member (
userid varchar2(50) not null,
userpw varchar2(50) not null,
username varchar2(50) not null,
email varchar2(100),
regdate date default sysdate,
updatedate date default sysdate);

alter table tbl_member add constraint pk_member primary key (userid);

오라클의 경우 varchar 대신에 varchar2를 이용하도록 합니다.

regdate와 updatedate의 경우 오라클의 Date 타입을 이용하고, 데이터의 생성시 현재 시간을 기본으로 사용하기 위해서 sysdate 를 이용합니다.

오라클버전 P86-DataSource 설정하기

오라클 버전의 경우 root-context.xml은 아래와 같은 내용으로 수정되어야 합니다.

P88의 DataSource의 테스트 코드는 기존과 동일합니다.

package org.zerock.controller;

import java.sql.Connection;

import javax.inject.Inject;
import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"file:src/main/webapp/WEB-INF/spring/**/*.xml"})
public class DataSourceTest {

@Inject
private DataSource ds;

@Test
public void test()throws Exception {

Connection con = ds.getConnection();

System.out.println(con);

con.close();

}

}

오라클버전 P70- 오라클 연결 테스트와 jUnit

위의 화면은 Maven Repository에 등록된 오라클 드라이버입니다. ojdbc14버전의 드라이버만 지원합니다.

ojdbc14버전의 경우 JDK1.4에 최적화된 드라이버이므로, JDK1.6이상을 사용하는 경우라면 적합하지 않습니다.

만일 ojdbc14버전을 사용하는 경우 책의 설명에 나오는 JDBC의 로그를 기록하지 못하는 문제점 등이 발생합니다.

JDBC드라이버를 사용하는 방법에는 크게 다음과 같은 방법을 고려할 수 있습니다.

1. 복잡하지만 Maven에 JDBC드라이버를 추가해서 사용하는 방법
2. STS상의 프로젝트에 라이브러리를 추가해서 사용하는 방법
3. 클래스 패스를 이용해서 JDBC드라이버를 설정하고 사용하는 방법

1번의 경우 Maven 프로그램이 설치되어야 하기 때문에 복잡하므로, 초급자에게는 권장하고 싶지는 않습니다.

3번의 경우는 시스템 전체에 영향을 주기 때문에, 여러 프로젝트를 진행해야 하는 개발자들에게는 유용하지 않습니다.

이 블로그에서는 2번을 기준으로 설명하도록 합니다.

2. JDBC드라이버 파일의 확보

SQL Developer를 설치했다면 최신의 JDBC드라이버를 이미 가지고 있는 상태입니다.

SQL Developer의 압축이 풀린 경로에 jdbc폴더 내에 lib 폴더안에는 ojdbc6.jar파일이 포함되어 있습니다.

3. 프로젝트 내에 JDBC드라이버 추가하기

프로젝트 내에 JDBC드라이버를 추가하는 가장 간단한 방법은 'WEB-INF'폴더 내에 'lib'라는 폴더를 생성하고 JDBC 드라이버를 넣어주는 방식입니다.

작성하는 프로젝트의 'Properties'를 이용해서 추가한 'lib'폴더를 참조하도록 설정합니다.

4. JDBC의 연결 테스트 코드 작성하고 테스트 하기

위의 설정이 완료되었다면 아래와 같은 테스트 코드를 이용해서 JDBC연결 테스트 코드를 작성합니다.

package org.zerock.controller;

import java.sql.Connection;
import java.sql.DriverManager;

import org.junit.Test;

public class OracleConnectionTest {

private static final String DRIVER ="oracle.jdbc.driver.OracleDriver";
private static final String URL ="jdbc:oracle:thin:@localhost:1521:XE";
private static final String USER="zerock";
private static final String PW="zerock";

@Test
public void testConnect() throws Exception{

Class.forName(DRIVER);

try(Connection con = DriverManager.getConnection(URL, USER, PW)){

System.out.println(con);

}
}
}