Making the choice of RDBMS configurable

[John S. Green]

This post is for the book promotion for "Expert Oracle JDBC Programming" by R. M. Menon. I hope it is suitable.

This may be a really basic set of questions to an expert but I haven't the vaguest idea of the answers so here goes.

I have started working on an application which will definitely be using multiple RDBMSs - MySQL, Derby, MSSQL, Oracle, other ... Only one will be used by each installation, but the DB can be chosen at install time (or perhaps even be switchable later - not sure of this yet).

What are the best design patterns to use to ensure that a new database can be added to the code in the most a cost-effective and bug-minimising way? I was thinking of Strategy but I'm not sure that I am on the right track. I don't want to use lots of if/switch statements throughout the code.

Having read the post by George Stoianov on stored procedures, I would guess that in this case, stored procedures would not be suitable. However, I'm not in favour of sprinkling SQL statements all over my code. Does it make sense to name the queries and store them in some sort of Collection to be called up as the need arises? Should they be "prepared" in some way in advance of being used? I need a method that won't involve a slow startup of the application, so the "preparation" may have to take place before runtime.

[Kevin Conaway]

Hi John,

Could you link to the post regarding stored procedures? I'm interested to see what it has to say.

To my knowledge, for any large scale application, complete database independence is hard. For this reason, I like stored procedures. Making modifications only involves the stored procedures themselves leaving the Java code virtually untouched. However, if you do end up using database specific code in Java, remember one thing: program to interfaces!

You can also take a look at a framework like Hibernate

Kevin

[Joel McNary]

I'm a big proponent of keeping the application logic at the application tier -- not pushing it off to the database. For that reason, (and for the fact that I write applications for a multitude of databases), I, too strive for database independence. (Although I will turn toward Stored Procedures where they are warrented -- particularly for performance reasons in a system that is tied to a particular database implementation).

I solved this problem by writing my own persistence engine, where I can specify the datastore in configuration files and the SQL is dynamically generated according to those configuration files. Of course, a few years later this Hibernate thing appeared which does almost same thing.

I've also seen systems with hard-coded queries. They achieve database independence through subclassing the classes that store queries.

Either way you do it, it is a type of Factory pattern -- you pass in the configuration that indicates the type of databse, and you are passed back an object that provides SQL for that database. So, for example, in my persistence engine I have a JDBCPersistencePort, which has subclasses like OraclePersistencePort, MySQLPeristencePort, AccessPersistencePort, etc. I make the call and get the correct PersistencePort (of whatever type -- I don't really care. It could be a FlatFilePersistencePort). I call the methods on it to do what I want and it's done.

[John S. Green]

Sorry Kevin, I should've put the link to George's post in my original question. here it is:

https://coderanch.com/t/303722/JDBC/java/Expert-Oracle-JDBC-Programming-book