This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes Servlets and the fly likes Getting Database Connection from Servlet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "Getting Database Connection from Servlet" Watch "Getting Database Connection from Servlet" New topic
Author

Getting Database Connection from Servlet

Mark Reyes
Ranch Hand

Joined: Jul 09, 2007
Posts: 426
Hi All,

I just would like to clear my doubt regarding obtaining a connection from each servlet coming from a connection pool.
Basically, I have been seeing this type of construct wherein the connections are being initialize at the
servlets init() method per each servlets.



But I have been following this for sometime wherein each connection is being gathered on the doXXX methods.



I often wonder which one is the proper and optimize approach in getting the DB connection or are both these methods considered not optimize.
Thanks


Sean Clark ---> I love this place!!!
Me ------> I definitely love this place!!!
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61106
    
  66

You should not hold a database connection open for one millisecond longer than it is actually needed. How does that help you decide?


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Mark Reyes
Ranch Hand

Joined: Jul 09, 2007
Posts: 426
You should not hold a database connection open for one millisecond longer than it is actually needed. How does that help you decide?


Thanks for the reply Bear.

I actually dont get what you mean by this clearly. But allow me to answer my question based on your reply. Does it mean that its practical to get the connection during the doXXX method? Since an init() is being called by the container when it starts-up but it doesnt mean that a request already comes in.
On the contrary, I am not getting a connection on the init() method but I am just initializing the datasource object on my first approach. The getting of my connection thru the call to the dataSource.getConnection() happens in the doXXX method.

Can you give me pointers please? I am not exactly sure about my answer.

giridharan radhakrishnan
Greenhorn

Joined: Nov 26, 2008
Posts: 4
Mark Reyes wrote:Hi All,

I just would like to clear my doubt regarding obtaining a connection from each servlet coming from a connection pool.
Basically, I have been seeing this type of construct wherein the connections are being initialize at the
servlets init() method per each servlets.



But I have been following this for sometime wherein each connection is being gathered on the doXXX methods.



I often wonder which one is the proper and optimize approach in getting the DB connection or are both these methods considered not optimize.
Thanks



Hi,
Performing a InitialContextLokup() may not be a good idea because every time when a user logs your servlet container is going to call service() method which in turn calls the doGet() or doPost() methods. So this piece of intialcontextlookup will be executed may times and this is not a good idea.
So better try to create the datasource object only once i.e. a singleton at the server startup. This can be achieved by having a servlet i.e. configured as loadonStartup in web.xml and make the datasource as a singleton object. Hope my reply makes sense..
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61106
    
  66

Best practice, of course, would be to abstract all this away in the Model layer -- your presentation layer shouldn't have any DB code in it.

One-time initialization should be performed up-front (by whatever layer), and then connections should be grabbed and quickly released as needed. Grabbing a connection and holding it in case the code later needs it is a very poor thing to do.
giridharan radhakrishnan
Greenhorn

Joined: Nov 26, 2008
Posts: 4
Yes! getting a connection object in inti method is not a great idea...my last post just suggested to avoid frequent IntialContextLookups and also to emphasize that create datasource as a singleton.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

giridharan radhakrishnan wrote:my last post just suggested to avoid frequent IntialContextLookups


Why would that be something to avoid?
Hebert Coelho
Ranch Hand

Joined: Jul 14, 2010
Posts: 754

Gatther all the data you want to persist in the database, then you do the work with the database.


[uaiHebert.com] [Full WebApplication JSF EJB JPA JAAS with source code to download] One Table Per SubClass [Web/JSF]
Mark Reyes
Ranch Hand

Joined: Jul 09, 2007
Posts: 426
Hi All,

Maybe my initial code wasn't clear enough on what I have been doing. Allow me please to show the snippet of my codes from my servlet(s).
I do think that I am explicitly closing my connection at the end of the servlet's service method.



Yes! getting a connection object in inti method is not a great idea...my last post just suggested to avoid frequent IntialContextLookups and also to emphasize that create datasource as a singleton.


Hi giridharan, I often hear this but I really dont know how is this being done. Can you share me a resource or link where I could read this. Thanks.


giridharan radhakrishnan wrote:my last post just suggested to avoid frequent IntialContextLookups



Why would that be something to avoid?


Hi Paul, this has been my practice eversince. I always do the context lookup's on all my service method. It's just that, I think that there is a better way other than doing this so that's why I post here at the ranch.
Mark Reyes
Ranch Hand

Joined: Jul 09, 2007
Posts: 426
Hi All,

While searching for an answer to my question, I found this http://www.javaranch.com/journal/200601/JDBCConnectionPooling.html topic regarding connection pooling at javaranch.

Its a little bit dated so I am not sure if this is still the optimized approach and this has been my approach for some time. Can somebody please share the best
practice in getting a database connection from a connection pool? A link or a tutorial would be better.

Mark Reyes
Ranch Hand

Joined: Jul 09, 2007
Posts: 426
Hi All,

This is my third attempt..
I was checking below approach in obtaining a proper datasource connection from a connection pool.
I basically created a class that will be initialized one time only. This is the class where I will get the connection



I created a listener class and has chosen the ServletContextListener to create my class.


And in my servlet I just get the connection from my DBUtilClass.



Please kindly advise if this is correct or optimize approach. Thanks.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Getting Database Connection from Servlet