Using multiple Oracle JDBC drivers in a single Java application?

Question

I want to connect to two different Oracle databases (one 8.0.5.0.0 and one 12c) via JDBC. I have both JDBC drivers that can individually and successfully connect to the corresponding database through simple hello world applications. Below I put them together in one Java application, which, unfortunately, no longer works (with loading both drivers).

I read this post: Process multiple JDBC drivers from MOST VENDOR . Option 1 mentioned here may be the way to go, but there seems to be one serious problem:

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

Any tips on how to connect to these two Oracle databases using one 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 at adavnce!

Answer 1

If you do not register drivers, you avoid loading them with the same class loader.

Then you can create connections using two different drivers, loading them through separate class loaders:

 // 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 can also use a “different” driver through DriverManager , but I'm not sure about that.

There are some angular cases where access to certain Oracle classes becomes a bit complicated, but in general, you can easily use the connections created through it.

Answer 2

I see two different solutions for different constellations.

(Using the same (?) Classes with different versions would usually be ideal for OSGi , or if your application is NetBeans RCP, their modular system. It works using class loaders that separate the software from the modules, so one of the different modules can load different banks with different versions of the same product.)

(Alternatively, you can use another application with its own Oracle kernel, accessed through RMI .)

You can use the same method: either write your own ClassLoader delegation, which loads the correct jar, or uses a conceptually simpler RMI which, however, requires system resource management.

So,

• Do not use import from suppliers; which contradicts the JDBC provider uncertainty.

• Make sure javax.sql interfaces come from java-ee jar.

---

Choosing a ClassLoader Solution:

It is best to make your own delegation driver with the jdbc:myswitch:8: ... protocol. Then it can use two different instances of the loader class. For example, using a URLClassLoader with a file:/... path.

You can create two separate instances of the custom delegation driver so that you can use the delegation template.

 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 the Factory design template to get what kind of connection you would like to have, and then save it on one Singleton that connects to each database.

So, each of your connections to the database is Singletons, and being an instansated Factory with this ENUM, you set it as a parameter.