How to load the JDBC Driver Dynamically and use it with connection pool without using DriverManager?

Question

I'm trying to load the JDBC Driver dynamically for some purpose. It will use diffrent Driver with different connection pools and these Drivers need to work independently.

Following code will work:

URL u = new URL("https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.15/mysql-connector-java-8.0.15.jar");
        String classname = "com.mysql.cj.jdbc.Driver";
        URLClassLoader ucl = new URLClassLoader(new URL[] { u });
        Driver d = (Driver)Class.forName(classname, true, ucl).newInstance();
        Properties properties = new Properties();
        properties.put("user", "olm_api");
        properties.put("password", "passw0rd");
        Connection connection = d.connect("jdbc:mysql://172.18.30.50:3306/olm_api?useSSL=false&allowPublicKeyRetrieval=true", properties);

As you can see: I need to load the Driver from the Internet. This is because I don't which Driver our clients will use, so I give them the option to specify their Drivers location.

In addition, I need to make several Drivers in different versions work together (e.g. mysql-connector-java-8.0.15, mysql-connector-java-5.1.45, maybe more). I try to use diffirent ClassLoader to Load Different Driver into JVM. But a found didn't working. It seems that DriverManager registered the Drivres and the Driver loaded earlier will always work and the later drivers won't work. I know deregisterDriver() could deregister earlier Driver and may make later Drivers work, but I need to keep earlier Driver working while I use other Drivers. In other words, I wish different Drivers working in the meantime.

I know DataSource doesn't need Drivermanager, but I haven't find how to feed different Driver to DataSource dynamically.

Furthermore, the problem appears when I use BasicDataSource from Apache Commons DBCP. I've write a shorter code to verify my idea. The getDriverClassLoader(url) method used DriverManager to register Driver from url. The code is working, but if I comment the line DriverManager.registerDriver(new DriverShim(d));, it won't work anymore. I think the BasicDataSource class needs DriverManager, but I can't use DriverManager for several Driver at the same time.

So, what should I do then? Implementing a connection pool without DriverManager? Can someone help me?

public class DBCPConnectPool {
    private volatile BasicDataSource dataSource;

    public DBCPConnectPool(String url){
        dataSource = new BasicDataSource();
        dataSource.setMaxActive(8);
        dataSource.setMaxIdle(8);
        dataSource.setMaxWait(1000);
        dataSource.setUrl("jdbc:mysql://172.18.30.50:3306/olm_api?useSSL=false&allowPublicKeyRetrieval=true");
        dataSource.setUsername("olm_api");
        dataSource.setPassword("passw0rd");
        dataSource.setDriverClassLoader(getClassLoader(url));
    }

    private ClassLoader getClassLoader(String url) {
        URL u = null;
        try {
            u = new URL(url);
        } catch (MalformedURLException e) {
            e.printStackTrace();
        }
        String classname = "com.mysql.cj.jdbc.Driver";
        URLClassLoader ucl = new URLClassLoader(new URL[] { u });
        try {
            Driver d = (Driver)Class.forName(classname, true, ucl).newInstance();
            DriverManager.registerDriver(new DriverShim(d));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ucl;
    }

    public Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

Caused by: java.sql.SQLException: No suitable driver
    at java.sql.DriverManager.getDriver(DriverManager.java:315)
    at org.apache.commons.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1437)
    ... 43 more

How to load the JDBC Driver Dynamically and use it with connection pool without using DriverManager?

0 Answers0