6. Servlet - connection pool
[[ 컨넥션 풀 ]]
- DBMS와 접속된 컨넥션들을 여러개 만들어 놓고(컨넥션풀) DB와 접속할때 사용중이지 않은 컨넥션 객체를 얻어와 사용하고 작업이 끝나면 컨넥션객체를 다시 반환한다.
- 성능향상을 위해 사용한다.
- 아파치 dbcp 설정으로 사용하지만 아래는 connection pool을 작성해본 소스임.
[ ConnectionPoolBean.java ]
package test.db;
import java.sql.*;
import java.util.*;
public class ConnectionPoolBean{
String url, usr, pwd;
HashMap<Connection,Boolean> h; //pool장
int increment = 3;
public ConnectionPoolBean() throws ClassNotFoundException, SQLException{
Connection con = null;
try{
Class.forName("oracle.jdbc.OracleDriver");
}catch(ClassNotFoundException cnfe){
System.out.println(cnfe.getMessage());
}
url = "jdbc:oracle:thin:@localhost:1521:XE";
usr = "scott";
pwd = "tiger";
h = new HashMap<Connection,Boolean>();
for(int i=0; i<5; i++)
{
//컨넥션 객체 얻어오기
con = DriverManager.getConnection(url, usr, pwd);
//컨넥션객체를 Map에 담기(사용중이 아닌 상태라는 표시로 false저장)
h.put(con,false);
}
System.out.println("ConnectionPoolBean created ...");
}
//사용중인 아닌 컨넥션 반환
public synchronized Connection getConnection()
throws SQLException{
Connection con = null;
Boolean b = null;
Set<Connection> e = h.keySet();
Iterator<Connection> it=e.iterator();
while(it.hasNext()){
//Map에서 컨넥션 얻어오기
con = it.next();
//상태 얻어오기(사용중:true,사용중이 아님:false)
b = h.get(con);
if(!b){//사용중이지 않으면
//사용중인 상태(true)로 바꾸고
h.put(con,true);
//컨넥션 리턴
return con;
}
}
//컨넥션이 모두 사용중일때 새로운 컨넥션을 3개 얻어와 Map에 저장
for(int i=0; i<increment; i++){
h.put(DriverManager.getConnection(url,usr,pwd), false);
}
return getConnection();
}
public void returnConnection(Connection returnCon)
throws SQLException {
Connection con = null;
Set<Connection> e = h.keySet();
Iterator<Connection> it=e.iterator();
while(it.hasNext()){
con = it.next();
if(con == returnCon){
h.put(con, Boolean.FALSE);
break;
}
}
keepConSu(5);
}
//사용중이지 않은 컨넥션 객체를 su만큼 유지하는 메소드
public void keepConSu(int su) throws SQLException{
Connection con = null;
Boolean b = null;
int count = 0;
Set<Connection> e = h.keySet();
Iterator<Connection> it=e.iterator();
while(it.hasNext()){
con = it.next();
b = h.get(con);
if(!b){//컨넥션이 사용중이 아니면
count++;//사용중이지 않은 컨넥션 갯수 세기
if(count >su){//사용중이지 않은 컨넥션이 5보다 크면
h.remove(con);//Map에서 제거하기
con.close();//db접속 해제하기
}
}
}
}
public void closeAll() throws SQLException{
Connection con = null;
Set<Connection> e = h.keySet();
Iterator<Connection> it=e.iterator();
while(it.hasNext()){
con = it.next();
h.remove(con);
con.close();
}
}
}
커넥션풀 사용 예제>
Connection pool을 사용하여 con객체로 db접속 후 insert, select 처리 하는 예제 (db는 oracle xe11g )
[[ web.xml ]]
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
<display-name>day03_ConnectionPool</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>insert</servlet-name>
<servlet-class>test.servlet.InsertServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>insert</servlet-name>
<url-pattern>/insert.do</url-pattern>
</servlet-mapping>
</web-app>
[[ main.html ]]
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<a href="insert.html">회원추가</a><br/>
<a href="list.do">회원목록</a><br/>
</body>
</html>
[[ ListServlet.java ]]
package test.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import test.db.ConnectionPoolBean;
/**
* Servlet implementation class ListServlet
*/
@WebServlet("/list.do")
public class ListServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
ConnectionPoolBean cp;
public void init() throws ServletException {
ServletContext sc=getServletContext();
cp=(ConnectionPoolBean)sc.getAttribute("cp");
if(cp==null){
try{
cp=new ConnectionPoolBean();
sc.setAttribute("cp", cp);
}catch(SQLException se){
System.out.println(se.getMessage());
}catch(ClassNotFoundException ce){
System.out.println(ce.getMessage());
}
}
}
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=euc-kr");
PrintWriter pw = response.getWriter();
Connection con = null;
PreparedStatement pstmt= null;
ResultSet rs = null;
try{
con=cp.getConnection();
String sql="select * from members";
pstmt=con.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
pw.println(rs.getString(1) + ",");
pw.println(rs.getString(2) + ",");
pw.println(rs.getString(3) + ",");
pw.println(rs.getString(4) + ",");
pw.println(rs.getString(5) + "<br/>");
}
pw.close();
}catch(SQLException se){
System.out.println(se.getMessage());
}finally{
try{
if(rs!=null) rs.close();
if(pstmt!=null) pstmt.close();
if(con!=null) cp.returnConnection(con);
}catch(SQLException se){
System.out.println(se.getMessage());
}
}
}
}
[[ insert.html ]]
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
<style type="text/css">
#regbox{
width:300px;
}
#regbox label{
width:100px;
display: block;
float:left;
}
</style>
</head>
<body>
<form method="post" action="insert.do">
<fieldset id="regbox">
<legend>회원가입</legend>
<label for="id">아이디</label>
<input type="text" name="id"/><br/>
<label for="pwd">비밀번호</label>
<input type="password" name="pwd"/><br/>
<label for="email">이메일</label>
<input type="text" name="email"/><br/>
<label for="phone">전화번호</label>
<input type="text" name="phone"/><br/>
<input type="submit" value="가입"/>
<input type="reset" value="취소"/>
</fieldset>
</form>
</body>
</html>
[[ InsertServlet.java ]]
package test.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import test.db.ConnectionPoolBean;
public class InsertServlet extends HttpServlet{
ConnectionPoolBean cp;
//서블릿이 처음으로 요청될때 호출
//서블릿컨텍스트에서 컨넥션 풀 얻어오기
@Override
public void init() throws ServletException {
//서블릿컨텍스트 참조객체 얻어오기
ServletContext sc=getServletContext();
try{
//서블릿컨텍스트 영역에서 컨넥션풀 얻어오기
cp=(ConnectionPoolBean)sc.getAttribute("cp");
//서블릿컨텍스트 영역에 컨넥션풀이 존재하지 않으면
if(cp==null){
//컨넥션풀 객체 생성
cp=new ConnectionPoolBean();
//서블릿컨텍스트 영역에 컨넥션풀 객체를 저장하기
sc.setAttribute("cp",cp);
}
}catch(ClassNotFoundException ce){
System.out.println(ce.getMessage());
}catch(SQLException se){
System.out.println(se.getMessage());
}
}
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
//전송된 파라미터 얻어오기
req.setCharacterEncoding("euc-kr");
String id=req.getParameter("id");
String pwd=req.getParameter("pwd");
String email=req.getParameter("email");
String phone=req.getParameter("phone");
//파라미터를 db에 저장하기
Connection con=null;
PreparedStatement pstmt=null;
try{
//컨넥션풀에서 컨넥션객체 얻어오기
con=cp.getConnection();
String sql="insert into members values(?,?,?,?,sysdate)";
pstmt=con.prepareStatement(sql);
pstmt.setString(1,id);
pstmt.setString(2,pwd);
pstmt.setString(3,email);
pstmt.setString(4,phone);
pstmt.executeUpdate();
}catch(SQLException se){
System.out.println(se.getMessage());
}finally{
try{
if(pstmt!=null) pstmt.close();
//컨넥션풀에 컨넥션객체 반환하기
if(con!=null) cp.returnConnection(con);
}catch(SQLException se){}
}
//결과 응답하기
resp.setContentType("text/html;charset=euc-kr");
PrintWriter pw=resp.getWriter();
pw.println("회원가입성공!");
pw.close();
}
}