[JSP] Connection Pool

웹 프로그램은 실질적으로 데이터베이스에 연결하고, 질의를 던지고, 결과를 받아오는 부분에서 많은 시간을 소요하게 된다. 많은 시간을 소요한 다는 것은 서버 쪽에 많은 부하를 안겨주는 사실을 뜻하기도 한다. Connection Pool을 사용하면 보다 빠르고 부하가 적게 걸리도록 효율적으로 데이터베이스에 연동할 수 있다.

 

ConnectionPool을 사용한 데이터베이스 연결

DBConnectionMgr.java

/**

* Copyright(c) 2001 iSavvix Corporation (http://www.isavvix.com/)

*

* All rights reserved

*

* Permission to use, copy, modify and distribute this material for

* any purpose and without fee is hereby granted, provided that the

* above copyright notice and this permission notice appear in all

* copies, and that the name of iSavvix Corporation not be used in

* advertising or publicity pertaining to this material without the

* specific, prior written permission of an authorized representative of

* iSavvix Corporation.

*

* ISAVVIX CORPORATION MAKES NO REPRESENTATIONS AND EXTENDS NO WARRANTIES,

* EXPRESS OR IMPLIED, WITH RESPECT TO THE SOFTWARE, INCLUDING, BUT

* NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND

* FITNESS FOR ANY PARTICULAR PURPOSE, AND THE WARRANTY AGAINST

* INFRINGEMENT OF PATENTS OR OTHER INTELLECTUAL PROPERTY RIGHTS. THE

* SOFTWARE IS PROVIDED "AS IS", AND IN NO EVENT SHALL ISAVVIX CORPORATION OR

* ANY OF ITS AFFILIATES BE LIABLE FOR ANY DAMAGES, INCLUDING ANY

* LOST PROFITS OR OTHER INCIDENTAL OR CONSEQUENTIAL DAMAGES RELATING

* TO THE SOFTWARE.

*

*/

package ch11;

 

import java.sql.*;

import java.util.Properties;

import java.util.Vector;

 

/**

* Manages a java.sql.Connection pool.

*

* @author Anil Hemrajani

*/

public class DBConnectionMgr {

private Vector connections = new Vector(10);

private String _driver = "com.mysql.jdbc.Driver",

_url = "jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8",

_user = "root",

_password = "1234";

private boolean _traceOn = false;

private boolean initialized = false;

private int _openConnections = 10;

private static DBConnectionMgr instance = null;

 

public DBConnectionMgr() {

}

 

/** Use this method to set the maximum number of open connections before

unused connections are closed.

*/

 

public static DBConnectionMgr getInstance() {

if (instance == null) {

synchronized (DBConnectionMgr.class) {

if (instance == null) {

instance = new DBConnectionMgr();

}

}

}

return instance;

}

 

public void setOpenConnectionCount(int count) {

_openConnections = count;

}

 

public void setEnableTrace(boolean enable) {

_traceOn = enable;

}

 

/** Returns a Vector of java.sql.Connection objects */

public Vector getConnectionList() {

return connections;

}

 

/** Opens specified "count" of connections and adds them to the existing pool */

public synchronized void setInitOpenConnections(int count)

throws SQLException {

Connection c = null;

ConnectionObject co = null;

 

for (int i = 0; i < count; i++) {

c = createConnection();

co = new ConnectionObject(c, false);

connections.addElement(co);

trace("ConnectionPoolManager: Adding new DB connection to pool (" + connections.size() + ")");

}

}

 

/** Returns a count of open connections */

public int getConnectionCount() {

return connections.size();

}

 

/** Returns an unused existing or new connection. */

public synchronized Connection getConnection()

throws Exception {

if (!initialized) {

Class c = Class.forName(_driver);

DriverManager.registerDriver((Driver) c.newInstance());

initialized = true;

}

Connection c = null;

ConnectionObject co = null;

boolean badConnection = false;

 

for (int i = 0; i < connections.size(); i++) {

co = (ConnectionObject) connections.get(i);

// If connection is not in use, test to ensure it's still valid!

if (!co.inUse) {

try {

badConnection = co.connection.isClosed();

if (!badConnection)

badConnection = (co.connection.getWarnings() != null);

} catch (Exception e) {

badConnection = true;

e.printStackTrace();

}

// Connection is bad, remove from pool

if (badConnection) {

connections.removeElementAt(i);

trace("ConnectionPoolManager: Remove disconnected DB connection #" + i);

continue;

}

c = co.connection;

co.inUse = true;

trace("ConnectionPoolManager: Using existing DB connection #" + (i + 1));

break;

}

}

 

if (c == null) {

c = createConnection();

co = new ConnectionObject(c, true);

connections.addElement(co);

trace("ConnectionPoolManager: Creating new DB connection #" + connections.size());

}

return c;

}

 

/** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */

public synchronized void freeConnection(Connection c) {

if (c == null)

return;

 

ConnectionObject co = null;

 

for (int i = 0; i < connections.size(); i++) {

co = (ConnectionObject) connections.get(i);

if (c == co.connection) {

co.inUse = false;

break;

}

}

 

for (int i = 0; i < connections.size(); i++) {

co = (ConnectionObject) connections.get(i);

if ((i + 1) > _openConnections && !co.inUse)

removeConnection(co.connection);

}

}

 

public void freeConnection(Connection c, PreparedStatement p, ResultSet r) {

try {

if (r != null) r.close();

if (p != null) p.close();

freeConnection(c);

} catch (SQLException e) {

e.printStackTrace();

}

}

 

public void freeConnection(Connection c, Statement s, ResultSet r) {

try {

if (r != null) r.close();

if (s != null) s.close();

freeConnection(c);

} catch (SQLException e) {

e.printStackTrace();

}

}

 

public void freeConnection(Connection c, PreparedStatement p) {

try {

if (p != null) p.close();

freeConnection(c);

} catch (SQLException e) {

e.printStackTrace();

}

}

 

public void freeConnection(Connection c, Statement s) {

try {

if (s != null) s.close();

freeConnection(c);

} catch (SQLException e) {

e.printStackTrace();

}

}

 

/** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */

public synchronized void removeConnection(Connection c) {

if (c == null)

return;

 

ConnectionObject co = null;

for (int i = 0; i < connections.size(); i++) {

co = (ConnectionObject) connections.get(i);

if (c == co.connection) {

try {

c.close();

connections.removeElementAt(i);

trace("Removed " + c.toString());

} catch (Exception e) {

e.printStackTrace();

}

break;

}

}

}

 

private Connection createConnection()

throws SQLException {

Connection con = null;

 

try {

if (_user == null)

_user = "";

if (_password == null)

_password = "";

 

Properties props = new Properties();

props.put("user", _user);

props.put("password", _password);

 

con = DriverManager.getConnection(_url, props);

} catch (Throwable t) {

throw new SQLException(t.getMessage());

}

return con;

}

 

/** Closes all connections and clears out the connection pool */

public void releaseFreeConnections() {

trace("ConnectionPoolManager.releaseFreeConnections()");

 

Connection c = null;

ConnectionObject co = null;

 

for (int i = 0; i < connections.size(); i++) {

co = (ConnectionObject) connections.get(i);

if (!co.inUse)

removeConnection(co.connection);

}

}

 

/** Closes all connections and clears out the connection pool */

public void finalize() {

trace("ConnectionPoolManager.finalize()");

 

Connection c = null;

ConnectionObject co = null;

 

for (int i = 0; i < connections.size(); i++) {

co = (ConnectionObject) connections.get(i);

try {

co.connection.close();

} catch (Exception e) {

e.printStackTrace();

}

co = null;

}

connections.removeAllElements();

}

 

private void trace(String s) {

if (_traceOn)

System.err.println(s);

}

}

 

class ConnectionObject {

public java.sql.Connection connection = null;

public boolean inUse = false;

 

public ConnectionObject(Connection c, boolean useFlag) {

connection = c;

inUse = useFlag;

}

}

 

usingJDBCPooljsp.jsp

<%@ page contentType="text/html;charset=EUC-KR"  import="java.sql.*, ch11.*" %>
<%
   DBConnectionMgr pool = DBConnectionMgr.getInstance();
 
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;
   
	String id = "", 
			   pwd = "", 
			   name = "", 
			   num1 = "", 
			   num2 = "", 
			   email = "", 
		       phone = "", 
		       zipcode = "", 
		       address = "", 
		       job = "";
	int counter = 0;
   try{
	  conn = pool.getConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery("select * from tblRegister");
%>
<html>
<head>
<title>JSP에서 데이터베이스 연동</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#FFFFCC">
<h2>JSP 스크립틀릿에서 Connection Pool을 이용한 데이터베이스 연동 예제</h2>
<br/>
<h3>회원정보</h3>
<table bordercolor="#0000ff" border="1">
<tr>
   <td><strong>ID</strong></td>
   <td><strong>PWD</strong></td>
   <td><strong>NAME</strong></td>
   <td><strong>NUM1</strong></td>
   <td><strong>NUM2</strong></td>
   <td><strong>EMAIL</strong></td>
   <td><strong>PHONE</strong></td>
   <td><strong>ZIPCODE/ADDRESS</strong></td>
   <td><strong>JOB</strong></td>	
</tr>
<%
      if(rs!=null){
         while(rs.next()){
            id = rs.getString("id");
            pwd = rs.getString("pwd");
            name = rs.getString("name");
            num1 = rs.getString("num1");
            num2 = rs.getString("num2");
            email = rs.getString("email");
            phone = rs.getString("phone");
            zipcode = rs.getString("zipcode");
            address = rs.getString("address");
            job = rs.getString("job");
%>
<tr>
	<td><%= id %></td>
	<td><%= pwd %></td>
	<td><%= name %></td>
	<td><%= num1 %></td>
	<td><%= num2 %></td>
	<td><%= email %></td>
	<td><%= phone %></td>
	<td><%= zipcode %>/<%= address %></td>
	<td><%= job %></td>
	</tr>
<%
            counter++;
         }//end while
      }//end if
%>
</table><br/>
total records : <%= counter %> 
<%
   }catch(SQLException sqlException){
      System.out.println("sql exception");
   }catch(Exception exception){
      System.out.println("exception");
   }finally{
      if( rs != null ) 
         try{ rs.close(); } 
         catch(SQLException ex) {}
      if( stmt != null ) 
         try { stmt.close(); } 
         catch(SQLException ex) {}
      if( conn != null ) 
         try{ pool.freeConnection(conn); } 
         catch(Exception ex){}
   }
%>

 

출력 결과

 

ConnectionPool과 Bean을 이용한 데이터베이스 연결

RegisterMgrPool.java

package ch11;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

public class RegisterMgrPool {
	
	private DBConnectionMgr pool = null;
	
	public RegisterMgrPool() {
 	 try{
 	   pool = DBConnectionMgr.getInstance();
 	   }catch(Exception e){
 	      System.out.println("Error : 커넥션 얻어오기 실패");
 	   }
     }
 
    public Vector<RegisterBean> getRegisterList() {
	   Connection conn = null;
	   Statement stmt = null;
	   ResultSet rs = null;
	   Vector<RegisterBean> vlist = new Vector<RegisterBean>();	   
       try {
          conn = pool.getConnection();
          String strQuery = "select * from tblRegister";
          stmt = conn.createStatement();
          rs = stmt.executeQuery(strQuery);
		  while (rs.next()) {
             RegisterBean bean = new RegisterBean();
             bean.setId (rs.getString("id"));
			 bean.setPwd (rs.getString("pwd"));
 			 bean.setName (rs.getString("name"));
 			 bean.setNum1 (rs.getString("num1"));
 			 bean.setNum2 (rs.getString("num2"));
 			 bean.setEmail (rs.getString("email"));
 			 bean.setPhone (rs.getString("phone"));
 			 bean.setZipcode (rs.getString("zipcode"));
 			 bean.setAddress (rs.getString("address"));
 			 bean.setJob (rs.getString("job"));
 			vlist.addElement(bean);
          }
       } catch (Exception ex) {
          System.out.println("Exception" + ex);
       } finally {
	      pool.freeConnection(conn);
       }
       return vlist;
    }
 }

 

usingJDBCPoolBean.jsp

<%@ page contentType="text/html; charset=EUC-KR"%>
<%@ page import="java.util.*, ch11.*"%>
<jsp:useBean id="regMgr" class="ch11.RegisterMgrPool" />
<html>
<head> 
<title>JSP에서 데이터베이스 연동</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#FFFFCC">
<h2>Bean과 커넥션 풀을 사용한 데이터베이스 연동 예제</h2><br/>
<h3>회원정보</h3>
<table bordercolor="#0000ff" border="1">
<tr>
   <td><strong>ID</strong></td>
   <td><strong>PASSWD</strong></td>
   <td><strong>NAME</strong></td>
   <td><strong>NUM1</strong></td>
   <td><strong>NUM2</strong></td>
   <td><strong>EMAIL</strong></td>
   <td><strong>PHONE</strong></td>
   <td><strong>ZIPCODE/ADDRESS</strong></td>
   <td><strong>JOB</strong></td>	
</tr>
<%
	Vector<RegisterBean> vlist = regMgr.getRegisterList();
	int counter = vlist.size();
	for(int i=0; i<vlist.size(); i++){
   		RegisterBean regBean =vlist.get(i);
%>
<tr>
	<td><%=regBean.getId()%></td>
	<td><%=regBean.getPwd()%></td>
	<td><%=regBean.getName()%></td>
	<td><%=regBean.getNum1()%></td>
	<td><%=regBean.getNum2()%></td>
	<td><%=regBean.getEmail()%></td>
	<td><%=regBean.getPhone()%></td>
	<td><%=regBean.getZipcode()%>/<%=regBean.getAddress()%></td>
	<td><%=regBean.getJob()%></td>
</tr>
<%
   }
%>
</table>
<br/>
<br/>
total records : <%= counter %> 
</body>
</html>

 

출력 결과

 

ConnectionPool을 통한 데이터베이스 연결, 그리고 자바빈즈를 사용해서 데이터베이스 관련 코드를 분리시킨 방법은 실무에서 적용하기에 가장 바람직한 형태라고 할 수 있다.