Connection pooling

Question

We plan to implement connection pooling as that appears to be the right mechanism to use when database connections are frequently opening and closing. We display our code below, but need to know where must we do the connection pooling definition. Should it be in the main() method itself or in the ConnectionHandler class? Another thing: what are the opinions regarding BoneCP?

public class cServer
{

   class ConnectionHandler implements Runnable {
        ConnectionHandler(Socket receivedSocketConn1) {
          this.receivedSocketConn1=receivedSocketConn1;
        }
   public void run(){
          createConnection();
          processData();
          closeConnection();
       }
    }

    public void main()
    {
    try 
    {
      final ServerSocket serverSocketConn = new ServerSocket(8000);             
      while (true){
        try{
            Socket socketConn1 = serverSocketConn.accept();
            new Thread(new ConnectionHandler(socketConn1)).start();                     
       }
       catch(Exception e){
         e.printStackTrace(System.out);
       }
      }
    } 
    catch (Exception e){
      e.printStackTrace(System.out);
    }

    }

}

You don't have to 'do the definition of connection pooling' yourself at all. The DBMS vendor does that. — user207421, Mar 27 '12 at 02:02
@duffymo I know I want to use BoneCp but where to define the pools? — user837306, Mar 27 '12 at 02:28

Eugene Retunsky · Answer 1 · 2012-03-27T02:17:06.323

4

I would recommend to use existing connection pooling solutions (e.g. C3PO). Or use built-in db pools of an application server (all of them provide with such a feature).

edited Mar 27 '12 at 02:17

answered Mar 27 '12 at 02:07

Eugene Retunsky

13,009
4
52
55

+1 for not re-inventing existing, tested, proven technology. OP, do you have a good reason for rolling this all yourselves? – Will Chesterfield Mar 27 '12 at 02:27
@evgeny any sample on how to work on this? Yes I plan to use Bone-Cp. We are using mysql so does mysql have built in pools? – user837306 Mar 27 '12 at 02:28
It doesn't matter what database you use. Just use google search for "C3PO tutorial" or "Tomcat connection pool" etc. depending on what environment you use. You'll find sample of configuration files etc. Also you can use Hibernate or EJB persistence instead of working with raw JDBC (I suppose this is the case as long as you're going to write own connection pool). – Eugene Retunsky Mar 27 '12 at 02:32
@all I am not planning write my own pooling I want to use existing one that the Bone Cp is just I would like to know where to config it and its related information. – user837306 Mar 27 '12 at 02:45

score 1 · Answer 2 · edited May 23 '17 at 10:34

1

Well the pool itself should not reside in the ConnectionHandler, since it will be lost once the ConnectionHandler finishes running. Nevertheless the ConnectionHandler must have a reference to the pool, in order to be able to actually obtain a database connection. So I would say store the pool in your cServer class, instantiate it in your main(), and give each ConnectionHandler a reference to it when the ConnectionHandler is spawned.

I've used both C3P0 and DBCP but have never heard of Bone CP till now. It looks promising, but that's about all I can say about it. Here is a related post comparing C3P0 and DBCP.

edited May 23 '17 at 10:34

Community

1
1

answered Mar 27 '12 at 02:10

sparc_spread

10,643
11
45
59

since you have experience about this pool normally what will happen when once the pool is all finished will new connection be built or they have to wait for existing one to be released. Any major issue we which need to be on look out when using connection pooling method. – user837306 Mar 27 '12 at 02:30
c3p0 always creates new connections, and you can control the rate at which old ones expire (http://www.mchange.com/projects/c3p0/#managing_pool_size). DBCP does allow you to set a maximum pool size, but you can remove the maximum by setting it to -1 (http://commons.apache.org/dbcp/configuration.html). In terms of other major issues, I would say perhaps avoid c3p0 because it is still version 0.9 after having been around for many years. DBCP is quite actively maintained. – sparc_spread Mar 27 '12 at 02:54
By the way, by "always creates new connections," I meant that C3P0 always creates new connections when all current connections are in use, which I think is the situation that you were asking about. It does not create new connections if there are free ones available. Same is true of DBCP. – sparc_spread Mar 27 '12 at 03:26
so the setting of pooling what normally you guess use around 100 is it too high? – user837306 Mar 27 '12 at 03:43
It really depends on your application: number of users, connection capacity of the underlying DBMS, etc. I've always found that you have to experiment to get the optimal size. – sparc_spread Mar 27 '12 at 03:57
Here's some more material that addresses your configuration question. It also seems to say good things about BoneCP and not-so-good things about C3P0: http://stackoverflow.com/questions/3643966/java-connection-pooling-best-practices – sparc_spread Mar 27 '12 at 04:26
let me try now and I will get back here in case I am stuck – user837306 Mar 27 '12 at 14:04
I have now started to use BoneCp actually what different should be on the look out? – user837306 Apr 05 '12 at 15:36
I haven't used BoneCp so I couldn't really say. – sparc_spread Apr 08 '12 at 03:05

score 1 · Answer 3 · answered Mar 27 '12 at 02:31

1

If you're deploying in a Java EE container, use that container's connection pooling via JNDI.

If you're not deploying in a Java EE container, use a library such as BonePC, DBCP, or C3P0.

answered Mar 27 '12 at 02:31

Kaleb Brasee

51,193
8
108
113

what is meant by the EE container? I think I will go with BonePC any experience on that? – user837306 Mar 27 '12 at 02:46
A Java EE (Enterprise Edition) container is provided by an application server -- Websphere, JBoss, GlassFish, Weblogic, etc. If you're developing a web app you'll likely be in a Java EE container (unless you're using a servlet container like Tomcat or Jetty). Desktop apps won't be in an EE container. – Kaleb Brasee Mar 27 '12 at 02:58
Given that you have your own `main()` and are opening a `ServerSocket`, you are not running in a JavaEE container. However, @KalebBrasee is right, if you were, you would use the container's built-in pooling. – sparc_spread Mar 27 '12 at 03:03

Connection pooling

3 Answers3