wood burning stoves 2.0*
The moose likes Servlets and the fly likes Multi-database support Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "Multi-database support" Watch "Multi-database support" New topic
Author

Multi-database support

Matt Williams
Greenhorn

Joined: Sep 10, 2001
Posts: 9
OK, so here is my problem and I am looking for the best solution to fix it. I want to create a JSP driven site that can run on multiple databases. The only simularity that the databases will have is the structure, but as we all know it isn't that easy to mix the SQL statements together. I don't have the money to create an application that will change up the SQL statements and make them work for all of the databases this is implemented on, but I will be able to program the SQL to make it run correctly on each platform.
My problem is that I want to have SQL modules that can be changed based upon what DB I am using. Does anyone have any ideas on what might work and what would be the easiest way to do this.
Thanks,
Matt
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
My problem is that I want to have SQL modules that can be changed based upon what DB I am using.
Not sure I understand your question, but here are my thoughts:
  1. What do you mean by SQL modules, and why would you want to change them per DB?
  2. If your DBs have the same structure, why not just program in standard SQL, as opposed to using any DB-specific SQL extensions. Then, if your JSP files use JDBC to connect to your DB's, you should be able to migrate relatively seamlessly from DB to DB.

  3. ------------------
    Miftah Khan
    - Sun Certified Programmer for the Java� 2 Platform
    - Sun Certified Web Component Developer for the Java� 2 Platform, Enterprise Edition
Dave Soto
Ranch Hand

Joined: Sep 15, 2001
Posts: 55
Let's all chant together...
JDBC... JDBC... JDBC... JDBC...
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
In most cases, you'd be completely mad to develop at the JDBC level. Look at an decent OM mapper such as Sun Transparent Persistence, Torque,Castor or Object Relational Bridge. These are just three examples; there's loads around.
- Peter
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16250
    
  21

Enterprise Java Beans?


Customer surveys are for companies who didn't pay proper attention to begin with.
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
Peter, thank you for your references. At first when I read your comment "In most cases, you'd be completely mad to develop at the JDBC level", I thought to myself, "What is he talking about?!?!" But then, I followed your Sun link and read http://www.sun.com/forte/ffj/resources/articles/transparent.html , and I have to say, it really opened my eyes!!! Now, I completely understand where you're coming from. And the whole concept of data objects, which used to just seem like a neat but theoretical concept to me, now seems very practical and applicable.
Thank you,
-Miftah
------------------
Miftah Khan
- Sun Certified Programmer for the Java� 2 Platform
- Sun Certified Web Component Developer for the Java� 2 Platform, Enterprise Edition
[This message has been edited by Miftah Khan (edited October 02, 2001).]
Gerry Giese
Ranch Hand

Joined: Aug 02, 2001
Posts: 247
I use an abstraction to allow my programs to dynamically switch databases at startup based on a properties file, but it could be modified to allow for a collection of connection objects. I set up for Connection, Query, and Exception, then sublcass off of them for Oracle and Sybase in my case. The Query and Exception classes are returned/used from the Connection so they are of the proper type. It's still rather crude, but heck, it works! I was forced to do this primarily becuase Sybase has funky exception handling that doesn't work with Oracle nicely. Both databases use the same SQL. Hope this helps!:
JDBCConnector.java

JDBCQuery.java

Here's some usage code snippets:


CJP (Certifiable Java Programmer), AMSE (Anti-Microsoft Software Engineer)
Author of Posts in the Saloon
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Multi-database support