I am looking to write the Java application which should be able to run against multiple database namely Oracle, Sql Server and DB2, however only one at any given time. Properties file will be used to configure the database driver and connection params etc. I am looking for Tips, Pitfalls and any help to avoid the problems rather than discovering them later.
The first approach which I can think of is using DAO factory. So there will be a DAO factory and implementation of DAO's for different databases. But with this approach you need to ship the code (at least the class files) for three different implementation of dao's.
The other way could be to implement your API as JDBC is implemented i.e. you write interfaces then provide the implementation to the interfaces for each database and put these implementation in seperate jar files. Now depending on the database you would be using distribute the respective jar file with your application.
Thanks Ankur - Certainly one way of doing it - But I was thinking more along the lines of one code fits all approach, may be ambitious or lazy whatever angle you look at it. [ March 16, 2006: Message edited by: jav maqbool ]
Forgot to mention that the best approach would be to go for an already available ORM tool like Hibernate. You would now write your code which is very much database independent, it is based on your configuration and settings that at run time hibernate will generate the database dependent code(queries).
This is very much similar to the second approach I had mentioned earlier but then here you do not have to write the code to access the different databases as that is provided by hiberante and is pretty much transparent to the code we would be writing.
I got htis idea while going through a Similar Topic. You might be also intereseted in visiting it!!