Loading the JDBC driver jar dynamically from external location I have a small project in which I get the URL, username, password, and driver from a text file and I have to fire a query on the database by dynamically reading the jar file in a predefined directory, register the jar, get a connection object and possibly de-register it. Each query may talk to a different database schema or even DBMS. The directory consists of all JDBC driver jars. I need to pick one based on the driver class name that I get from the previously mentioned text file.

I have written the following code so far. Does it has any memory issues or performance issues? 

protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String dbUrl = "jdbc:mysql://localhost:3306/test";
        String userName = "foo";
        String password = "bar";
        String driverName = "com.mysql.jdbc.Driver";

        final URLClassLoader loader = (URLClassLoader) ClassLoader.getSystemClassLoader();
        String jar = "D:\\Drivers\\mysql-connector-java-5.1.6.jar";

        System.out.println(this.getClass().getClassLoader().getClass() + " is the class loader");
        System.out.println("Before loading the drivers in driver manager are ");
        listDrivers();
        try {
            Method method = URLClassLoader.class.getDeclaredMethod("addURL", URL.class);
            method.setAccessible(true);
            method.invoke(loader, new File(jar).toURI().toURL());

            Class&lt;?&gt; classToLoad = Class.forName(driverName, true, loader);
            Driver driver = (Driver) classToLoad.newInstance();
            DriverManager.registerDriver(new DriverShim(driver));
            Connection connection = DriverManager.getConnection(dbUrl, userName, password);
            System.out.println("is Connection null " + (connection == null) + ". After loading the drivers in driver manager are ");
            //Do business specific action
            listDrivers();
            DriverManager.deregisterDriver(driver);
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (MalformedURLException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
    }

    private void listDrivers() {
        java.util.Enumeration enumeration = java.sql.DriverManager.getDrivers();
        while (enumeration.hasMoreElements()) {
            Driver driverAsObject = (Driver) enumeration.nextElement();
            System.out.println("Driver: " + driverAsObject + ":" + driverAsObject.getMajorVersion() + ":" + driverAsObject.getMinorVersion());
        }
    }


The dependency DriverShim is obtained from here.

How can one do it better?
There is a possible performance issue in connecting and disconnecting from a database with each request.  Setting up a connection can be expensive, so if these connections are not pooled/cached/reused on the driver level, you may want to look into libraries or techniques for JDBC connection pooling.

(Also be sure to close connections you get from your driver.  Database connections are external resources that can not be reliably handled by the garbage collectors, and need to be explicitly cleaned up.)

As far as I can tell, you already have the driver you want; why work through DriverManager?  Ask your driver for the connection:


  Connection   connect(String url, Properties info)
  
  Attempts to make a database connection to the given URL.


The reflection workaround assumes that the system class loader is an URLClassLoader and that you have the privileges to call its addURL method.  That's not necessarily true, especially for a server environment.

And, uh, I don't think it really needs saying, but the error handling is... less than optimal. ;-)