서블렛 + JDBC 연동시 코딩 고려사항 -제2탄-
[JDBC Connection Pooling]

최근수정일자 : 2001.01.19
최근수정일자 : 2001.03.20(샘플예제추가)
최근수정일자 : 2001.10.22(디버깅을 위한 로직추가)
최근수정일자 : 2001.10.29(Oracle JDBC2.0 샘플추가)
최근수정일자 : 2001.11.08(윤한성님 도움 OracleConnectionCacheImpl 소스수정)
최근수정일자 : 2001.11.09(Trace/Debugging을 위한 장문의 사족을 담)

5. JDBC Connection Pooling 을 왜 사용해야 하는가 ?

 Pooling 이란 용어는 일반적인 용어입니다. Socket Connection Pooling, Thread 
 Pooling, Resource Pooling 등 "어떤 자원을 미리 Pool 에 준비해두고 요청시 Pool에
 있는 자원을 곧바로 꺼내어 제공하는 기능"인 거죠.

 JDBC Connection Pooling 은 JDBC를 이용하여 자바에서 DB연결을 할 때, 미리 Pool에
 물리적인 DB 연결을 일정개수 유지하여 두었다가 어플리케이션에서 요구할 때 곧바로
 제공해주는 기능을 일컫는 용어입니다. JDBC 연결시에, (DB 종류마다, 그리고 JDBC 
 Driver의 타입에 따라 약간씩 다르긴 하지만 ) 대략 200-400 ms 가 소요 됩니다. 기껏
 0.2 초 0.4 초 밖에 안되는 데 무슨 문제냐 라고 반문할수도 있습니다.
 
 하지만, 위 시간은 하나의 연결을 시도할 때 그러하고, 100 - 200개를 동시에 연결을
 시도하면 얘기가 완전히 달라집니다.

 아래는 직접 JDBC 드라이버를 이용하여 연결할 때와 JDBC Connection Pooling 을 사용 
 할 때의 성능 비교 결과입니다.


 ------------------------------------------------------------------
 테스트 환경
 LG-IBM 570E Notebook(CPU:???MHz , MEM:320MB)
 Windows NT 4.0 Service Pack 6 
 IBM WebSphere 3.0.2.1 + e-Fixes
 IBM HTTP Server 1.3.6.2
 IBM UDB DB2 6.1

 아래에 첨부한 파일는 자료를 만들때 사용한 JSP소스입니다. (첨부파일참조)

 [HttpConn.java] 간단한 Stress Test 프로그램


 아래의 수치는 이 문서 이외에는 다른 용도로 사용하시면 안됩니다. 테스트를 저의
 개인 노트북에서 측정한 것이고, 또한 테스트 프로그램 역시 직접 만들어한 것인
 만큼, 공정성이나 수치에 대한 신뢰를 부여할 수는 없습니다. 
 그러나 JDBC Connection Pooling 적용 여부에 따른 상대적인 차이를 설명하기에는 
 충분할 것 같습니다.

 테스트 결과

 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 매번 직접 JDBC Driver 연결하는 경우
 java HttpConn http://localhost/db_jdbc.jsp -c <동시유저수> -n <호출횟수> -s 0

 TOTAL( 1,10)  iteration=10 ,  average=249.40 (ms),   TPS=3.93
 TOTAL(50,10)  iteration=500 , average=9,149.84 (ms), TPS=4.83
 TOTAL(100,10)  iteration=1000 , average=17,550.76 (ms), TPS=5.27
 TOTAL(200,10)  iteration=2000 , average=38,479.03 (ms), TPS=4.89
 TOTAL(300,10)  iteration=3000 , average=56,601.89 (ms), TPS=5.01

 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 DB Connection Pooing 을 사용하는 경우
 java HttpConn http://localhost/db_pool_cache.jsp -c <동시유저수> -n <호출횟수> -s 0

 TOTAL(1,10)  iteration=10 , average=39.00 (ms), TPS=23.26
 TOTAL(1,10)  iteration=10 , average=37.10 (ms), TPS=24.33
 TOTAL(50,10)  iteration=500 , average=767.36 (ms), TPS=45.27
 TOTAL(50,10)  iteration=500 , average=568.76 (ms), TPS=61.26
 TOTAL(50,10)  iteration=500 , average=586.51 (ms), TPS=59.79
 TOTAL(50,10)  iteration=500 , average=463.78 (ms), TPS=67.02
 TOTAL(100,10)  iteration=1000 , average=1,250.07 (ms), TPS=57.32
 TOTAL(100,10)  iteration=1000 , average=1,022.75 (ms), TPS=61.22
 TOTAL(200,10)  iteration=1462 , average=1,875.68 (ms), TPS=61.99
 TOTAL(300,10)  iteration=1824 , average=2,345.42 (ms), TPS=61.51

 NOTE: average:평균수행시간, TPS:초당 처리건수
 ------------------------------------------------------------------

 즉, JDBC Driver 를 이용하여 직접 DB연결을 하는 구조는 기껏 1초에 5개의 요청을
 처리할 수 있는 능력이 있는 반면, DB Connection Pooling 을 사용할 경우는 초당
 60여개의 요청을 처리할 수 있는 것으로 나타났습니다.
 12배의 성능향상을 가져온거죠. (이 수치는 H/W기종과 측정방법, 그리고 어플리케이션에
 따라 다르게 나오니 이 수치자체에 너무 큰 의미를 두진 마세요)


 주의: 흔히 "성능(Performance)"을 나타낼 때, 응답시간을 가지고 얘기하는 경향이
  있습니다. 그러나 응답시간이라는 것은 ActiveUser수가 증가하면 당연히 그에 따라
  느려지게 됩니다. 반면 "단위시간당 처리건수"인 TPS(Transaction Per Second) 혹은
  RPS(Request Per Second)는 ActiveUser를 지속적으로 끌어올려 임계점을 넘어서면,
  특정수치 이상을 올라가지 않습니다. 따라서 성능을 얘기할 땐 평균응답시간이 아니라
  "단위시간당 최대처리건수"를 이야기 하셔야 합니다.
  성능(Performance)의 정의(Definition)은 "단위시간당 최대처리건수"임을 주지하세요.
  성능에 관련한 이론은 아래의 문서를 통해, 함께 연구하시지요.
  [강좌]웹기반시스템하에서의 성능에 대한 이론적 고찰
  http://www.javaservice.net/~java/bbs/read.cgi?m=resource&b=consult&c=r_p&n=1008701211

 PS: IBM WebSphere V3 의 경우, Connection 을 가져올 때, JNDI를 사용하게 되는데
   이때 사용되는 DataSource 객체를 매번 initialContext.lookup() 을 통해 가져오게
   되면, 급격한 성능저하가 일어납니다.(NOTE: V4부터는 내부적으로 cache를 사용하여
   성능이 보다 향상되었습니다)
   DataSource를 매 요청시마다 lookup 할 경우 다음과 같은 결과를 가져왔습니다.

   java HttpConn http://localhost/db_pool.jsp -c <동시유저수> -n <호출횟수> -s 0

   TOTAL(1,10)  iteration=10 , average=80.00 (ms), TPS=11.61
   TOTAL(50,10)  iteration=500 , average=2,468.30 (ms), TPS=16.98
   TOTAL(50,10)  iteration=500 , average=2,010.43 (ms), TPS=18.18
   TOTAL(100,10)  iteration=1000 , average=4,377.24 (ms), TPS=18.16
   TOTAL(200,10)  iteration=1937 , average=8,991.89 (ms), TPS=18.12

   TPS 가 18 이니까 DataSource Cache 를 사용할 때 보다 1/3 성능밖에 나오지 않는 거죠.



6. JDBC Connection Pooling 을 사용하여 코딩할 때 고려사항

 JDBC Connecting Pooling은 JDBC 1.0 스펙상에 언급되어 있지 않았습니다. 그러다보니
 BEA WebLogic, IBM WebSphere, Oracle OAS, Inprise Server, Sun iPlanet 등 어플리케
 이션 서버라고 불리는 제품들마다 그 구현방식이 달랐습니다.
 인터넷에서 돌아다니는 Hans Bergsten 이 만든 DBConnectionManager.java 도 그렇고,
 JDF 에 포함되어 있는 패키지도 그렇고 각자 독특한 방식으로 개발이 되어 있습니다.
 
 JDBC를 이용하여 DB연결하는 대표적인 코딩 예를 들면 다음과 같습니다.
 
 ------------------------------------------------------------------
 [BEA WebLogic Application Server]

    import java.sql.*;

    // Driver loading needed.
    static {
      try {
        Class.forName("weblogic.jdbc.pool.Driver").newInstance();          
      }
      catch (Exception e) { 
        ...
      }
    }

    ......

    Connection conn = null;
    Statement stmt = null;
    try {
      conn = DriverManager.getConnection("jdbc:weblogic:pool:<pool_name>", null);
      stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select ....");
      while(rs.next()){
        .....
      }
      rs.close();
    }
    catch(Exception e){
      .....
    }
    finally {
      if ( stmt != null ) try{stmt.close();}catch(Exception e){}
      if ( conn != null ) try{conn.close();}catch(Exception e){}
    }


 ------------------------------------------------------------------
 IBM WebSphere Application Server 3.0.2.x / 3.5.x

    /* IBM WebSphere 3.0.2.x for JDK 1.1.8 */
    //import java.sql.*;
    //import javax.naming.*;
    //import com.ibm.ejs.dbm.jdbcext.*;
    //import com.ibm.db2.jdbc.app.stdext.javax.sql.*;

    /* IBM WebSphere 3.5.x for JDK 1.2.2 */
    import java.sql.*;
    import javax.sql.*;
    import javax.naming.*;

    // DataSource Cache 사용을 위한 ds 객체 static 초기화
    private static DataSource ds = null;
    static {
      try {
        java.util.Hashtable props = new java.util.Hashtable();
        props.put(Context.INITIAL_CONTEXT_FACTORY, 
                  "com.ibm.ejs.ns.jndi.CNInitialContextFactory");
        Context ctx = null;
        try {
          ctx = new InitialContext(props);
          ds = (DataSource)ctx.lookup("jdbc/<data_source_name>");
        }
        finally {
          if ( ctx != null ) ctx.close();
        }
      }
      catch (Exception e) {
       ....
      }
    }

    .....   

    Connection conn = null;
    Statement stmt = null;
    try {
      conn = ds.getConnection("userid", "password");
      stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select ....");
      while(rs.next()){
        .....
      }
      rs.close();
    }
    catch(Exception e){
      .....
    }
    finally {
      if ( stmt != null ) try{stmt.close();}catch(Exception e){}
      if ( conn != null ) try{conn.close();}catch(Exception e){}
    }

 ------------------------------------------------------------------
 IBM WebSphere Application Server 2.0.x
  
    import java.sql.*;
    import com.ibm.servlet.connmgr.*;


    // ConnMgr Cache 사용을 위한 connMgr 객체 static 초기화

    private static IBMConnMgr connMgr = null;
    private static IBMConnSpec	spec = null;
    static {
      try {
        String poolName = "JdbcDb2";  // defined in WebSphere Admin Console
        spec = new IBMJdbcConnSpec(poolName, false,  
              "com.ibm.db2.jdbc.app.DB2Driver",
              "jdbc:db2:<db_name>",   // "jdbc:db2://ip_address:6789/<db_name>",
              "userid","password");					
        connMgr = IBMConnMgrUtil.getIBMConnMgr();
      }
      catch(Exception e){
        .....
      }
    }

    .....

    IBMJdbcConn cmConn = null; // "cm" maybe stands for Connection Manager.
    Statement stmt = null;
    try {
      cmConn = (IBMJdbcConn)connMgr.getIBMConnection(spec);    
      Connection conn = jdbcConn.getJdbcConnection();
      stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select ....");
      while(rs.next()){
        .....
      }
      rs.close();
    }
    catch(Exception e){
      .....
    }
    finally {
      if ( stmt != null ) try{stmt.close();}catch(Exception e){}
      if ( cmConn != null ) try{cmConn.releaseIBMConnection();}catch(Exception e){}
    }
    // NOTE: DO NOT "conn.close();" !!


 ------------------------------------------------------------------
 Oracle OSDK(Oracle Servlet Development Kit)

    import java.sql.*;
    import oracle.ec.ctx.*;


    .....
    
    oracle.ec.ctx.Trx trx = null;
    Connection conn = null;
    Statement stmt = null;
    try {
      oracle.ec.ctx.TrxCtx ctx = oracle.ec.ctx.TrxCtx.getTrxCtx();
      trx = ctx.getTrx();
      conn = trx.getConnection("<pool_name>");
      stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select ....");
      while(rs.next()){
        .....
      }
      rs.close();
    }
    catch(Exception e){
      .....
    }
    finally {
      if ( stmt != null ) try{stmt.close();}catch(Exception e){}
      if ( conn != null ) try{ trx.close(conn,"<pool_name>");}catch(Exception e){}
    }
    // NOTE: DO NOT "conn.close();" !!


 ------------------------------------------------------------------
 Hans Bergsten 의 DBConnectionManager.java

    import java.sql.*;

    .....
    
    db.DBConnectionManager connMgr = null;
    Connection conn = null;
    Statement stmt = null;
    try {
      connMgr = db.DBConnectionManager.getInstance();
      conn = connMgr.getConnection("<pool_name>");
      stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select ....");
      while(rs.next()){
        .....
      }
      rs.close();
    }
    catch(Exception e){
      .....
    }
    finally {
      if ( stmt != null ) try{stmt.close();}catch(Exception e){}
      if ( conn != null ) connMgr.freeConnection("<pool_name>", conn);
    }
    // NOTE: DO NOT "conn.close();" !!

 ------------------------------------------------------------------
 JDF 의 DB Connection Pool Framework

    import java.sql.*;
    import com.lgeds.jdf.*;
    import com.lgeds.jdf.db.*;
    import com.lgeds.jdf.db.pool.*;


    private static com.lgeds.jdf.db.pool.JdbcConnSpec spec = null;
    static {
      try {
        com.lgeds.jdf.Config conf = new com.lgeds.jdf.Configuration();
        spec =  new com.lgeds.jdf.db.pool.JdbcConnSpec(
             conf.get("gov.mpb.pbf.db.emp.driver"),
             conf.get("gov.mpb.pbf.db.emp.url"),
             conf.get("gov.mpb.pbf.db.emp.user"),
             conf.get("gov.mpb.pbf.db.emp.password")
          );
      }
      catch(Exception e){
        .....
      }
    }

    .....

    PoolConnection poolConn = null;
    Statement stmt = null;
    try {
      ConnMgr mgr = ConnMgrUtil.getConnMgr();
      poolConn = mgr.getPoolConnection(spec);
      Connection conn = poolConnection.getConnection();
      stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select ....");
      while(rs.next()){
        .....
      }
      rs.close();
    }
    catch(Exception e){
      .....
    }
    finally {
      if ( stmt != null ) try{stmt.close();}catch(Exception e){}
      if ( poolConn != null ) poolConn.release();
    }
    // NOTE: DO NOT "conn.close();" !!

 ------------------------------------------------------------------


 여기서 하고픈 얘기는 DB Connection Pool 을 구현하는 방식에 따라서 개발자의 소스도
 전부 제 각기 다른 API를 사용해야 한다는 것입니다.
 프로젝트를 이곳 저곳 뛰어 본 분은 아시겠지만, 매 프로젝트마나 어플리케이션 서버가
 다르고 지난 프로젝트에서 사용된 소스를 새 프로젝트에 그대로 적용하지 못하게 됩니다.
 JDBC 관련 API가 다르기 때문이죠.
 같은 제품일지라도 버전업이 되면서 API가 변해버리는 경우도 있습니다. 예를 들면,
 IBM WebSphere 버전 2.0.x에서 버전 3.0.x로의 전환할 때, DB 연결을 위한 API가
 변해버려 기존에 개발해둔 400 여개의 소스를 다 뜯어 고쳐야 하는 것과 같은 상황이
 벌어질 수도 있습니다.
 닷컴업체에서 특정 패키지 제품을 만들때도 마찬가지 입니다. 자사의 제품이 어떠한
 어플리케이션 서버에서 동작하도록 해야 하느냐에 따라 코딩할 API가 달라지니 소스를
 매번 변경해야만 하겠고, 특정 어플리케이션 서버에만 동작하게 하려니 마켓시장이
 좁아지게 됩니다.
 IBM WebSphere, BEA WebLogic 뿐만 아니라 Apache JServ 나 JRun, 혹은 Tomcat 에서도
 쉽게 포팅하길 원할 것입니다.

 이것을 해결하는 방법은 우리들 "SE(System Engineer)"만의 고유한 "Connection Adapter
 클래스"를 만들어서 사용하는 것입니다.

 예를 들어 개발자의 소스는 이제 항상 다음과 같은 유형으로 코딩되면 어떻겠습니까 ?


    -----------------------------------------------------------
    .....
    ConnectionResource resource = null;
    Statement stmt = null;
    try {
      resource = new ConnectionResource();
      Connection conn = resource.getConnection();

      stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select ....");
      while(rs.next()){
        .....
      }
      rs.close();
    }
    catch(Exception e){
      .....
    }
    finally {
      if ( stmt != null ) try{stmt.close();}catch(Exception e){}
      if ( resource != null ) resource.release();
    }
    // NOTE: DO NOT "conn.close();" !!
    -----------------------------------------------------------



 이 때 ConnectionResource 는 다음과 같은 형식으로 누군가 한분이 만들어 두면 되겠죠.

    -----------------------------------------------------------
    import java.sql.*;
    public class ConnectionResource
    {
      private java.sql.Connection conn = null;
      public ConnectionResource() throws Exception {
         ......
         conn =  ..... GET Connection by Connection Pooling API
      }
      public Connection getConnection() throws Exception {
         return conn;
      }
      public void release(){
         // release conn into "Connection Pool"
         .......
      }
    }
    -----------------------------------------------------------



  예를 들어 IBM WebSphere Version 3.0.2.x 의 경우를 든다면 다음과 같이 될 겁니다.

  -----------------------------------------------------------
  package org.jsn.connpool;
  /*
   * ConnectionResource V 1.0
   * JDBC Connection Pool Adapter for IBM WebSphere V 3.0.x/3.5.x
   * Author: WonYoung Lee, javaservice@hanmail.net, 011-898-7904
   * Last Modified : 2000.11.10
   * NOTICS: You can re-distribute or copy this source code freely, 
   *         you can NOT remove the above subscriptions.
  */

  /* IBM WebSphere 3.0.2.x for JDK 1.1.8 */
  //import java.sql.*;
  //import javax.naming.*;
  //import com.ibm.ejs.dbm.jdbcext.*;
  //import com.ibm.db2.jdbc.app.stdext.javax.sql.*;

  /* IBM WebSphere 3.5.x for JDK 1.2.2 */
  import java.sql.*;
  import javax.sql.*;
  import javax.naming.*;

  public class ConnectionResource
  {
     private static final String userid = "userid";
     private static final String password = "password"
     private static final String datasource = "jdbc/<data_source_name>";
     private static DataSource ds = null;

     private java.sql.Connection conn = null;

     public ConnectionResource() throws Exception {
       synchronized ( ConnectionResource.class ) {
         if ( ds == null ) {
           java.util.Hashtable props = new java.util.Hashtable();
           props.put(Context.INITIAL_CONTEXT_FACTORY, 
              "com.ibm.ejs.ns.jndi.CNInitialContextFactory");
           Context ctx = null;
           try {
             ctx = new InitialContext(props);
             ds = (DataSource)ctx.lookup("jdbc/<data_source_name>");
           }
           finally {
             if ( ctx != null ) ctx.close();
           }
         }
       }
       conn =  ds.getConnection( userid, password );
     }
     public Connection getConnection() throws Exception {
        if ( conn == null ) throw new Exception("Connection is NOT avaiable !!");
        return conn;
     }
     public void release(){
        // release conn into "Connection Pool"
        if ( conn != null ) try { conn.close(); }catch(Excepton e){}
        conn = null;
     }
   }
   -----------------------------------------------------------
          


  
  만약 Hans Bersten 의 DBConnectionManager.java 를 이용한 Connection Pool 이라면
  다음과 같이 만들어 주면 됩니다.

   -----------------------------------------------------------
   package org.jsn.connpool;
   import java.sql.*;
   public class ConnectionResource
   {
      private String poolname = "<pool_name>";
      private Connection conn = null;
      private db.DBConnectionManager connMgr = null;

      public ConnectionResource() throws Exception {
         connMgr = db.DBConnectionManager.getInstance();
         conn =  connMgr.getConnection(poolname);
      }
      public Connection getConnection() throws Exception {
         if ( conn == null ) throw new Exception("Connection is NOT avaiable !!");
         return conn;
      }
      public void release(){
         if ( conn != null ) {
           // Dirty Transaction을 rollback시키는 부분인데, 생략하셔도 됩니다.
           boolean autoCommit = true;
           try{ autoCommit = conn.getAutoCommit(); }catch(Exception e){}
           if ( autoCommit == false ) {
             try { conn.rollback(); }catch(Exception e){}
             try { conn.setAutoCommit(true); }catch(Exception e){}
           }

           connMgr.freeConnection(poolname, conn);
           conn = null;
         }
      }
   }
   -----------------------------------------------------------


  또, Resin 1.2.x 의 경우라면 다음과 같이 될 겁니다.
  -----------------------------------------------------------
  package org.jsn.connpool;
  /*
   * ConnectionResource V 1.0
   * JDBC Connection Pool Adapter for Resin 1.2.x
   * Author: WonYoung Lee, javaservice@hanmail.net, 011-898-7904
   * Last Modified : 2000.10.18
   * NOTICS: You can re-distribute or copy this source code freely, 
   *         you can NOT remove the above subscriptions.
  */
  import java.sql.*;
  import javax.sql.*;
  import javax.naming.*;
  public class ConnectionResource
  {
     private static final String datasource = "jdbc/<data_source_name>";
     private static final String userid = "userid";
     private static final String password = "password"
     private static DataSource ds = null;

     private java.sql.Connection conn = null;

     public ConnectionResource() throws Exception {
        synchronized ( ConnectionResource.class ) {
          if ( ds == null ) {
            Context env = (Context) new InitialContext().lookup("java:comp/env");
            ds = (DataSource) env.lookup(datasource);
          }
        }    
        conn =  ds.getConnection( userid, password );
     }
     public Connection getConnection() throws Exception {
        if ( conn == null ) throw new Exception("Connection is NOT avaiable !!");
        return conn;
     }
     public void release(){
        // release conn into "Connection Pool"
        if ( conn != null ) try { conn.close(); }catch(Excepton e){}
        conn = null;
     }
   }
   -----------------------------------------------------------


  Oracle 8i(8.1.6이상)에서 Oracle의 JDBC 2.0 Driver 자체가 제공하는 Connection 
  Pool을 이용한다면 다음과 같이 될 겁니다.
  -----------------------------------------------------------
  package org.jsn.connpool;
  /*
   * ConnectionResource V 1.0
   * JDBC Connection Pool Adapter for Oracle JDBC 2.0
   * Author: WonYoung Lee, javaservice@hanmail.net, 011-898-7904
   * Last Modified : 2001.10.29
   * NOTICS: You can re-distribute or copy this source code freely, 
   *         but you can NOT remove the above subscriptions.
  */
  import java.sql.*;
  import javax.sql.*;
  import oracle.jdbc.driver.*;
  import oracle.jdbc.pool.*;

  public class ConnectionResource
  {
     private static final String dbUrl = "jdbc:oracle:thin@192.168.0.1:1521:ORCL";
     private static final String userid = "userid";
     private static final String password = "password"
     private static OracleConnectionCacheImpl oraclePool = null;
     private static boolean initialized = false;

     private java.sql.Connection conn = null;

     public ConnectionResource() throws Exception {
        synchronized ( ConnectionResource.class ) {
          if ( initialized == false ) {
            DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
            oraclePool = new OracleConnectionCacheImpl();
            oraclePool.setURL(dbUrl);
            oraclePool.setUser(userid);
            oraclePool.setPassword(password);
            oraclePool.setMinLimit(10);
            oraclePool.setMaxLimit(50);
            //oraclePool.setCacheScheme(OracleConnectionCacheImpl.FIXED_WAIT_SCHEME);
            // FIXED_WAIT_SCHEME(default), DYNAMIC_SCHEME, FIXED_RETURN_NULL_SCHEME
            //oraclePool.setStmtCacheSize(0); //default is 0

            initialized = true;
          }
        }    
        conn = oraclePool.getConnection();
     }
     public Connection getConnection() throws Exception {
        if ( conn == null ) throw new Exception("Connection is NOT avaiable !!");
        return conn;
     }
     public void release(){
        // release conn into "Connection Pool"
        if ( conn != null ) try { conn.close(); }catch(Exception e){}
        conn = null;
     }
   }
   -----------------------------------------------------------

  또한 설령 Connection Pool 기능을 지금을 사용치 않더라도 향후에 적용할 계획이
  있다면, 우선은 다음과 같은 Connection Adapter 클래스를 미리 만들어 두고 이를
  사용하는 것이 효과적입니다. 향후에 이 클래스만 고쳐주면 되니까요.
  Oracle Thin Driver를 사용하는 경우입니다.

  -----------------------------------------------------------
  import java.sql.*;
  public class ConnectionResource
  {
     private static final String userid = "userid";
     private static final String password = "password"
     private static final String driver = "oracle.jdbc.driver.OracleDriver"
     private static final String url = "jdbc:oracle:thin@192.168.0.1:1521:ORCL";
     private static boolean initialized = false;

     private java.sql.Connection conn = null;

     public ConnectionResource() throws Exception {
        synchronized ( ConnectionResource.class ) {
          if ( initialized == false ) {
              Class.forName(driver);
              initialized = true;
          }
        }
        conn = DriverManager.getConnection( url, userid, password );
     }
     public Connection getConnection() throws Exception {
        if ( conn == null ) throw new Exception("Connection is NOT avaiable !!");
        return conn;
     }
     public void release(){
        if ( conn != null ) try { conn.close(); }catch(Excepton e){}
        conn = null;
     }
   }
   -----------------------------------------------------------

 
 프로그램의 유형이나, 클래스 이름이야 뭐든 상관없습니다. 위처럼 우리들만의 고유한
 "Connection Adapter 클래스"를 만들어서 사용한다는 것이 중요하고, 만약 어플리케이션
 서버가 변경된다거나, DB Connection Pooling 방식이 달라지면 해당 ConnectionResource
 클래스 내용만 살짝 고쳐주면 개발자의 소스는 전혀 고치지 않아도 될 것입니다.

 NOTE: ConnectionResource 클래스를 만들때 주의할 것은 절대 Exception 을 클래스 내부에서
   가로채어 무시하게 하지 말라는 것입니다. 그냥 그대로 throw 가 일어나게 구현하세요.
   이렇게 하셔야만 개발자의 소스에서 "Exception 처리"를 할 수 있게 됩니다.

 NOTE2: ConnectionResource 클래스를 만들때 반드시 package 를 선언하도록 하세요.
   IBM WebSphere 3.0.x 의 경우 JSP에서 "서블렛클래스패스"에 걸려 있는 클래스를
   참조할 때, 그 클래스가 default package 즉 package 가 없는 클래스일 경우 참조하지
   못하는 버그가 있습니다. 통상 "Bean"이라 불리는 클래스 역시 조건에 따라 인식하지
   않을 수도 있습니다.
   클래스 다지인 및 설계 상으로 보더라도 package 를 선언하는 것이 바람직합니다.

 NOTE3: 위에서  "userid", "password" 등과 같이 명시적으로 프로그램에 박아 넣지
   않고, 파일로 관리하기를 원한다면, 그렇게 하셔도 됩니다.
   JDF의 Configuration Framework 을 참조하세요.


 PS: 혹자는 왜 ConnectionResource 의 release() 메소드가 필요하냐고 반문할 수도 있습
   니다. 예를 들어 개발자의 소스가 다음처럼 되도록 해도 되지 않느냐라는 거죠.

    -----------------------------------------------------------
    .....
    Connection conn = null;
    Statement stmt = null;
    try {
      conn = ConnectionResource.getConnection(); // <---- !!!

      stmt = conn.createStatement();
      .....
    }
    catch(Exception e){
      .....
    }
    finally {
      if ( stmt != null ) try{stmt.close();}catch(Exception e){}
      if ( conn != null ) try{conn.close();}catch(Exception e){} // <---- !!!
    }
    -----------------------------------------------------------

  이렇게 하셔도 큰 무리는 없습니다. 그러나, JDBC 2.0 을 지원하는 제품에서만
  conn.close() 를 통해 해당 Connection 을 실제 close() 시키는 것이 아니라 DB Pool에
  반환하게 됩니다. BEA WebLogic 이나 IBM WebSphere 3.0.2.x, 3.5.x 등이 그렇습니다.
  그러나, 자체제작된 대부분의 DB Connection Pool 기능은 Connection 을 DB Pool에
  반환하는 고유한 API를 가지고 있습니다. WebSphere Version 2.0.x 에서는 
  cmConn.releaseIBMConnection(), Oracle OSDK 에서는 trx.close(conn, "<pool_name">);
  Hans Bersten 의 DBConnectionManager 의 경우는 
  connMgr.freeConnection(poolname, conn); 등등 서로 다릅니다. 이러한 제품들까지
  모두 지원하려면 "release()" 라는 우리들(!)만의 예약된 메소드가 꼭 필요하게 됩니다.

  물론, java.sql.Connection Interface를 implements 한 별도의 MyConnection 을 만들어
  두고, 실제 java.sql.Connection 을 얻은 후 MyConnection의 생성자에 그 reference를
  넣어준 후, 이를 return 시에 넘기도록 하게 할 수 있습니다. 이때, MyConnection의
  close() 함수를 약간 개조하여 DB Connection Pool로 돌아가게 할 수 있으니까요.


 PS: 하나 이상의 DB 를 필요로 한다면, 다음과 같은 생성자를 추가로 만들어서 구분케
  할 수도 있습니다.

  -----------------------------------------------------------
  ....
  private String poolname = "default_pool_name";
  private String userid = "scott";
  private String password = "tiger";
  public ConnectionResource() throws Exception {
    initialize();
  }
  public ConnectionResource(String poolname) throws Exception {
    this.poolname = poolname;
    initialize();
  }
  public ConnectionResource(String poolname,String userid, String passwrod)
  throws Exception 
  {
    this.poolname = poolname;
    this.userid = userid;
    this.password = password;
    initialize();
  }
  private void initialize() throws Exception {
    ....
  }
  ...
  -----------------------------------------------------------



-------------------------------------------------------------------------------
2001.03.20 추가
2001.10.22 수정
2001.11.09 수정(아래 설명을 추가함)

실 운영 사이트의 장애진단 및 튜닝을 다녀보면, 장애의 원인이 DataBase 연결개수가
지속적으로 증가하고, Connection Pool 에서 더이상 가용한 연결이 남아 있지 않아
발생하는 문제가 의외로 많습니다. 이 상황의 십중팔구는 개발자의 코드에서 Pool로
부터 가져온 DB연결을 사용하고 난 후, 이를 여하한의 Exception 상황에서도 다시 
Pool로 돌려보내야 한다는 Rule 를 지키지 않아서 발생한 문제가 태반입니다.
이름만 말하면 누구나 알법한 큼직한 금융/뱅킹사이트의 프로그램소스에서도 마찬가지
입니다.
문제는 분명히 어떤 특정 응용프로그램에서 DB Connection 을 제대로 반환하지 않은
것은 분명한데, 그 "어떤 특정 응용프로그램"이 꼭집어 뭐냐 라는 것을 찾아내기란
정말 쉽지 않습니다. 정말 쉽지 않아요. 1초당 수십개씩의 Request 가 다양하게 들어
오는 상황에서, "netstat -n"으로 보이는 TCP/IP 레벨에서의 DB연결수는 분명히 증가
하고 있는데, 그 수십개 중 어떤 것이 문제를 야기하느냐를 도저히 못찾겠다는 것이지요.
사용자가 아무도 없는 새벽에 하나씩 컨텐츠를 꼭꼭 눌러 본들, 그 문제의 상황은
대부분 정상적인 로직 flow 에서는 나타나지 않고, 어떤 특별한 조건, 혹은 어떤
Exception 이 발생할 때만 나타날 수 있기 때문에, 이런 방법으로는 손발과 눈만 아프게
되곤 합니다.

따라서, 애초 부터, 이러한 상황을 고려하여, 만약, 개발자의 코드에서 실수로 DB연결을
제대로 Pool 에 반환하지 않았을 때, 그 개발자 프로그램 소스의 클래스 이름과 함께
Warnning 성 메세지를 남겨 놓으면, 되지 않겠느냐는 겁니다.

Java 에서는 java.lang.Object 의 finalize() 라는 기막힌 메소드가 있습니다. 해당
Object instance의 reference 가 더이상 그 어떤 Thread 에도 남아 있지 않을 경우
JVM의 GC가 일어날 때 그 Object 의 finalize() 메소드를 꼭 한번 불러주니까요.

계속 반복되는 얘기인데, 아래 글에서 또한번 관련 의미를 찾을 수 있을 것입니다.
Re: DB Connection Pool: Orphan and Idle Timeout
http://www.javaservice.net/~java/bbs/read.cgi?m=devtip&b=servlet&c=r_p&n=1005294960

아래는 이것을 응용하여, Hans Bergsten 의 DBConnectionManager 를 사용하는 
ConnectionAdapter 클래스를 만들어 본 샘플입니다. Trace/Debuging 을 위한 몇가지
기능이 첨가되어 있으며, 다른 ConnectionPool을 위한 Connection Adapter 를 만들때도
약간만 수정/응용하여 사용하실 수 있을 것입니다.


/**
 * Author : Lee WonYoung, javaservice@hanmail.net
 * Date   : 2001.03.20, 2001.10.22
 */

import java.util.*;
import java.sql.*;
import java.io.*;
import java.text.SimpleDateFormat;

public class ConnectionResource
{
    private boolean DEBUG_MODE = true;
    private String DEFAULT_DATASOURCE = "idb";  // default db name
    private long GET_CONNECTION_TIMEOUT = 2000; // wait only 2 seconds if no
                                                // avaiable connection in the pool
    private long WARNNING_MAX_ELAPSED_TIME = 3000;

    private SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd/HHmmss");
    private db.DBConnectionManager manager = null;
    private Connection conn = null;
    private String datasource = DEFAULT_DATASOURCE;
    private String caller = "unknown";
    private long starttime = 0;

    private static int used_conn_count = 0;
    private static Object lock = new Object();

    // detault constructor
    public ConnectionResource()
    {
        manager = db.DBConnectionManager.getInstance();
    }

    // For debugging, get the caller object reference
    public ConnectionResource(Object caller_obj)
    {
        this();
        if ( caller_obj != null ) 
            caller = caller_obj.getClass().getName();
    }

    public Connection getConnection()  throws Exception
    { 
        return getConnection(DEFAULT_DATASOURCE);
    }

    public Connection getConnection(String datasource)  throws Exception
    { 
        if ( conn != null ) throw new Exception
            ("You must release the connection first to get connection again !!");

        this.datasource = datasource;
        // CONNECTION_TIMEOUT is very important factor for performance tuning
        conn = manager.getConnection(datasource, GET_CONNECTION_TIMEOUT);
        synchronized( lock ) { ++used_conn_count; }
        starttime = System.currentTimeMillis();
        return conn;
    }

    // you don't have to get "connection reference" as parameter,
    // because we already have the referece as the privae member variable.
    public synchronized void release() throws Exception {  
        if ( conn == null ) return;
        // The following is needed for some DB connection pool.
        boolean mode = true;
        try{
            mode = conn.getAutoCommit();
        }catch(Exception e){}
        if ( mode == false ) {
            try{conn.rollback();}catch(Exception e){}
            try{conn.setAutoCommit(true);}catch(Exception e){}
        }
        manager.freeConnection(datasource, conn);
        conn = null;
        int count = 0;
        synchronized( lock ) { count = --used_conn_count; }
        if ( DEBUG_MODE ) {
            long endtime = System.currentTimeMillis();
            if ( (endtime-starttime) > WARNNING_MAX_ELAPSED_TIME ) {
                System.err.println(df.format(new java.util.Date()) + 
                    ":POOL:WARNNING:" + count +
                    ":(" + (endtime-starttime) + "):" +
                    "\t" + caller
                );
            }
        }
    }
    // finalize() method will be called when JVM's GC time.
    public void finalize(){
        // if "conn" is not null, this means developer did not release the "conn".
        if ( conn != null ) {
            System.err.println(df.format(new java.util.Date()) + 
                ":POOL:ERROR connection was not released:" +
                used_conn_count + ":\t" + caller
            );
            release();
        }
    }
}

-------------------------------------------------------------------------------

위의 Connection Adaptor 클래스를 Servlet 이나 JSP에서 사용할 때는 다음과 같이
사용할 수 있습니다.


사용법: DB Transaction 처리가 필요치 않을 때...


ConnectionResource resource = null;
Connection conn = null;
Statement stmt = null;
try{
    // For debugging and tracing, I recommand to send caller's reference to 
    // the adapter's constructor.
    resource = new ConnectionResource(this); // <--- !!!
    //resource = new ConnectionResource();

    conn = resource.getConnection();
    // or you can use another database name
    //conn = resource.getConnection("other_db");

    stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select ...");
    while(rs.next()){
        .....
    }
    rs.close();

}
//catch(Exception e){
//    // error handling if you want
//    ....
//}
finally{
    if ( stmt != null ) try{stmt.close();}catch(Exception e){}
    if ( conn != null ) resource.release(); // <--- !!!
}

-------------------------------------------------------------------------------
사용법: Transaction 처리가 필요할 때.

ConnectionResource resource = null;
Connection conn = null;
Statement stmt = null;
try{
    // For debugging and tracing, I recommand to send caller's reference to 
    // the adapter's constructor.
    resource = new ConnectionResource(this);
    //resource = new ConnectionResource();

    conn = resource.getConnection();
    // or you can use another database name
    //conn = resource.getConnection("other_db");

    conn.setAutoCommit(false); // <--- !!!

    stmt = conn.createStatement();
    stmt.executeUpdate("update ...");
    stmt.executeUpdate("insert ...");
    stmt.executeUpdate("update ...");
    stmt.executeUpdate("insert ...");
    stmt.executeUpdate("update ...");
    
    int affected = stmt.executeUpdate("update....");
    // depends on your business logic,
    if ( affected == 0 ) 
        throw new Exception("NoAffectedException");
    else if ( affected > 1 ) 
        throw new Exception("TooManyAffectedException:" + affected);

    conn.commit(); //<<-- commit() must locate at the last position in the "try{}"
}
catch(Exception e){
    // if error, you MUST rollback 
    if ( conn != null ) try{conn.rollback();}catch(Exception e){}

    // another error handling if you want
    ....
    throw e; // <--- throw this exception if you want
}
finally{
    if ( stmt != null ) try{stmt.close();}catch(Exception e){}
    if ( conn != null ) resource.release(); // <-- NOTE: autocommit mode will be
                                            //           initialized
}
-----------------------------------------------------------------

PS: 더 깊이있는 내용을 원하시면 다음 문서들을 참조 하세요...
    JDF 제4탄 - DB Connection Pool
    http://www.javaservice.net/~java/bbs/read.cgi?m=jdf&b=framework&c=r_p&n=945156790

    JDF 제5탄 - DB Connection Resource Framework
    http://www.javaservice.net/~java/bbs/read.cgi?m=jdf&b=framework&c=r_p&n=945335633

    JDF 제6탄 - Transactional Connection Resource Framework
    http://www.javaservice.net/~java/bbs/read.cgi?m=jdf&b=framework&c=r_p&n=945490586


PS: IBM WebSphere의 JDBC Connection Pool 에 관심이 있다면 다음 문서도 꼭 참조
 하세요...
    Websphere V3 Connection Pool 사용법
    http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=970209527
    WebSphere V3 DB Connection Recovery 기능 고찰
    http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=967473008

------------------------
NOTE: 2004.04.07 추가
그러나, 2004년 중반을 넘어서는 이 시점에서는, ConnectionResource와 같은 Wapper의
중요성이 별로 높이 평가되지 않는데, 그 이유는 Tomcat 5.0을 비롯한 대부분의 WAS(Web
Application Server)가 자체의 Connection Poolinig기능을 제공하며, 그 사용법은 다음과
같이 JDBC 2.0에 준하여 모두 동일한 형태를 띠고 있기 때문입니다.

InitialContext ctx = new InitialContext();
DataSource ds = (DataSoruce)ctx.lookup("java:comp/env/jdbc/ds");
Connection conn = ds.getConnection();
...
conn.close();

결국 ConnectionResource의 release()류의 메소드는 Vendor별로, JDBC Connection Pool의
종류가 난무하던 3-4년 전에는 어플리케이션코드의 Vendor종속성을 탈피하기 위해 의미를
가졌으나, 지금은 굳이 필요가 없다고 보여 집니다.

단지, caller와 callee의 정보, 즉, 응답시간을 추적한다거나, close() 하지 않은
어플리케이션을 추적하는 의미에서의 가치만 남을 수 있습니다.  (그러나, 이것도,
IBM WebSphere v5의 경우, 개발자가 conn.close() 조차 하지 않을 지라도 자동을 해당
Thread의 request ending시점에 "자동반환"이 일어나게 됩니다.)

---------------------
2005.01.21 추가
JDBC Connection/Statement/ResultSet을 close하지 않은 소스의 위치를 잡아내는 것은
실제 시스템 운영 중에 찾기란 정말 모래사장에서 바늘찾기 같은 것이었습니다.
그러나, 제니퍼(Jennifer2.0)과 같은 APM을 제품을 적용하시면, 운영 중에, 어느 소스의
어느 위치에서 제대로 반환시키지 않았는지를 정확하게 찾아줍니다.
뿐만 아니라, 모든 SQL의 수행통계 및 현재 수행하고 있는 어플리케이션이 어떤 SQL을
수행중인지 실시간으로 확인되니, 성능저하를 보이는 SQL을 튜닝하는 것 등, 많은 부분들이
명확해져 가고 있습니다. 이젠 더이상 위 글과 같은 문서가 필요없는 세상을 기대해 봅니다.

-------------------------------------------------------  
  본 문서는 자유롭게 배포/복사 할 수 있으나 반드시
  이 문서의 저자에 대한 언급을 삭제하시면 안됩니다
================================================
  자바서비스넷 이원영
  E-mail: javaservice@hanmail.net
  PCS:011-898-7904
================================================

최초작성일자: 2000/09/05 16:19:47 
최근 수정일 : 2001.01.27
최근 수정일 : 2001.03.12(nested sql query issue)
최근 수정일 : 2001.03.13(transaction)
최근 수정일 : 2001.03.20(instance variables in JSP)
최근 수정일 : 2001.04.03(문맥수정)
최근 수정일 : 2002.02.06("close 할 땐 제대로..." 추가사항첨가)
최근 수정일 : 2002.02.25("transaction관련 추가")
최근 수정일 : 2002.06.11(PreparedStatement에 의한 ResultSet close 이슈)
최근 수정일 : 2002.06.18(PreparedStatement관련 추가)
최근 수정일 : 2002.12.30(Instance Variable 공유 1.2 추가)


다들 아실법한 단순한 얘깁니다만, 아직 많은 분들이 모르시는 것 같아 다시한번 
정리합니다. 아래의 각각의 예제는 잘못 사용하고 계시는 전형적인 예들입니다.

1. 서블렛에서 instance variable 의 공유

1.1 서블렛에서 instance variable 의 공유 - PrintWriter - 

  다음과 같은 코드를 생각해 보겠습니다.

 import java.io.*;
 import javax.servlet.*;
 import javax.servlet.http.*;

 public class CountServlet extends HttpServlet {
     private PrintWriter out = null; // <-------------- (1)
 
     public void doGet(HttpServletRequest req, HttpServletResponse res)  
         throws ServletException, IOException
     {
         res.setContentType("text/html");
         out = res.getWriter();
         for(int i=0;i<20;i++){
             out.println("count= " + (i+1) + "<br>");  // <---- (2)
             out.flush();
             try{Thread.sleep(1000);}catch(Exception e){}
         }
    }
  }

 위의 CountServlet.java 를 컴파일하여 돌려 보면, 1초간격으로 일련의 숫자가 올라가는
 것이 보일 겁니다.(서블렛엔진의 구현방식에 따라 Buffering 이 되어 20초가 모두 지난
 후에서 퍽 나올 수도 있습니다.)
 혼자서 단일 Request 를 날려 보면, 아무런 문제가 없겠지만, 이제 브라우져 창을 두개 이상
 띄우시고 10초의 시간 차를 두시면서 동시에 호출해 보세요... 이상한 증상이 나타날
 겁니다. 먼저 호출한 창에는 10 까지 정도만 나타나고, 10초 뒤에 호출한 창에서는 먼저
 호출한 창에서 나타나야할 내용들까지 덤으로 나타나는 것을 목격할 수 있을 겁니다.

 이는 서블렛의 각 호출은 Thread 로 동작하여, 따라서, 각 호출은 위의 (1) 에서 선언한
 instance variable 들을 공유하기 때문에 나타나는 문제입니다.

 위 부분은 다음과 같이 고쳐져야 합니다.

  public class CountServlet extends HttpServlet {
     //private PrintWriter out = null;
 
     public void doGet(HttpServletRequest req, HttpServletResponse res)  
         throws ServletException, IOException
     {
         PrintWriter out = null; // <--- 이 쪽으로 와야죠 !!!
         res.setContentType("text/html");
         out = res.getWriter();
         for(int i=0;i<20;i++){
             out.println("count= " + (i+1) + "<br>");  // <---- (2)
             out.flush();
             try{Thread.sleep(1000);}catch(Exception e){}
         }
    }
  }

 국내 몇몇 Servlet 관련 서적의 일부 예제들이 위와 같은 잘못된 형태로 설명한 
 소스코드들이 눈에 띕니다. 빠른 시일에 바로 잡아야 할 것입니다.

 실제 프로젝트 환경에서 개발된 실무시스템에서도, 그러한 책을 통해 공부하신듯, 동일한
 잘못된 코딩을 하고 있는 개발자들이 있습니다. 결과적으로 테스트 환경에서는 나타나지
 않더니만, 막상 시스템을 오픈하고나니 고객으로 부터 다음과 같은 소리를 듣습니다. 
 "내 데이타가 아닌데 남의 데이타가 내 화면에 간혹 나타나요. refresh 를 누르면 또, 
 제대로 되구요" .....
 

1.2 서블렛에서 instance variable 의 공유

 앞서의 경우와 의미를 같이하는데, 다음과 같이 하면 안된다는 얘기지요.

  public class BadServlet extends HttpServlet {
     private String userid = null;
     private String username = null;
     private int hitcount = 0;
 
     public void doGet(HttpServletRequest req, HttpServletResponse res)  
         throws ServletException, IOException
     {
         res.setContentType("text/html");
         PrintWriter out = res.getWriter();
         userid = request.getParameter("userid");
         username = request.getParameter("username");
         hitcount = hitcount + 1;
         ....
    }
  }

 새로운 매 HTTP 요청마다 userid/username변수는 새롭게 할당됩니다. 문제는 그것이 특정
 사용자에 한하여 그러한 것이 아니라, BadServlet의 인스턴스(instance)는 해당
 웹컨테이너(Web Container)에 상에서 (예외경우가 있지만) 단 하나만 존재하고, 서로 다른
 모든 사용자들의 서로 다른 모든 요청들에 대해서 동일한 userid/username 및 count 변수를
 접근하게 됩니다. 따라서, 다음과 같이 메소드 안으로 끌어들여 사용하여야 함을 강조합니다.

  public class BadServlet extends HttpServlet {
     //private String userid = null; // <---- !!
     //private String username = null; // <---- !!
     private int hitcount = 0;
 
     public void doGet(HttpServletRequest req, HttpServletResponse res)  
         throws ServletException, IOException
     {
         res.setContentType("text/html");
         PrintWriter out = res.getWriter();
         String userid = request.getParameter("userid"); // <---- !!
         String username = request.getParameter("username"); // <---- !!

         //또한, instance 변수에 대한 접근은 적어도 아래처럼 동기화를 고려해야...
         synchronized(this){ hitcount = hitcount + 1; } 
         ....
    }
  }


1.3 서블렛에서 instance variable 의 공유  - DataBase Connection -

 public class TestServlet extends HttpServlet {
     private final static String drv = "oracle.jdbc.driver.OracleDriver";
     private final static String url = "jdbc:orache:thin@210.220.251.96:1521:ORA8i";
     private final static String user = "scott";
     private final static String password = "tiger";

     private ServletContext context;
     private Connection conn = null;  <--- !!!
     private Statement stmt = null; <------ !!!
     private ResultSet rs = null; <------ !!!
 
     public void init(ServletConfig config) throws ServletException {
         super.init(config);
         context = config.getServletContext();
         try {
             Class.forName(drv);
         }
         catch (ClassNotFoundException e) {
             throw new ServletException("Unable to load JDBC driver:"+ e.toString());
         }
     }
     public void doGet(HttpServletRequest req, HttpServletResponse res)  
         throws ServletException, IOException, SQLException
     {
         String id = req.getParameter("id");
         conn = DriverManager.getConnection(url,user,password);   ---- (1)
         stmt = conn.createStatement();  ---------- (2)
         rs = stmt.executeQuery("select .... where id = '" + id + "'"); ----- (3)
         while(rs.next()) { ----------- (4)
            ......  --------- (5)
         }  
         rs.close();  -------- (6)
         stmt.close();  ---- (7)
         conn.close();  --- (8)
         .....
    }
  }

  위에서 뭐가 잘못되었죠? 여러가지가 있겠지만, 그 중에 하나가 java.sql.Connection과
  java.sql.Statment, java.sql.ResultSet을 instance variable 로 사용하고 있다는 것입니다.

  이 서블렛은 사용자가 혼자일 경우는 아무런 문제를 야기하지 않습니다. 그러나 여러사람이
  동시에 이 서블렛을 같이 호출해 보면, 이상한 증상이 나타날 것입니다.
  그 이유는 conn, stmt, rs 등과 같은 reference 들을 instance 변수로 선언하여 두었기 
  때문에 발생합니다.  서블렛은 Thread로 동작하며 위처럼 instance 변수 영역에 선언해 둔
  reference 들은 doGet(), doPost() 를 수행하면서 각각의 요청들이 동시에 공유하게 됩니다.

  예를 들어, 두개의 요청이 약간의 시간차를 두고 비슷한 순간에 doGet() 안으로 들어왔다고
  가정해 보겠습니다.
  A 라는 요청이 순차적으로 (1), (2), (3) 까지 수행했을 때, B 라는 요청이 곧바로 doGet()
  안으로 들어올 수 있습니다. B 역시 (1), (2), (3) 을 수행하겠죠...
  이제 요청 A 는 (4) 번과 (5) 번을 수행하려 하는데, 가만히 생각해 보면, 요청B 로 인해
  요청A에 의해 할당되었던 conn, stmt, rs 의 reference 들은 바뀌어 버렸습니다.
  결국, 요청 A 는  요청 B 의 결과를 가지고 작업을 하게 됩니다. 반면, 요청 B 는
  요청 A 의 의해 rs.next() 를 이미 수행 해 버렸으므로, rs.next() 의 결과가 이미 close 
  되었다는 엉뚱한 결과를 낳고 마는 거죠...
  다른 쉬운 얘기로 설명해 보면, A, B 두사람이 식탁에 앉아서 각자 자신이 준비해 온 사과를
  하나씩 깎아서 식탁 위의 접시에 올려 놓고 나중에 먹어려 하는 것과 동일합니다. A 라는
  사람이 열심히 사과를 깎아 접시에 담아둘 때, B 라는 사람이 들어와서 A가 깎아둔 사과를
  버리고 자신이 깎은 사과를 대신 접시에 담아 둡니다. 이제 A라는 사람은 자신이 깎아서
  담아 두었다고 생각하는 그 사과를 접시에서 먹어버립니다. 곧이어 B라는 사람이 자신의
  사과를 접시에서 먹어려 하니 이미 A 가 먹고 난 후 였습니다. 이는 접시를 두 사람이
  공유하기 때문에 발생하는 문제잖습니까.
  마찬가지로 서블렛의 각 Thread는 instance variable 를 공유하기 때문에 동일한 문제들을
  발생하게 됩니다.

  따라서 최소한 다음처럼 고쳐져야 합니다.

 public class TestServlet extends HttpServlet {
     private final static String drv = "...";
     private final static String url = "....";
     private final static String user = "...";
     private final static String password = "...";

     private ServletContext context;
 
     public void init(ServletConfig config) throws ServletException {
         super.init(config);
         context = config.getServletContext();
         try {
             Class.forName(drv);
         }
         catch (ClassNotFoundException e) {
             throw new ServletException("Unable to load JDBC driver:"+ e.toString());
         }
     }
     public void doGet(HttpServletRequest req, HttpServletResponse res)  
         throws ServletException, IOException, SQLException
     {
         Connection conn = null;  <----- 이곳으로 와야죠..
         Statement stmt = null; <-------
         ResultSet rs = null; <---------

         String id = req.getParameter("id");
         conn = DriverManager.getConnection(url,user,password); 
         stmt = conn.createStatement();
         rs = stmt.executeQuery("select ..... where id = '" + id + "'");
         while(rs.next()) {
            ......  
         }  
         rs.close();
         stmt.close();
         conn.close();
         .....
     }
  }


1.4 JSP에서 Instance Variable 공유

 JSP에서 아래처럼 사용하는 경우가 위의 경우와 동일한 instance 변수를 공유하는 경우가
 됩니다.
   ---------------------------------------------------------
   <%@ page session=.... import=.... contentType=........ %>
   <%! 
       Connection conn = null;
       Statement stmt = null;
       ResultSet rs = null;
       String userid = null;
   %>
   <html><head></head><body>
   <%
       ........
       conn = ...
       stmt = .....

       uesrid = ......
   %>
   </body></html>
   ---------------------------------------------------------

   마찬가지로 위험천만한 일이며, 여러 Thread 에 의해 그 값이 변할 수 있는 변수들은
   <%! ... %> 를 이용하여 선언하시면 안됩니다. 이처럼 instance 변수로 사용할 것은
   다음 처럼, 그 값이 변하지 않는 값이거나, 혹은 공유변수에 대한 특별한 관리를
   하신 상태에서 하셔야 합니다.

   <%!  private static final String USERID = "scott";
        private static final String PASSWORD = "tiger";
   %>

   JSP에서의 이와 같은 잘못된 유형도, 앞선 서블렛의 경우처럼 일부 국내 JSP관련 
   서적에서 발견됩니다.  해당 서적의 저자는 가능한 빨리 개정판을 내셔서 시정하셔야
   할 것입니다. 해당 책은 출판사나 책의 유형, 그리고 글자체로 추정건데, 초보자가
   쉽게 선택할 법한 책인 만큼 그 파급력과 영향력이 너무 큰 듯 합니다.
 
   이와 같은 부분이 실 프로젝트에서 존재할 경우, 대부분 시스템 오픈 첫날 쯤에
   문제를 인식하게 됩니다. Connection reference 가 엎어쳐지므로 Pool 에 반환이
   일어나지 않게 되고, 이는 "connection pool"의 가용한 자원이 부하가 얼마 없음에도
   불구하고 모자라는 현상으로 나타나며, 때론 사용자의 화면에서는 엉뚱한 다른
   사람의 데이타가 나타나거나, SQLException 이 나타납니다.

   NOTE: 어떻게하란 말입니까? 각 호출간에 공유되어서는 안될 변수들은 <%! ...%> 가
   아니라, <% ... %> 내에서 선언하여 사용하란 얘깁니다. JSP가 Pre-compile되어
   Servlet형태로 변환될 때, <%! ... %>는 서블렛의 instance variable로 구성되는 반면,
   <% ... %>는 _jspService() 메소드 내의 method variable로 구성됩니다.



2. 하나의 Connection을 init()에서 미리 연결해 두고 사용하는 경우.

 public class TestServlet extends HttpServlet {
     private final static String drv = "oracle.jdbc.driver.OracleDriver";
     private final static String url = "jdbc:orache:thin@210.220.251.96:1521:ORA8i";
     private final static String user = "scott";
     private final static String password = "tiger";

     private ServletContext context;
     private Connection conn = null;  <--- !!!
 
     public void init(ServletConfig config) throws ServletException {
         super.init(config);
         context = config.getServletContext();
         try {
             Class.forName(drv);
             conn = DriverManager.getConnection(url,user,password);
         }
         catch (ClassNotFoundException e) {
             throw new ServletException("Unable to load JDBC driver:"+ e.toString());
         }
         catch (SQLException e) {
             throw new ServletException("Unable to connect to database:"+ e.toString());
         }
     }
     public void doGet(HttpServletRequest req, HttpServletResponse res)  
         throws ServletException, IOException, SQLException
     {
         Statement stmt = null;
         ResultSet rs = null;
         String id = req.getParameter("id");
         stmt = conn.createStatement();
         rs = stmt.executeQuery("select ..... where id = '" + id + "'");
         while(rs.next()) {
            ......  
         }  
         rs.close(); 
         stmt.close();
         .....
     }
     public void destroy() {
         if ( conn != null ) try {conn.close();}catch(Exception e){}
     }     
  }

 위는 뭐가 잘못되었을 까요?  서블렛당 하나씩 java.sql.Connection 을 init()에서 미리
 맺어 두고 사용하는 구조 입니다.

 얼핏 생각하면 아무런 문제가 없을 듯도 합니다. doGet() 내에서 별도의 Statement와 
 ResultSet 을 사용하고 있으니, 각 Thread는 자신만의 Reference를 갖고 사용하게 되니까요.

 이 구조는 크게 세가지의 문제를 안고 있습니다. 하나는 DB 연결자원의 낭비를 가져오며,
 두번째로 수많은 동시사용자에 대한 처리한계를 가져오고, 또 마지막으로 insert, update,
 delete 와 같이 하나 이상의 SQL문장을 수행하면서 단일의 Transaction 처리를 보장받을
 수 없다는 것입니다.

 1) DB 자원의 낭비

   위의 구조는 서블렛당 하나씩 java.sql.Connection 을 점유하고 있습니다. 실 프로젝트에서
   보통 서블렛이 몇개나 될까요? 최소한 100 개에서 400개가 넘어 갈 때도 있겠죠?
   그럼 java.sql.Connection에 할당 되어야 할 "DB연결갯수"도 서블렛 갯수 많큼 필요하게
   됩니다. DB 연결 자원은 DB 에서 설정하기 나름이지만, 통상 maximum 을 셋팅하기
   마련입니다. 그러나 아무런 요청이 없을 때도 400 여개의 DB연결이 연결되어 있어야 한다는
   것은 자원의 낭비입니다.
    
 2) 대량의 동시 사용자 처리 불가.

   또한, 같은 서블렛에 대해 동시에 100 혹은 그 이상의 요청이 들어온다고 가정해 보겠습
   니다. 그럼 같은 java.sql.Connection 에 대해서 각각의 요청이 conn.createStatement() 를
   호출하게 됩니다.
   문제는 하나의 Connection 에 대해 동시에 Open 할 수 있는 Statement 갯수는 ( 이 역시
   DB 에서 셋팅하기 나름이지만 ) maximum 제한이 있습니다. Oracle 의 경우 Default는 50
   입니다. 만약 이 수치 이상을 동시에 Open 하려고 하면 "maximum open cursor exceed !"
   혹은 "Limit on number of statements exceeded"라는 SQLExceptoin 을 발생하게 됩니다.

   예를 들어 다음과 같은 프로그램을 실행시켜 보세요.

   public class DbTest {
     public static void main(String[] args) throws Exception {
        Class.forName("jdbc driver...");
        Connection conn = DriverManager.getConnection("url...","id","password");
        int i=0;
        while(true) {
           Statement stmt = conn.createStatement();
           System.out.println( (++i) + "- stmt created");
        }
     }
   }

   과연 몇개 까지 conn.createStement() 가 수행될 수 있을까요? 이는 DB에서 설정하기 나름
   입니다. 중요한 것은 그 한계가 있다는 것입니다.
   또한 conn.createStatement() 통해 만들어진 stmt 는 java.sql.Connection 의 자원이기
   때문에 위처럼 stmt 의 reference 가 없어졌다고 해도 GC(Garbage Collection)이 되지
   않습니다.


  3) Transaction 중복현상 발생

  예를 들어 다음과 같은 서비스가 있다고 가정해 보겠습니다.

  public void doGet(HttpServletRequest req, HttpServletResponse res)  
      throws ServletException, IOException, SQLException
  {
      Statement stmt = null;
      String id = req.getParameter("id");
      try {
          conn.setAutoCommit(false);
          stmt = conn.createStatement();
          stmt.executeUpdate("update into XXXX..... where id = " + id + "'");
          stmt.executeUpdate("delete from XXXX..... where id = " + id + "'");
          stmt.executeUpdate(".... where id = " + id + "'");
          stmt.executeUpdate(".... where id = " + id + "'");
          conn.commit();
      }
      catch(Exception e){
          try{conn.rollback();}catch(Exception e){}
      }
      finally {
         if ( stmt != null ) try{stmt.close();}catch(Exception e){}
         conn.setAutoCommit(true);
      }
      .....
  }

  아무런 문제가 없을 듯도 합니다. 그러나 위의 서비스를 동시에 요청하게 되면, 같은 
  java.sql.Connection 을 갖고 작업을 하고 있으니 Transaction 이 중첩되게 됩니다.
  왜냐면, conn.commit(), conn.rollback() 과 같이 conn 이라는 Connection 에 대해서
  Transaction 이 관리되기 때문입니다. 요청 A 가 총 4개의 SQL문장 중 3개를 정상적으로
  수행하고 마지막 4번째의 SQL문장을 수행하려 합니다. 이 때 요청 B가 뒤따라 들어와서
  2개의 SQL 문장들을 열심히 수행했습니다.  근데, 요청 A에 의한 마지막 SQL 문장
  수행중에 SQLException 이 발생했습니다. 그렇담 요청 A 는 catch(Exception e) 절로
  분기가 일어나고 conn.rollback() 을 수행하여 이미 수행한 3개의 SQL 수행들을 모두
  rollback 시킵니다. 근데,,, 문제는 요청 B 에 의해 수행된 2개의 SQL문장들도 같이
  싸잡아서 rollback() 되어 버립니다. 왜냐면 같은 conn 객체니까요. 결국, 요청B 는
  영문도 모르고 마지막 2개의 SQL문장만 수행한 결과를 낳고 맙니다.

  따라서 정리하면, Connection, Statement, ResultSet 는 doGet() , doPost() 내에서
  선언되고 사용되어져야 합니다.

  public void doGet(HttpServletRequest req, HttpServletResponse res)  
      throws ServletException, IOException, SQLException 
  {
      Connection conn = null;  <----- 이곳으로 와야죠..
      Statement stmt = null; <-------
      ResultSet rs = null; <---------
      .....
  }




 3. Exception 이 발생했을 때도 Connection 은 닫혀야 한다 !!

  public void doGet(HttpServletRequest req, HttpServletResponse res)  
       throws ServletException, IOException, SQLException
  {
      String id = req.getParameter("id");

      Connection conn = DriverManager.getConnection("url...","id","password");
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("ssselect * from XXX where id = '" + id + "'");
      while(rs.next()) {
         ......  
      }  
      rs.close();
      stmt.close();
      conn.close();
      .....
  }

  위에선 뭐가 잘못되었을까요? 네, 사실 특별히 잘못된 것 없습니다. 단지 SQL문장에 오타가
  있다는 것을 제외하곤.... 근데, 과연 그럴까요?
  SQLException 이라는 것은 Runtime 시에 발생합니다. DB 의 조건이 맞지 않는다거나
  개발기간 중에 개발자의 실수로 SQL문장에 위처럼 오타를 적을 수도 있죠.
  문제는 Exception 이 발생하면 마지막 라인들 즉, rs.close(), stmt.close(), conn.close()
  가 수행되지 않는다는 것입니다.
  java.sql.Connection 은 reference 를 잃더라도 JVM(Java Virtual Machine)의 GC(Garbage 
  Collection) 대상이 아닙니다. 가뜩이나 모자라는 "DB연결자원"을 특정한 어플리케이션이
  점유하고 놓아 주지 않기 때문에 얼마안가 DB Connection 을 더이상 연결하지 못하는
  사태가 발생합니다.

  따라서 다음처럼 Exception 이 발생하든 발생하지 않든 반드시 java.sql.Connection 을 
  close() 하는 로직이 꼭(!) 들어가야 합니다.

  public void doGet(HttpServletRequest req, HttpServletResponse res)  
       throws ServletException, IOException, SQLException 
  {
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;
      String id = req.getParameter("id");
      try {
        conn = DriverManager.getConnection("url...","id","password");
        stmt = conn.createStatement();
        rs = stmt.executeQuery("sselect * from XXX where id = '" + id + "'");
        while(rs.next()) {
         ......  
        }
        rs.close();
        stmt.close();
      }
      finally {
         if ( conn != null ) try {conn.close();}catch(Exception e){}
      }
      .....
  }

  참고로, 실프로젝트의 진단 및 튜닝을 나가보면 처음에는 적절한 응답속도가 나오다가
  일정한 횟수 이상을 호출하고 난 뒤부터 엄청 응답시간이 느려진다면, 십중팔구는 위처럼
  java.sql.Connection 을 닫지 않아서 생기는 문제입니다.
  가용한 모든 Connection 을 연결하여 더이상 연결시킬 Connection 자원을 할당할 수 없을
  때, 대부분 timewait 이 걸리기 때문입니다. 일단 DB관련한 작업이 들어오는 족족
  timewait에 빠질 경우, "어플리케이션서버"에서 동시에 처리할 수 있는 최대 갯수만큼
  호출이 차곡차곡 쌓이는 건 불과 몇분 걸리지 않습니다. 그 뒤부터는 애궂은 dummy.jsp
  조차 호출이 되지 않게 되고,   누군가는 "시스템 또 죽었네요"라며 묘한 웃음을 짓곤
  하겠죠....



4. Connection 뿐만 아니라 Statement, ResultSet 도 반드시 닫혀야 한다 !!

4.1  3번의 예제에서 Connection 의 close() 만 고려하였지 Statement 나 ResultSet 에 대한
  close는 전혀 고려 하지 않고 있습니다. 무슨 문제가 있을까요? Statement 를 닫지 않아도
  Connection 을 닫았으니 Statement 나 ResultSet 은 자동으로 따라서 닫히는 것 아니냐구요?
  천만의 말씀, 만만의 콩깎지입니다.

  만약, DB Connection Pooling 을 사용하지 않고 직접 JDBC Driver 를 이용하여 매번 DB
  연결을 하였다가 끊는 구조라면 문제가 없습니다.
  그러나 DB Connection Pooling 은 이젠 보편화되어 누구나 DB Connection Pooling 을 사용
  해야한다는 것을 알고 있습니다. 그것이 어플리케이션 서버가 제공해 주든, 혹은 작은
  서블렛엔진에서 운영하고 있다면 직접 만들거나, 인터넷으로 돌아다니는 남의 소스를 가져다
  사용하고 있을 겁니다.

  이처럼 DB Connection Pooling 을 사용하고 있을 경우는 Conneciton 이 실제 close()되는
  것이 아니라 Pool에 반환되어 지게 되는데, 결국 reference가 사리지지 않기 때문에 GC시점에
  자동 close되지 않게 됩니다.
  특정 Connection 에서 열어둔 Statement 를  close() 하지 않은채 그냥 반환시켜 놓게 되면,
  언젠가는 그 Connection 은 다음과 같은   SQLException 을 야기할 가능성을 내포하게 됩니다.

  Oracle :
    java.sql.SQLException : ORA-01000: maximum open cursor exceeded !!
                            (최대열기 커서수를 초과했습니다)
  UDB DB2 :
    COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC 드라이버] CLI0601E  유효하지 않은
      명령문 핸들 또는 명령문이 닫혔습니다. SQLSTATE=S1000
    COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0129E  핸들(handle)이 
      더이상 없습니다. SQLSTATE=HY014        
    COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0954C  응용프로그램
      힙(heap)에 명령문을 처리하기 위해 사용 가능한 저장영역이 충분하지 않습니다.
      SQLSTATE=57011

  이유는 앞 2)번글에서 이미 언급드렸습니다. 보다 자세한 기술적 내용은 아래의 글을
  참고하세요.
  Connection/Statement 최대 동시 Open 수
  http://www.javaservice.net/~java/bbs/read.cgi?m=devtip&b=jdbc&c=r_p&n=972287002

  따라서 또다시 3번의 소스는 다음과 같은 유형으로 고쳐져야 합니다.

  public void doGet(HttpServletRequest req, HttpServletResponse res)  
       throws ServletException, IOException, SQLException
  {
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;
      String id = req.getParameter("id");
      try {
        conn = ...<getConnection()>...; // (편의상 생략합니다.)
        stmt = conn.createStatement();
        rs = stmt.executeQuery("sselect * from XXX where id = '" + id + "'");
        while(rs.next()) {
         ......  
        }
        // rs.close();
        // stmt.close();
      }
      finally {
        if ( rs != null ) try {rs.close();}catch(Exception e){}
        if ( stmt != null ) try {stmt.close();}catch(Exception e){} // <-- !!!!
        if ( conn != null ) ...<releaseConnection()>...; // (편의상 생략)

      }
      .....
  }

 4.2 사실 위와 같은 구조에서, java.sql.Statement의 쿼리에 의한 ResultSet의 close()에
  대한 것은 그리 중요한 것은 아닙니다. ResultSet은 Statement 가 close() 될 때 함께
  자원이 해제됩니다. 따라서 다음과 같이 하셔도 됩니다.

  public void doGet(HttpServletRequest req, HttpServletResponse res)  
       throws ServletException, IOException, SQLException
  {
      Connection conn = null;
      Statement stmt = null;
      String id = req.getParameter("id");
      try {
        conn = ...<getConnection()>...;
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("sselect * from XXX where id = '" + id + "'");
        while(rs.next()) {
         ......  
        }
        rs.close(); //<--- !!!
      }
      finally {
        // if ( rs != null ) try {rs.close();}catch(Exception e){}
        if ( stmt != null ) try {stmt.close();}catch(Exception e){}
        if ( conn != null ) ...<releaseConnection()>...;
      }
      .....
  }


 4.3  가장 대표적으로 잘못 프로그래밍하고 있는 예를 들라면 다음과 같은 유형입니다.

  Connection conn = null;
  try {
      conn = ...<getConnection()>....;
      Statement stmt = conn.createStatement();
      stmt.executeUpdate("....");  <--- 여기서 SQLException 이 일어나면...
      .....
      .....
      .....  <-- 만약,이쯤에서 NullPointerException 이 일어나면 ?
      .....
      stmt.close(); <-- 이것을 타지 않음 !!!
  }
  finally{
     if ( conn != null ) ...<releaseConnection()>...;
  }


 4.4  Statement 가 close() 되지 않는 또 하나의 경우가 다음과 같은 경우입니다.

  Connection conn = null;
  Statement stmt = null;
  try {
    conn = .....
    stmt = conn.createStatement(); // ....(1)
    rs = stmt.executeQuery("select a from ...");
    .....
    rs.close();

    stmt = conn.createStatement(); // ....(2)
    rs = stmt.executeQuery("select b from ...");
    ....
  }
  finally{
    if ( rs != null ) try {rs.close();}catch(Exception e){}
    if ( stmt != null ) try{stmt.close();}catch(Exception e){}
    if ( conn != null ) ....
  }

  즉, 두번 stmt = conn.createStatement() 를 수행함으로써, 먼저 생성된 stmt 는 
  (2)번에 의해 그 reference 가 엎어쳐버리게 되어 영원히 close() 되지 않은채 
  남아 있게 됩니다.
  이 경우, (2)번 문장을 주석처리하여야 겠지요. 한번 생성된 Statement 로 여러번
  Query 를 수행하면 됩니다.

  Connection conn = null;
  Statement stmt = null;
  try {
    conn = .....
    stmt = conn.createStatement(); // ....(1)
    rs = stmt.executeQuery("select a from ...");
    .....
    rs.close();

    // stmt = conn.createStatement(); // <--- (2) !!!
    rs = stmt.executeQuery("select b from ...");
    ....
  }
  finally{
    if ( rs != null ) try {rs.close();}catch(Exception e){}
    if ( stmt != null ) try{stmt.close();}catch(Exception e){}
    if ( conn != null ) ....
  }


 4.5  Statement 뿐만 아니라 PreparedStatement 를 사용할 때로 마찬가지 입니다.
  ....
  PreparedStatement pstmt = conn.prepareStatement("select ....");
  ....
  이렇게 만들어진  pstmt 도 반드시 pstmt.close() 되어야 합니다.

  예를 들면, 다음과 같은 코드를 생각할 수 있습니다.

  Connection conn = null;
  try {
    conn = ...<getConnection()>...;
    PreparedStatement pstmt = conn.prepareStatement("select .... ?...?");
    pstmt.setString(1,"xxxx");
    pstmt.setString(2,"yyyy");
    ResultSet rs = pstmt.executeQuery(); <--- 여기서 SQLException 이 일어나면
    while(rs.next()){
      ....
    }
    rs.close();
    pstmt.close();  <-- 이것을 타지 않음 !!!
  }
  finally{
     if ( conn != null ) ...<releaseConnection()>...;
  }

  따라서 같은 맥락으로 다음과 같이 고쳐져야 합니다.
  
  Connection conn = null;
  PreparedStatement pstmt = null; // <-------- !!
  ResultSet rs = null;
  try {
    conn = ...<getConnection()>...;
    pstmt = conn.prepareStatement("select .... ?...?");
    pstmt.setString(1,"xxxx");
    pstmt.setString(2,"yyyy");
    rs = pstmt.executeQuery(); <--- 여기서 SQLException 이 일어나더라도...
    while(rs.next()){
      ....
    }
    //rs.close();
    //pstmt.close(); 
  }
  finally{
    if ( rs != null ) try {rs.close();}catch(Exception e){}
    if ( pstmt != null ) try {pstmt.close();}catch(Exception e){} // <-- !!!!
    if ( conn != null ) ...<releaseConnection()>...;
  }


 4.6  PreparedStatement 에 관련해서 다음과 같은 경우도 생각할 수 있습니다.

 4.6.1 앞서의 4.4에서 Statement를 createStatement() 연거푸 두번 생성하는 것과
  동일하게 PreparedStatement에서도 주의하셔야 합니다. 예를 들면 다음과 같습니다.

  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  try {
    conn = .....
    pstmt = conn.prepareStatement("select a from ..."); // ....(1)
    rs = pstmt.executeQuery();
    .....
    rs.close();

    pstmt = conn.prepareStatement("select b from ..."); // <--- (2) !!!
    rs = pstmt.executeQuery();
    ....
  }
  finally{
    if ( rs != null ) try {rs.close();}catch(Exception e){}
    if ( pstmt != null ) try{pstmt.close();}catch(Exception e){} // <--- (3)
    if ( conn != null ) ...<releaseConnection()>...;
  }

  Statement의 인스턴스는 conn.createStatement() 시에 할당되어 지는 반면, 
  PreparedStatement는 conn.prepareStatement("..."); 시에 할당되어 집니다. 위의 경우에서
  설령 마지막 finally 절에서 pstmt.close() 를 하고 있기는 하지만, (1)번에서 할당되어진
  pstmt 는 (2)에서 엎어쳤기 때문에 영원히 close() 되지 않게 됩니다. 따라서, 다음과
  같이 코딩되어야 한다는 것은 자명합니다.

  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  try {
    conn = .....
    pstmt = conn.prepareStatement("select a from ..."); // ....(1)
    rs = pstmt.executeQuery();
    .....
    rs.close();
    pstmt.close(); // <------- !!!!! 이처럼 여기서 먼저 close() 해야지요.

    pstmt = conn.prepareStatement("select b from ..."); // <--- (2)
    rs = pstmt.executeQuery();
    ....
  }
  finally{
    if ( rs != null ) try {rs.close();}catch(Exception e){}
    if ( pstmt != null ) try{pstmt.close();}catch(Exception e){} // <--- (3)
    if ( conn != null ) ...<releaseConnection()>...;
  }

  혹은 다음과 같이 서로 다른 두개의 PreparedStatement를 이용할 수도 있습니다.
  
  Connection conn = null;
  PreparedStatement pstmt1 = null;
  ResultSet rs1 = null;
  PreparedStatement pstmt2 = null;
  ResultSet rs2 = null;
  try {
    conn = .....
    pstmt1 = conn.prepareStatement("select a from ..."); // ....(1)
    rs1 = pstmt1.executeQuery();
    .....
    pstmt2 = conn.prepareStatement("select b from ..."); // <--- (2)
    rs2 = pstmt2.executeQuery();
    ....
  }
  finally{
    if ( rs1 != null ) try {rs1.close();}catch(Exception e){}
    if ( pstmt1 != null ) try{pstmt1.close();}catch(Exception e){} // <--- (3)
    if ( rs2 != null ) try {rs2.close();}catch(Exception e){}
    if ( pstmt2 != null ) try{pstmt2.close();}catch(Exception e){} // <--- (4)
    if ( conn != null ) ...<releaseConnection()>...;
  }


 4.6.2 아래는 앞서의 4.6.1과 같은 맥락인데, for loop 안에서 사용된 경우입니다.

  Connection conn = null;
  PreparedStatement pstmt = null;
  try {
    conn = ...<getConnection()>...;
    for(int i=0;i<10;i++){
      pstmt = conn.prepareStatement("update .... ?... where id = ?"); //... (1)
      pstmt.setString(1,"xxxx");
      pstmt.setString(2,"id"+(i+1) );
      int affected = pstmt.executeUpdate();
      if ( affected == 0 ) throw new Exception("NoAffected");
      else if ( affedted > 1 ) throw new Exception("TooManyAffected");
    }
  }
  finally{
     if ( pstmt != null ) try {pstmt.close();}catch(Exception e){} // ...(2)
     if ( conn != null ) ...<releaseConnection()>...;
  }

  이 경우가 실제 프로젝트 performace 튜닝을 가 보면 종종 발견되는 잘못된
  유형 중의 하나입니다. 핵심은 pstmt.prepareStatement("update..."); 문장을
  수행할 때 마다 내부적으로 pstmt 가 새로 할당된다는 것에 있습니다.
  결국, (1)번 문장이 for loop 을 돌면서 9개는 pstmt.close()가 되지 않고, 마지막
  하나의 pstmt 만  finally 절의 (2)번에서 close 되지요...
  따라서 다음처럼 고쳐져야 합니다.

  Connection conn = null;
  PreparedStatement pstmt = null;
  try {
    conn = ...<getConnection()>...;
    pstmt = conn.prepareStatement("update .... ?... where id = ?");
    for(int i=0;i<10;i++){
      pstmt.clearParameters();      
      pstmt.setString(1,"xxxx");
      pstmt.setString(2,"id"+(i+1) );
      int affected = pstmt.executeUpdate();
      if ( affected == 0 ) throw new Exception("NoAffected");
      else if ( affedted > 1 ) throw new Exception("TooManyAffected");
    }
  }
  finally{
     if ( pstmt != null ) try {pstmt.close();}catch(Exception e){}
     if ( conn != null ) ...<releaseConnection()>...;
  }

  PreparedStatement 라는 것이, 한번 파싱하여 동일한 SQL문장을 곧바로 Execution할 수
  있는 장점이 있는 것이고, 궁극적으로 위와 같은 경우에 효과를 극대화 할 수 있는
  것이지요.

  어느 개발자의 소스에서는 위의 경우를 다음과 같이 for loop 안에서 매번
  conn.prepareStatement(...)를 하는 경우를 보았습니다.

  Connection conn = null;
  
  try {
    conn = ...<getConnection()>...;
    for(int i=0;i<10;i++) {
        PreparedStatement pstmt = null;
        try{
            pstmt = conn.prepareStatement("update .... ?... where id = ?"); 
            pstmt.clearParameters();      
            pstmt.setString(1,"xxxx");
            pstmt.setString(2,"id"+(i+1) );
            int affected = pstmt.executeUpdate();
            if ( affected == 0 ) throw new Exception("NoAffected");
            else if ( affedted > 1 ) throw new Exception("TooManyAffected");
        }
        finally{
            if ( pstmt != null ) try {pstmt.close();}catch(Exception e){}
        }
    }
  }
  finally{
     if ( conn != null ) ...<releaseConnection()>...;
  }

  위 경우는 장애관점에서 보면 사실 별 문제는 없습니다. 적어도 닫을 건 모두 잘 닫고
  있으니까요. 단지 효율성의 문제가 대두될 수 있을 뿐입니다.

 4.7 생각해 보면, Statement 나 PreparedStatement 가 close() 되지 않는 유형은
  여러가지가 있습니다. 그러나 열려진 Statement는 반드시 close()되어야 한다라는
  단순한 사실에 조금만 신경쓰시면 어떻게 프로그래밍이 되어야 하는지 쉽게
  감이 오실 겁니다. 단지 신경을 안쓰시니 문제가 되는 것이지요...

  Statement 를 닫지 않는 실수를 한 코드가 400-1000여개의 전 어플리케이션을 통털어
  한두군데에 숨어 있는 경우가 제일 찾아내기 어렵습니다. 한두번 Statement 를
  close 하지 않는다고 하여 곧바로 문제로 나타나지 않기 때문입니다.
  하루나 이틀, 혹은 며칠지나서야, Oracle 의 경우, "maximum open cursor exceed"
  에러를 내게 됩니다. oracle 의 경우, 위와 같은 에러를 conn.createStatement()시에
  발생하므로 (경우에 따라) 큰 문제로 이어지지는 않습니다. 찾아서 고치면 되니까요.

  반면, DB2 의 경우는 메모리가 허용하는 한도까지 지속적인 메모리 증가를 야기합니다.
  급기야 "핸들(handle)이 더이상 없습니다", "응용프로그램 힙(heap)에 명령문을 
  처리하기 위해 사용 가능한 저장영역이 충분하지 않습니다", "유효하지 않은 명령문
  핸들 또는 명령문이 닫혔습니다" 등과 같은 에러를 내지만, 여기서 그치는 것이 아니라
  새로운 connection 을 맺는 시점에 SIGSEGV 를 내면 crashing 이 일어납니다.
  java.lang.OutOfMemoryError 가 발생하기 때문이지요.

  Oracle 의 경우도 사실 상황에 따라 심각할 수 있습니다. 예를 들어, 어떤 개발자가
  "maximum open cursor exceed"라는 Oracle SQL 에러 메세지를 만나고는 자신이
  코딩을 잘못한 것은 염두에 두지 않고, 무조건 DBA에게 oraXXX.ini 파일에서
  OPEN_CURSORS 값을 올려달라고 요청하고, DBA는 그 조언(?)을 충실히 받아들여 
  Default 50 에서 이를 3000 으로 조정합니다. 여기서 문제는 깊숙히(?) 숨겨집니다.
  close() 안된 Statement 가 3000 회에 도달하지 전까진 아무도 문제를 인식하지
  못하기 때문이죠. 그러나, Statement가 하나씩 close()되지 않은 갯수에 비례하여
  Oracle 엔진의 메모리 사용은 자꾸만 증가하고, 전체적인 성능저하를 야기하지만, 
  이를 인식하기란 막상 시스템을 오픈하고 나서 3-4일이 지난 후에 "DB성능이 이상하네,
  응답속도가 느리네" 하면서 또 한번의 "maximum open cursor exceed" 메세지를
  확인하고 난 뒤의 일이 되곤 합니다.

  에러가 없는 정상적인 로직 flow 에서는 대부분 Statement가 잘 닫힐 겁니다. 그러나,
  어떤 이유에서건 아주 드물게 Runtime Exception 이 발생하여 exception throwing으로
  인해 "stmt.close()"를 타지 않는 경우가 제일 무섭지요. 정말 무섭지요...

  Statement, PreparedStatement, CallableStatement 모두 마찬가지 입니다.



5. close() 를 할 땐 제대로 해야 한다!!

 5.1 다음과 같은 프로그램 형식을 생각할 수 있습니다.

  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  try{
     conn = ...<getConnection()>...; //.......(1)
     stmt = conn.createStatement();    //.............(2)
     rs = stmt.executeQuery("select .....");  // .....(3)
     while(rs.next()){
       ......
     }
  }
  finally {
     try {
        rs.close(); //........(4)
        stmt.close(); //......(5)
        ...<releaseConneciton()>...; //......(6)
     }catch(Exception e){}
  }

  위에선 뭐가 잘못되었을까요? 다 제대로 한듯 한데....
  finally 절에서 rs, stmt, conn 을 null check 없이, 그리고 동일한 try{}catch 절로
  실행하고 있습니다.
  예를 들어, (1), (2) 번을 거치면서 conn 과 stmt 객체까지는 제대로 수행되었으나
  (3)번 Query문장을 수행하는 도중에 SQLException 이 발생할 수 있습니다. 그러면, 
  finally  절에서 (4) 번 rs.close()를 수행하려 합니다. 그러나, executeQuery()가
  실패 했기 때문에 rs 의 reference 는 null 이므로 rs.close() 시에 
  NullPointerException 이 발생합니다.  결국 정작 반드시 수행되어야할 (5)번, (6)번이
  실행되지 않습니다. 따라서 반드시(!) 다음과 같은 형식의 코딩이 되어야 합니다.

  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  try{
     conn = ...<getConnection()>...;
     stmt = conn.createStatement();
     rs = stmt.executeQuery("select .....");
     while(rs.next()){
       ......
     }
  }
  catch(Exception e){
     ....
  }
  finally {
     if ( rs != null ) try{rs.close();}catch(Exception e){}
     if ( stmt != null ) try{stmt.close();}catch(Exception e){}
     if ( conn != null ) ...<releaseConnection()>...;
  }

 같은 맥락으로 PreparedStatement 를 사용할 경우도, 다음과 같은 코딩은 잘못되었습니다.

  Connection conn = null;
  PreparedStatement pstmt = null;
  try{
     conn = ...<getConnection()>...; //.......(1)
     pstmt = conn.prepareStatement("ddelete from EMP where empno=7942"); //...(2)
     int k = pstmt.executeUpdate();  // .....(3)
     ......
  }
  finally {
     try {
        pstmt.close(); //......(4)
        ...<releaseConneciton()>...; //......(5)
     }catch(Exception e){}
  }
  왜냐면, SQL문장에 오타가 있었고, 이는 prepareStatement("ddelete..")시점에 에러가
  발생하여 pstmt 가 여전히 null 인 상태로 finally 절로 분기되기 때문인 것이죠.

 5.2.0 앞서 4.2에서도 언급했지만, java.sql.Statement의 executeQuery()에 의한 ResultSet은
  Statement 가 close 될때 자원이 같이 해제되므로 다음과 같이 하여도 그리 문제가 되진
  않습니다. 
     
  Connection conn = null;
  Statement stmt = null;
  //ResultSet rs = null;
  try{
     conn = ...<getConnection()>...;
     stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("select .....");
     while(rs.next()){
       ......
     }
     rs.close(); // <---- !!!
  }
  catch(Exception e){
     ....
  }
  finally {
     //if ( rs != null ) try{rs.close();}catch(Exception e){}
     if ( stmt != null ) try{stmt.close();}catch(Exception e){}
     if ( conn != null ) ...<releaseConnection()>...;
  }


5.2.1  그러나 PreparedStatement에 의한 ResultSet close()는 얘기가 다를 수 있습니다.
  (2002.06.11 추가 사항)
  
  많은 분들이, "아니 설령 ResultSet를 close하지 않았더라도 Statement/PreparedStatement를
  close하면 함께 ResultSet로 close되는 것 아니냐, JDBC 가이드에서도 그렇다고
  나와 있다, 무슨 개뿔같은 소리냐?" 라구요.

  그러나, 한가지 더 이해하셔야 할 부분은, 웹스피어, 웹로직과 같은 상용 웹어플리케이션
  서버들은 성능향상을 위해 PreparedStatement 및 심지어 Statement에 대한 캐싱기능을
  제공하고  있습니다. pstmt.close() 를 하였다고 하여 정말 해당 PreparedStatement가
  close되는 것이 아니라, 해당 PreparedeStatement가 생겨난 java.sql.Connection당 지정된
  개수까지 웹어플리케이션서버 내부에서 reference가 사라지지 않고 캐시로 남아 있게 됩니다.
  결국, 연관된 ResultSet 역시 close()가 되지 않은 채 남아있게 되는 것이지요. 명시적인
  rs.close()를 타지 않으면, 웹어플리케이션서버의 JVM내부 힙영역 뿐만아니라, 쿼리의 결과로
  데이타베이스에서 임시로 만들어진 메모리데이타가 사라지지 않는 결과를 낳게 됩니다. 
  특히 ResultSet이 닫히지 않으면 DB에서의 CURSOR가 사라지지 않습니다. 
  또한, CURSOR를 자동으로 닫거나 닫지 않는 등의 옵션은 WAS마다 WAS의 DataSource설정에서
  CORSOR 핸들링에 대한 별도의 옵션을 제공하게 되며 특히 아래 [항목6]에서 다시 언급할
  nested sql query 처리시에 민감한 반응을 하게 됩니다.

  따라서, 앞서의 코딩 방법을 반드시 다음처럼 ResultSet도 close하시기를 다시금 정정하여
  권장 드립니다.

  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null; // <---- !!!
  try{
     conn = ...<getConnection()>...;
     pstmt = conn.prepareStatement("select .....");
     rs = pstmt.executeQuery(); // <----- !!!
     while(rs.next()){
       ......
     }
     //rs.close(); // <---- !!!
  }
  catch(Exception e){
     ....
  }
  finally {
     if ( rs != null ) try{rs.close();}catch(Exception e){} // <---- !!!
     if ( pstmt != null ) try{pstmt.close();}catch(Exception e){}
     if ( conn != null ) ...<releaseConnection()>...;
  }

  PS: 웹어플리케이션서버에서의 PreparedStatement 캐싱기능에 관한 부분은 아래의 글들을
  참조하세요.
  Connection Pool & PreparedStatement Cache Size
  http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=995572195
  WebLogic에서의 PreparedStatement Cache -서정희-
  http://www.javaservice.net/~java/bbs/read.cgi?m=dbms&b=jdbc&c=r_p&n=1023286823



 5.3 간혹, 다음과 같은 코딩은 문제를 야기하지 않을 것이라고 생각하는 분들이 많습니다.

 finally{
    try{
      if ( stmt != null ) stmt.close();
      if ( conn != null ) conn.close();
    }catch(Exception e){}
 }
 저명한 많은 책에서도 위처럼 코딩되어 있는 경우를 종종봅니다. 맞습니다. 특별히 문제성이
 있어보이지는 않습니다. 그러나, 간혹 웹어플리케이션서버의 Connection Pool과 연계하여
 사용할 땐 때론 문제가 될 때도 있습니다. 즉, 만약, stmt.close()시에 Exception이 throw
 되면 어떻게 되겠습니까? 
 아니 무슨 null 체크까지 했는데, 무슨 Exception이 발생하느냐고 반문할 수도 있지만,
 오랜 시간이 걸리는 SQL JOB에 빠져 있다가 Connection Pool의 "Orphan Timeout"이 지나
 자동으로 해당 Connection을 Pool에 돌려보내거나 혹은 특별한 marking처리를 해 둘 수
 있습니다. 이 경우라면 stmt.close()시에 해당 웹어플리케이션서버에 특화된 Exception이
 발생하게 됩니다. 그렇게 되면 conn.close()를 타지 못하게 되는 사태가 벌어집니다.
 따라서, 앞서 하라고 권장한 형식으로 코딩하세요.


6. Nested (Statemet) SQL Query Issue !!

 6.1 아래와 같은 코딩을 생각해 보겠습니다.

  Connection conn = null;
  Statement stmt = null;
  try{
     conn = ...<getConnection()>...;
     stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("select deptno ...where id ='"+ id +"'");
     while(rs.next()){
       String deptno = rs.getString("deptno");
       stmt.executeUpdate(
           "update set dept_name = ... where deptno = '"+ deptno +"'"
       );
       ......
     }
     rs.close();
  }
  catch(Exception e){
     ....
  }
  finally {
     if ( stmt != null ) try{stmt.close();}catch(Exception e){}
     if ( conn != null ) ...<releaseConnection()>...;
  }

  위 코드는 사실 실행하자 말자 다음과 같은 에러를 만나게 됩니다.

  DB2 : -99999: [IBM][CLI Driver] CLI0115E 커서 상태가 유효하지 않습니다. 
        SQLSTATE=24000 
  Oracle : 

  에러는 두번째 while(rs.next()) 시에 발생합니다. 왜냐면, Statement가 nested하게
  실행된 executeUpdate() 에 의해 엎어쳐 버렸기 때문입니다. ResultSet 은 
  Statement와 밀접하게 관련이 있습니다. ResultSet은 자료를 저장하고 있는 객체가
  아니라, 데이타베이스와 step by step으로 상호 통신하는 Interface 일 뿐이기 때문입니다.
  따라서 위 코드는 다음처럼 바뀌어져야 합니다.

  Connection conn = null;
  Statement stmt1 = null;
  Statement stmt2 = null;
  try{
     conn = ...<getConnection()>...;
     stmt1 = conn.createStatement();
     stmt2 = conn.createStatement();
     ResultSet rs = stmt1.executeQuery("select deptno ...where id ='"+ id +"'");
     while(rs.next()){
       String deptno = rs.getString("deptno");
       stmt2.executeUpdate(
           "update set dept_name = ... where deptno = '"+ deptno +"'"
       );
       ......
     }
     rs.close();
  }
  catch(Exception e){
     ....
  }
  finally {
     if ( stmt1 != null ) try{stmt1.close();}catch(Exception e){}
     if ( stmt2 != null ) try{stmt2.close();}catch(Exception e){}
     if ( conn != null ) ...<releaseConnection()>...;
  }

  즉, ResultSet에 대한 fetch 작업이 아직 남아 있는 상태에서 관련된 Statement를
  또다시 executeQuery()/executeUpdate() 를 하면 안된다는 것입니다.

  PS: IBM WebSphere 환경일 경우, 아래의 글들을 추가로 확인하시기 바랍니다.
  349   Re: Function sequence error  (Version 3.x)
  http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=991154615
  486   WAS4.0x: Function sequence error 해결  
  http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=1015345459



7. executeUpdate() 의 결과를 비즈니스 로직에 맞게 적절히 활용하라.

 7.1 아래와 같은 코딩을 생각해 보겠습니다.

  public void someMethod(String empno) throws Exception {
      Connection conn = null;
      Statement stmt = null;
      try{
         conn = ...<getConnection()>...;
         stmt = conn.createStatement();
         stmt.executeUpdate(
             "UPDATE emp SET name='이원영' WHERE empno = '" + empno + "'"
         );
      }
      finally {
         if ( stmt != null ) try{stmt.close();}catch(Exception e){}
         if ( conn != null ) ...<releaseConnection()>...;
      }
  }

  사실 흔히들 이렇게 하십니다. 별로 잘못된 것도 없어보입니다. 근데, 만약 
  DB TABLE에 해당 empno 값이 없으면 어떻게 될까요? SQLException 이 발생
  하나요? 그렇지 않죠? 아무런 에러없이 그냥 흘러 내려 옵니다. 그러면, Update를
  하러 들어 왔는데, DB에 Update할 것이 없었다면 어떻게 해야 합니까? 그냥 무시하면
  되나요? 안되죠..  따라서, 다음 처럼, 이러한 상황을 이 메소드를 부른 곳으로
  알려 줘야 합니다.

  public void someMethod(String empno) throws Exception {
      Connection conn = null;
      Statement stmt = null;
      try{
         conn = ...<getConnection()>...;
         stmt = conn.createStatement();
         int affected = stmt.executeUpdate(
             "UPDATE emp SET name='이원영' WHERE empno = '" + empno + "'"
         );
         if ( affected == 0 ) throw new Exception("NoAffectedException");
         else if ( affected > 1 ) throw new Exception("TooManyAffectedException");
      }
      finally {
         if ( stmt != null ) try{stmt.close();}catch(Exception e){}
         if ( conn != null ) ...<releaseConnection()>...;
      }
  }

  물론 이러한 부분들은 해당 비즈니스로직이 뭐냐에 따라서 다릅니다. 그것을 무시케
  하는 것이 비즈니스 로직이었다면 그냥 무시하시면 되지만, MIS 성 어플리케이션의
  대부분은 이처럼 update 나 delete 쿼리의 결과에 따라 적절한 처리를 해 주어야
  할 것입니다.



8. Transaction 처리를 할 땐 세심하게 해야 한다.

 단, 아래는 웹어플리케이션서버의 JTA(Java Transaction API)기반의 트렌젝션처리가 아닌,
 java.sql.Connection에서의 명시적인 conn.setAutoCommit(false) 모드를 통한 트렌젝션처리에
 대해서 언급 하고 있습니다.

 8.1 Non-XA JDBC Transaction(명시적인 java.sql.Connection/commit/rollback)

  Connection conn = null;
  Statement stmt = null;
  try{
     conn = ...<getConnection()>...;
     stmt = conn.createStatement();
     stmt.executeUpdate("UPDATE ...."); // -------- (1)
     stmt.executeUpdate("DELETE ...."); // -------- (2)
     stmt.executeUpdate("INSERT ...."); // -------- (3)
  }
  finally {
     if ( stmt != null ) try{stmt.close();}catch(Exception e){}
     if ( conn != null ) ...<releaseConnection()>...;
  }

  위와 같은 코딩은 아무리 비즈니스적 요구가 간소하더라도, 실 프로젝트에서는
  있을 수가 없는 코딩입니다.(JTS/JTA가 아닌 명시적인 Transaction 처리시에..)

  다들 아시겠지만, (1), (2) 번까지는 정상적으로 잘 수행되었는데, (3)번문장을
  수행하면서 SQLException 이 발생하면 어떻게 되나요? (1),(2)은 이미 DB에 반영된
  채로 남아 있게 됩니다. 대부분의 비즈니스로직은 그렇지 않았을 겁니다.
  
  "(1),(2),(3)번이 모두 정상적으로 수행되거나, 하나라도 잘못되면(?) 모두 취소
  되어야 한다" 
  
  가 일반적인 비즈니스적 요구사항이었을 겁니다. 따라서, 다음처럼 코딩 되어야
  합니다.

  Connection conn = null;
  Statement stmt = null;
  try{
     conn = ...<getConnection()>...;
     conn.setAutoCommit(false);
     stmt = conn.createStatement();
     stmt.executeUpdate("UPDATE ...."); // -------- (1)
     stmt.executeUpdate("DELETE ...."); // -------- (2)
     stmt.executeUpdate("INSERT ...."); // -------- (3)

     conn.commit(); // <-- 반드시 try{} 블럭의 마지막에 와야 합니다.
  }
  catch(Exception e){
     if ( conn != null ) try{conn.rollback();}catch(Exception ee){}
     // error handling if you want
     
     throw e;  // <--- 필요한 경우, 호출한 곳으로 Exception상황을 알려줄
               //      수도 있습니다
  }
  finally {
     if ( stmt != null ) try{stmt.close();}catch(Exception e){}
     // in some connection pool, you have to reset commit mode to "true"
     if ( conn != null ) ...<releaseConnection()>...;
  }


 8.2 auto commit mode 를 "false"로 셋팅하여 명시적인 Transaction 관리를 할 때,
  정말 조심해야 할 부분이 있습니다. 예를 들면 다음과 같은 경우입니다.

  Connection conn = null;
  Statement stmt = null;
  try{
     conn = ...<getConnection()>...;
     conn.setAutoCommit(false);
     stmt = conn.createStatement();
     stmt.executeUpdate("UPDATE ...."); // ----------------------- (1)
     ResultSet rs = stmt.executeQuery("SELECT ename ..."); // ---- (2)
     if ( rs.next() ) {
        conn.commit(); // ------------------- (3)
     }
     else {
        conn.rollback(); // ----------------- (4)
     }
  }
  finally {
     if ( rs != null ) try{rs.close();}catch(Exception e){}
     if ( stmt != null ) try{stmt.close();}catch(Exception e){}
     // in some connection pool, you have to reset commit mode to "true"
     if ( conn != null ) ...<releaseConnection()>...;
  }

  코드가 왜 위처럼 됐는지는 저도 모르겠습니다.  단지 비즈니스가 그러했나보죠..

  문제는 만약, (2)번 executeQuery()문장을 수행하면서 SQLException 이나 기타의
  RuntimeException 이 발생할 때 입니다.
  commit() 이나 rollback()을 타지 않고, finally 절로 건너 뛰어 Statement를
  닫고, connection 은 반환됩니다. 이때, commit() 이나 rollback()이 되지 않은채
  (1)번 UPDATE 문이 수행된채로 남아 있게 됩니다.  이는 DB LOCK을 점유하게
  되고, 경우에 따라 다르겠지만, 다음번 요청시에 DB LOCK으로 인한 hang현상을
  초래할 수도 있습니다.
  일단 한두개의 어플리케이션이 어떠한 이유였든, DB Lock 을 발생시키면, 해당
  DB에 관련된 어플리케이션들이 전부 응답이 없게 됩니다. 이 상황이 조금만
  지속되면, 해당 waiting 을 유발하는 요청들이 어플리케이션서버의 최대 동시
  접속처리수치에 도달하게 되고, 이는 전체 시스템의 hang현상으로 이어지게
  되는 것이죠..


  따라서, 비즈니스 로직이 어떠했든, 반드시 지켜져야할 사항은 다음과 같습니다.

  "conn.setAutoComm(false); 상태에서 한번 실행된 Update성 SQL Query는 이유를
   막론하고 어떠한 경우에도 commit() 이나 rollback() 되어야 한다."

  위의 경우라면 다음처럼 catch 절에서 rollback 시켜주는 부분이 첨가되면 되겠네요.

  Connection conn = null;
  Statement stmt = null;
  try{
     conn = ...<getConnection()>...;
     conn.setAutoCommit(false);
     stmt = conn.createStatement();
     stmt.executeUpdate("UPDATE ...."); 
     ResultSet rs = stmt.executeQuery("SELECT ename ...");
     if ( rs.next() ) {
        conn.commit();
     }
     else {
        conn.rollback();
     }
  }
  catch(Exception e){  // <---- !!!!!
     if ( conn != null ) try{conn.rollback();}catch(Exception ee){}
     throw e;
  }
  finally {
     if ( rs != null ) try{rs.close();}catch(Exception e){}
     if ( stmt != null ) try{stmt.close();}catch(Exception e){}
     // in some connection pool, you have to reset commit mode to "true"
     if ( conn != null ) ...<releaseConnection()>...;
  }


 8.3 모든 경우의 수를 생각하라.

  다음과 같은 경우를 생각해 보겠습니다.

  Connection conn = null;
  Statement stmt = null;
  try{
     conn = ...<getConnection()>...;
     conn.setAutoCommit(false);
     stmt = conn.createStatement();
     stmt.executeUpdate("UPDATE ...."); 
     String idx = name.substring(3);
     ResultSet rs = stmt.executeQuery("SELECT ename ... where idx=" + idx);
     if ( rs.next() ) {
        .....
     }
     rs.close(); rs = null;

     stmt.executeUpdate("UPDATE ...."); 
     conn.commit();
  }
  catch(SQLException e){ 
     if ( conn != null ) try{conn.rollback();}catch(Exception ee){}
     throw e;
  }
  finally {
     if ( rs != null ) try{rs.close();}catch(Exception e){}
     if ( stmt != null ) try{stmt.close();}catch(Exception e){}
     // in some connection pool, you have to reset commit mode to "true"
     if ( conn != null ) ...<releaseConnection()>...;
  }

  잘 찾아 보세요. 어디가 잘못되었습니까? 잘 안보이시죠?

  Connection conn = null;
  Statement stmt = null;
  try{
     conn = ...<getConnection()>...;
     conn.setAutoCommit(false);
     stmt = conn.createStatement();
     stmt.executeUpdate("UPDATE ...."); //---- (1)
     String idx = name.substring(3); //<------ (2) NullPointerExceptoin ?
     ResultSet rs = stmt.executeQuery("SELECT ename ... where idx=" + idx);
     if ( rs.next() ) {
        .....
     }
     rs.close(); rs = null;

     stmt.executeUpdate("UPDATE ...."); 
     conn.commit();
  }
  catch(SQLException e){ //<------ (3) 이 부분을 탈까?
     if ( conn != null ) try{conn.rollback();}catch(Exception ee){}
     throw e;
  }
  finally {
     if ( rs != null ) try{rs.close();}catch(Exception e){}
     if ( stmt != null ) try{stmt.close();}catch(Exception e){}
     // in some connection pool, you have to reset commit mode to "true"
     if ( conn != null ) ...<releaseConnection()>...;
  }

  위 코드를 보듯, 만약 (1)을 수행 후 (2)번 에서 NullPointerException 이나 
  ArrayIndexOutOfBoundException이라도 나면 어떻게 되죠? catch(SQLException ...)에는
  걸리지 않고 곧바로 finally 절로 건너띄어 버리네요. 그럼 (1)에서 update 된 것은
  commit()이나 rollback() 없이 connection 이 반환되네요... ;-)
  어떻게 해야 합니까? SQLException만 잡아서 되는 것이 아니라, catch(Exception ...)과
  같이 모든 Exception 을 catch해 주어야 합니다.


 8.4 위 주석문에서도 언급해 두었지만, Hans Bergsteins 의 DBConnectionManager.java
  와 같은 Connection Pool 을 사용할 경우에, 개발자의 코드에서 transaction auto
  commit mode 를 명시적으로 "false"로 한 후, 이를 그냥 pool 에 반환하시면,
  그 다음 사용자가 pool 에서 그 connection 을 사용할 경우, 여전히 transaction
  mode 가 "false"가 된다는 것은 주지하셔야 합니다. 따라서, DBConnectionManger의
  release method를 수정하시든지, 혹은 개발자가 명시적으로 초기화한 후 pool 에
  반환하셔야 합니다. 그렇지 않을 경우, DB Lock 이 발생할 수 있습니다.
  반면, IBM WebSphere 나 BEA WebLogic 과 같인 JDBC 2.0 스펙에 준하는 Connection
  Pool을 사용할 경우는 반환할 당시의 transaction mode 가 무엇이었든 간에,
  pool 에서 꺼내오는 connection 의 transaction mode는 항상 일정합니다.
  (default 값은 엔진의 설정에 따라 달라집니다.)
  그렇다고 commit 시키지 않은 실행된 쿼리가 자동으로 commit/rollback 되는 것은
  아닙니다. 단지 auto commit 모드만 자동으로 초기화 될 뿐입니다.

  PS:WAS의 JTS/JTA 트렌젝션 기반으로 운영될 경우는 명시적으로 commit/rollback되지
   않은 트렌젝션은 자동으로 rollback되거나 commit됩니다. default는 WAS 설정에 따라
   다를 수 있습니다.

  ---------------
  NOTE: 자바서비스컨설팅의 WAS성능관리/모니터링 툴인 제니퍼(Jennifer 2.0)를 적용하면,
  어플리케이션에서 명시적으로 commit/rollback시키지 않고 그대로 반환시킨 어플리케이션의
  소스 위치를 실시간으로 감지하여 알려줍니다. 이를 만약 수작업으로 한다면, 수많은 코드
  중 어디에서 DB lock을 유발 시키는 코드가 숨어있는지를 찾기가 경우에 따라 만만치 않은
  경우가 많습니다.

8.5 XA JDBC Driver, J2EE JTS/JTA
  JDBC 2.0, 표준 javax.sql.DataSource를 통한 JDBC Connection을 사용할 경우에,
  대부분의 상용WAS제품들은 J2EE의 표준 JTS(Java Transaction Service)/JTA(Java Transaction
  API)를 구현하고 있습니다. 특별히, 하나 이상의 데이타베이스에서 2 phase-commit과
  같은 XA Protocol를 지원하고 있지요(사실 WAS에서 2PC가 지원되기 시작한 것은 몇년
  되지 않습니다. 2PC를 사용하려면 반드시 XA-JDBC Driver가 WAS에 설치되어야 합니다)

  샘플 예제는 다음과 같습니다.

    ...
    javax.transaction.UserTransaction tx = null;
    java.sql.Connection conn1 = null;
    java.sql.Statement stmt1 = null;

    java.sql.Connection conn2 = null;
    java.sql.Statement stmt2 = null;
    java.sql.CallableStatement cstmt2 = null;
    
    try {
        javax.naming.InitialContext ctx = new javax.naming.InitialContext();
        tx = (javax.transaction.UserTransaction) ctx.lookup("java:comp/UserTransaction");
        // 트렌젝션 시작
        tx.begin();

        // -------------------------------------------------------------------------
        // A. UDB DB2 7.2 2PC(XA) Test
        // -------------------------------------------------------------------------
        javax.sql.DataSource ds1 = 
            (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/DB2");
        conn1 = ds1.getConnection();

        stmt1 = conn1.createStatement();
        stmt1.executeUpdate(
            "insert into emp(empno,ename) values(" + empno + ",'Name" + empno + "')"
        );
        stmt1.executeUpdate(
            "update emp set ename = 'LWY" + count + "' where empno = 7934"
        );
        java.sql.ResultSet rs1 = stmt1.executeQuery("select empno,ename from emp");
        while(rs1.next()){
            ...
        }
        rs1.close();
        
        // -------------------------------------------------------------------------
        // B. Oracle 8.1.7 2PC(XA) Test
        // -------------------------------------------------------------------------
        javax.sql.DataSource ds2 =
            (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/ORA8i");
        conn2 = ds2.getConnection();
        
        stmt2 = conn2.createStatement();
        stmt2.executeUpdate(
            "update emp set ename = 'LWY" + count + "' where empno = 7934"
        );
        java.sql.ResultSet rs2 = stmt2.executeQuery("select empno,ename from emp");
        while(rs2.next()){
            ...
        }
        rs2.close();


        // -------------------------------------------------------------------------
        // 트렌젝션 commit
        tx.commit();
    }
    catch(Exception e){
        // 트렌젝션 rollback
        if ( tx != null ) try{tx.rollback();}catch(Exception ee){}
        ...
    }
    finally {
        if ( stmt1 != null ) try { stmt1.close();}catch(Exception e){}
        if ( conn1 != null ) try { conn1.close();}catch(Exception e){}

        if ( stmt2 != null ) try { stmt2.close();}catch(Exception e){}
        if ( conn2 != null ) try { conn2.close();}catch(Exception e){}
    }

  

NOTE: 위에서 설명한 하나하나가 제 입장에서 보면 너무나 가슴깊이 다가오는 
  문제들입니다. 개발하시는 분의 입장에서 보면, 위의 가이드에 조금 어긋났다고
  뭐그리 문제겠느냐고 반문하실 수 있지만, 수백본의 소스코드 중에 위와 같은 규칙을
  준수하지 않은 코드가  단 하나라도 있다면, 잘 운영되던 시스템이 며칠에 한번씩
  에러를 야기하거나 응답이 느려지고 급기야 hang 현상을 초래하는 결과를 가져 옵니다.
  정말(!) 그렇습니다.

NOTE: 위에서 사용한 코딩 샘플들은 JDBC Connection Pooling 은 전혀 고려치 않고
  설명드렸습니다. 그러했기 때문에 <getConnection()>, <releaseConnection()> 이란
  Pseudo 코드로 설명했던 것입니다.
  반드시 "서블렛 + JDBC 연동시 코딩 고려사항 -제2탄-" 을 읽어 보세요.
  http://www.javaservice.net/~java/bbs/read.cgi?m=devtip&b=servlet&c=r_p&n=968522077


-------------------------------------------------------  
  본 문서는 자유롭게 배포/복사 할 수 있으나 반드시
  이 문서의 저자에 대한 언급을 삭제하시면 안됩니다
================================================
  자바서비스넷 이원영
  E-mail: javaservice@hanmail.net
  PCS:010-6239-6498
================================================