The infamous java.sql.SQLException: No suitable dr

Question

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]

Answer 1

I had this exact issue when developing a Spring Boot application in STS, but ultimately deploying the packaged war to WebSphere(v.9). Based on previous answers my situation was unique. ojdbc8.jar was in my WEB-INF/lib folder with Parent Last class loading set, but always it says it failed to find the suitable driver.

My ultimate issue was that I was using the incorrect DataSource class because I was just following along with online tutorials/examples. Found the hint thanks to David Dai comment on his own question here: Spring JDBC Could not load JDBC driver class [oracle.jdbc.driver.OracleDriver]

Also later found spring guru example with Oracle specific driver: https://springframework.guru/configuring-spring-boot-for-oracle/

Example that throws error using org.springframework.jdbc.datasource.DriverManagerDataSource based on generic examples.

@Config
@EnableTransactionManagement
public class appDataConfig {
 \* Other Bean Defs *\
    @Bean
    public DataSource dataSource() {
        // configure and return the necessary JDBC DataSource
        DriverManagerDataSource dataSource = new DriverManagerDataSource("jdbc:oracle:thin:@//HOST:PORT/SID", "user", "password");
        dataSource.setSchema("MY_SCHEMA");
        return dataSource;
    }
}

And the corrected exapmle using a oracle.jdbc.pool.OracleDataSource:

@Config
@EnableTransactionManagement
public class appDataConfig {
/* Other Bean Defs */
@Bean
    public DataSource dataSource() {
        // configure and return the necessary JDBC DataSource
        OracleDataSource datasource = null;
        try {
            datasource = new OracleDataSource();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        datasource.setURL("jdbc:oracle:thin:@//HOST:PORT/SID");
        datasource.setUser("user");
        datasource.setPassword("password");

        return datasource;
    }
}

Answer 2

It might be worth noting that this can also occur when Windows blocks downloads that it considers to be unsafe. This can be addressed by right-clicking the jar file (such as ojdbc7.jar), and checking the 'Unblock' box at the bottom.

Windows JAR File Properties Dialog:

Answer 3

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

This exception can have basically two causes:

1. JDBC driver is not loaded

You need to ensure that the JDBC driver is placed in server's own /lib folder.

Or, when you're actually not using a server-managed connection pool data source, but are manually fiddling around with DriverManager#getConnection() in WAR, then you need to place the JDBC driver in WAR's /WEB-INF/lib and perform ..

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

.. in your 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?

2. Or, JDBC URL is in wrong syntax

You need to ensure that the 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://host/database
• jdbc:postgresql://host:port/database

In case of MySQL it is documented here.

The general format for a JDBC URL for connecting to a MySQL server is as follows, with items in square brackets ([ ]) being optional:

jdbc:mysql://[host1][:port1][,[host2][:port2]]...[/[database]] » [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

In case of Oracle it is documented here.

There are 2 URL syntax, old syntax which will only work with SID and the new one with Oracle service name.

Old syntax jdbc:oracle:thin:@[HOST][:PORT]:SID

New syntax jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE

---

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

Answer 4

I found the followig tip helpful, to eliminate this issue in Tomcat -

be sure to load the driver first doing a Class.forName(" org.postgresql.Driver"); in your code.

This is from the post - https://www.postgresql.org/message-id/[e-mail]

The jdbc code worked fine as a standalone program but, in TOMCAT it gave the error -'No suitable driver found'

Answer 5

url="jdbc:postgresql//localhost:5432/mmas"

That URL looks wrong, do you need the following?

url="jdbc:postgresql://localhost:5432/mmas"

Answer 6

I was using jruby, in my case I created under config/initializers

postgres_driver.rb

$CLASSPATH << '~/.rbenv/versions/jruby-1.7.17/lib/ruby/gems/shared/gems/jdbc-postgres-9.4.1200/lib/postgresql-9.4-1200.jdbc4.jar'

or wherever your driver is, and that's it !