0719 DBCP 커넥션풀

커넥션풀

서버는 동시에 사용할 수 있는 사람의 수라는 개념이 존재한다.
즉 일정 수가 넘어가면 접속을 할 수 없게 된다.
접속을 하면 커넥션 객체가 생성된다.
커넥션 풀이란 커넥션 객체들을 미리 생성하고 한번에 관리하는 걸 말한다.
누군가 접속을 하게 되면 커넥션풀에서 커넥션 객체를 가져와 할당시키는것이다.
접속이 되고 나면 커넥션을 풀에 반환을 한다.

WEB-INF폴더에 jar파일을 3개 추가해주고
commons-collections-3.1.jar
commons-dbcp-1.2.1.jar
commons-pool-1.2.jar

META-INF폴더에 
Context.xml 을 만들어준다.

<?xml version="1.0" encoding="UTF-8"?>
<Context>
	<Resource name="jdbc/OracleDB"
	auth="Container"
	type="javax.sql.DataSource"
	username="sqlid"
	password="sqlpw"
	driverClassName="oracle.jdbc.driver.OracleDriver"
	factory="org.apache.commons.dbcp.BasicDataSourceFactory"
	url="jdbc:oracle:thin:@localhost:1521:orcl"
	maxActive="500"
	maxIdle="100"/> //유지하고 있을 커넥션의 수
</Context>

dbcp.jsp

<%@page import="java.sql.ResultSet"%>
<%@page import="javax.sql.DataSource"%>
<%@page import="javax.naming.InitialContext"%>
<%@page import="javax.naming.Context"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
dbcp.jsp<br>

<% 
	request.setCharacterEncoding("UTF-8");
	
	Connection conn=null;
	PreparedStatement ps=null;
	
	Context initContext = new InitialContext();
    //InitialContext 생성 => 이름으로 객체를 찾을 수 있기 위함
	Context envContext = (Context)initContext.lookup("java:comp/env");
    //톰캣(서버)를 찾는다.
  
	DataSource ds=(DataSource)envContext.lookup("jdbc/OracleDB");
    //xml 파일에 등록했던 이름중 OracleDB를 찾는다.
	conn=ds.getConnection();
	System.out.println("conn:"+conn);
	
	String username=request.getParameter("username");
	if(username !=null){
		int age=Integer.parseInt(request.getParameter("age"));
			
		
		String sql="insert into tdbcp values(?,?)";
		
		ps=conn.prepareStatement(sql);
		ps.setString(1,username);
		ps.setInt(2,age);
		ps.executeUpdate();
	}
%>
<h2>dbcp.jsp</h2>

<form name="myform" method="post" action="dbcp.jsp">
	이름 : <input type="text" name="username"><br><br>
	나이 : <input type="text" name="age"><br><br>
	<input type="submit" value="등록">
</form>

<hr>
<%
	String sql="select * from tdbcp";
	ps= conn.prepareStatement(sql);
	ResultSet rs=ps.executeQuery();
	while(rs.next()){
		out.print(rs.getString("username")+","+rs.getInt("age")+"<br>");
	}
	rs.close();
	ps.close();
	conn.close();
%>