Page 1 of 3 123 LastLast
Results 1 to 20 of 58

Thread: Connection Pool

  1. #1
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Question Connection Pool

    Hi. I search for connection pool and read about them, and if I understand it correctly connection pool is like a collection of open connection. If a connection is establish or created it should be added to connection pool, and if that connection is closed it should be removed in connection pool, while it is open I can use it again and again.

    While reading these tutorials and explanations in connection pool I have some questions:

    1. Is pool of connections can only use in a certain computer? Like ComputerA
    cannot share its connection pool to ComputerB?

    2. Where connection.close() should be place?

    Is it correct to use connection ONLY when selecting/loading record? after I got the returned records/data I close the connection at finally statement. Same as Adding, editing and deleting records. And while it is processing I place a progress bar so the user will have to wait for it to be completed and to do some process again.. Which means I will only open connection one at a time.

    Thanks for the explanation. :)
    Last edited by mine0926; 06-09-2011 at 07:43 AM.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default

    You've got some misconceptions.
    As you say, the pool is a collection of open connections.

    Where you would normally open a connection yourself (ie JDBC) you would now instead get it from the pool.
    After that you treat the connection as you would a normal connection, including closing it.
    The magic comes in that when you call close() on your connection it does not actually close the connection, but simply returns it back to the pool. That part is invisible to your code, though.

  3. #3
    JosAH's Avatar
    JosAH is online now Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,783
    Blog Entries
    7
    Rep Power
    21

    Default

    A simple pooled connection is an implementation of the Connection interface that wraps a 'raw' connection (possibly obtained from a DriverManager); it delegates all method calls of the Connection interface to the wrapped (decorated) raw connection except for the close() method. It returns itself to the pool and doesn't close the raw connection. When you want to obtain a connection, check this pool and get a pooled connection if one is available in the pool; if there isn't create a new pooled connection. That way you can do with those (pooled) connections whatever you want, i.e. close them when you want (they just return to the pool). Closing and opening a raw connection may take a lot of freeing and allocating database resources; this simple pooled connection scenario avoids this. Note that more elaborate scenarios exist, e.g. the pooled connection might invalidate itself (i.e. return itself to the pool or even close the raw connection) after a certain time out period.

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  4. #4
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    I got this code from http://www.developer.com/img/2009/11...PoolClass.html

    Is this correct? You told me that I simply call close() method but in the example there is a method returnConnectionToPool(Connection connection), so should I call this after I close()?

    Can you give some link that has example of connection pool?
    Java Code:
    import java.util.*;
    import java.sql.*;
    
    class DBConnectionManager
    {
    
        String databaseUrl = "jdbc:mysql://localhost:3306/myDatabase";
        String userName = "userName";
        String password = "userPass";
    
        Vector connectionPool = new Vector();
    
        public DBConnectionManager()
        {
            initialize();
        }
    
        public DBConnectionManager(
            //String databaseName,
            String databaseUrl,
            String userName,
            String password
            )
        {
            this.databaseUrl = databaseUrl;
            this.userName = userName;
            this.password = password;
            initialize();
        }
    
        private void initialize()
        {
            //Here we can initialize all the information that we need
            initializeConnectionPool();
        }
    
        private void initializeConnectionPool()
        {
            while(!checkIfConnectionPoolIsFull())
            {
                System.out.println("Connection Pool is NOT full. Proceeding with adding new connections");
                //Adding new connection instance until the pool is full
                connectionPool.addElement(createNewConnectionForPool());
            }
            System.out.println("Connection Pool is full.");
        }
    
        private synchronized boolean checkIfConnectionPoolIsFull()
        {
            final int MAX_POOL_SIZE = 5;
    
            //Check if the pool size
            if(connectionPool.size() < 5)
            {
                return false;
            }
    
            return true;
        }
    
        //Creating a connection
        private Connection createNewConnectionForPool()
        {
            Connection connection = null;
    
            try
            {
                Class.forName("com.mysql.jdbc.Driver");
                connection = DriverManager.getConnection(databaseUrl, userName, password);
                System.out.println("Connection: "+connection);
            }
            catch(SQLException sqle)
            {
                System.err.println("SQLException: "+sqle);
                return null;
            }
            catch(ClassNotFoundException cnfe)
            {
                System.err.println("ClassNotFoundException: "+cnfe);
                return null;
            }
    
            return connection;
        }
    
        public synchronized Connection getConnectionFromPool()
        {
            Connection connection = null;
    
            //Check if there is a connection available.
            //There are times when all the connections in the pool may be used up
            if(connectionPool.size() > 0)
            {
                connection = (Connection) connectionPool.firstElement();
                connectionPool.removeElementAt(0);
            }
            //Giving away the connection from the connection pool
            return connection;
        }
        [b]
        public synchronized void returnConnectionToPool(Connection connection)
        {
            //Adding the connection from the client back to the connection pool
            connectionPool.addElement(connection);
        }[/b]
    
        public static void main(String args[])
        {
            DBConnectionManager dbConnectionManager = new DBConnectionManager();
        }
    }

  5. #5
    JosAH's Avatar
    JosAH is online now Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,783
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by mine0926 View Post
    I got this code from http://www.developer.com/img/2009/11...PoolClass.html

    Is this correct? You told me that I simply call close() method but in the example there is a method returnConnectionToPool(Connection connection), so should I call this after I close()?
    That example uses plain Connections so you shouldn't close them when you're done with them; instead just return them to the pool; the scenario I sketched makes this transparent to your code, i.e. your code will work with ordinary Connections as well as pooled connections; the close() method either returns a pooled connection to the pool or simply closes the connection if it was a raw connection. I like my scenario better ...

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default

    That is also an example of a home coded ConnectionPool. And not a terribly good one at that.
    It's not how a real connection pool (ie Apaches DBCP) works, which is how Jos described it.

  7. #7
    JosAH's Avatar
    JosAH is online now Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,783
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by Tolls View Post
    That is also an example of a home coded ConnectionPool. And not a terribly good one at that.
    It's not how a real connection pool (ie Apaches DBCP) works, which is how Jos described it.
    Have you ever read the API documentation for Apache's DBCP? Eeeww! It made me go cross eyed; it was so cryptic, it took me an entire day to understand it. If the author of that documentation ever shows up here I want him/her to meet my rotating knives ;-)

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default

    Well, lots of Apache stuff is poorly documented. That's often the way with open source projects. Coding is the fun stuff, and documentation often (not always, but often) gets left to one side.

    Have to say, I've only ever used it plugged into something else that actually did the work...

  9. #9
    JosAH's Avatar
    JosAH is online now Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,783
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by Tolls View Post
    Well, lots of Apache stuff is poorly documented.
    You can say that again ...

    Quote Originally Posted by Tolls View Post
    Well, lots of Apache stuff is poorly documented.
    Yes, I agree ...

    Quote Originally Posted by Tolls View Post
    Well, lots of Apache stuff is poorly documented.
    Yep, there is no need to repeat yourself ...

    Quote Originally Posted by Tolls View Post
    Well, lots of Apache stuff is poorly documented.
    Yep, stop it now; we agree ...

    Quote Originally Posted by Tolls View Post
    Well, lots of Apache stuff is poorly documented.
    Aaarrggghhh!!

    kindest regards,

    Jos ;-)
    cenosillicaphobia: the fear for an empty beer glass

  10. #10
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default

    Just wanted to make sure you got the message...

  11. #11
    JosAH's Avatar
    JosAH is online now Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,783
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by Tolls View Post
    Just wanted to make sure you got the message...
    A lot of Apache's documentation (and naming) style reminds me of Spring's stuff: overly long names and complex interaction between classes: "A ServletInterceptorHandlerBuilder can be created by consulting the AbstractServletInterceptorHandlerBuilderFactory if it was configured in your XML file; no DataSourceHandlerConnectorFactory needs to be defined". Those sentences make me go "huh?"

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  12. #12
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default

    It does start to delve into the realms of Hungarian notation.

  13. #13
    JosAH's Avatar
    JosAH is online now Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,783
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by Tolls View Post
    It does start to delve into the realms of Hungarian notation.
    Yep, that's exactly why I don't like Spring; it may be unreasonable but I really dislike their naming convention ...

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  14. #14
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Hi, thanks for the reply. Do you have any link for a good connection pool example?

    Thanks again. :)

  15. #15
    JosAH's Avatar
    JosAH is online now Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,783
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by mine0926 View Post
    Hi, thanks for the reply. Do you have any link for a good connection pool example?
    Apache DBCP is a fine pooled connection implementation; it's just the API documentation that sucks (imho). A skeleton of a pooled Connection goes like this:

    Java Code:
    class PooledConnection implements Connection {
    
    	private Connection connection;
    
    	PooledConnection(Connection connection) {
    		
    		this.connection= connection;
    	}
    	
    	// all methods delegate to the wrapped connection here	
    
    
    	// return this connection to the pool
    	public void close() throws SQLException {
    		
    		// return this object to the pool
    	}
    
    	// return the wrapped connection
    	Connection getConnection() { return connection; }
    }
    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  16. #16
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Thanks. I will try find good tutorial or example of Apache DBCP. Can I post some link in this thread so I can ask you guys if the provided example is correct.

  17. #17
    JosAH's Avatar
    JosAH is online now Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,783
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by mine0926 View Post
    Thanks. I will try find good tutorial or example of Apache DBCP. Can I post some link in this thread so I can ask you guys if the provided example is correct.
    The best litmus test is experiment with it and see if it works; if it doesn't, ask here and we'l see what we can do; it would be a bit silly if you just dump your code or a link here and ask us "will this work?" so we have to do the experimenting ...

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  18. #18
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    That is my aim when I learn connection pool. Right now I do not have idea on how to do it, i do not know the classes and methods available on Apache DBCP (all I know now is how connection pool works and the benefit of using it). As you and Tolls said, API documentation of Apache DBCP is a bit poor, so I need to search and see other's documentation, tutorial and example to learn using Apache DBCP.

    Iam sorry if you find hard to understand what I am trying to explain. .:: nose bleed ::.

    Right now I am reading Apache DBCP documentation as a start and to know what are the available classes and method. Thank you for the reply. :)

  19. #19
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default

    Where are you planning on using it?
    Connection pools are simply a tool, with a configuration.
    Once configured that's it...

  20. #20
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Quote Originally Posted by Tolls View Post
    Where are you planning on using it?
    Right now I am developing an application that connects to database, I am planning to implement connection pool there.
    But would like to test and understand it first so that whatever happens I can debug it.

    Quote Originally Posted by Tolls View Post
    Connection pools are simply a tool, with a configuration.
    Once configured that's it...
    Im a little confuse, you mean connection pool is like a MySQL that I have to install and configure once then I can
    use it like magic? if that so, why when I search for connection pool results show lots of example codes, and most of the example code I cannot understand.

    Thanks for the reply, I really appreciate your efforts to help me. :)

Page 1 of 3 123 LastLast

Similar Threads

  1. Access to app server db connection pool
    By metazone in forum JDBC
    Replies: 3
    Last Post: 03-26-2011, 04:45 PM
  2. Replies: 2
    Last Post: 12-15-2010, 07:11 AM
  3. using snaq.db for connection pool
    By Li Xiao in forum JDBC
    Replies: 1
    Last Post: 08-08-2010, 11:28 AM
  4. Using Database connection pool
    By amsagara in forum Advanced Java
    Replies: 0
    Last Post: 08-11-2009, 01:44 PM
  5. connection pool for db2
    By paty in forum JDBC
    Replies: 1
    Last Post: 08-06-2007, 03:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •