Connection Pool

싱글 커넥션

: 같은 커넥션을 이용하는 DAO들의 작업에 영향을 준다.
하나의 connection 객체(contextListener)를 공유하면 예외발생시 문제의 소지가 있다.
하나에서 rollback 했는데 다른 dao들에게까지 영향을 미칠 수 있다.

해결책은 무엇일까?
-> 쓸 때마다 생성하는 것은 낭비이다.

---

Connection Pool

: 미리 여러개를 만들어놓고 돌아가면서 쓸 수 있도록 하는 방식.
빌려주고 반납하는 방식!
미리 만들어 놓는 것과 필요할 때마다 만드는 것은 속도차이가 많이 난다.
미리 만들어놓은 것이므로 속도 저하가 발생하지 않는다.

DBConnectionPool.java

package spms.util;

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

public class DBConnectionPool {
	final int PRE_POOL_SIZE = 10;
	String url;
	String username;
	String password;
	ArrayList<Connection> connList = new ArrayList<Connection>();
	
	public DBConnectionPool(String driver, String url,
					String username, String password) throws Exception{
		this.url = url;
		this.username = username;
		this.password = password;
		
		Class.forName(driver);
		
		//미리 pool size만큼(10개) 만들어놓기 
		for(int i=0; i<PRE_POOL_SIZE; i++) {
			connList.add(DriverManager.getConnection(url, username, password));
		}
	}
	
	// Connection 객체를 요청하면 대여해줌
	public Connection getConnection() throws Exception{
		// 현재 저장된 여유분이 존재한다면
		if(connList.size() > 0) {
			// 1번째 Connection객체를 꺼낸다
			//remove는 remove된 객체를 반환한다 
			Connection conn = connList.remove(0);
			
//			DBMS는 일정시간 아무 요청이 없는 경우 timeout에 의해 연결이 해제된다.
//			그러므로 확인을 해야한다.
			// Connection객체가 DB와 연결되어 있다면
			if(conn.isValid(10)) {
				// 꺼낸 것을 리턴한다
				return conn;
			}
		}
		
		//여유분이 없으면 새로 연결해서 리턴한다 
		return DriverManager.getConnection(url, username, password);
	}
	
	//빌려서 사용하고 다 사용했으면 Connection 객체를 반납한다 
	public void returnConnection(Connection conn) throws Exception{
		if(conn != null && conn.isClosed()==false) {
			connList.add(conn);
		}
	}
	
	//종료시 모든 Connection 객체 닫기
	public void closeAll() {
		for(Connection conn : connList) {
			try {
				conn.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}

MemberDao.java

package spms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import spms.util.DBConnectionPool;
import spms.vo.Member;

public class MemberDao {

	DBConnectionPool connPool;

	public void setDBConnectionPool(DBConnectionPool connPool) {
		this.connPool = connPool;
	}

	public List<Member> selectList() throws Exception {
		Connection connection = null;
		Statement stmt = null;
		ResultSet rs = null;
		final String sqlSelect = "SELECT mno,mname,email,cre_date" + "\r\n" + "FROM members" + "\r\n"
				+ "ORDER BY mno ASC";

		try {
			// 커넥션풀에서 Connection객체를 빌려온다
			connection = connPool.getConnection();

			stmt = connection.createStatement();
			rs = stmt.executeQuery(sqlSelect);

			ArrayList<Member> members = new ArrayList<Member>();

			while (rs.next()) {
				members.add(new Member().setNo(rs.getInt("mno")).setName(rs.getString("mname"))
						.setEmail(rs.getString("email")).setCreatedDate(rs.getDate("cre_date")));
			}

			return members;

		} catch (Exception e) {
			throw e;
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}

			// 다 썼으면 반납하자
			connPool.returnConnection(connection);
		}
	}

	public int insert(Member member) throws Exception {
		Connection connection = null;
		int result = 0;
		PreparedStatement stmt = null;
		final String sqlInsert = "INSERT INTO members(email,pwd," + "\r\n" + "mname,cre_date,mod_date)" + "\r\n"
				+ "VALUES(?, ?, ?, NOW(), NOW())";

		try {
			// 커넥션풀에서 Connection객체를 빌려온다
			connection = connPool.getConnection();

			stmt = connection.prepareStatement(sqlInsert);
			stmt.setString(1, member.getEmail());
			stmt.setString(2, member.getPassword());
			stmt.setString(3, member.getName());
			result = stmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}

			// 다 썼으면 반납하자
			connPool.returnConnection(connection);
		}

		return result;
	}

	public int delete(int no) throws Exception {
		Connection connection = null;
		int result = 0;
		Statement stmt = null;
		final String sqlDelete = "DELETE FROM MEMBERS WHERE MNO=";

		try {
			// 커넥션풀에서 Connection객체를 빌려온다
			connection = connPool.getConnection();

			stmt = connection.createStatement();
			result = stmt.executeUpdate(sqlDelete + no);

		} catch (Exception e) {
			throw e;

		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}

			// 다 썼으면 반납하자
			connPool.returnConnection(connection);
		}

		return result;
	}

	public Member selectOne(int no) throws Exception {
		Connection connection = null;
		Member member = null;
		Statement stmt = null;
		ResultSet rs = null;

		final String sqlSelectOne = "SELECT MNO,EMAIL,MNAME,CRE_DATE FROM MEMBERS" + " WHERE MNO=";

		try {
			// 커넥션풀에서 Connection객체를 빌려온다
			connection = connPool.getConnection();
			stmt = connection.createStatement();
			rs = stmt.executeQuery(sqlSelectOne + no);
			if (rs.next()) {
				member = new Member().setNo(rs.getInt("MNO")).setEmail(rs.getString("EMAIL"))
						.setName(rs.getString("MNAME")).setCreatedDate(rs.getDate("CRE_DATE"));

			} else {
				throw new Exception("해당 번호의 회원을 찾을 수 없습니다.");
			}

		} catch (Exception e) {
			throw e;
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}

			// 다 썼으면 반납하자
			connPool.returnConnection(connection);
		}

		return member;
	}

	public int update(Member member) throws Exception {
		Connection connection = null;
		int result = 0;
		PreparedStatement stmt = null;
		final String sqlUpdate = "UPDATE MEMBERS SET EMAIL=?,MNAME=?,MOD_DATE=now()" + " WHERE MNO=?";
		try {
			// 커넥션풀에서 Connection객체를 빌려온다
			connection = connPool.getConnection();
			stmt = connection.prepareStatement(sqlUpdate);
			stmt.setString(1, member.getEmail());
			stmt.setString(2, member.getName());
			stmt.setInt(3, member.getNo());
			result = stmt.executeUpdate();

		} catch (Exception e) {
			throw e;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}

			// 다 썼으면 반납하자
			connPool.returnConnection(connection);
		}

		return result;
	}

	public Member exist(String email, String password) throws Exception {
		Connection connection = null;
		Member member = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		final String sqlExist = "SELECT MNAME,EMAIL FROM MEMBERS" + " WHERE EMAIL=? AND PWD=?";

		try {
			// 커넥션풀에서 Connection객체를 빌려온다
			connection = connPool.getConnection();
			
			stmt = connection.prepareStatement(sqlExist);
			stmt.setString(1, email);
			stmt.setString(2, password);
			rs = stmt.executeQuery();
			if (rs.next()) {
				member = new Member().setName(rs.getString("MNAME")).setEmail(rs.getString("EMAIL"));
			} else {
				return null;
			}
		} catch (Exception e) {
			throw e;

		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}

			// 다 썼으면 반납하자
			connPool.returnConnection(connection);
		}

		return member;
	}
}

ContextLoaderListener

package spms.listeners;

import java.sql.Connection;
import java.sql.DriverManager;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import spms.dao.MemberDao;
import spms.util.DBConnectionPool;

public class ContextLoaderListener implements ServletContextListener {	
	DBConnectionPool connPool;
	
	@Override
	public void contextDestroyed(ServletContextEvent sce) {
		try {
			System.out.println("contextDestroyed");
			
			//웹 어플리케이션이 종료시 모든 Connection 객체를 닫는다 
			connPool.closeAll();
		}catch(Exception e) {
			e.printStackTrace();
		}

	}

	@Override
	public void contextInitialized(ServletContextEvent sce) {
		try {
			System.out.println("contextInitialized");
			ServletContext sc = sce.getServletContext();

			connPool = new DBConnectionPool(
					sc.getInitParameter("driver"),
					sc.getInitParameter("url"),
					sc.getInitParameter("username"),
					sc.getInitParameter("password"));
			
			
			//dao 객체를 만들었고 dao객체 필드영역인 conn에 db를 연결했다.
			MemberDao memberDao = new MemberDao();
			memberDao.setDBConnectionPool(connPool);
			
			//conn객체 대신 memberDao 객체를 공유 
			sc.setAttribute("memberDao", memberDao);
											
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		
		
	}

}

ContextLoaderListener에서 driver, url, id, pwd를 가져와서 memberDao에 심어준다. 서블릿에서는 dao를 활용해서 db에 연결이 가능한 것이다.