Chap 12. 데이터베이스 프로그래밍 기초

Chap 12. 데이터베이스 프로그래밍 기초

* 데이터베이스 생성

MYSQL 디렉터리\bin> mysql -u root -p

password : ******

mysql> create database [DB 명]

-u 는 명령 수행할 계정 지정

-p 이후에는 db 이름을 지정한다.

사용자 추가는..

mysql> grant [권한목록] on [DB명].* to '[계정]'@'[서버]' identified by '[암호]'

ex)

mysql> grant select, insert, update, delete, create, drop on testDB.* to 'testID'@'%' identified by 'password';

서버명에 % 를 지정하면 모든 서버에서 접근 가능하다는 것.

cf) 한글 데이터를 저장할 경우 다음과 같은 명령어로 DB에 접근하는 것이 좋다.

> mysql -u root -p --default-character-set=euckr

* JSP 에서 JDBC 프로그래밍 하기.

-

JDBC 의 구조

JDBC 는 Java Database Connectivity 의 약자.

자바에서 데이터베이스와 관련된 작업을 처리할 수 있도록 도와주는 API 이다.

DBMS 종류에 상관없이 하나의 JDBC API 를 사용해서 데이터베이스 작업을 처리할 수 있다.

-

JDBC 드라이버 준비하기.

DBMS 에 알맞은 JDBC 드라이버를 준비해야 한다.

JDBC 드라이버는 클래스 형태로 존재하며 일반적으로 jar 파일로 제공된다.

WEB-INF/lib 디렉터리에 해당 jar 파일을 복사해주면 세팅 끝!

-

JDBC 프로그래밍의 코딩 스타일

1. JDBC 드라이버 로딩

2. 데이터베이스 커넥션 구함

3. 쿼리 실행을 위한 Statement 객체 생성

4. 쿼리 실행

5. 쿼리 실행 결과 사용

6. Statement 종료

7. 데이터베이스 커넥션 종료

-

DBMS 와의 통신을 위한 JDBC 드라이버

try{

Class.forName( "JDBC 드라이버 클래스 이름" );

}

catch( ClassNotFoundException ex ){

// 에러처리

}

MYSQL - com.mysql.jdbc.Driver

오라클 - oracle.jdbc.driver.OracleDriver

MSSQL - com.microsoft.sqlserver.jdbc.SQLServerDriver

-

데이터베이스 식별을 위한 JDBC URL

jdbc:[DBMS]:[데이터베이스식별자]

ex) jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=euckr

-

데이터베이스 커넥션

Connection DriverManager.getConnection( String jdbcURL );

Connection DriverManager.getConnection( String jdbcURL, String user, String password );

SQLException 을 던지곤 한다.

-

Statement 를 사용한 쿼리 실행.

Statement stmt = conn.createStatement();

ResultSet stmt.executeQuery( String query );

int stmt.executeUpdate( String query );

-

ResultSet 에서 LONG VARCHAR 타입 값 읽어오기.

LONG VARCHAR 타입은 대량의 텍스트 데이터를 저장할 때 사용된다.

ResultSet 에서 LONG VARCHAR 타입의 데이터를 읽어오기 위해서는 getCharacterStream() 메서드를 사용해야 한다.

-

Statement 를 이용한 쿼리 실행 시 작은 따옴표 처리.

쿼리 실행시 작은 따옴표가 들어가면 작은 따옴표 두 개를 사용하는 형태로 변경해줘야 한다.

-

PreparedStatement 를 사용한 쿼리 실행

Statement 와 동일한 기능이나, 미리 compile 을 해놓는 형태라 반복적으로 사용할 경우 성능이 더 좋으며, SQLInjection 에도 안정적이다.

차이점은 PreparedStatement 는 SQL 쿼리의 틀을 미리 생성해 놓고 값을 나중에 지정한다.

pstmt = conn.prepareStatement( "insert into MEMBER( MEMBERID, NAME, EMAIL ) values (?,?,?)" );

pstmt.setString( 1, "id" );

pstmt.setString( 2, "감자" );

...

-

PreparedStatement 에서 LONG VARCHAR 타입 값 지정하기.

setCharacterStream( int index, Reader reader, int length ) 를 이용한다.

-

웹 어플리케이션 구동 시 JDBC 드라이버 로딩하기.

Class.forName() 을 통해서 JDBC 드라이버를 로딩할 경우, 매 request 에 계속 로딩을 한다.

JDBC 드라이버를 웹 어플리케이션이 시작할 떄 1회 로딩하는 것이 가장 좋다.

public class DBLoader extends HttpServlet{

public void init(ServletConfig config) throws ServletException{

try{

String drivers = config.getInitParameter( "jdbcdriver" );

StringTokenizer st = new StringTokenizer( drivers, "," );

while( st.hasMoreTokens() ){

String jdbcDriver = st.nextToken();

Class.forName( jdbcDriver );

}

}

catch( Exception e ){

throw new ServletException( e );

}

}

}

web.xml 에 jdbc driver 관련된 parameter 와 initialize 를 위한 DBLoader 최초 1회 실행 설정을 해준다.

<web-app ...>

<servlet>

<servlet-name>JDBCDriverLoader</servlet-name>

<servlet-class>loader.DBLoader</servlet-class>

<init-param>

<param-name>jdbcdriver</param-name>

<param-value>com.mysql.jdbc.Driver</param-value>

</init-param>

<load-on-startup>1</load-on-startup>

</servlet>

</web-app>

* JDBC 에서 트랜잭션 처리

트랜잭션을 구현하는 방법은 크게 2가지가 있다.

1. JDBC 의 오토 커밋 모드를 false 로 지정

conn.setAutoCommit( false );

try{

conn.commit();

}

catch( SQLException e ){

conn.rollback();

}

2. JTA ( Java Transaction API ) 를 이용.

* 커넥션 풀

데이터베이스 커넥션을 접속할 때마다 생성해서 사용하면, 커넥션을 생성하고 닫는 데 시간은 물론 리소스도 많이 사용한다.

동시 접속자수가 많은 경우 전체 성능을 낮추는 원인이 된다.

성능 문제 해결을 위한 일반적인 방식이 커넥션 풀 기법이다.

-

커넥션 풀이란?

데이터베이스 커넥션을 미리 만들어서 풀 속에 저장해두고 있다가 필요할 때 커넥션을 풀에서 가져다 쓰고 다시 풀에 반환하는 기법.

커넥션 풀을 사용하면 커넥션을 생성하고 닫는데 시간이 소모되지 않아 어플리케이션 실행 속도가 빨라진다.

또한, 생성될 수 있는 커넥션 수를 제어하기 때문에 동시 접속자가 증가해도 웹 어플리케이션이 쉽게 다운되지 않는다.

-

DBCP 를 이용해서 커넥션 풀 사용하기.

DBCP 는 자카르타 프로젝트에서 제공하는 API.

DBCP 사용 방법은..

1. DBCP 관련 Jar 파일 및 JDBC 드라이버 Jar 파일 설치.

2. 커넥션 풀 관련 설정 파일 초기화.

3. 커넥션 풀 관련 드라이버 로딩하기.

4. 커넥션 풀로부터 커넥션 사용하기.

(1) 필요한 jar 파일 복사하기

commons-dbcp-[version].jar

commons-pool-[version].jar

위 두 파일을 WEB-INF/lib 폴더에 위치시킨다.

(2) 설정 파일 작성 및 위치.

pool.jocl 파일을 작성한다.

<object class="org.apache.commons.dbcp.PoolableConnectionFactory"

xmlns="http://apache.org/xml/xmlns/jakarta/commons/jocl">

<object class="org.apache.commons.dbcp.DriverManagerConnectionFactory">

<string value="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=euckr"/>

<string value="userName"/>

<string value="password"/>

</object>

<object class="org.apache.commons.pool.impl.GenericObjectPool">

<object class="org.apache.commons.pool.PoolableObjectFactory" null="true"/>

</object>

<object class="org.apache.commons.pool.KeyedObjectPoolFactory" null="true"/>

<string null="true"/>

<boolean value="false"/>

<boolean value="true"/>

</object>

DBCP API 는 웹 어플리케이션의 클래스 패스로부터 jocl 설정 파일을 검색하므로 WEB-INF/classes 디렉터리에 DBCP 커넥션 풀 설정 파일을 위치시켜야 한다.

(3) 커넥션 풀 초기화하기.

public class DBCPInit extends HttpServlet{

public void init(ServletConfig config) throws ServletException{

try{

String drivers = config.getInitParameter( "jdbcdriver" );

StringTokenizer st = new StringTokenizer( drivers, "," );

while( st.hasMoreTokens() ){

String jdbcDriver = st.nextToken();

Class.forName( jdbcDriver );

}

Class.forName("org.apache.commons.dbcp.PoolingDriver" );

}

catch( Exception e ){

throw new ServletException( e );

}

}

}

(4) 커넥션 풀로부터 커넥션 사용하기.

...

String jdbcDriver = "jdbc:apache:commons:dbcp:/pool";  // pool 은 [pool 이름]

conn = DriverManager.getConnection( jdbcDriver );

...

conn.close() 를 호출하면 커넥션이 닫히는 것이 아니라 커넥션 풀로 반환된다.

(5) 커넥션 풀 설정하기.

...

<object class="org.apache.commons.pool.impl.GenericObjectPool">

<object class="org.apache.common.pool.PoolableObjectFactory">

<int value="10" /> <!-- maxActive -->

<byte value="1" /> <!-- whenExhaustedAction -->

<long value="10000" /> <!-- maxWait -->

<int value="10" /> <!--maxIdle -->

<int value="3" /> <!-- minIdle -->

<boolean value="true" /> <!-- testOnBorrow -->

<boolean value="true" /> <!-- testOnReturn -->

<long value="600000" /> <!-- timeBetweenEvictionRunsMillis -->

<int value="5" /> <!-- numTestsPerEvictionRun -->

<long value="360000" /> <!-- minEvictableIdleTimeMillis -->

<boolean value="true" /> <!-- testWhileIdle -->

</object>

...

</object>

maxActive

커넥션 풀이 제공할 최대 커넥션 개수

whenExhaustedAction

커넥션 풀에서 가져올 수 있는 커넥션이 없을 때 어떻게 동작할지를 지정한다. 1일 경우 maxWait 속성에서 지정한 시간만큼 커넥션을 구할때까지 기다리며, 0일 경우 에러를 발생시킨다. 2일 경우 일시적으로 커넥션을 생성해서 사용한다.

maxWait

whenExhaustedAction 속성 값이 1일 때 사용되는 대기 시간, 단위는 milliSec 이며, 0보다 작을 경우 무한히 대기한다.

maxIdle

 사용되지 않고 풀에 저장될 수 있는 최대 커넥션 개수, 음수일 경우 제한이 없다.

minIdle

사용되지 않고 풀에 저장될 수 있는 최소 커넥션 개수.

testOnBorrow

true일 경우 커넥션 풀에서 커넥션을 가져올 때 커넥션이 유효한지 여부를 검사한다.

testOnReturn   

true일 경우 커넥션 풀에 커넥션을 반환할 때 커넥션이 유효한지의 여부를 검사한다.

timeBetweenEvictionRunsMillis

사용되지 않은 커넥션을 추출하는 쓰레드의 실행 주기를 지정한다. 양수가 아닐 경우 실행되지 않는다. 단위는 MilliSec.

numTestsPerEvictionRun

사용되지 않는 커넥션을 몇 개 검사할지 지정한다.

minEvictableIdleTimeMillis

사용되지 않는 커넥션을 추출할 때 이 속성에서 지정한 시간 이상 비활성화 상태인 커넥션만 추출한다. 양수가 아닌 경우 비활성화된 시간으로 풀에서 제거되지 않는다. 시간 단위는 Millisec.

testWhileIdle

true 일 경우 비활성화 커넥션을 추출할 떄 커넥션이 유효한지의 여부를 검사해서 유효하지 않은 커넥션은 풀에서 제거한다.

maxActive : 사이트의 최대 커넥션 사용량을 기준으로 지정.

minIdle : 시스템 사용이 가장 적은 시간을 기준으로 설정해주는 것이 좋다.

timeBetweenEvictionRunsMillis : 보통 10~20분 단위가 좋다. ( 시간대에 따라 적절한 값이 다르다. )

testWhileIdle : 활성화 시키는 것이 좋다.