File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Database Access Design 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 "Database Access Design" Watch "Database Access Design" New topic

Database Access Design

Gabriel Cane
Ranch Hand

Joined: Mar 27, 2001
Posts: 39
I'm writing a web application that accesses a PostgreSQL database using JDBC. My code works fine and my application runs (mostly) smoothly, but I'm wondering if I have the best database access design.
This is what I'm doing now. I have created database accessor classes for each table in the database that I want to access. I've attempted to make the whole thing as generic as possible, writing methods to SELECT, INSERT and UPDATE records, as well as other methods specific the application itself. I declare my Connection as a class level variable, instantiate it in the constructor, and override finalize(), where I issue the Connection close() method.
Here is a portion of my code:
public class ClientDB implements WebClientProps{
Connection con;
* Opens the connection to the database.
public ClientDB() {
//gets properties from properties file.
try {
String driver = [name of driver];
String url = [url of database];
con = DriverManager.getConnection(url, [username], [password];
} //catch statments
//various database accessor methods
* Ensures that the <code>Connection</code> is closed when the instance is garbage collected.
protected void finalize() throws Throwable {
Is this the right approach, or am I putting too much stress on the database? My application is constantly sending queries to the database. Also, I'm afraid that by creating a new connection everytime I instantiate my DB classes, I may be opening and closing too many connections too quickly.
Is there a better way to do this? Should I be using connection pooling? Or could I somehow keep the same instance of the object in the session without it getting garbage collected after each servlet method uses it?
Any suggestions, opinions or insights are welcome.

Sun Certified Programmer for the Java 2 Platform
Tina Coleman
Ranch Hand

Joined: Dec 12, 2001
Posts: 150
A couple of thoughts:
1) You don't necessarily have to write a data access object (DAO) for each table in the database. The DAO acts as a mapping layer between objects and tables, but doesn't necessarily have to map 1 to 1 to the database. For instance, if you have data in a table that's only accessed through some other table (perhaps your object has a collection of some set of data, so you have another table to take care of the 1..n relationship), then I wouldn't expect to see a DAO for that referenced table.
2) I'd expect that you'll run into a problem with the number of connections that you have open. You'll want to release a connection as soon as possible (so that you don't have to increase your license costs on your database), rather than holding onto it throughout the life of your object. Whether you use connection pooling or just close your connection as soon as you're done with your operation (and get a new one when you need it), you'll make your application much more scalable.
3) In terms of getting the info from the properties file, do those as static initialization on the class so that you're not continually accessing the file with each object creation. Presumably, that stuff changes rarely, and when it does change, you'll need to restart your application anyway (so as to not have various objects accessing different databases).
I agree. Here's the link:
subject: Database Access Design
It's not a secret anymore!