• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Better approach to get DB Connection

 
Kishore Balla
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I've started preparing for SCWCD with HFSJ one week back and I'm trying to implement these concepts while learning...

I'm confused about one thing..

Approach one: Write a java bean which returns connection object on calling its get method. So, in every servlet which requires DB access which call this method to get connection and after updating DB, it will close the connection.

Approach two: Write a servlet context listner and store the connection object as serlvetcontext attribute. So, the connection object will be available for the whole web-app and can be used in any servlet.

Which one of the above two is better approach? Can any one let me know with reasons.

Thanks in advance
[ September 03, 2006: Message edited by: Kishore Balla ]
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Approach one: This is inefficient because creating a database connection is expensive and slow.

Approach two: I'm assuming you mean a single Connection object shared among the entire application. This will be a major bottleneck. All servlets that do data access will effectively be on a single thread -- only 1 servlet can do data access at a time.

A better approach it to use a Database Connection Pooling package such as C3PO or DBCP. The pool can manage the connections (open and close them as necessary) and distribute them to servlets (or Data Access Objects) as needed.
 
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper
Posts: 4968
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Always take your database connections from a pool, and close the connection when you're done with it. If the connection is taken from a pool, the connection isn't actually closed, but instead, is realeased into the pool for use by another resource.
 
Kishore Balla
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess maintaining pool of connections is the functionality of server. So if we assume that server is doing its job then can I go for approach one (getting connection where ever necessary)
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess maintaining pool of connections is the functionality of server.


Yes, servers can provide Connection pooling capability. (Tomcat includes DBCP.) But calling:



does not return a connection from a connection pool.

Have you configured the connection pool and used it to acquire the Connection?

I don't want to you assume that just because you are running a container that all Connections are automatically pooled.
 
Kishore Balla
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm using the below code to get the db connection



I cannot configure the Tomcat as its not in my control (I'm hosting my site in Godaddy.com)
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, you could be using a pool. It depends on which driver you are using. What url are you using to connect? Does it start with "jdbc:apache:commons:d bcp:"?
[ September 04, 2006: Message edited by: Scott Johnson ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic