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 driverName = "com.mysql.jdbc.Driver";

String jar = "D:\\Drivers\\mysql-connector-java-5.1.6.jar";

listDrivers();
try {
method.setAccessible(true);

DriverManager.registerDriver(new DriverShim(driver));
System.out.println("is Connection null " + (connection == null) + ". After loading the drivers in driver manager are ");
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. ;-)

• Thank you for your comments. I have seens apache tomcat's class loader, which is a urlclassloader. In which case would the reflection work around would fail? Can you provide an example? – phoenix Nov 25 '14 at 16:34
• You'll run into a SecurityException when you deploy to a Tomcat installation (or any JVM) with the security manager activated and call setAccessible(true), unless your code has been granted the ReflectPermission "suppressAccessChecks". Also, if the system class loader is not an URLCLassLoader as defined by platform or boot options, but I doubt that's common. – JvR Nov 25 '14 at 18:12