[유용한 Database Connection Pool 관련 java 코드 2-연재] Database Connection Pool java WCDBConnectionPool.java

지난번 글에서 database connection에 대한 정보를 저장하는 WCDBConnection 에 대해서 설명을 하였습니다.
java에 Connection class를 직접 사용하지 않고 WCDBConnection class를 만들어 사용하는 이유를 설명하였습니다.
하나의 database에 대해서 database connection들이 여러개일 경우 이들을 관리하는 class가 필요합니다.

WCDBConnectionPool.java

이 class는 하나의 database에 대한 database connection들을 관리합니다.
다시한번 database connection 구조에 대해서 설명하겠습니다.

WCDBConnection.java 는 database Connection object를 저장하는 class입니다.
WCDBConnectionPool.java 는 하나의 database에서 여러개의 connection을 사용할 수 있도록 connection 들을 관리하는 pool입니다.
WCDBConnectionManager.java 는 database connection pool 여러개를 관리하는 class입니다.
WCStmt는 WCDBConnectionManager의 getConnection을 호출하여 Connection을 얻고 이를 사용하여 SQL을 처리하는 class 입니다.

즉 아래와 같은 구조입니다.

WDDBConnectionManager
-> WCDBConnectionPool : database AAA
    -> WCDBConnection -> Connection
    -> WCDBConnection -> Connection
-> WCDBConnectionPool : database BBB
    -> WCDBConnection -> Connection
    -> WCDBConnection -> Connection
WCStmt

WCDBConnectionPool은 여러개의 Database Connection들을 관리합니다.

database에 연결하기 위해서는 JDBC driver를 loading해야 합니다.
driver는 JDBC class 이름입니다.
아래의 소스는 환경설정 파일을 읽어서 JDBC driver를 loading하는 방법과
API를 사용하여 JDBC driver를 loading하는 방법이 있습니다.
일단 driver loading 부분은 WCDBConnectionManager에서 다루도록 하겠습니다.
아래의 driver 정보와 url 정보는 참고하세요.

url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
drivers=org.gjt.mm.mysql.Driver
dsn=WDLDatabasePool
user=root
password=
maxconn=50
charset=utf8
databasetype=mysql
timeout=1000000

WCDBConnectionPool 에는 다음과 같은 변수가 있습니다.
m_vConnections : database connection이 설정된 모든 WCDBConnection를 저장
m_vFreeConnections : m_vConnections에 저장된 WCDBConnection 중에서 현재 free 상태인 connection

getConnection()을 호출하여 database connection을 얻어옵니다.
-> getConnectionEx()
  -> m_vFreeConnections 에서 connection을 하나 얻어서 connection이 valid한지를 점검
   -> closed된 connection인지 점검
   -> timeout 여부 점검
-> newConnection() : free인 connection이 없을 경우 new connection을 생성
  -> WCDBConnection object를 생성하고 Connection을 생성, m_vConnections에 새로 생성한 connection을 삽입

freeConnection(WCDBConnection oDBConn)
-> WCDBConnection의 상태를 free로 설정
-> m_vFreeConnections에 WCDBConnection object를 삽입하여 free connection 목록에 삽입하여
이후 다시 사용할 수 있도록함

실제 database connection을 얻어오고 free하는 전 과정은 WCDBConnectionManager.java를
설명하면서 글을 올리겠습니다.

아래의 code를 mysql, mssql, oracle 등 여러 database에서 test를 하였고
또한 많은 site에서 현재 사용하고 있는 소스입니다. 도움이 되었으면 합니다.

아래의 소스가 완벽히 돌기 위해서는 WCDBConnection,WCDBConnectionManager,WCDBStmt,WCProperties
,WCVector,WCLog,WCSystem 등의 source코드가 필요합니다.
이러한 소스코드도 모두 차차 글을 올리며 설명하겠습니다.
미리 보시려면 Web Development Library(www.webdevlib.net)에서 download 받아 사용하실 수 있습니다.
다음번 글에는 WCDBConnectionManager class의 소스와 설명을 올리겠습니다.

- 소스코드 WCDBConnectionPool.java 시작
package wdl;
import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Date;
public class WCDBConnectionPool
{
  private Vector m_vFreeConnections = new Vector();
  private Vector m_vConnections = new Vector();
  public int m_nMaxConn = 0;
  public String m_sName;
  public String m_sPassword;
  public String m_sUrl;
  public String m_sUser;
  public String m_sCharset;
  public String m_sDatabaseType;
  public String m_sDriverName;
  public int m_nSeqNo = 0;
  public long m_nTimeout = 0;
  public Driver m_oDriver = null;
  public int m_nLogInit = 0;
  public boolean m_bLog = true;
  public int getMaxConn()
  {
   return m_nMaxConn;
  }
  public void setMaxConn(int nMaxConn)
  {
   m_nMaxConn = nMaxConn;
  }
  public WCDBConnectionPool()
  {
  }
  public void setName(String sName)
  {
   m_sName = new String(sName);
  }
  public void init(String sUrl, String sUser, String sPassword,int maxConn)
  {
   m_oDriver = null;
   m_sUrl = new String(sUrl);
   m_sUser = new String(sUser);
   m_sPassword = new String(sPassword);
   m_nMaxConn = maxConn;
   m_sCharset = new String("");
   m_nSeqNo = 0;
   m_sDriverName = new String("");
  }
  public void setDriver(Driver oDriver)
  {
   m_oDriver = oDriver;
  }
  public void setTimeout(long nTimeout)
  {
   m_nTimeout = nTimeout;
  }
  public Driver getDriver()
  {
   return m_oDriver;
  }
  public void setDriverName(String sDriverName)
  {
   m_sDriverName = sDriverName;
  }
  public String getDriverName()
  {
   return m_sDriverName;
  }
  public String getName()
  {
   return m_sName;
  }
  public String getUser()
  {
   return m_sUser;
  }
  public String getCharset()
  {
   return m_sCharset;
  }
  public String getDatabaseType()
  {
   return m_sDatabaseType;
  }
  public String getPassword()
  {
   return m_sPassword;
  }
  public String getUrl()
  {
   return m_sUrl;
  }
  public void setCharset(String sCharset)
  {
   m_sCharset = sCharset;
  }
  public void setDatabaseType(String sDatabaseType)
  {
   m_sDatabaseType = sDatabaseType;
  }
  public synchronized void freeConnection(WCDBConnection oDBConn)
  {
   if (oDBConn == null)
    return;
   try
   {
    oDBConn.setFree(true);
    m_vFreeConnections.addElement(oDBConn);
   }
   catch (Exception ex)
   {
    WCLog.getInstance().printLog("exception at WCDBConnectionPool.freeConnection "+ex.toString());
   }
   {
    this.log("WCDBConnectionPool.java freeConnection : succ to free a connection "
     +"dsn="+oDBConn.getName()
     +",id="+oDBConn.getID()
     +",connection count="+getConnectionCount()
     +",free connection count="+getFreeConnectionCount()
     );
    notifyAll();
   }
  }
  public synchronized void freeConnection2(Connection oConn)
  {
   if (oConn == null)
    return;
   try
   {
    WCDBConnection oDBConn = getConnection(oConn);
    if (oDBConn != null)
    {
     freeConnection(oDBConn);
    }
   }
   catch (Exception ex)
   {
    WCLog.getInstance().printLog("exception at WCDBConnectionPool.freeConnection2 "+ex.toString());
   }
   notifyAll();
  }
  public synchronized int getConnectionCount()
  {
   if (m_vConnections == null)
    return 0;
   int nCount = m_vConnections.size();
   return nCount;
  }
  public synchronized int getFreeConnectionCount()
  {
   int nCount = m_vFreeConnections.size();
   return nCount;
  }
  public Vector getConnections()
  {
   return m_vConnections;
  }
  public synchronized WCDBConnection getConnection(Connection oConn)
  {
   Enumeration allConnections = m_vConnections.elements();
   while (allConnections.hasMoreElements())
   {
    WCDBConnection oDBConn = (WCDBConnection) allConnections.nextElement();
    if (oDBConn.getConnection() == oConn)
    {
     return oDBConn;
    }
   }
   return null;
  }
  public synchronized WCDBConnection getConnection()
  {
   WCDBConnection oDBConn = null;
   while (true)
   {
    oDBConn = getConnectionEx();
    if (oDBConn == null)
    {
     if (m_nMaxConn == 0 || getConnectionCount() < m_nMaxConn)
     {
      oDBConn = newConnection();
      break;
     }
     try
     {
      long timeout = 100;
      wait(timeout);
     }
     catch (InterruptedException ex)
     {
      WCLog.getInstance().printLog("exception at WCDBConnectionPool.getConnection "+ex.toString());
     }
    }
    else
    {
     break;
    }
   }
   if (oDBConn == null)
   {
   }
   return oDBConn;
  }
  private WCDBConnection getConnectionEx()
  {
   WCDBConnection oDBConn = null;
   if (m_vFreeConnections.size() > 0)
   {
    oDBConn = (WCDBConnection) m_vFreeConnections.firstElement();
    m_vFreeConnections.removeElementAt(0);
    try
    {
     if (oDBConn.getConnection().isClosed())
     {
      this.log("WCDBConnectionPool.java connection is broken,dsn="+m_sName);
      m_vConnections.removeElement(oDBConn);
      oDBConn.close();
      oDBConn = getConnection();
     }
     else if (oDBConn.isTimeout(m_nTimeout))
     {
      this.log("WCDBConnectionPool.java connection is timeout,dsn="+m_sName+",timeout="+m_nTimeout);
      m_vConnections.removeElement(oDBConn);
      oDBConn.close();
      oDBConn = getConnection();
     }
    }
    catch (Exception ex)
    {
     WCLog.getInstance().printLog("WCDBConnectionPool.java release : Removed bad WCDBConnection from " + m_sName);
     m_vConnections.removeElement(oDBConn);
     oDBConn.close();
     oDBConn = getConnection();
    }
    if (oDBConn != null)
    {
     this.log("WCDBConnectionPool.java getConnectionEx succ to get a free connection dsn="+m_sName);
    }
   }
   if (oDBConn != null)
   {
    oDBConn.setFree(false);
   }
   return oDBConn;
  }
  public synchronized void log(String msg)
  {
   if (m_nLogInit <= 0)
   {
    String sVal = getEnvValue("wdl.WCDBConnectionPool.log");
    if (WCString.compareNoCase("true",sVal) == 0)
    {
     m_bLog = true;
    }
    else if (WCString.compareNoCase("false",sVal) == 0)
    {
     m_bLog = false;
    }
    m_nLogInit = 1;
   }
   if (m_bLog == true)
   {
    WCLog.getInstance().printLog(msg);
   }
  }
  public synchronized WCDBConnection getConnection(long timeout)
  {
   long startTime = new Date().getTime();
   WCDBConnection con = null;
   while ((con = getConnection()) == null)
   {
    try
    {
     wait(timeout);
    }
    catch (InterruptedException ex)
    {
     WCLog.getInstance().printLog("exception at WCDBConnectionPool.getConnection "+ex.toString());
    }
    if ((new Date().getTime() - startTime) >= timeout)
    {
     return null;
    }
   }
   return con;
  }
  public synchronized void release()
  {
   this.log("WCDBConnectionPool.java release : closing the connection pool : " + getName());
   Enumeration allConnections = m_vConnections.elements();
   while (allConnections.hasMoreElements())
   {
    WCDBConnection con = (WCDBConnection) allConnections.nextElement();
    try
    {
     con.close();
     this.log("WCDBConnectionPool.java release : Closed WCDBConnection for pool " + m_sName);
    }
    catch (Exception ex)
    {
     WCLog.getInstance().printLog("WCDBConnectionPool.java release : Can't close WCDBConnection for pool " + m_sName);
    }
   }
   m_vConnections.removeAllElements();
   Driver oDriver = getDriver();
   String sDriverName = "";
   if (oDriver != null)
   {
    sDriverName = oDriver.getClass().getName();
    try
    {
     DriverManager.deregisterDriver(oDriver);
     this.log("WCDBConnectionPool.java release : succ to deregister JDBC driver=" + sDriverName);
    }
    catch (SQLException e)
    {
     WCLog.getInstance().printLog("WCDBConnectionPool.java release : fail to deregister JDBC driver=" + sDriverName);
    }
   }
  }
  public WCDBConnection newConnection()
  {
   Connection con = null;
   WCDBConnection oConn = null;
   try
   {
    if (WCString.isEmpty(m_sUser))
    {
     con = DriverManager.getConnection(m_sUrl);
    }
    else
    {
     con = DriverManager.getConnection(m_sUrl, m_sUser, m_sPassword);
    }
    oConn = new WCDBConnection(this);
    m_nSeqNo ++;
    oConn.setID(m_nSeqNo);
    oConn.setConnection(con);
    m_vConnections.addElement(oConn);
   }
   catch (SQLException e)
   {
    WCLog.getInstance().printLog("WCDBConnectionPool.java newConnection : fail to create a new database connection for url="+m_sUrl+",drivername="+m_sDriverName+",userid="+m_sUser+",password="+m_sPassword+",dsn=" + m_sName +",error="+e.toString());
    return null;
   }
   this.log("WCDBConnectionPool.java newConnection : succ to create a new connection for dsn="+m_sName
    +",connection count="+getConnectionCount()
    +",free connection count="+getFreeConnectionCount());
   return oConn;
  }
  public static String getEnvValue(String sProp)
  {
   WCEnv env = WCEnv.getInstance();
   if (env != null)
   {
    WCProperties rcd = env.getProperties();
    if (rcd != null)
    {
     String sVal = rcd.getStrValue(sProp);
     return sVal;
    }
   }
   return "";
  }
  public synchronized Driver registerDriver(String sDriverName)
  {
   String sPoolName = getName();
   try
   {
    WCProperties rEnv = WCEnv.getInstance().getProperties();
    if (WCString.parseBoolean(rEnv.getStrValue("wdl.init.verbose")) == true)
    {
     WCLog.getInstance().printLog("WCDBConnectionPool.java registerDriver : try to register JDBC driver="+sDriverName);
    }
    m_oDriver = (Driver)Class.forName(sDriverName).newInstance();
    DriverManager.registerDriver(m_oDriver);
    if (WCString.parseBoolean(rEnv.getStrValue("wdl.init.verbose")) == true)
    {
     WCLog.getInstance().printLog("WCDBConnectionPool.java registerDriver : succ to register JDBC driver=" + sDriverName);
    }
    if (m_oDriver == null)
    {
     if (WCString.parseBoolean(rEnv.getStrValue("wdl.init.verbose")) == true)
     {
      WCLog.getInstance().printLog("WCDBConnectionPool.java registerDriver : fail to register the driver="+sDriverName);
     }
     return null;
    }
    return m_oDriver;
   }
   catch (Exception ex)
   {
    WCProperties rEnv = WCEnv.getInstance().getProperties();
    if (WCString.parseBoolean(rEnv.getStrValue("wdl.init.verbose")) == true)
    {
     WCLog.getInstance().printLog("WCDBConnectionPool.java registerDriver : fail to register the driver="+sDriverName);
    }
   }
   return null;
  }
}
- 소스코드 WCDBConnectionPool.java 끝

출처 : 고급 웹 UI 개발 라이브러리 Web Development Library 소스공개 : http://www.webdevlib.net