Support Oracle and Access data layers in Java

I have done something similar: supporting Oracle and HSQLDB for unit tests.

I used a parent class with an abstract protected function modifySql(String sqlText). The interface would call the this function before executing a command or registering a preparedStatement, and the descendant HSQLDB class would change the SQL text accordingly (doing things like changing NUMBER to NUMERIC etc..) before calling standard JDBC functions. The Oracle class would obviously do nothing in the function.

I standardized on the application using Oracle SQL, then changed it for the other types (have also started a MySQL class). The modifySQL function can get a bit lengthy, and you end up having to swap text round to handle sequences etc., but you soon end up with something workable.

Apart from the different JDBC driver issues, this is all I needed to do to treat the databases in exactly the same way in the main application. In this way you don't have to store lots of different query formats for the different database types: and it's obviously viable to have them simply hard-coded into the application.

You could store it like final string variables in your code, or in xml files, like this: http://nicodewet.com/2009/12/29/clean-code-store-native-sql-statements-in-external-file/ (or using some libraries).

We do this at work. We have our own custom DAL and support Access, SQL, and Oracle. We use multiple forms of queries but the focus for your question is we store them in XML Files. Depending on how you write the queries will determine how difficult it is for you. Below is an example of how you can store queries to be retrieved.

<Command ID = "3">
<Provider = "Default">
<QueryText>
   Select * from MyTable
</QueryText>
</Provider>
<Provider = "Oracle">
<QueryText>
   Select * from dual
</QueryText>
</Provider>
<Command>

Then when you go to run your query in the application - you call the text based on your provider. If you need a custom query for the provider - your code should be able to pull the correct node.

The easiest way to do this is to implement it using the IDB generic interfaces as then cast as needed.

Here is a good explanation of how this can be achieved.

http://stevencalise.wordpress.com/2009/10/16/constructing-a-dal-in-c/

All this being said it really depends on how much control over your DAL you need. In some situations an ORM tool would work better - but you need to confirm data support. You also have to be careful of the data providers when it comes to bit level (32 versus 64 bit) when working with Access and / or Oracle. SQL is much more forgiving.