Is there a way that my app support SQLServer and Oracle?

Here at work there's a requirement of a web aplication that works with SQL Server and Oracle. We do database access with JDBC and, actually, we write a SQL statement for SQLServer and another one for Oracle (we do things like change the '+' for '||', case for decode,sysdate() for getDate()); and we select the statement to execute acording to the DBMS. Is there a way to avoid this double-coding? Maybe a persistence framework??

Try Hibernate. It takes care of these SQL portability issues for you. It's a great library for DB interaction from Java.

Originally posted by Steve McLeod:
Try Hibernate. It takes care of these SQL portability issues for you. It's a great library for DB interaction from Java.

Not all issues, there are times stored procedures and/or hand-written queries are optimal in large enough systems. As great as hiberate/jdo frameworks are, they often decrease performance compared to JDBC.

To answer Carlos's original question though, you should look into factory patterns to get the job done in a pure JDBC environment (since this a JDBC forum, else perhaps we should move to the object-relational mapping forum). The basic pattern in this case would be to declare an DAO interface and have an Oracle and SQL Server implementation. For example, let's say you have a query that reads user information, such as "public String getUserInformationQuery()". Define it in the interface DAOAccess.java then define the precise implementations in each class SQLServerDAOAccess.java (implements DAOAccess) and OracleDAOAccess.java (implements DAOAccess).

The 'factory' part of the pattern is that once setup, you use it in the following manner getDAOAccess().getUserInformationQuery(). In this manner, getDAOAccess() determines which database to give you based on some system or application setting. In short, the factory gives you a usable instance, and in your code you don't really care which version you have so long as they can both be used in the same manner.

It's a lot of setup and maintenance, but in the end is a very powerful technique. Keep in mind supporting two databases for any application is non-trivial, there's no quick solution. Also, the factory pattern allows you to support any number of databases just by dropping in a new implementation of a class that extends DAOAccess.
[ May 28, 2008: Message edited by: Scott Selikoff ]

I'm not familiar with Hibernate XD, but i do some research ..thanks! And how about EJB3?? I read some post that say it's the same