::: 강좌/소스/문서 :::

강좌/소스/문서 성격에 맞지 않는 광고,비방,질문의 글은 즉시 삭제하며
내용을 복사하여 사용할 경우 반드시 이곳(http://www.howto.pe.kr)을 출처로 명시하여 주세요


Category

  신훈재(2004-06-18 14:14:06, Hit : 9511, Vote : 1857
 [자바] DB 컨넥션 풀

[ DB 컨넥션 풀 ]

- DBConnectionManager.java와 db.properties 두개의 파일을 CLASSPATH가 잡혀 있지
  않은 디렉토리에 복사 한다.
- db.properties 파일을 자신의 환경에 맞게 설정 한다.
  읽고/쓰기 권한이 있는 디렉토리를 로그 파일의 경로로 적어준다.
  mydb., mydb2.은 DB를 대표하는 임의의 이름이다.
  .maxconn=0는 최대 접속 수를 설정하는 것으로 0 이라면 제한을 두지 않는 것이다.
- DB 접속하기
   DBConnectionManager connMgr;
   connMgr = DBConnectionManager.getInstance ( );
   Connection db = connMgr.getConnection ( "mydb" );//"mydb"는 db.properties 파일에 지정된 DB 대표이름.
- DB 접속 돌려주기
   connMgr.freeConnection ( "mydb", db );//db는 Connection의 인스턴스.

1. 예제(두개의 파일로 나누어 DB 연결관리하는 연결풀)

db.properties
----------------------------------------------------------
drivers=postgresql.Driver sun.jdbc.odbc.JdbcOdbcDriver
logfile=/path/to/pool.log
mydb.url=jdbc:postgresql:dbname
mydb.maxconn=0
mydb.user=owner
mydb.password=ownerpassword
mydb2.url=jdbc:postgresql:dbname2
mydb2.maxconn=20
mydb2.user=owner2
mydb2.password=owner2password
----------------------------------------------------------

DBConnectionManager.java
----------------------------------------------------------
import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Date;

public class DBConnectionManager
{
    static private DBConnectionManager instance; // The single instance
    static private int clients;

    private Vector drivers = new Vector();
    private PrintWriter log;
    private Hashtable pools = new Hashtable();
    
    static synchronized public DBConnectionManager getInstance()
    {
        if (instance == null)
            instance = new DBConnectionManager();
        clients++;
        return instance;
    }
    
    private DBConnectionManager()
    {
        init();
    }
    
    public void freeConnection(String name, Connection con)
    {
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);
        if (pool != null)
            pool.freeConnection(con);
    }
        
    public Connection getConnection(String name)
    {
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);
        if (pool != null)
            return pool.getConnection();

        return null;
    }
    
    public Connection getConnection(String name, long time)
    {
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);
        if (pool != null)
            return pool.getConnection(time);

        return null;
    }
    
    // Closes all open connections and deregisters all drivers.
    public synchronized void release()
    {
        // Wait until called by the last client
        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()); }
        }
    }
    
    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");
                String password = props.getProperty(poolName + ".password");
                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);
            }
        }
    }
    
    // Loads properties and initializes the instance with its values.
    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");
        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); }
        }
    }
    
    // Writes a message to the log file.
    private void log(String msg)
    {
        log.println(new Date() + ": " + msg);
    }

    // Writes a message with an Exception to the log file.
    private void log(Throwable e, String msg)
    {
        log.println(new Date() + ": " + msg);
        e.printStackTrace(log);
    }

    
    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;
        
        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;
        }
        
        public synchronized void freeConnection(Connection con)
        {
            // Put the connection at the end of the Vector
            freeConnections.addElement(con);
            checkedOut--;
            notifyAll();
        }
        
        public synchronized Connection getConnection()
        {
            Connection con = null;
            if (freeConnections.size() > 0)
            {
                // Pick the first Connection in the Vector to get round-robin usage
                con = (Connection) freeConnections.firstElement();
                freeConnections.removeElementAt(0);
                try
                {
                    if (con.isClosed())
                    {
                        log("Removed bad connection from " + name);
                        con = getConnection(); // Try again recursively
                    }
                }
                catch (SQLException e)
                {
                    log("Removed bad connection from " + name);
                    con = getConnection(); // Try again recursively
                }
            }
            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; // Timeout has expired
            }
            return con;
        }
        
        // Closes all available 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();
        }
        
        // Creates a new connection, using a userid and password if specified.
        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;
        }
    }
}
---------------------------------------------------------------------------------------


2. 예제2 (직접 DB 연결관리하는 연결풀)

서블릿 프로그래밍(삼각형츨판사)에 나오는 예제
----------------------------------------------------------------------------------------
import java.sql.*;
import java.util.*;

public class ConnectionPool
{
        private static ConnectionPool pool=new ConnectionPool ( );
        private static Hashtable connections;
        private int increment;
        private String dbURL, user, password;
        
        private ConnectionPool ( )
        {
                try
                {
                        Class.forName ( "com.inet.tds.TdsDriver" );
                        this.dbURL = "jdbc:inetdae:localhost:1433?database=shoppingmall&sql7=true";
                        this.user = "sa";
                        this.password = "ashley";
                        this.increment = 1;
                        
                        int initialConnections = 1;    
                        connections = new Hashtable ( );
                        
                        //Hashtable에 Connection객체를 저장. False는 Connection객체가 사용하지 않고 있다는 표시
                        for ( int i = 0; i < initialConnections; i++ )
                                connections.put ( DriverManager.getConnection ( dbURL, user, password ),Boolean.FALSE );
                }
                catch ( Exception e ) { }
        }

        //ConnectioPool함수 입력 파라메타(jdbc URL,사용자 ID,사용자 패스워드,jdbc 드라이버 이름,초기 연결 갯수,증가분)
        public ConnectionPool ( String dbURL,String user, String password, String driverClassName, int initialConnections, int increment )
                throws SQLException, ClassNotFoundException,InstantiationException,IllegalAccessException
        {
                // 명시된 드라이버를 메모리로 올림
                Class.forName ( driverClassName ).newInstance ( );
                this.dbURL = dbURL;
                this.user = user;
                this.password = password;
                this.increment = increment;
                
                connections = new Hashtable ( ); // Hashtable객체를 생성
                
                //Hashtable에 Connection객체를 저장. False는 Connection객체가 사용하지 않고 있다는 표시
                for ( int i = 0; i < initialConnections; i++ )
                        connections.put ( DriverManager.getConnection ( dbURL, user, password ), Boolean.FALSE );
        }
  
        //생성된 ConnectionPool객체를 리턴함
        public static ConnectionPool getInstance()
        {
                return pool;
        }
        
        public Connection getConnection() throws SQLException
        {
                Connection con = null;
                
                //Hashtable에서 모든 키 값을 꺼내 그 키에 해당하는 Boolean값을 받아서
                //현재 그 Connection객체가 사용하는지 사용하지 않는지를 검사
                //만일 사용하지 않는다면 사용하는 것으로 바꾸고 Connection객체를 리턴함
                Enumeration cons = connections.keys ( );
                synchronized ( connections )
                {
                        while ( cons.hasMoreElements ( ) )
                        {
                                con = (Connection) cons.nextElement ( );
                                
                                Boolean b = (Boolean) connections.get ( con );
                                if ( b == Boolean.FALSE )
                                {
                                        // So we found an unused connection.
                                        // Test its integrity with a quick setAutoCommit(true) call.
                                        // For production use, more testing should be performed,
                                        // such as executing a simple query.
                                        /*try
                                        {        con.setAutoCommit(true);
                                        }
                                        catch ( SQLException e )
                                        {        // Problem with the connection, replace it.
                                                con = DriverManager.getConnection(dbURL, user, password);
                                        } */
                                        
                                        connections.put ( con, Boolean.TRUE ); // Update the Hashtable to show this one's taken
                                        
                                        return con; // Return the connection
                                }
                        }
                }
                
                //아래 코드 for구문을 수행하고 있다면 Connection객체에 여유분이 없다는 표시
                //따라서 Connection객체를 increment변수에 지정된 만큼 더 만드는 코드 부분  
                for(int i = 0; i < increment; i++)
                        connections.put(DriverManager.getConnection(dbURL, user, password),Boolean.FALSE);
                
                // 다시 새로운 Connection객체를 얻기 위해서 getConnection()함수를 호출해서 나온 결과를 리턴함
                return getConnection();
        }
  
        //Connection객체를 사용했으면 return하는 코드로서 먼저 Hashtable에서 키들을 전부 꺼낸 다음 Enumeration객체를 사용해서
        //returnConnection객체에서 받은 Connection객체와 Hashtabel에서 꺼낸 객체가 같은 객체인지를 검사해서
        //만일 같은 객체이면 Hashtable에서 해당하는 객체를 False로 전환하고 그리고 Connection객체를 Close 함.  
        public void returnConnection(Connection returned)
        {
                Connection con;
                Enumeration cons = connections.keys ( );
                while ( cons.hasMoreElements ( ) )
                {
                        con = (Connection) cons.nextElement ( );
                        if ( con == returned )
                        {      
                                connections.put ( con, Boolean.FALSE );
                                try
                                {
                                        returned.close ( );
                                }
                                catch ( SQLException e ) { }
                                connections.remove ( con );
                                break;
                        }
                }
        }
        /*  
        protected void finalize ( ) throws Throwable
        {
                Enumeration cons = connections.keys ( );
                Connection con = null;
                
                while ( cons.hasMoreElements ( ) )
                {  
                        con = (Connection) cons.nextElement ( );
                        try
                        {
                                con.close ( );
                        }
                        catch ( SQLException e ) { }
                }
                super.finalize ( );
        }
        */
}
----------------------------------------------------------------------------------------






  [Java/PHP] [자바] DB 컨넥션 풀  신훈재 2004/06/18 9511 1857
22   [Java/PHP] [자바] JDBC-ODBC 연결 예제  신훈재 2004/06/18 9046 1672
21   [Java/PHP] [자바] 시스템 프로퍼티를 이용한 드라이버 로드  신훈재 2004/06/18 5487 1425
20   [Java/PHP] [자바] 오라클 각종 에러  신훈재 2004/06/18 6490 1671
19   [Java/PHP] [자바] 활용팁(오라클)  신훈재 2004/06/18 5997 1603
18   [Java/PHP] [자바] ResultSet 질의결과  신훈재 2004/06/18 6112 1317
17   [Java/PHP] [자바] Select예제  신훈재 2004/06/18 6250 1562
16   [Java/PHP] [자바] Update예제  신훈재 2004/06/18 5414 1333
15   [Java/PHP] [자바] Insert 예제  신훈재 2004/06/18 6493 1673
14   [Java/PHP] [자바] Query Statement  신훈재 2004/06/18 7763 1468
13   [Java/PHP] [자바] JDBC Connect  신훈재 2004/06/18 6718 1489
12   [Java/PHP] [자바] JDBC 코딩시 주위사항(jsp, servlet)  신훈재 2004/06/18 6138 1357
11   [Java/PHP] [자바] 여러종류의 데이타베이스 테스트 소스  신훈재 2004/06/18 6421 1457
10   [Java/PHP] [자바] JDBC 테스트 소스(오라클)  신훈재 2004/06/18 5437 1345
9   [Java/PHP] [소스] snmp 를 이용한 시스템 가용율 구하기 예제  김영대 2003/12/01 9570 2092
8   [Java/PHP] [소스] Oolong 언어를 이용한 Factorial 계산 프로그램 예제  김영대 2003/11/10 6634 1683
7   [Java/PHP] [문서] Java Web Services Development Pack (JWSDP) 1.0 tutorial  김영대 2003/03/24 5851 1457
6   [Java/PHP] [문서] Java Swing v1.1.1 도움말  김영대 2003/03/24 5782 1470
5   [Java/PHP] [문서] Java Servlet Development Kit (JSDK) v2.3 도움말  김영대 2003/03/24 5520 1454
4   [Java/PHP] [문서] Java 2 SDK, Enterprise Edition (J2EE) 1.3.1 도움말  김영대 2003/03/24 5617 1469

1 [2]
 

Copyright 1999-2017 Zeroboard / skin by zero