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 maintaining a connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "maintaining a connection" Watch "maintaining a connection" New topic
Author

maintaining a connection

Dave Robbins
Ranch Hand

Joined: Sep 16, 2003
Posts: 131
Hello All,

I have an application that consists of an applet and a servlet
the applet sends sql queries to the servlet, which runs the queries against a mysql database, then packages up the results and returns them to the applet
this all work fine in my development environment
when I deploy the servlet it works for a while then dies
I suspect some kind of timeout problem with the way I use the jdbc connection
what's the right way to do this
currently, when the servlet starts up I creat a jdbc connection and use it over and over (I suspect it times out after a while)
should I create a new connection for each query?
seems like a performance bottleneck
can I give the connection some kind of flag when I create it to tell it it needs to remain open indefinately?

Thanks
Dave
Michael Duffy
Ranch Hand

Joined: Oct 15, 2005
Posts: 163
Originally posted by Dave Robbins:
Hello All,

I have an application that consists of an applet and a servlet
the applet sends sql queries to the servlet, which runs the queries against a mysql database, then packages up the results and returns them to the applet
this all work fine in my development environment
when I deploy the servlet it works for a while then dies
I suspect some kind of timeout problem with the way I use the jdbc connection
what's the right way to do this
currently, when the servlet starts up I creat a jdbc connection and use it over and over (I suspect it times out after a while)
should I create a new connection for each query?
seems like a performance bottleneck
can I give the connection some kind of flag when I create it to tell it it needs to remain open indefinately?

Thanks
Dave


Yes, I'd recommend creating a connection for each query. It is a performance hit if you don't use a container-managed connection pool, but I'll assume that you know that.

Using only one connection per user can also be a bottleneck. The number of simultaneous users are limited to the max number your database allows; a connection pool extends that number a great deal.

You have to be careful about how to you close resources. Make sure you get the connection and close it in a finally block in the narrowest scope possible.

You'll also be wise not to put JDBC code in your servlet. Servlets listen for HTTP requests. They should defer database interactions to other objects. Put all the JDBC code into a separate object that you can test off-line, without having to start up the servlet container. Once you have it working, have your servlet instantiate it and call its methods to work with the database.


%
Adam Richards
Ranch Hand

Joined: Nov 03, 2005
Posts: 135
Another approach would be to use a connection pool. Just get the connection object when you need it, and return it the to pool when done.
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
A connection pool is a big win. Without it, your app will not scale.
What are you using for a servlet container? Tomcat supports connection
pooling, as will any application server.


There is no emoticon for what I am feeling!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: maintaining a connection