• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Better approach to get DB Connection

 
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author and cow tipper
Posts: 5009
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
reply
    Bookmark Topic Watch Topic
  • New Topic