Death Note

아래의 코드를 사용하면 jsp페이지에서 statement및 connection을

혹시 닫는것을 잊어도 별문제 없습니다.

사용해보시고 리플달아주세요.

import java.sql.*;
/**
시스템에 떠도는 커넥션 및 스테이트먼트 닫아주기
*/
public class Db {   
   private static boolean initialized = false;
   private static Object lock = new Object();
public static final java.util.Vector usecons = new java.util.Vector(10,10);
private static Db db = null;
private static BadConKillerThread killerThread = null;
public Db(){
 synchronized(lock){
  if(initialized == false){   
   initialized = true;
   if(killerThread==null){
    killerThread = new BadConKillerThread();
    killerThread.start();
   }      
  }
 }   
}
/**
* 5분마다 혹시 오래된 유령커넥션이 있는지 체크해서 제거한다.
*/
static class BadConKillerThread extends Thread{
 static boolean ever = true;
 static int sleep_time = 60000;//1분
 static long con_alive_time = 120000;//2분
 static long now_time;
 public void run(){
  while(ever){
   try{
    Thread.sleep(sleep_time);
   }catch(Exception e){}
   now_time = System.currentTimeMillis();
   for(java.util.Enumeration enum=usecons.elements();enum.hasMoreElements();){
    MVConnection con = (MVConnection)enum.nextElement();
    if(now_time-con.create_time>con_alive_time){
     try{
     con.close();
     }catch(Exception ee){}
     //2분 경과한 커넥션을 대여 커넥션으로 부터 삭제한다.
     //이때 연결된 statement도 모두 끊고 커넥션도 닫히게 된다.
    }
   }
  }
 }
};
//이메소를 호출해서 객체를 얻어옵니다.
public static Db getInstance(){
 if(db == null){
  return db= new Db();
 }
 return db;
}
public static java.sql.Connection getConnection() throws java.sql.SQLException {
 //DBPool.getConnection은 여러분의 시스템에 맞게 고치세요.
 java.sql.Connection con = new MVConnection(DBPool.getConnection());
 usecons.add(con);//대여 컨넥션풀에 MVConnection저장
 return con;  
}

}
//최범균 님의 MVConnection
public class MVConnection implements Connection {
      
       private Connection conn; // 실제 커넥션
      
       private java.util.List statementList; // statement를 저장

 

       public long create_time;
      
       public MVConnection(Connection conn) {
           this.conn = conn;
           statementList = new java.util.ArrayList();

           create_time = System.currentTimeMillis();
       }
      
       public void closeAll() {
           for (int i = 0 ; i < statementList.size() ; i++) {
               Statement stmt = (Statement)statementList.get(i);
               try {
                   stmt.close();
               } catch(SQLException ex) {}
           }
       }
      
       public void close() throws SQLException {
           this.closeAll();//모든 statement닫기
           conn.close();//커넥션 닫거나 풀에 되돌리기
  Db.usecons.removeElement(this);//대여 컨네션풀로부터 MVConnection을 삭제
       }
      
       public Statement createStatement() throws SQLException {
           Statement stmt = conn.createStatement();
           statementList.add(stmt);
           return stmt;
       }
      
       public CallableStatement prepareCall(String sql) throws SQLException {
           CallableStatement cstmt = conn.prepareCall(sql);
           statementList.add(cstmt);
           return cstmt;
       }
      
       public PreparedStatement prepareStatement(String sql) throws SQLException {
           PreparedStatement pstmt = conn.prepareStatement(sql);
           statementList.add(pstmt);
           return pstmt;
       }
       public void clearWarnings() throws SQLException{conn.clearWarnings();}
 //void close()  
 public void commit() throws SQLException {conn.commit();}          
 //Statement createStatement()        
 public Statement createStatement(int resultSetType, int resultSetConcurrency)  throws SQLException{return conn.createStatement(resultSetType,resultSetConcurrency);}         
 public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)  throws SQLException{return conn.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability);}
 public boolean getAutoCommit() throws SQLException{return conn.getAutoCommit();}
 public String getCatalog()  throws SQLException{return conn.getCatalog();}
 public int getHoldability() throws SQLException{return conn.getHoldability();}
 public DatabaseMetaData getMetaData()  throws SQLException {return conn.getMetaData();}
 public int getTransactionIsolation() throws SQLException {return conn.getTransactionIsolation();}
 public java.util.Map getTypeMap() throws SQLException {return conn.getTypeMap();}
 public SQLWarning getWarnings() throws SQLException {return conn.getWarnings();}
 public boolean isClosed() throws SQLException {return conn.isClosed();}
 public boolean isReadOnly() throws SQLException{return conn.isReadOnly();}
 public String nativeSQL(String sql) throws SQLException {return conn.nativeSQL(sql);}
 //CallableStatement prepareCall(String sql)
 public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency)  throws SQLException {return conn.prepareCall(sql,resultSetType,resultSetConcurrency);}
 public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return conn.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability);}
 //PreparedStatement prepareStatement(String sql)
 public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {return conn.prepareStatement(sql,autoGeneratedKeys);}
 public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {return conn.prepareStatement(sql,columnIndexes);}
 public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {return conn.prepareStatement(sql,resultSetType,resultSetConcurrency);}
 public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return conn.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability);}
 public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {return conn.prepareStatement(sql,columnNames);}
 public void releaseSavepoint(Savepoint savepoint) throws SQLException {conn.releaseSavepoint(savepoint);}
 public void rollback() throws SQLException {conn.rollback();}
 public void rollback(Savepoint savepoint) throws SQLException {conn.rollback(savepoint);}
 public void setAutoCommit(boolean autoCommit) throws SQLException {conn.setAutoCommit(autoCommit);}
 public void setCatalog(String catalog) throws SQLException {conn.setCatalog(catalog);}
 public void setHoldability(int holdability) throws SQLException {conn.setHoldability(holdability);}
 public void setReadOnly(boolean readOnly) throws SQLException {conn.setReadOnly(readOnly);}
 public Savepoint setSavepoint() throws SQLException {return conn.setSavepoint();}
 public Savepoint setSavepoint(String name) throws SQLException {return conn.setSavepoint(name);}
 public void setTransactionIsolation(int level) throws SQLException {conn.setTransactionIsolation(level);}
 public void setTypeMap(java.util.Map map) throws SQLException {conn.setTypeMap(map);}
     
}