Help coderanch get a
new server
by contributing to the fundraiser
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Making the choice of RDBMS configurable

 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 1844
Eclipse IDE Ruby Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
I miss the old days when I would think up a sinister scheme for world domination and you would show a little emotional support. So just look at this tiny ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/t/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic