File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes database connections Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "database connections" Watch "database connections" New topic
Author

database connections

devayani Devasthali
Ranch Hand

Joined: Aug 25, 2005
Posts: 40
hello All,
can anybody tell me the efficient way of establishing db connection and closing them while developing simple web application?
thanx in advance
regards
devayni
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
What container are you using?


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
devayani Devasthali
Ranch Hand

Joined: Aug 25, 2005
Posts: 40
hello,
I am using tomcat 5
database - access / mysql
regards
devayani
devayani Devasthali
Ranch Hand

Joined: Aug 25, 2005
Posts: 40
can anybody pls tell me one thing -
which way is the right one to establish database conenction in web application?
1) establish db connection in init method and close it in destroy. In init method everytime before establishing check for existinng connection if not found then only call getConnect method
2) through out the application, whever reqd establish the connection and immediately close them.
in which case db connections are opened?
thanx in advance
regards
devayani
Vinny Menon
Ranch Hand

Joined: Jan 10, 2006
Posts: 62

Hi Devyani
I would suggest you create a custom class for database related activities e.g. MyDataBaseClass and include methods like


And use this class throughout your application by e.g.:
MyDataBaseClass.openConnection()
MyDataBaseClass.closeConnection(conn).


hth
cheers
vinny m


Regards,Vinny M.
proud Fan of European Champion CHELSEA FC
"If you don't see the bug where you're looking, perhaps you're looking in the wrong place" -James Gosling
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
I once suggested using a servlet filter to get a connection from the connection pool (you are using a connection pool since you are using Tomcat?), setting it as an attribute of the request so that it could be used down the line, and finally closing it an the end of the filter.

Another forum member criticised this approach. What do you think? If you aren't working in a framework where this is taken care of, what's wrong with using a servlet filter to handle acquiring and releasing a connection?


There is no emoticon for what I am feeling!
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
You have to be a bit careful with servlet filters *if* you are trying to have an application work with multiple vendors. With WebLogic 7.0, for example, I've seen servlet filters routinely hit twice per request because of how WebLogic uses its own filters to manage security for the web tier. Not a big deal if you know about it and code accordingly, but confusing otherwise.

The other thing that seems a little odd about it is that generally I'd consider a filter as something optional that augments the functionality of a servlet. Example: an application that uses a filter to compress the HTML output stream if the browser supports compression. Your servlet not having a database connection doesn't sound like an option. Nothing stopping you from using a filter this way, but if I were the developer coming along six months later to work on the code, I'd definitely be confused if there wasn't some very clear javadoc in the servlet code telling me about the dependency on the servlet filter in order to get database connections.


Reid - SCJP2 (April 2002)
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
Hmmm... another thing I use ServletFilters for is redirecting requests to a "permission not granted" page if the user is trying to access a page he doesn't have persmission to see. Our permission formulas are too complex to use the web.xml security stuff to fulfil.

I don't think I'd file that under "optional" behavior! Is this another abuse of servlet filters?
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
You asked for "the efficient way of establishing db connection". This is done by creating a method-ready pool of JDBC Connections on server startup. Search this forum or the internet for a connection pool which works with Tomcat.

You should get your Connection as late as possible and guarantee release back to the pool by closing it in a finally block. And always use a local variable to reference the Connection.
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
Originally posted by Roger Chung-Wee:

You should get your Connection as late as possible and guarantee release back to the pool by closing it in a finally block. And always use a local variable to reference the Connection.


Here's my situation, which is pretty typical: I'm using JSF to wire backing beans to my JSP. Suppose I have a page with several separate tables on it:

<h :dataTable value="#{pc_backingBean.customerList}" ...>

<h :dataTable value="#{pc_backingBean.supplierList}" ...>

Method getCustomerList and the others each select a list of beans from the database. Should *each* of these methods be getting and releasing a connection from the pool every time they are called, or is this micro-management? If I handled my connections in this way, some pages would be acquiring and releasing connections a dozen times just to render themselves. Or should I try to arrange it so that one connection is used per client request cycle.
[ February 16, 2006: Message edited by: Jeff Albertson ]
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Originally posted by Jeff Albertson:
Hmmm... another thing I use ServletFilters for is redirecting requests to a "permission not granted" page if the user is trying to access a page he doesn't have persmission to see. Our permission formulas are too complex to use the web.xml security stuff to fulfil.

I don't think I'd file that under "optional" behavior! Is this another abuse of servlet filters?


I think you knew you were going out of your way to stretch the point when you clicked "Add Reply".

The sum total of all the functionality you install is obviously intended to achieve some purpose and thus arguably nothing is "optional" from a business standpoint. I let the Sales guys worry about those issues. My point was simply that from the standpoint of understanding how the software components work, if "A" fundamentally breaks when "B" is missing, then "B" isn't optional, thus its presence is mandatory in order for "A" to do anything useful. That means from a design standpoint, the distribution of behavioural responsibility is atypical for a component-based architecture when, as in this specific case, one of those components normally serves in the capacity of a decorator. "B" can't be a true decorator of "A" if "A" breaks when "B" is missing.
[ February 16, 2006: Message edited by: Reid M. Pinchback ]
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Originally posted by Jeff Albertson:
If I handled my connections in this way, some pages would be acquiring and releasing connections a dozen times just to render themselves. Or should I try to arrange it so that one connection is used per client request cycle.


It probably depends on how much code is involved. If all the work required for a page is pretty compact, then keeping track of when you got the connection and when you released it is feasible. From a Java coding standpoint what Roger is proposing would still help you, i.e.:



Besides, the effort to acquire a connection from a connection pool, if you are using a connection pool, isn't that great; a logical artifact is created, but the physical artifact is simply allocated. I wouldn't put it inside two or three layers of nested loops, but I wouldn't lose sleep if it happened two or three times for a page.
[ February 16, 2006: Message edited by: Reid M. Pinchback ]
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
Originally posted by Reid M. Pinchback:


Besides, the effort to acquire a connection from a connection pool, if you are using a connection pool, isn't that great; a logical artifact is created, but the physical artifact is simply allocated. I wouldn't put it inside two or three layers of nested loops, but I wouldn't lose sleep if it happened two or three times for a page.


Perhaps this is more of a question for the JSF forum (and I'm about to post my question there), but there is no single method like renderMyPage (that I'm aware of, in JSF) where a connection can be acquired. It's all very bean-oriented.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: database connections
 
Similar Threads
How to get connection at each request?
what is needed to find a job with java?
jdo and eclipse
connection
enterprise application with many users