DBCP(DataBase Connection Pool)의 사용 - Java Application

[01] DBCP(DataBase Connection Pool)의 사용
- 다운로드:
http://jakarta.apache.org

- DBCP: DBCP 구현 Liarary
http://jakarta.apache.org/site/downloads/downloads_commons-dbcp.cgi
commons-dbcp-1.2.1.jar

- Commons Collections: Collection Framework 구현
http://jakarta.apache.org/site/downloads/downloads_commons-collections.cgi
commons-collections-3.1.jar

- Commons Pool: 객체 버퍼링 라이브러리 구현
http://jakarta.apache.org/site/downloads/downloads_commons-pool.cgi
commons-pool-1.2.jar

1. DBCP구현

>>>>> DBCPConnectionMgr.java

package phonecalc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.impl.GenericObjectPool;

public class DBCPConnectionMgr {
String jdbcDriver="oracle.jdbc.driver.OracleDriver";
String jdbcURL="jdbc:oracle:thin:@172.16.11.1:1521:ora10g2";
String user="ejb2030_04_1";
String password = "oracle";

/**
* 생성자
*
*/
public DBCPConnectionMgr() {
super();
try{
setupDriver(jdbcDriver, jdbcURL, user, password);
}catch(Exception ex){
ex.printStackTrace();
}
}

/**
* Connection을 리턴함
* @return
*/
public Connection getConnection(){
Connection con = null;
try {
con = DriverManager.getConnection("jdbc:apache:commons:dbcp:ora10g2");
} catch(SQLException ex) {
ex.printStackTrace();
} finally {
}
return con;
}

/**
* Connection 설정
* @param jdbcDriver
* @param jdbcURL
* @param user
* @param password
* @throws Exception
*/
public void setupDriver(String jdbcDriver,
String jdbcURL,
String user,
String password) throws Exception {
// JDBC 드라이버 로딩
Class.forName(jdbcDriver);

// Connection Pool 생성
GenericObjectPool connectionPool = new GenericObjectPool(null);
connectionPool.setMaxActive(20); //최대 20개 접속 지원
connectionPool.setMaxIdle(5); //5개 대기중 설정

// 실제 DB와의 커넥션을 연결해주는 팩토리 생성
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
jdbcURL, // JDBC URL
user, // 사용자
password);

// Connection Pool이 PoolableConnection 객체를 생성할 때 사용할
// PoolableConnectionFactory 생성
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
connectionFactory,
connectionPool,
null, // statement pool
null, // 커넥션 테스트 쿼리: 커넥션이 유효한지 테스트할 때 사용되는 쿼리.
false, // read only 여부
true); // auto commit 여부

// Pooling을 위한 JDBC 드라이버 생성 및 등록
PoolingDriver driver = new PoolingDriver();

// JDBC 드라이버에 커넥션 풀 등록
driver.registerPool("ora10g2", connectionPool);
}

public void freeConnection(Connection con, PreparedStatement pstmt, ResultSet rs) {
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
freeConnection(con);
} catch (SQLException e) {
e.printStackTrace();
}
}

public void freeConnection(Connection con, Statement stmt, ResultSet rs) {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
freeConnection(con);
} catch (SQLException e) {
e.printStackTrace();
}
}

public void freeConnection(Connection con, PreparedStatement pstmt) {
try {
if (pstmt != null) pstmt.close();
freeConnection(con);
} catch (SQLException e) {
e.printStackTrace();
}
}

public void freeConnection(Connection con, Statement stmt) {
try {
if (stmt != null) stmt.close();
freeConnection(con);
} catch (SQLException e) {
e.printStackTrace();
}
}

public void freeConnection(Connection con) {
try {
if (con != null) con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

public void freeConnection(Statement stmt) {
try {
if (stmt != null) stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

public void freeConnection(PreparedStatement pstmt) {
try {
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

public void freeConnection(ResultSet rs) {
try {
if (rs != null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

}

2. DBCP JAVA Test

>>>>> DBCP_Test.java

package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import phonecalc.DBCPConnectionMgr;

public class DBCP_Test {

public static void main(String[] args) {
DBCPConnectionMgr dbcp = new DBCPConnectionMgr();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql="SELECT count(*) as cnt FROM tab";

try{
con = dbcp.getConnection();
pstmt = con.prepareStatement(sql);

rs = pstmt.executeQuery();
if (rs.next()){
System.out.println(rs.getInt("cnt"));
}
}catch(Exception e){
System.out.println(e);
}finally{
dbcp.freeConnection(con, pstmt, rs);
}
}
}