I am working on a product development(a WAR file to enable web access to a database). I am concerned about the portability. Are all the moajor database vendors support JDBC2 interfaces like ConnectionPoolDataSource etc. If not, what's the best practice for portability across databases?
JDBC2 has been around for 4-5 years now and, as such, I would expect that all major vendors have been supporting it for years. As I caveat to that, I'm not sure if there are areas of the specification that are optional. If that's the case then, in the interest of portability, you can either do your research, or avoid those areas.
IMHO, the real trick in writing a database-independent solution comes not in writing the Java, but in writing the SQL. My advice would be to stick to the ANSI SQL-92 standard as far as possible as, even now, some of the major vendors do not fully implement the ANSI SQL-99 standard.
That's the easy part. Ahem! The next issue is what to do about all those pesky little utility functions for handling dates, type conversions, nulls, maths functions, etc. Again, I believe there is an ANSI set of these but I've a suspicion that support amongst the major vendors is limited.
A further headache is that, as far as I'm aware (and I'd be happy to be corrected on this), the ubiquitous MySql still doesn't support sub-queries.
Anyway, there's some food for thought and some pointers. If you follow these up I'd be interested to hear what you find out.