Handle multiple JDBC drivers from the SAME VENDOR

Question

I came across with a big problem yesterday. In my current project I use ojdbc6 implementation of Oracle's JDBC for a connection, but also I would need to handle for example oracle 8 databases, which is totally impossible with this JAR. You would say that I should use ojdbc14 for example which was true for some tests, but lets assume that later on I will need to handle 2 kind of databases from the same vendor, but we know that there is no existing implementation for BOTH and I need to have those simultaneously loaded. Same interface (and well, not just same interface, same class-structure, just different implementation inside!), same URL connection prefix -> JDBC connection will use one driver, but I cannot load multiple of them. So what now?

• My first idea was to load the JARs with different classloaders, maybe I could load the same package structure with the same classes separated from each other? I don't really think so, maybe that was a silly idea of mine. This could be also a general problem later not with just JDBC drivers, so even if you cannot answer to my question but you know what is lacking here please tell me

• Even if I could do a separate loading of class implementations of the same class names, how I can tell to the DriverManager when creating a connection to use the EXACT driver instead of finding one based on the connection url's prefix? (where I mean jdbc:oracle:thin for example).

I feel like a total dumb now because I think this is not an entirely extraordinary idea to handle in the Java world, BUT I totally don't know how to handle.

Thanks for y'all in advance

Answer 1

You actually have a couple of options:

1. You can try to load the drivers from different class loaders. That will work if you need only pure JDBC in your application. I doubt that you will get Hibernate to work with such a setup.

   Eventually, you will have to run code where you will need to see instances from both classloaders and here, you will get ClassCastExceptions (two classes with the same full qualified name are different when they were loaded from different class loaders).

2. You can split your application into two. The second one would a small server which takes commands from your original app and translates those into JDBC for the database. The small server talks to Oracle 8 while your app only talks to one database.

   This approach would allow you to keep the two concerns completely separate but you won't be able to run joins on the two databases.

3. You can link the old Oracle 8 database in your new database using CREATE DATABASE LINK. That makes the old tables visible as if they were part of the new database. You app only talks to one DB and Oracle handles the details internally.

   Maybe Oracle 8 is too old for this to work but I'd definitely give it a try.

4. Oracle JDBC drivers are more compatible that you might expect. When you say "which is totally impossible with this JAR", did you try it? I used an Oracle 10 driver to connect to Oracle 7 in the past. Not every feature was supported but I could run the standard queries and updates.

Answer 2

#jdbc.properties
oracle.driver=oracle.jdbc.OracleDriver
oracle.url=jdbc:oracle:thin:@//localhost/xe
oracle.user=scott
oracle.password=tiger

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://localhost/sales
mysql.user=root

mssql.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
mssql.url=jdbc:sqlserver://192.168.1.175;databaseName=sales
mssql.user=dbviewer
mssql.password=dbviewer

And then read the properties file:

class QueryTest2 {

   public static void main(String[] args) throws Exception{
        Properties settings = new Properties();
        FileInputStream fin = new FileInputStream("jdbc.properties");
        settings.load(fin);
        fin.close();
        String dvr = settings.getProperty(args[0] + ".driver");
        String url = settings.getProperty(args[0] + ".url");
        String usr = settings.getProperty(args[0] + ".user");
        String pwd = settings.getProperty(args[0] + ".password");
        Class.forName(dvr);
        Connection con = DriverManager.getConnection(url, usr, pwd);
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("select pno,price,stock from products");
        while(rs.next()){
            System.out.printf("%d\t%.2f\t%d%n", rs.getInt(1), rs.getDouble(2), rs.getInt("stock"));
        }
        rs.close();
        stmt.close();
        con.close();
    }
}