• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Connection management best practices

 
Mike Southgate
Ranch Hand
Posts: 183
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Some questions on the design of database connection classes...

I've written a simple singleton connection manager. It has 2 static methods Connection getConnection() and void releaseConnection(Connection). It just has a Connection[] and an array to keep track of whether the connection exists and if it is being used or not. It fills in the array with active connections as it's asked for them and keeps track of which ones can be re-used when releaseConnection is called.

A couple of issues:
1) what if a connection isn't used in a long time? My class will simply pass it on to the calling class who'll get an error. Is there a way I can test for the status of the connection to the database before I send it to a client?

2) My class relies on the client explicitly telling it when it's done by calling releaseConnection(Connection). Is there a way I could have the connection manager do it?

3) I use static methods in my database access classes to do the selects. That way I don't have to instantiate the class before doing the select I just call a static get() method that returns an instance of the class. Using this approach forces me to have static Connection variables that the database classes use. This introduces the posibility of the class clobering the value of the static connection by different instances of the class. Any ideas on a good way to get around this?

The above issues suggest that my design is bad. What are the best practices is this regard?

ms
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1) i have heard from jeanne (one of our bartender), that connection becomes stale when remain unused for a long time. so its better to use queue mechanism instead of stack. and why not go for something already implemented, use jakarta commons project DBCP.

2) it sounds fine. it is the normal case everywhere in the world. isn't it?

3) why it forces you to use static connection?? couldn't really get you, pardon.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mike,
Why are you writing your own connection pool? The application server provides this functionality.

Now on to your questions:
1) Adeel beat me to it! Yes, you will get a StaleConnectionException. There is an isClosed() method, but it doesn't work with connection pooling.

2) How would the connection manager know that the class is done? A class could concievably use a connection for a very long time. There isn't a method to show when the last time a connection was used.
You could write a wrapper around the real connection that added this functionality if you wanted to. The wrapper would pass on the real calls and update a timestamp.

3) You should use a collection of some sort to store the connections. You may want to have more than one and it makes your code more generic. Personally, I would suggest not using static methods. Instance methods make the class more flexible should you want to make future changes.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic