Deployed NetBeans web app that uses JDBC gives "java.sql.SQLException: No suitable driver found"

Question

I have a Java web app using JDBC created in NetBeans deployed to Tomcat that gives a "java.sql.SQLException: No suitable driver found for ..." error. The same app works fine when I run it as a non-web Java app. I have the jar of the JDBC driver I'm using added to the libraries of the web app.

Answer 1

The solution was (in the case of SQL Server) to call DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver()). Make sure to call this before doing any JDBC stuff. You have to call this method with the appropriate JDBC driver. To not hardcode a driver, you can use Class.forName("full.driver.name").newInstance(). This is probably not a NetBeans specific issue, but a Tomcat/JDBC issue.

Note that this seems more like a workaround than a true solution because why would this be required in a web app and not in a non-web app?

Answer 2

Tomcat might need to find that JAR in the server/lib for Tomcat 5.x or /lib for 6.x. There's a hierarchy of class loaders: bootstrap, app server, WAR. If the Tomcat app server is looking for the JDBC driver JAR, it won't find it in the WAR.

But not finding the driver class would get you a ClassNotFoundException.

"java.sql.SQLException: No suitable driver found for ..." usually means that the connection URL has a syntax error. I'd check that first. Don't be fooled by "it works fine as a non-web app." That tells me that your web app is different. If you've set up a JNDI connection pool, which is a recommended best practice, perhaps your URL isn't exactly the same.

Answer 3

java.sql.SQLException: No suitable driver found

To be clear, this exception can have two causes:

1. The driver is not loaded.
2. The (wrong) URL didn't return true for Driver#acceptsURL() for any of the loaded drivers.

Now back to your "workaround" which is actually the solution:

Note that this seems more like a workaround than a true solution because why would this be required in a web app and not in a non-web app?

JDBC4 has a new feature: the DriverManager will auto-load any drivers which are in the classpath and definied in service loaders of JAR files. This will only work in Java6 environments. Maybe your problem is that the webserver is using a Java5 JVM.

I would however not always rely on it and just stick with using Class#forName(). In a well designed DAO layer you need to do this only once. Or, even better, just let the webcontainer manage a datasource for it so that you can take benefit from connection pooling and thus a major improvement in connecting performance.

Further on I would clear out one misconception:

Class.forName("full.driver.name").newInstance()

The newInstance() call is actually not required for properly designed JDBC drivers. A decent JDBC driver will register itself as follows:

static {
    DriverManager.registerDriver(new ThisDriver());
}

But poorly designed JDBC drivers (of which there should actually be none nowadays, this has only occurred in one of the oldest versions of the MySQL JDBC driver if I am correct) did the following:

private ThisDriver() {
    DriverManager.registerDriver(this);
}

which thus really needed the newInstance() call to get itself registered. This became a myth because everyone was since then recommended to do so. That's not needed. Just leave it away and you'll save an unnecessary instantiation.

Hope this helps.