This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Connection Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection Question" Watch "Connection Question" New topic
Author

Connection Question

Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

I have written some Help Desk software for the company I work for. There are about 15 people that currently use this software. Right now, when information is needed from the database I open a connection, retreive/send my data, then close the connection.
Is this the optimal way to do this, or could I get away with opening a connection to the database when the application loads, and close the connection only when I leave the application. That way I never have to open a connection again, possibly speeding up the application??
Any advice would be appreciated.


GenRocket - Experts at Building Test Data
Andres Gonzalez
Ranch Hand

Joined: Nov 27, 2001
Posts: 1561
Hi Gregg.
Right now, when information is needed from the database I open a connection, retreive/send my data, then close the connection

I'd say it is "ok" if you only have 15 people using the system, but it is not a very good idea to do this. Opening connections requires system resources.
instead, you can use a broker (have you heard about connection pooling?). If you are not using one yet, you simply create this broker with say, 20 connections, and you just get the connection whenever you need to do some database stuff and free the connection when you are done. This is a much, much better approach.
www.javaexchange.com has a class that implements connection pooling, very easy to use and free. You might want to give it a try.
hope it helps..


I'm not going to be a Rock Star. I'm going to be a LEGEND! --Freddie Mercury
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

With near-on 1000 posts, I'd hope Gregg has heard of Connection Pooling!
A general Java development rule is to worry about getting it right first, then worry about performance later. I'd look at opening a connection for each query and closing it after.
With 15 users you wouldn't expect the connection performance hit to be too bad, so just take it.
Definitely write with pooling in mind though. You may find you need it and don't want to do a complete rewrite to achieve this.
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Thanks guys! Yes, I have heard of connection pooling. However, the general consensus regarding connection pooling that I have read on this site is that under 200 users, it's really not a necessity.
Along the same lines here, if I open 20 connections using the connection pooling method, and just retrieve one of those connections when I need it, then return it to the queu, are there any issues with leaving connections open for a long period of time like that, say 8 hours?
sim sim
Ranch Hand

Joined: Jun 05, 2002
Posts: 55
Guys, I have a similar doubt as Gregg has. In a connection pool if the connection objects are lying for long time without getting used, wont they get staled? How to handle such situations.
Sim Sim.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connection Question
 
Similar Threads
Creating a database connection class
Leave connection Open or Close it???
cleanup when swing app exits
Hibernate with Application server connection pool
Catching Ctrl C