[JSP / Servlet] 데이터베이스 커넥션 풀(DBCP)

자카르타 DBCP API를 이용한 커넥션 풀 사용

데이터베이스 커넥션 풀(DBCP : DataBase Connection Pool)

 

□ 커넥션 풀 개요

: 접속 인원이 많은 웹 페이지에서 데이터베이스의 효율성과 속도를 높이기 위해서 사용

■ 데이터베이스 커넥션은 데이터베이스에 한 번 연결하기 위한 작업

- 매번 새로운 DB 연결이 요청될 때마다 Connection 객체를 생성하므로 시스템에 과부하를 일으킴. 
  커넥션 풀을 사용해서 개선

■ 커넥션 객체들을 생성한 후, 커넥션 객체가 필요한 경우 작성한 객체를 할당해 주고,

    사용 후에 다시 커넥션 풀로 회수하는 방법 사용

■ service() 메소드와 커넥션 객체

 

□ 커넥션 풀의 전략

■ service() 메소드(사용자 요청)당 1개씩 할당

■ 커넥션의 수를 제한

■ 커넥션 객체 관리자가 다 쓰면 자원 회수

 

□ 커넥션 풀 구현 방법

 

 

□ 자카르타 DBCP API를 이용한 커넥션 풀

■ 톰캣의 5.0.x 버전부터 DBCP API를 사용한 커넥션 풀 제공

■ 커넥션 풀 사용 방법

- DBCP API관련 jar파일 설치

- DBCP에 관한 정보 설정 - server.xml

- JNDI 리소스 사용 설정 - web.xml

- JSP 페이지에서 커넥션 풀 사용

 

■ DBCP API 관련 jar 파일 배치

http://commons.apache.org/ 에서 다운로드 후 압축 해제

 

- [Collections] : 자카르타(Jakarta) DBCP API가 사용하는 자카르타 Pool API의 jar 파일

→ http://commons.apache.org/proper/commons-collections/download_collections.cgi

- [DBCP] : 자카르타 DBCP API 관련 jar 파일

→ http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi

- [Pool] : Pool API가 사용하는 자카르타 Collection API의 jar 파일

→ http://commons.apache.org/proper/commons-pool/download_pool.cgi

[프로젝트] - [WebContent] - [WEB-INF] - [lib] 에 JAR DBCP API 관련 jar 파일 배치

 

 

■ DBCP에 관한 정보 설정 - server.xml

- 설정할 파일 위치 : 실제 서비스 환경(톰캣홈\conf)

                                      이클립스 가상환경[Project Explorer]뷰의 [Servers] - [Tomcat v8.0 Server…]

※ 사용자 설정에 따라 붉은 글씨를 수정할 것

- Oracle

<Resource name="jdbc/myoracle" auth="Container"               type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver"               url="jdbc:oracle:thin:@127.0.0.1:1521:xe"               username="DBTEST" password="1234" maxTotal="20" maxIdle="10"               maxWaitMillis="-1"/>

 

- MySQL

<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"                maxTotal="100" maxIdle="30" maxWaitMillis="10000"                username="DBTEST" password="1234" driverClassName="com.mysql.jdbc.Driver"                url="jdbc:mysql://localhost:3306/myDB"/>

 

 

■ JNDI 리소스 사용 설정 - web.xml

: server.xml에 저장된 JNDI 리소스를 자바빈(javaBean) 또는 JSP 페이지에서 사용하기 위해 설정

 

- Oracle

<resource-ref>  <description>Oracle Datasource example</description>  <res-ref-name>jdbc/myoracle</res-ref-name>  <res-type>javax.sql.DataSource</res-type>  <res-auth>Container</res-auth> </resource-ref>

 

- MySQL

<resource-ref>       <description>DB Connection</description>       <res-ref-name>jdbc/TestDB</res-ref-name>       <res-type>javax.sql.DataSource</res-type>       <res-auth>Container</res-auth>   </resource-ref>

 

■ JSP 페이지 또는 자바빈(javaBean)에서 커넥션 풀 사용

Context initContext = new InitialContext(); Context envContext  = (Context)initContext.lookup("java:/comp/env"); DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle"); Connection conn = ds.getConnection();

 

 

트랜잭션 처리

: 여러 단계의 작업을 하나로 처리하는 것

​

​□ 하나로 인식된 작업이 모두 성공적으로 끝나면 commit, 문제 발생 시 rollback되어 작업 수행 전단계로 모든과정이

    ​회수됨

 

□ JSP에서 제공하는 트랜잭션 처리 메소드

■ commit() : 트랜잭션의 commit 수행

■ rollback() : 트랜잭션의 rollback 수행

 

□ JSP는 오토커밋(Autocommit)

■ 트랜잭션을 처리할 때는 오토커밋 해제

setAutoCommit(false);

 

□ 사용 예

conn.setAutoCommit(false);   생략     sql = "insert into buy (buy_id, buyer, book_id, book_title, buy_price, buy_count,";     sql += "book_image, buy_date, account, deliveryName, deliveryTel , deliveryAddress)";     pstmt.executeUpdate(); 생략 pstmt = conn.prepareStatement( "delete from cart where buyer=?"); pstmt.setString(1, id); pstmt.executeUpdate(); conn.commit(); conn.setAutoCommit(true);

[출처] [JSP / Servlet] 데이터베이스 커넥션 풀(DBCP)|작성자 Charles