DataBase connection pool 기법

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

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

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

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

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이라는 폴더에 삽입한다