Java - database connection management in connection pool [duplicate]

Question

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!

Answer 1

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.