I have been given the task of porting an application that I wrote two years ago in VB to java, and make it work with MS SQL, and Progress database. The problem is not just that they will be different databases, but they will have different table/field names as well. The application currently only has two or three stored procedures, which I was going to do away with, so that I would only be doing selects and updates. Initially I thought I would be able to make it 100% database independent, but now I am having second thoughts about that. I have had several ideas about how to go about doing this but they all have problems. My first idea was to have a flat file, that held my pseudo table and field names, as well as the real table and field names. If we wanted to run the application on another system (ERP system) I would add to this file only. If I parsed this file every time I needed a field/table name it would create a lot of overhead. If I read the file once when the app starts it would solve this problem, but I wasn't sure what the best way to hold this data would be. I started coding this using a vectors, but this got messy with all the tables and fields that I would be using. Another idea that I had would be to create what I think is the best table/field names, and make views in each system that would alias the real database into my schema. I would be interested to hear any advice on how to handle this. -Jeremiah [ October 02, 2003: Message edited by: Jeremiah Elliott ]
I would suggest using the DataAccessObject pattern and the Factory pattern, both mentioned here: DAO Pattern Basically, you can create a Interface that provides all the methods your application uses to retrieve data (e.g. MyApplicationDAO). You can then create database specific implementations of that Interface (e.g. MyApplicationMSSQLDAO, MyApplicationPostgresDAO). Use a Factory to retrieve the correct implementation (MyApplicationDAOFactory.getDAO()). Using this technique several benefits: 1) support for any number of databases (just add a new DAO implementation) 2) allows you to take advantage of database specific features rather than coding to the lowest common denominator. 3) allows easier integration of alternative datasources should the need arise (e.g. XML, LDAP, etc) since your DAO interface hides implementation details from your application. (Hint: Make sure your interface is pure and does not pass any information that would tie you to a specific data implementation) What are the downsides? 1) More code to write 2) May be duplicating some work (queries, logic, etc) if different databases are similar enough. (You can alleviate some of this if there are opportunities for one implementation to extend another, just be careful because forcing that relationship where it should not go can cause horrendous code and a maintenance nightmare.) In my opinion, the benefits far outweigh the costs. I always use this approach even if I only have one implentation of a datasource to support just to make sure that I have flexibility for future changes.