Multiple DB support in J2EE

[D. Rose]

Hi,

I have a design requirement to support multiple Databases in J2EE.
I am planning to use JDBC as I can not use any Database specific features like stored procedures.
Is there any other option? My application is data centric so will use of JDBC decrease performance?

[Paul Sturrock]

ORM technologies are designed to solve this issue. You might consider Hibernate, OJB, Toplink etc. If none suit - you could write your own JDBC framework using the DAO pattern perhaps.

[D. Rose]

Hi Paul,

Thanks for your answer. We thought of some ORM but would it allow fine tuning of queries? I have heard that queries generated by ORM are not optimal always.

[Paul Sturrock]

Fine tuning is usually possible since most ORMs will allow through direct SQL queries if you absolutely must use them. If performance is the real issue, then JDBC with stored procedures probably remains the quickest. Of course this requires DB work for every DB you support. Platform independance is always going to be a trade off wiht other things - so it depends which is more important. My experience has been that the cost of development time to implement and support DB work for each required database is more expensive than the cost of chucking more hardware resorces at a not exactly lightning quick application. I can say that ORMs perform quickly enough for our company's purposes, but the only way you will know for sure is a little prototyping and profiling for your specific requiremenets.