Please be aware that I have looked and looked but I'm not capable of finding a solution.

I am writing a Java application and I have to support both Oracle and Access databases. I presently have two classes that implement their very own version of Connect().

OracleDatabase : IDatabase

AccessDatabase : IDatabase

Let us state that I have to produce a table known as "MY_TABLE". Wouldso would Time passes about supporting both database types? I figured about something similar to...

IDatabase.Execute(IDatabase.GetCreateMyTableQuery())

I am while using factory pattern to choose the database implementation. However , I'm not sure how you can keep queries.

I've done such like: supporting Oracle and HSQLDB for unit tests.

I made use of a parent or gaurdian class by having an abstract protected function modifySql(String sqlText). The interface would call the this function before performing an order or signing up a preparedStatement, and also the descendant HSQLDB class would alter the SQL text accordingly (doing such things as altering NUMBER to Number etc..) before calling standard JDBC functions. The Oracle class would clearly do nothing at all within the function.

I standardized around the application using Oracle SQL, then transformed it for that other forms (also have began a MySQL class). The modifySQL function could possibly get a little extended, and also you finish up needing to swap text round to deal with sequences etc., however, you soon finish track of something workable.

Aside from the various JDBC driver issues, this really is all I desired to complete to deal with the databases in the identical means by the primary application. In by doing this it's not necessary to store many different query formats for that different database types: and it is clearly viable to possess them simply hard-coded in to the application.

You can store it like final string variables inside your code, or perhaps in xml files, such as this: http://nicodewet.com/2009/12/29/clean-code-store-native-sql-statements-in-external-file/ (or with a couple libraries).

We all do this at the office. We've our very own custom DAL and support Access, SQL, and Oracle. We use multiple types of queries however the focus for the real question is we store them in XML Files. For the way you are writing the queries determines how difficult it's for you personally. Below is a good example of the best way to store queries to become retrieved.

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

Then when you attend run your query within the application - you call the written text according to your provider. Should you prefer a custom query for that provider - your code should have the ability to pull the right node.

The simplest method of doing this really is to implement it while using IDB generic connects as then cast when needed.

This is a good explanation of methods this is often accomplished.

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

All of this being stated it truly is dependent how much treatments for your DAL you'll need. In certain situations an ORM tool works better - but you have to confirm data support. You might also need to be cautious about the information companies if this involves bit level (32 versus 64 bit) whenever using Access or Oracle. SQL is a lot more forgiving.