Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

connection pooling implementation

 
Nidhi Singhal
Ranch Hand
Posts: 89
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

What is the right way to implement connection pooling in a web application?
I am using javax.sql.ConnectionPoolDataSource for creating a connection pool.

Thanks,
Nidhi
 
Nidhi Singhal
Ranch Hand
Posts: 89
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I want to know that if i am making a datasource object in the init() method of a servlet, then how should i pass that object for obtaining connection in other classes (using datasource.getConnection() method)

please let me know the correct way to do so...

thanks,
Nidhi
 
Jeff Albertson
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What app server/servlet container are you using? For example, if you are using Tomcat, you usually configure your connection pool there, and it will register it via the JNDI.

Are you using an web app framework or data persistence framework/service like Hibernate? Again, it may handle the data source/connection a certain way to make your life a whole lot simpler.

If you are doing this from scratch, a simple approach would be to have a servlet filter that gets the data source from the JNDI and gets a connection from that and adds that connect into your request context (ServletRequest's setAttribute). Most books on writing web apps will have a chapter on database connectivity.
[ January 12, 2006: Message edited by: Jeff Albrechtsen ]
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to know that if i am making a datasource object in the init() method of a servlet, then how should i pass that object for obtaining connection in other classes (using datasource.getConnection() method)

You can have a static DataSource variable which the other classes can then access, eg MyServlet.ds.getConnection(). This will be fine as long as the servlet is loaded.

Alternatively, pass the DS variable as an arg to the other classes' methods.
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are doing this from scratch, a simple approach would be to have a servlet filter that gets the data source from the JNDI and gets a connection from that and adds that connect into your request context (ServletRequest's setAttribute).

I won't recommend this. Obtaining the Connection should be done as late as possible, so the method which uses the Connection should be the one to obtain the Connection. The same method should also close the Connection so that it can be returned to the pool.
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One note. Current versions of appservers may give you a logical proxy for a connection, not the real physical connection. There may not even be a strict 1-1 association between those two notions. As such, sometimes "close" can have less to do with being a good steward of connection resources than "commit". It is part of how appservers try to provide useful improvements (caching of prepared statements, transparent failover, etc.).
 
Nidhi Singhal
Ranch Hand
Posts: 89
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks. Your suggestions have been of great help.

I am currently using websphere app developer and have configured the connection pool settings there.

I feel using a static datasource object variable is a good idea.

Is there any way to use abstract class for this ..as database factory...suppose if i have two connection pool objects?

Thanks
Nidhi
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Current versions of appservers may give you a logical proxy for a connection, not the real physical connection. There may not even be a strict 1-1 association between those two notions. As such, sometimes "close" can have less to do with being a good steward of connection resources than "commit". It is part of how appservers try to provide useful improvements (caching of prepared statements, transparent failover, etc.).

This is true. For instance, WebLogic Server provides a logical Connection on request from the connection pool. This allows the server instance to manage the Connection for the pool, enable connection pool features and maintain the quality of connections provided to applications. Although it is possible to obtain a physical Connection from the pool, this is highly discouraged.

Anyway, the point I was making is that it is important to use the Connections for as short a period as possible in order to reduce the possibility of problems such as premature pool depletion and pool connection leakage. In this context, the close() method is invoked on a wrapper Connection object to return the Connection to the pool rather than actually closing the Connection.
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there any way to use abstract class for this ..as database factory...suppose if i have two connection pool objects?

Let's say that you have two DBs, one for testing, one for live running. You set up a connection pool for each and simply point your DataSource to whichever pool is needed.

However, if you need two separate DBs for your app, then create separate connection pools and separate DataSources for the pools.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic