It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Request critique and better way on my database connections Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Request critique and better way on my database connections" Watch "Request critique and better way on my database connections" New topic

Request critique and better way on my database connections

Mike Jenkins
Ranch Hand

Joined: Jul 23, 2006
Posts: 57
I have a Servlet that calls another class file where I am choosing one of several different database actions. I am connecting to the database several times in the class file and I assume there is a better more efficient way of doing this?

The below is only showing 2 out of 4 methods where I am showing dataHit and dataInsert methods. The other methods I left out (for space reasons in this post) are dataUpdate and dataDelete methods.

Please advise.
[ September 19, 2006: Message edited by: Mike Jenkins ]
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 862
1) Eliminate redundant code
2) Don't hard code user name, password, driver and url
3) Put resource 'closes' in finally block
4) Don't gobble exceptions and simply write them to a log.
5) Give your methods more meaningful names than 'dataHit' - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

6) Use a prepared statement instead of a statement.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
If you want to be efficient with database connections, and other object you will be using, or need performance plan in advance for that.

What I mean is if you know that a certain operation is going to take 2 or 3 calls to methods that will have to close and reopen the connection in sequence you may/will be better off performing all of this in one single method. The catch here is that you will have code redundancy if you do not plan it out properly. You may consider using a connection pool as then you can keep closing and opening but your performance will not suffer substantially. You can also encapsulate the actual method sequence and open and close your Connection there something of a Template method.

Having one spot for performing common operation closing objects, is good too, as you do not have to make many changes in different places in your code, for logging etc., and can easily change the way you handle things at a later point in time. I used to close my stuff in the general fashion in the method and copy that over and over again and then I would find myself troubleshooting a bigger problem with connection leaks and would have to recode a bunch of statement all over the place. Sometime after that incident, I saw someone using a method to close connections and have been doing it ever since. Thank God for open source and forums like this one ;)

Good luck!
I agree. Here's the link:
subject: Request critique and better way on my database connections
jQuery in Action, 3rd edition