Efficient database connections?

[Louis Fatham]

I'm new to programming in Java, so please forgive what might be a dumb question.

I wrote an application that makes many calls to a relational database. When I first wrote the program, I didn't take a lot of care to use code efficiently, and ended up repeating the same statements over and over throughout, making a single database connection to perform multiple select statements, and return multiple resultsets. And the thing ran like a champ.

But since I'm trying to go back and clean it up, I decided to move all the database calls to a separate class file (makes sense, I thought). Since the calls would use the same format, but use the resultsets differently, I decided to return the resultset back to the calling program, and scan it there. That works, however the processing speed fell massively. I have verified that I am closing the resultsets after every call (and subsequent processing of that resultset in the calling program). I think it's probably due to the fact that before, I was limiting the number of connection statements by having the db-logic inline with the main code, whereas now, every database call requires a new connection. Could that be the cause of the massive slowdown?
If that's so, can you suggest how I should organize the logic? Is it normal to isolate the database calls to a class of their own? Is it orthodox to return a resultset to the calling program, if the processing of the resultset differs for each statement performing the database lookup?
I'm just trying to write good code....

Thanks!

[Adeel Ansari]

Originally posted by Louis Fatham:
But since I'm trying to go back and clean it up, I decided to move all the database calls to a separate class file (makes sense, I thought).

Nice thought.

Every database call requires a new connection. Could that be the cause of the massive slowdown?

May be, but not massive. You can implement connection pooling mechanism.

Is it normal to isolate the database calls to a class of their own?

It is considered as best practice.

If that's so, can you suggest how I should organize the logic? Is it normal to isolate the database calls to a class of their own? Is it orthodox to return a resultset to the calling program, if the processing of the resultset differs for each statement performing the database lookup?
I'm just trying to write good code....

I suggest you to read a bit about DAO (Data Access Object) and VO (Value Object) in order to get the best.

Wish you luck.

[Louis Fatham]

Thanks, Adeel! I'll look into that.

[Joe Nguyen]

Perhaps, you haven't closed the connection soon enough. Consider creating a disconnected resultset (Value Object), using connection pool, and preparedStatement caching.

[Ben Souther]

Getting the connection is usually the biggest bottle neck your app will face (unless it's a web app, in which the internet will be #1).

If you were re-using a single connection, your first shot was probably much more efficient that the cleaned up code.

As Adeel has stated, your best bet is to implement a connection pool.
http://jakarta.apache.org/commons/dbcp