I am looking for suggestions for the proper method to separate database details from my classes. I am struggling with the proper class design to minimize problems should at some point my connection details or database tables change. Thanks in advance.
Your best friend in this area is a text file. Whether it's a properties file, or web.xml, some other kind of configuration file, you want to put as much as possible into a text file and read it back at runtime. That way, many small changes can be handled just by modifying that configuration. So, for example, the JDBC connection string (either including username and password, or with these separately), and all your SQL queries can easily go into such a file and be loaded in at runtime.
As far as dealing with changes to tables, some major changes are always going to require code mods, but you'd be surprised how much externalizing the queries this way can buy you. If you don't assume the query result has a certain number of columns, but either get columns by name from the result set, or (in a reporting situation) simply loop over all the columns, then you can make substantial changes to a query without code mods. In that reporting situation, of course, you'd want to use ResultSetMetadata to get the list of column names to display.
This is a great topic for architectural layering. Make a persisence management layer that has Plain Old Java Object inputs and outputs with no hint that there is a database behind it. See Data Access Object and Transfer Object in the Sun J2EE Patterns.
One option is to use an object-relational mapping framework. My current project uses a proprietary tool that generates Java code from Rose models. Probably not what I'd recommend. Hibernate is a very popular tool; I have zero hands-on with it but it may be just what you need.
Another option is to hand throw your data acccessors. You can put a lot of functionality into a base class to connect, execute queries, etc, and wind up with a custom object that only knows its SQL and how to map result sets or insert statements to Java objects.
Let us know if any of that sounds interesting.
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Great responses so far! This is exactly the type of information I was having trouble finding on my own. I'm currently in a situation at work where I am trying to sell Java as a viable option. I've been given a small project and need to impress my boss as much as possible. She has no experience with any oo language so I need to make a good impression
Well doing some research on these suggestions I feel a full blown dao design pattern might be a bit much for this project. I am thinking of using a data mapper interface that will sit between my class and the rdb. Let me know your thoughts on this approach. Again thanks!
DAOs are a Core J2EE Design Pattern, which means that they are well known and accepted. This is important for long term maintenance
The DAO design pattern also provides a simple, consistent API for data access that does not require knowledge of JDBC, EJB, Hibernate, or JDO interfaces. This halso elps considerably with longer term maintenance.
DAOs offer clear benefits from an architectural perspective. The DAO approach provides flexibility to change an application's persistence technology over time without the need to re-engineer application logic that interacts with the DAO tier. For example, there may be performance benefits in changing an application�s performance mechanism from using Entity Beans to using direct JDBC calls from a session bean, or even a move to an alternative persistence framework, such as JDO or Hibernate. Without a DAO tier in place, this sort of transition would require extensive re-engineering of existing application code.
You may find these blog postings on "DAO versus ORM" and "Choosing a Java persistence strategy" interesting: