my dog learned polymorphism
The moose likes JDBC and Relational Databases and the fly likes Connection Pooling Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Connection Pooling" Watch "Connection Pooling" New topic

Connection Pooling

Meet Gaurav
Ranch Hand

Joined: Oct 08, 2008
Posts: 492
Am new to java.
Am using data source in OC4J.. I have a query

I heard Connection pooling reusing the connections. I have an application that gets a connection from datasource and executing a Query and then am closing a connection. How come the connection pool maintain this closed connection in a pool..

Forgive me if this question is foolish..

I guess
while we closing some connections the pool will open a new connection..
[ December 17, 2008: Message edited by: Meet Gaurav ]
Madhumathi Jeyaraj

Joined: Dec 17, 2008
Posts: 1
Connection pooling is some thing like a pool where in the connections objects are available and ready to use kind of.. We can use a connection and release from use to the pool(we need not close the pool, we are leaving the object for further use)..So we are actually avoiding spending time in opening and closing the connections.

Hope this helps!

Devendra Thomare

Joined: Dec 13, 2008
Posts: 15
HI ...

we will have one ConnectionPool class (singletone class)

in this class declare method getConnection() which will give you connection

whenever you want a connection call the method getConnection

suppose you have decided that your pool will have max 10 connection

so suppose you have called this method 10 time and hence you have 10 connections open ...

now suppose some module is done with this connection this module wont close this connection rather it will return it to pool ...

for that you will have to maintain one count in connection pool and also flag to indicate that particular connection in use or not ....

i know this is very high level ....

hope you are getting what i wanted to tell you ....

in case you still have doubt i will send you code skeleton ....

another thing ....if you are using some application /web server then getting datasouce from that and hence connection will automatically take care of connection need to do it manually ...

Amit Ghorpade

Joined: Jun 06, 2007
Posts: 2851

"Devendra" please check your private messages for an important administrative matter. You can see them by clicking the My Private Messages link above.

And welcome to Javaranch

|Asking Good Questions|
raj malhotra
Ranch Hand

Joined: Feb 22, 2007
Posts: 288
Connection pool does not contain the connection object of type Connection but PooledConnection.And connection pool actually returns you the handle(or proxy) for the physical connection held by PooledConnection object. so when you call the close() method on handle or proxy,the Datasource implementation merely remove the association between the handle or proxy) object and PooledConnection object.
Hope it will clear the concept to certain level.
[ December 18, 2008: Message edited by: raj malhotra ]
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Originally posted by raj malhotra:
Connection pool does not contain the connection object of type Connection but PooledConnection.

That is confusing at two ways.

The pooled connection is certainly of type java.sql.Conenction. How would one use it as a Connection otherwise? The "PooledConnection" is not necessarily called that. It can be called anything, depending on the implementor.

Point is that the connection pool wraps each connection with its own implementation of java.sql.Connection. It decorates (HAS-A) the actual Conntection returned by the JDBC driver and overrides the close() method. The overriden close() method determines whether to return the connection to the pool or to actually close it.

This is a pretty good tutorial to understand about it: (don't use it for production though, it's a very bare implementation, just for learning purposes).
[ December 19, 2008: Message edited by: Bauke Scholtz ]
raj malhotra
Ranch Hand

Joined: Feb 22, 2007
Posts: 288
Hi Bauke
I am not sure how implementer has implemented it, may be the same way as Ejb implements proxy pattern.
But the below text is from sun j2se 1.4 - public interface PooledConnection.Its is a part of API ,which Sun provides for vendor imlementation.Also connection pooling can be maintained in our own application,rather than using from application server ,then your suggested approach seems to be suitable.

public interface PooledConnection
An object that provides hooks for connection pool management. A PooledConnection object represents a physical connection to a data source. The connection can be recycled rather than being closed when an application is finished with it, thus reducing the number of connections that need to be made.

An application programmer does not use the PooledConnection interface directly; rather, it is used by a middle tier infrastructure that manages the pooling of connections.

When an application calls the method DataSource.getConnection, it gets back a Connection object. If connection pooling is being done, that Connection object is actually a handle to a PooledConnection object, which is a physical connection.

The connection pool manager, typically the application server, maintains a pool of PooledConnection objects. If there is a PooledConnection object available in the pool, the connection pool manager returns a Connection object that is a handle to that physical connection. If no PooledConnection object is available, the connection pool manager calls the PooledConnection method getConnection to create a new physical connection and returns a handle to it.

When an application closes a connection, it calls the Connection method close. When connection pooling is being done, the connection pool manager is notified because it has registered itself as a ConnectionEventListener object using the ConnectionPool method addConnectionEventListener. The connection pool manager deactivates the handle to the PooledConnection object and returns the PooledConnection object to the pool of connections so that it can be used again. Thus, when an application closes its connection, the underlying physical connection is recycled rather than being closed.

The physical connection is not closed until the connection pool manager calls the PooledConnection method close. This method is generally called to have an orderly shutdown of the server or if a fatal error has made the connection unusable.

[ December 20, 2008: Message edited by: raj malhotra ]
I agree. Here's the link:
subject: Connection Pooling
It's not a secret anymore!