Using Multiple Oracle JDBC drivers in one Java application?

Question

I want to connect to two different Oracle databases (one 8.0.5.0.0 and one 12c) via JDBC. I do have both JDBC drivers that can individually and successfully connect to the corresponding DB via simple "hello world" applications. Below, I have put both of them together into one Java application, which unfortunately does not work anymore (with both drivers being loaded).

I have read this post: Handle multiple JDBC drivers from the SAME VENDOR . The option 1 mentioned there might be the way to go, but there seems to be one major problem:

It seems that OracleDataSource does not yet exist in the old version 8 driver and only has been introduced at later versions (in the 12c version driver it exists).

Any hints, on how to connect to these two Oracle databases with one single Java application and two JDBC drivers?

import java.sql.*;

class db {
    public static void main (String args []) throws SQLException {

        // Oracle 8 connection
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Connection c1 = DriverManager.getConnection(
                "jdbc:oracle:thin:@some-oracle-8-server:port:sid",
                "my-user",
                "my-password");
        Statement s1 = c1.createStatement ();
        ResultSet r1 = s1.executeQuery ("SELECT banner FROM V$VERSION WHERE banner LIKE 'Oracle%'");
        while (r1.next ()) {
            System.out.println(r1.getString (1));
        }
        c1.close();

        // Oracle 12 connection
        Connection c2 = DriverManager.getConnection(
                "jdbc:oracle:thin:@some-oracle-12-server:port:sid",
                "my-user",
                "my-password");
        Statement s2 = c2.createStatement ();
        ResultSet r2 = s2.executeQuery ("SELECT banner FROM V$VERSION WHERE banner LIKE 'Oracle%'");
        while (r2.next ()) {
            System.out.println(r2.getString (1));
        }
        c2.close();
    }
}

Thanks in adavnce!

Answer 1

If you don't register the drivers you avoid them being loaded by the same classloader.

Then you can create connections using the two different drivers by loading them through separate classloaders:

// Oracle 8 connection
File jar = new File("/path/to/oracle8.jar");
URL[] cp = new URL[1];
cp[0] = jar.toURI().toURL();
URLClassLoader ora8loader = new URLClassLoader(cp, ClassLoader.getSystemClassLoader());
Class drvClass = ora8loader.loadClass("oracle.jdbc.driver.OracleDriver");
Driver ora8driver = (Driver)drvClass.newInstance();

Properties props = new Properties();
// "user" instead of "username"
props.setProperty("user", "my-user");
props.setProperty("password", "my-password");
Connection ora8conn = ora8driver.connect("jdbc:oracle:thin:@some-oracle-8-server:port:sid",props);

Then do the same for the Oracle 12 driver.

You might also be able to still use the "other" driver through DriverManager, but I'm not sure about that.

There are some corner cases where accessing Oracle specific classes gets a bit complicated, but in general you can use the connections created through this without any problems.

Answer 2

I see two different solutions for different constellations.

(Using same (?) classes with different versions normally would be an idealh use case for OSGi, or if your application is a NetBeans RCP, their modular system. This works using class loaders that separate the software into modules, so one in different modules may load different jars with different versions of the same product.)

(Alternatively one might a different application with its own Oracle jar that is accessed using RMI.)

You might use the same technique: either write your own delegating ClassLoader that loads the right jar, or use the conceptually simpler RMI that however requires managing system resources.

So

1. Do not use imports from the vendors; that is contrary to the vendor indepence of JDBC.

2. Ensure that javax.sql interfaces come from a java-ee jar.

---

Picking the ClassLoader solution:

It would be best to make your own delegating Driver say with a protocol jdbc:myswitch:8: .... It could then use two different class loader instances. For instance using an URLClassLoader with a file:/... path.

One could make two separate instances of the custom delegating driver, so one can use the Delegate pattern.

public static class MySwitchDriver implements Driver {

    private final String oraURIPrefix;
    private final Driver delegateDriver;

    public MySwitchDriver(String oraURIPrefix) {
        this.oraURIPrefix = oraURIPrefix; // "8:" or "12:"
        String jarFileURI = oraURIPrefi.equals("8")
            ? "file:/... .jar" : "file:/... .jar";
        URLClassLoader classLoader = new URLClassLoader(...);
        delegateDriver = classLoader.loadClass(
                "oracle.jdbc.driver.OracleDriver", true);
        DriverManager.registerDriver(this);
    }

    private String delegateURL(String url) {
        // Something safer than this:
        return "jdbc:" + url.substring(
                "jdbc:myswitch".length
                + oraURIPrefix.length);
    }

    @Override
    public Connection connect(String url, Properties info)
            throws SQLException {
        String url2 = delegateURL(url);
        Properties info2 = info;
        return delegateDriver.connect(url2, info2);
    }

    @Override
    public boolean acceptsURL(String url) throws SQLException {
        return url.startsWith("jdbc:myswitch:" + oraURIPrefix)
            && delegateDriver.acceptsURL(delegateURL(url));
    }
...
}

Answer 3

You can use Factory design pattern in order to get which connection you would like to have, then, store it some Singleton which is making your connection to each databse.

So, each of your database connections are Singletons, and being instansated by the Factory with the given ENUM you put him as a parameter.