Why do developers not use a class having list for all drivers?

[vaibhav punekar]

Hi all developers,
We generally go for the database application by first finalising whether we will use Oracle or SQL,etc.Then accordingly we write the statements for connection,statement,resultset etc.
When it comes to shift of RDBMS we have to make changes to actual code of all the classes that communicate with the database.A friend of mine in the office has suggested writing a class that will have some array that will store the driver name and all its corresponding statements.In our actual code we just instanciate this class and the corresponding array element gets invoked.Do anybody use such method?Do we have any idea for such classes in any documentation or book?

[Peter den Haan]

If your database interaction can be caught in a small catalog of basic statements, then don't go with this idea - use a database persistence framework. There are plenty of commercial and even some open source ones around.
For general background material, read this white paper on database persistence frameworks, Rational's take on the subject andthis Cocobase white paper.
Where your database interaction is complex, a framework may not work. Instead, consider reading your SQL statements from a database-dependent properties file. Use PreparedStatement throughout so that arguments can be represented by question marks. If you do this, there's a good chance you can port to a different database simply by reconfiguring. Consider using a Map to hold the statements so you can give them meaningful names.
You don't need to use a properties file of course. Use whatever form of configuration is most convenient. Some use Strings in a special class which contains all SQL statements (which I don't like personally). For EJBs, you may store them in the bean's JNDI environment. If your application already has a system configuration file, you may consider putting them there.
The above options are not mutually exclusive. It is fairly common pattern to use a persistence framework for the routine stuff, supplemented by a number of SQL statements where the framework would be too inefficient.
- Peter

[This message has been edited by Peter den Haan (edited June 14, 2001).]

[vaibhav punekar]

But when it comes to opening and closing connection regularly and when the session management is the issue,I think Properties file is a good idea to go about evenif you have a routine statements like insert,update,alter etc.It saves a lot of time for the developer who is working on the changes.
Also it is rquired to write a separate class when the client requirement is to have multi-vendor compatibility.Because in that case we can not rely wholly on database dependent properties file.
Am I right Peter?Thanks for the reply and the links.