The infamous java.sql.SQLException: No suitable driver found

geoserver, jdbc, postgresql, tomcat

Solution

The infamous java.sql.SQLException: No suitable driver found

This exception can have basically two causes:

1. JDBC driver is not loaded

In case of Tomcat, you need to ensure that the JDBC driver is placed in server's own `/lib` folder.

Other servers have a similar way of placing the JAR file:

- GlassFish: put the JAR file in `/glassfish/lib`

- WildFly: put the JAR file in `/standalone/deployments`

Or, when you're actually not using a server-managed connection pool data source which you e.g. obtain via JNDI `@Resource` or configure in JPA's `persistence.xml`, but when you are manually fiddling around with `DriverManager#getConnection()` the low level way in a custom class in WAR, then you need instead to place the JDBC driver in WAR's `/WEB-INF/lib` and perform ..

Class.forName("com.example.jdbc.Driver");

.. in your custom code before the first `DriverManager#getConnection()` call, whereby you make sure that you do not swallow/ignore any `ClassNotFoundException` which can be thrown by it and continue the code flow as if nothing exceptional happened. See also Where do I have to place the JDBC driver for Tomcat's connection pool? and Submitting form to Servlet which interacts with database results in blank page.

2. Or, JDBC URL is in wrong syntax

You need to ensure that the specified JDBC URL is conform the JDBC driver documentation and keep in mind that it's usually case sensitive. When the JDBC URL does not return `true` for `Driver#acceptsURL()` for any of the loaded drivers, then you will also get exactly this exception.

In case of PostgreSQL it is documented here.

With JDBC, a database is represented by a URL (Uniform Resource Locator). With PostgreSQL®, this takes one of the following forms:

- `jdbc:postgresql:database`

- `jdbc:postgresql:/`

- `jdbc:postgresql://host/database`

- `jdbc:postgresql://host/`

- `jdbc:postgresql://host:port/database`

- `jdbc:postgresql://host:port/`

In case of MySQL it is documented here.

This is the generic format of the connection URL:

`protocol//[hosts][/database][?properties]`

This is an example of a simple single-host connection URL:

`jdbc:mysql://host1:33060/sakila`

In case of Oracle it is documented here.

The Thin driver offers these kinds of URL formats for all of them:

SID (no longer recommended by Oracle to be used):

`jdbc:oracle:thin:[<user>/<password>]@<host>[:<port>]:<SID>`

Services:

`jdbc:oracle:thin:[<user>/<password>]@//<host>[:<port>]/<service>`

TNSNames:

`jdbc:oracle:thin:[<user>/<password>]@<TNSName>`

See also:

- Where do I have to place the JDBC driver for Tomcat's connection pool?

- How to install JDBC driver in Eclipse web project without facing java.lang.ClassNotFoundexception

- How should I connect to JDBC database / datasource in a servlet based application?

- What is the difference between "Class.forName()" and "Class.forName().newInstance()"?

- Connect Java to a MySQL database

Problem

I'm trying to add a database-enabled JSP to an existing Tomcat 5.5 application (GeoServer 2.0.0, if that helps). The app itself talks to Postgres just fine, so I know that the database is up, user can access it, all that good stuff. What I'm trying to do is a database query in a JSP that I've added. I've used the config example in the Tomcat datasource example pretty much out of the box. The requisite taglibs are in the right place -- no errors occur if I just have the taglib refs, so it's finding those JARs. The postgres jdbc driver, postgresql-8.4.701.jdbc3.jar is in $CATALINA_HOME/common/lib. Here's the top of the JSP: ``` <%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <sql:query var="rs" dataSource="jdbc/mmas"> select current_validstart as ValidTime from runoff_forecast_valid_time </sql:query> ``` The relevant section from $CATALINA_HOME/conf/server.xml, inside the `<Host>` which is in turn within `<Engine>`: ``` <Context path="/gs2" allowLinking="true"> <Resource name="jdbc/mmas" type="javax.sql.Datasource" auth="Container" driverClassName="org.postgresql.Driver" maxActive="100" maxIdle="30" maxWait="10000" username="mmas" password="very_secure_yess_precious!" url="jdbc:postgresql//localhost:5432/mmas" /> </Context> ``` These lines are the last in the tag in webapps/gs2/WEB-INF/web.xml: ``` <resource-ref> <description> The database resource for the MMAS PostGIS database </description> <res-ref-name> jdbc/mmas </res-ref-name> <res-type> javax.sql.DataSource </res-type> <res-auth> Container </res-auth> </resource-ref> ``` Finally, the exception: ``` exception org.apache.jasper.JasperException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver" [...wads of ensuing goo elided] ```

Original source

Related problems