3

For a programming class I am taking I want to design a connection pool. I have been reading up on the concept, but 1 thing sounds like a contradiction to me and that is how long a created connection stays open.

If I understand it correctly a collection (pool) of Connections is created on application start up and requests can be made to obtain a connection. If the resource requesting the connection is done with its operations the connection is returned to this pool, but as far as I can tell it is never closed.

When I read other articles I always read it is good practice to close connections as soon as possible and never leave them open.

Is there any problem with keeping a connection open for the application lifetime? I know that the resultset and statement need to be cleared when recycling a connection, but any other objections to keeping a connection open?

Many thanks for your input!

StefanE
  • 103
  • 8
  • 1
    Creating a connection is slow. That's why you want to reuse them. Pooled connections aren't closed, they're simply returned to the pool. The important thing is to **free** resources when they're done being used. In a pooled connection's case it's freed when it's returned to the pool (i.e. `close()` has been called). – Kayaman May 03 '16 at 09:25

3 Answers3

2

Connection Pooling simply means creating, managing, and maintaining connection objects in advance.

There are quite a few things when we talk about Connection pool or connection management.

1.) If we have too many frequent calls to database, creating/closing connections is a lot more expensive operation. Better to keep a pool of connections. These connection objects are then managed by a pool manager that provides connections as and when requested by clients and returns them to the pool when it determines the client is finished with the Connection object.

2.) When it comes multi applications, where lot more applications are deployed, and each is actually hitting database with there own frequency, and 1-2 applications might hit database maybe once every hour or so. Then there we can go for a new connection every time.

In all cases, more important is that we free up the resources once our task is completed. There should never be lot more connection opens, which are being idle for a long time. This can impact application performance

Most of the application servers have a 2-tier connection pooling architecture where the pools are held in the application server memory. Application server handles the responsibilities of creating connection objects, adding them to the pool, assigning them to the incoming requests, taking the used connection objects back, returning them back to the pool, etc.

Configuration/Management

It's pretty configurable - the maximum connections, the minimum connections, the maximum number of idle connections, etc. these all parameters can be configured by the server administrator. On start up , server creates a fixed number (the configured minimum) of connection objects and adds them to the pool. Once all of these connection objects are exhausted by serving those many client requests then any extra request causes a new connection object to be created, added to the pool, and then to be assigned to server that extra request.

Ankur Singhal
  • 26,012
  • 16
  • 82
  • 116
  • when you are referring to application servers managing the connections you refer to something like this: https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html ? If I get it correctly it's not a serious problem to keep connections open for small applications and use a self made pool like stated here: http://stackoverflow.com/questions/2826212/need-code-to-create-connection-pool-in-java? – StefanE May 03 '16 at 11:37
  • @StefanE That does make sense, but i am talking about application servers on a global/high level, getting into application servers is different. You have shared for tomcat, then we have Jboss, weblogic etc.. – Ankur Singhal May 03 '16 at 11:39
  • I c your point. Thanks for the input, I think I have enough now to continue testing and convince my teacher :) – StefanE May 03 '16 at 11:42
  • @StefanE pls accept and upvote if it solves your purpose. – Ankur Singhal May 03 '16 at 11:43
  • I accepted, but seem to be still to lowly to upvote :) – StefanE May 03 '16 at 12:38
0

Connection Pool concept in java came to make available some number of connection objects to perform database transactions. It differes from server to server and depending up on the application business usage we can configure the no of connection objects that should be available for accepting a resource.

  1. Yes if the conncetion object is open and idle for more than the specified time the latency of accepting the connection will be in sleeping state and even the reference takes more memory and there is a chance of having memory leaks. Hence it is good practice to close the connection objects after a certain time frame by configuring the Pool.

2.The connection that we use in the JDBC statement are static connection that we use for that transaction not for all.Where as if we configure them in server and get the reference dynamically its easy to maintain no memory leaks and optimise the pool such no resources are wasted or held by idle objects.

Jagadish Sharma U
  • 496
  • 2
  • 11
0

Connection pooling mechanism is used to minimize the effort of creating and maintaining the connections with underlying database. When server starts, and connection pooling is identified, it opens initial number of specified connections with the database and uses from these connections unless all connections are busy; in which case, it opens new number of connections in the pool. Most of the connection pooling providers now allow you to specify minimum and maximum number of connections. So, unless maximum amount is reached and all previously opened connections in pool are being utilized, it opens new connections to serve the request and put them in pool. Server keep checking for the number of idle connections and if it finds that there are more number of idle connections than configured value of this parameter, server simply closes the extra number of idle connections, which are subsequently garbage collected.

java8.being
  • 464
  • 3
  • 11