DataBase connection pool 기법

데이터베이스 접속과 해제시 속도 저하 문제를 해결하기 위한 기법으로

데이터베이스 커넥션 풀 매니저가 어느 정도 데이터베이스 서버에 접속을 확보하고 있다가

클라이언트 요청 있을 때 연결해 주고, 클라이언트의 요청이 종료되면 연결을 커넥션 풀매니저에게 반환한다

DBConnectionManager 클래스 패키지를 많이 사용하며, 데이터베이스 성능을 높일 수 있다

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252

package oracle.dbpool;   import java.io.*; import java.sql.*; import java.util.*; import java.util.Date;   public class DBConnectionManager {     static private DBConnectionManager instance;       // 싱글 인스턴스     static private int clients;       private Vector drivers = new Vector();     private PrintWriter log;     private Hashtable pools = new Hashtable();       // DB 연결 인스턴스     static synchronized public DBConnectionManager getInstance() {         if (instance == null) {             instance = new DBConnectionManager();         }         clients++;         return instance;     }       // 생성자     private DBConnectionManager() {         init();     }           // DB 연결 해제     public void freeConnection(String name, Connection con) {         DBConnectionPool pool = (DBConnectionPool) pools.get(name);         if (pool != null) {             pool.freeConnection(con);         }     }       // DB 연결 설정 메소드     public Connection getConnection(String name) {         DBConnectionPool pool = (DBConnectionPool) pools.get(name);         if (pool != null) {             return pool.getConnection();         }         return null;     }           public synchronized void release() {         // 마지막 접속자 리턴시까지 기다림         if (--clients != 0) {             return;         }           Enumeration allPools = pools.elements();         while (allPools.hasMoreElements()) {             DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();             pool.release();         }         Enumeration allDrivers = drivers.elements();         while (allDrivers.hasMoreElements()) {             Driver driver = (Driver) allDrivers.nextElement();             try {                 DriverManager.deregisterDriver(driver);                 log("Deregistered JDBC driver " + driver.getClass().getName());             } catch (SQLException e) {                 log(e, "Can't deregister JDBC driver: " + driver.getClass().getName());             }         }     }           // @param 커넥션 풀 옵션     private void createPools(Properties props) {         Enumeration propNames = props.propertyNames();         while (propNames.hasMoreElements()) {             String name = (String) propNames.nextElement();             if (name.endsWith(".url")) {                 String poolName = name.substring(0, name.lastIndexOf("."));                 String url = props.getProperty(poolName + ".url");                 if (url == null) {                     log("No URL specified for " + poolName);                     continue;                 }                 String user = props.getProperty(poolName + ".user", "stud145");                 String password = props.getProperty(poolName + ".password", "pass145");                 String maxconn = props.getProperty(poolName + ".maxconn", "0");                 int max;                 try {                     max = Integer.valueOf(maxconn).intValue();                 } catch (NumberFormatException e) {                     log("Invalid maxconn value " + maxconn + " for " + poolName);                     max = 0;                 }                 DBConnectionPool pool = new DBConnectionPool(poolName, url, user, password, max);                 pools.put(poolName, pool);                 log("Initialized pool " + poolName);             }         }     }           // 초기화 파일 읽어오기     private void init() {         InputStream is = getClass().getResourceAsStream("db.properties");         Properties dbProps = new Properties();         try {             dbProps.load(is);         }   catch (Exception e) {             System.err.println("Can't read the properties file. " +                 "Make sure db.properties is in the CLASSPATH");             return;         }         String logFile = dbProps.getProperty("logfile", "DBConnectionManager.log");         try {             log = new PrintWriter(new FileWriter(logFile, true), true);         } catch (IOException e) {             System.err.println("Can't open the log file: " + logFile);             log = new PrintWriter(System.err);         }         loadDrivers(dbProps);         createPools(dbProps);     }           // 데이터베이스 드라이버 읽어 오기     private void loadDrivers(Properties props) {         String driverClasses = props.getProperty("drivers", "oracle.jdbc.driver.OracleDriver");         StringTokenizer st = new StringTokenizer(driverClasses);         while (st.hasMoreElements()) {             String driverClassName = st.nextToken().trim();             try {                 Driver driver = (Driver)                     Class.forName(driverClassName).newInstance();                 DriverManager.registerDriver(driver);                 drivers.addElement(driver);                 log("Registered JDBC driver " + driverClassName);             } catch (Exception e) {                 log("Can't register JDBC driver: " + driverClassName + ", Exception: " + e);             }         }     }           // 로그 파일에 기록 남기기     private void log(String msg) {         log.println(new Date() + ": " + msg);     }           // 로그파일에 시간 기록 남기기     private void log(Throwable e, String msg) {         log.println(new Date() + ": " + msg);         e.printStackTrace(log);     }           // DBConnectionPool 클래스     class DBConnectionPool {         private int checkedOut;         private Vector freeConnections = new Vector();         private int maxConn;         private String name;         private String password;         private String URL;         private String user;           /**          * connection pool. 생성하기          * @param name 풀 이름          * @param URL JDBC URL 옵션 설정          * @param user 데이터베이스 사용 계정          * @param password 데이터베이스 사용 암호          * @param maxConn  최대 DB POOL 갯수, 0일경우 무한대          */         public DBConnectionPool(String name, String URL, String user, String password, int maxConn) {             this.name = name;             this.URL = URL;             this.user = user;             this.password = password;             this.maxConn = maxConn;         }                   // @param con 연결 상태 체크         public synchronized void freeConnection(Connection con) {             freeConnections.addElement(con);             checkedOut--;             notifyAll();         }           public synchronized Connection getConnection() {             Connection con = null;             if (freeConnections.size() > 0) {                   con = (Connection) freeConnections.firstElement();                 freeConnections.removeElementAt(0);                 try {                     if (con.isClosed()) {                         log("Removed bad connection from " + name);                         con = getConnection();                     }                 } catch (SQLException e) {                     log("Removed bad connection from " + name);                     con = getConnection();                 }             }  else if (maxConn == 0 || checkedOut < maxConn) {                     con = newConnection();                 }             if (con != null) {                 checkedOut++;             }             return con;         }                   public synchronized Connection getConnection(long timeout) {             long startTime = new Date().getTime();             Connection con;             while ((con = getConnection()) == null) {                 try {                     wait(timeout);                 } catch (InterruptedException e) {}                     if ((new Date().getTime() - startTime) >= timeout) {                         return null;                     }             }             return con;         }                   // connections. 연결 해제         public synchronized void release() {             Enumeration allConnections = freeConnections.elements();             while (allConnections.hasMoreElements()) {                 Connection con = (Connection) allConnections.nextElement();                 try {                     con.close();                     log("Closed connection for pool " + name);                 } catch (SQLException e) {                     log(e, "Can't close connection for pool " + name);                 }             }             freeConnections.removeAllElements();         }                   private Connection newConnection() {             Connection con = null;             try {                 if (user == null) {                     con = DriverManager.getConnection(URL);                 } else {                     con = DriverManager.getConnection(URL, user, password);                 }                 log("Created a new connection in pool " + name);             } catch (SQLException e) {                 log(e, "Can't create a new connection for " + URL);                 return null;             }             return con;         }     } }

위 소스를 컴파일하고

관련파일 3개를 컴파일된 oracle-dbpool이라는 폴더에 삽입한다