20. Connection Pool

20-1. 커넥션 풀이란?

• 데이터 베이스와 통신하는 자원을 효율적으로 관리하기 위한 방법

 

                request                DB Access

브라우저 ------------> 웹서버----------------->DB

                                           1. DB Connection

                                           2. Data handling

                                           3. DB Connection Close

 

                                  ↓

 

                 request                DB Access

브라우저 ------------> 웹서버----------------->DB

                                           1. Connection Pool (rent, return)

• 커넥션을 미리 만들어놓고 필요할 때 마다 빌려서 씀                                           

 

 

 

20-2. 커넥션 풀 설정

 

context.xml에 추가

<Resource
    	auth="Container"
    	driverClassName="oracle.jdbc.driver.OracleDriver"
    	url="jdbc:oracle:thin:@localhost:1521:xe"
    	username="c##scott"
    	password="tiger"
    	name="jdbc/Oracle11g"
    	type="javax.sql.DataSource"
    	maxActive="4"
    	maxWait="10000" />

 

 

20-3. 커넥션 풀 구현

 

BookDAO.java

package com.servlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class BookDAO {

	DataSource dataSource;
	
	/*
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	String id = "scott";
	String pw = "tiger";
	*/
	
	public BookDAO() {
		try {
//			Class.forName(driver);
			Context context = new InitialContext();
			dataSource = (DataSource)context.lookup("java:comp/env/jdbc/Oracle11g");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<BookDTO> select() {
		
		ArrayList<BookDTO> list = new ArrayList<BookDTO>();
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		
		try {
//			con = DriverManager.getConnection(url, id, pw);
			con = dataSource.getConnection();
			String sql = "SELECT * FROM book";
			pstmt = con.prepareStatement(sql);
			res = pstmt.executeQuery();
			while (res.next()) {
				int bookId = res.getInt("book_id");
				String bookName = res.getString("book_name");
				String bookLoc = res.getString("book_loc");
				
				BookDTO bookDTO = new BookDTO(bookId, bookName, bookLoc);
				list.add(bookDTO);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(res != null) res.close();
				if(pstmt != null) pstmt.close();
				if(con != null) con.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		
		return list;
	}
	
}