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();
    }
}