[커넥션풀]

클라이언트에서 다수의 요청이 발생할 경우 데이터베이스에 부하가 발생하게 되는데, 이러한 문제를 해결하기 위해서 커넥션풀(DataBase Connection Pool) 기법을 이용한다.

 

1. WEB-INF/lib 디렉터리에 DBCP API 관련 jar 파일 복사

commons-dbcp2-2.1.jar

0.17MB

commons-logging-1.2.jar

0.06MB

commons-pool2-2.4.1.jar

0.11MB

 

2. 커넥션 풀 초기화 DBCPInit 서블릿 클래스

package jdbc;

import java.sql.DriverManager;
import java.util.Properties;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.apache.commons.dbcp2.ConnectionFactory;
import org.apache.commons.dbcp2.DriverManagerConnectionFactory;

import org.apache.commons.dbcp2.PoolableConnection;
import org.apache.commons.dbcp2.PoolableConnectionFactory;
import org.apache.commons.dbcp2.PoolingDriver;
import org.apache.commons.pool2.ObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;

public class DBCPInit extends HttpServlet{

	@Override
	public void init() throws ServletException{
		loadJDBCDriver();
		initConnectionPool();
	}
	
	private void loadJDBCDriver() {
		try {
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}catch(ClassNotFoundException ex) {
			throw new RuntimeException("fail to load JDBC Driver", ex);
		}
	}
	
	private void initConnectionPool() {
		try {
			String jdbcUrl="jdbc:oracle:thin:@localhost:1521:XE";
			String username = "hr";
			String pw = "hr";
			
			//실제 커넥션을 생성할 ConnectionFactory를 생성한다.
			ConnectionFactory connFactory = 
				new DriverManagerConnectionFactory(jdbcUrl, username, pw);
			
			//PoolableConnectionFactory를 생성해서 커넥션풀로 사용할 poolableConnection 생성
			PoolableConnectionFactory poolableConnFactory =
					new PoolableConnectionFactory(connFactory, null);
			poolableConnFactory.setValidationQuery("select 1");

			//커넥션 풀의 설정 정보 저장
			GenericObjectPoolConfig poolConfig = new GenericObjectPoolConfig();
			poolConfig.setTimeBetweenEvictionRunsMillis(1000L * 60L * 5L); //유휴 커넥션 검사 주기
			poolConfig.setTestWhileIdle(true); //풀에 보관중인 커넥션이 유효한지 검사할지 여부
			poolConfig.setMinIdle(4); //커넥션 최소 개수
			poolConfig.setMaxTotal(50); //커넥션 최대 개수

			//커넥션풀 생성
			GenericObjectPool<PoolableConnection> connectionPool =
					new GenericObjectPool<> (poolableConnFactory, poolConfig);
			poolableConnFactory.setPool(connectionPool);
			
			//커넥션풀을 사용할 JDBC 드라이버를 등록
			Class.forName("org.apache.commons.dbcp2.PoolingDriver");
			PoolingDriver driver = 
					(PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
			
			//커넥션풀 드라이버에 커넥션풀을 등록, 풀이름이 chap14이므로 프로그램에서 사용하는 JDBC URL은 "jdbc:apache:commons:dbcp:chap14"가 된다
			driver.registerPool("chap14", connectionPool);				
			
		} catch(Exception e) {
			throw new RuntimeException(e);
		}

		
	}
	
}

 

3. web.xml 커넥션 풀 초기화 서블릿 설정

웹어플리케이션을 구동할 때 DBCPInit 서블릿 클래스가 자동으로 시작됨.

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee; 
		http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
	version="3.1">

	<servlet>
		<servlet-name>DBCPInit</servlet-name>
		<servlet-class>jdbc.DBCPInit</servlet-class>
		<load-on-startup>1</load-on-startup>
	</servlet>

</web-app>

 

4. 커넥션 풀을 이용해서 커넥션 사용하기

<%

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try{
//풀이름으로 커넥션을 구함
	String jdbcDriver="jdbc:apache:commons:dbcp:chap14";
	String query = "select * from MEMBER2 order by MEMBERID";
	
    conn = DriverManager.getConnection(jdbcDriver);
	stmt = conn.createStatement();
	rs = stmt.executeQuery(query);
	
	
	while(rs.next()){
%>
<tr>
	<td><%= rs.getString("NAME") %></td>
	<td><%= rs.getString("MEMBERID") %></td>
	<td><%= rs.getString("EMAIL") %></td>
</tr>
<%
	}
} catch(SQLException ex){
	out.println(ex.getMessage());
	ex.printStackTrace();
} finally {
	
	if(rs != null) try{rs.close();} catch(SQLException ex){}
	if(stmt != null) try{stmt.close();} catch(SQLException ex){}
	if(conn != null) try{conn.close();} catch(SQLException ex){}
}

%>