Multi-database support

[Matt Williams]

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]

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.

------------------
Miftah Khan
- Sun Certified Programmer for the Java� 2 Platform
- Sun Certified Web Component Developer for the Java� 2 Platform, Enterprise Edition

[Dave Soto]

Let's all chant together...
JDBC... JDBC... JDBC... JDBC...

[Peter den Haan]

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]

Enterprise Java Beans?

[Anonymous]

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]

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

import java.sql.*;
import javax.naming.NamingException;
/////////////////////////////////////////////////////////////////////////////////
// Abstract Class: JDBCConnector
//
// Purpose: Plug-in class used to configure and obtain a generic JDBC connection.
/////////////////////////////////////////////////////////////////////////////////
public abstract class JDBCConnector
{
// set configuration for connecting with database
    public abstract void setConnectionConfig( String user, String password, String jdbcURL );
    public abstract void setConnectionConfig( String dataSourceURL ) throws NamingException;
// get a connection to the database
    public abstract java.sql.Connection getConnection();
    
    // get a wrapped JDBC exception object
    public abstract JDBCException getJDBCException();
// close connection to database
public abstract JDBCQuery getJDBCQuery();

public abstract void close( Connection conn );
}  // End of abstract class JDBCConnector

JDBCQuery.java

import java.io.*;
import java.sql.*;
import java.util.*;
/////////////////////////////////////////////////////////////////////////////////
// Interface: JDBCQuery
//
// Purpose:   Interface to configure and obtain a generic JDBC query.
/////////////////////////////////////////////////////////////////////////////////
public abstract class JDBCQuery
{
// Run a query using the queryString, return the received ResultSet
    public abstract java.sql.ResultSet getQueryResultSet( String queryString );
// Attach to connection to use for the query.
public abstract void setConnection( java.sql.Connection conn );
}  // End of class JDBCQuery

Here's some usage code snippets:

//Create JDBCConnector to retrive Connections from
try
{
// Instantiate the JDBCConnector implementation specified
// in the application settings
Class c = Class.forName( DBConnectorClassName.toString() );
_jdbcConnector = (JDBCConnector) c.newInstance();

if( DBDriverType.toString().equals("DataSource") )
{
_jdbcConnector.setConnectionConfig( DBURL.toString() );
}
else
{
_jdbcConnector.setConnectionConfig( DBUser.toString(), DBPassword.toString(), DBURL.toString() );
}
}
catch( ClassNotFoundException cnfe )
{
log( "Unable to construct JDBCConnector implementation - class not found exception", "ERROR" );
}
catch( IllegalAccessException iae )
{
log( "Unable to construct JDBCConnector implementation - illegal access exception", "ERROR" );
}
catch( InstantiationException ie )
{
log( "Unable to construct JDBCConnector implementation - instantiation exception", "ERROR" );
}
catch( Exception e )
{
log( "Unable to construct JDBCConnector implementation - unknown exception", "ERROR" );
}

    public Vector getAttendanceHistory()
    {
// Get a new connection from the JDBCConnector
_conn  = _jdbcConnector.getConnection();
// Set SQL query string using dynamic variables
String queryString = "SELECT " +
"sl_attendance_hist.emplid, " +
"sl_attendance_hist.sl_mon_time, " +
"sl_attendance_hist.sl_tue_time, " +
"sl_attendance_hist.sl_wed_time, " +
"sl_attendance_hist.sl_thu_time, " +
"sl_attendance_hist.sl_fri_time, " +
"sl_attendance_hist.sl_sat_time, " +
"sl_attendance_hist.sl_sun_time, " +
"FROM sl_attendance_hist " +
"WHERE emplid = " + "'" + _args.get("emplid") + "'";
// Create a table to store the query results in.
Vector _results = null;
    
        try
        {
        // Get a JDBCQuery object to run the query with 
JDBCQuery jdbcQuery = _jdbcConnector.getJDBCQuery();

// Give the JDBCQuery a connection to work with
jdbcQuery.setConnection( _conn );

// Run the query and return a ResultSet
_rs = jdbcQuery.getQueryResultSet( queryString );
// Process the ResultSet using business rules and return a results table
_results = getProcessedResults( _rs );
// Clean up
_jdbcConnector.close( _conn );
_conn = null;
}
        catch( Exception e )
        {
            log( "Exception in " + this.getClass().getName() +  ".getAttendanceHistory(): " + e.toString(), "ERROR" );
        }
        
        return _results;
    }