SQLException: no suitable driver found for jdbc:mysql between two maven projects

Question

I've 2 maven projects in my Eclipse (v4.7.0) workspace.

The first project contains some utility stuffs and holds the connection to my MySQL database through JDBC driver.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.example</groupId>
  <artifactId>dbtools</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>
  <name>DBTools</name>

<properties>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
</properties>

<dependencies>
    <!-- JDBC for MySQL -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>6.0.6</version>
    </dependency>
    <dependency>
        <groupId>commons-beanutils</groupId>
        <artifactId>commons-beanutils</artifactId>
        <version>1.9.3</version>
    </dependency>
    <dependency>
        <groupId>javax.persistence</groupId>
        <artifactId>persistence-api</artifactId>
        <version>1.0.2</version>
    </dependency>
</dependencies>
</project>

This first project is built as a jar and it is included in the second project (that contains the main application) as a maven dependency as shown in the pom.xml below:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <groupId>com.example</groupId>
 <artifactId>mainapp</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <packaging>war</packaging>
 <name>MainApp</name>

<properties>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
    <jersey2.version>2.25.1</jersey2.version>
    <jaxrs.version>2.0.1</jaxrs.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
</properties>

<build>
    <plugins>
        <plugin>
            <artifactId>maven-clean-plugin</artifactId>
            <version>2.6.1</version>
            <configuration>
                <filesets>
                    <fileset>
                        <directory>C:/apps/tomcat/webapps/mainapp</directory>
                        <includes>
                            <include>**</include>
                        </includes>
                        <followSymlinks>false</followSymlinks>
                    </fileset>
                </filesets>
            </configuration>
        </plugin>
        <plugin>
            <artifactId>maven-resources-plugin</artifactId>
            <executions>
                <execution>
                    <id>copy-appCtx</id>
                    <phase>generate-resources</phase>
                    <goals>
                        <goal>copy-resources</goal>
                    </goals>
                    <configuration>
                        <outputDirectory>C:/apps/tomcat/webapps/</outputDirectory>
                        <overwrite>true</overwrite>
                        <resources>
                            <resource>
                                <directory>../mainapp/target</directory>
                                <includes>
                                    <include>mainapp-0.0.1-SNAPSHOT.war</include>
                                </includes>
                            </resource>
                        </resources>
                    </configuration>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

<dependencies>
    <!-- JAX-RS -->
    <dependency>
        <groupId>javax.ws.rs</groupId>
        <artifactId>javax.ws.rs-api</artifactId>
        <version>${jaxrs.version}</version>
    </dependency>
    <!-- Jersey 2.25.1 -->
    <dependency>
        <groupId>org.glassfish.jersey.containers</groupId>
        <artifactId>jersey-container-servlet</artifactId>
        <version>${jersey2.version}</version>
    </dependency>
    <dependency>
        <groupId>org.glassfish.jersey.core</groupId>
        <artifactId>jersey-server</artifactId>
        <version>${jersey2.version}</version>
    </dependency>
    <dependency>
        <groupId>org.glassfish.jersey.core</groupId>
        <artifactId>jersey-client</artifactId>
        <version>${jersey2.version}</version>
    </dependency>

    <!-- Local DBTool -->
    <dependency>
        <groupId>com.example</groupId>
        <artifactId>dbtools</artifactId>
        <version>0.0.1-SNAPSHOT</version>
    </dependency>
</dependencies>
</project>

The 2nd project that is the main application is deployed as a war file. When I start the Tomcat (with 2nd app's war) I got a SQLException at runtime:

java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/mydb?autoReconnect=true&useSSL=false
at java.sql.DriverManager.getConnection(DriverManager.java:689)

I've read several question here in StackOverflow about this exception but I still haven't found a working solution :(

Inside the lib folder of my Tomcat installation folder I've placed the mysql-connector-java-6.0.6.jar.

I've also noticed that in the JAR file of the first project (opening it as an archive) there isn't the JDBC connector inside. Is it normal?

Inside the first project, I make the connection this way:

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/torre?autoReconnect=true&useSSL=false", "dbuser", "dbpass");

conn has type java.sql.Connection.

I've also tried to put:

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

before che "conn = ..." line but I got the same result :(

I'm using Tomcat 8.5 and JDK 1.8.

Any ideas how I can get rid of this problem? Am I missing something in the Maven or Eclipse build configuration?

Thanks in advance for your help! :)

Flocculus

Roberto, and when you search mysql-connector*.jar in you application folder - does it give you any result? E.g. webapps/<<your_application>>/WEB-INF/lib? Potentially it can be one more connector jar on the classpath, which came as transitive dependency. – Oar

Yes, if I search "mysql-connector*.jar" in "webapps/mainapp-0.0.1-SNAPSHOT/WEB-INF/lib" the only result is: mysql-connector-java-6.0.6.jar – Flocculus

does it make sense to make a screenshot of WEB-INF/lib so people could review any jars which could provide concurrent driver implementation? – Oar

Sure man, here you have WEB-INF/lib of the mainapp war deployed: ibb.co/b3UAu5. But I didn't understand what you said about transitive dependency...what is the problem? That there are more than one driver so they make a conflict? – Flocculus

The MySQL Connector/J 6.0.6 is a development version (of a branch now renamed to version 8), you should not use it in production. Try version 5.1.43 instead. – Roll

@MarkRotteveel thanks for the informations! I've switched to v5.1.43 but unfortunately still doesn't work. No suitable driver found for jdbc:mysql etc :( – Flocculus

@RobertoMilani could you run the quick example which I built: download. Tomcat should be plain without any additional driver (remove connector from tomcat/lib. It works for me. The only thing which should be changed is connection properties from index.jsp file. For me it works. If it won't work for you it will mean that something is wrong with your environment rather than with code. And we will figure it out. After navigating to host:port/app it should create a sample table in the schema which you will set in index.jsp – Oar

Your example revealed some really interesting things: I've run your project but it throws an InvalidConnectionAttributeException saying that "The server time zone value is unrecognized or represents more than one time zone" but I've fixed it by adding: "useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC" in the JDBC URI. A part from this minor problem, the project works with com.mysql.jdbc.Driver but giving a warning saying that the driver it's deprecated. I've changed to com.mysql.cj.jdbc.Driver and it works perfect with no warnings. – Flocculus

Then I switched to my project and I've added Class.forName("com.mysql.jdbc.Driver") before the conn = DriverManager.getConnection method and it works but WITHOUT saying it's deprecated. If I put Class.forName("com.mysql.cj.jdbc.Driver") in my project I got a ClassNotFoundException at runtime. How is it possible? The Tomcat context is the same for my app and yours. Without any Class.forName(..) and only with conn = DriverManager.getConnection method, both in my project and in yours I got a SQLException: No suitable driver found for jdbc:mysql. – Flocculus

I've omitted the Class.forName() statement because I've read that since Java 6 that includes JDBC 4.0, we no longer need to explicitly load JDBC drivers using Class.forName(). (source: onjava.com/2006/08/02/jjdbc-4-enhancements-in-java-se-6.html ). I'm compiling with JDK1.8, but how is it possible? Have I to check which version of JDBC I have? What do you think? Thanks for your help, I've just given you some reputation :) – Flocculus

@RobertoMilani, I think I didn't catch you on 100%. So if you still have issues with running your version. Does it make sense to give me your war file or even the whole preconfigured tomcat so I could see issue in details. So we could finaly fix it and provide here a proper solution =) – Oar

Sure man, here you have the war file: ufile.io/3o46a and here you have the whole workspace with sources: ufile.io/4h9qs :) All these stuffs are running on a Tomcat v8.5.20 dowloaded from the official site without any particular setting. I didn't touch the lib folder of the Tomcat that contains the following files: ibb.co/cmaJp5 Please check the MySQLConnector class inside dbtools project. Thanks for your help man! :) – Flocculus

You usually use a datasource when using an app server, see how here: #3485677 – Cue

Hi @Tome, I've read the approved answer on the question you have linked. 1) I'm doing it the basic way using DriverManager#getConnection() so no need to put MySQL connector in the tomcat/lib folder but even if I put it there I have my problem. 2) I'm doing it the basic DriverManager way (see sources that I've attached). 3) web.xml is present in the mainapp project (deployed as war). In the dbtools probject (deployed as jar) is not present; have I to put also in the dbtools project? Thanks – Flocculus

"Then I switched to my project and I've added Class.forName("com.mysql.jdbc.Driver") before the conn = DriverManager.getConnection method and it works but WITHOUT saying it's deprecated. If I put Class.forName("com.mysql.cj.jdbc.Driver") in my project I got a ClassNotFoundException at runtime. How is it possible?" You will only get this warning if you are using the Mysql Connector/J 6.x or 8.x. The version 5.x does not now that the Driver will be moved to a new package eventually so there is no warning. – Humidifier

@RobertoMilani, Can you please try by removing mysql connector from tomcat lib? You have in WEB-INF/lib through transitive dependency of project 1. This issue is related to driver registration. – Mendicity

Or if you do not want to remove it, then add <scope>provided</scope> to mysql driver in project1. Once this is done, then build project1 as mvn clean install; later in project2 mvn clean package. – Mendicity

Answer 1

Providing input as per my understanding

you mentioned that

I've also noticed that in the JAR file of the first project (opening it as an archive) there isn't the JDBC connector inside. Is it normal?

when you are building jar unlike war you will not find the dependencies[only one jar without maven-shade-plugin where it will not have its dependencies]

so use maven-shaded-plugin https://maven.apache.org/plugins/maven-shade-plugin/examples/resource-transformers.html

<project>        
  ...
  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-shade-plugin</artifactId>
        <version>3.1.0</version>
        <executions>
          <execution>
            <goals>
              <goal>shade</goal>
            </goals>
            <configuration>
              <transformers>
                <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                  <manifestEntries>
                    <Main-Class>com.yourpackage.YourClass</Main-Class>
                    <X-Compile-Source-JDK>${maven.compile.source}</X-Compile-Source-JDK>
                    <X-Compile-Target-JDK>${maven.compile.target}</X-Compile-Target-JDK>
                  </manifestEntries>
                </transformer>
              </transformers>
            </configuration>
          </execution>
        </executions>
      </plugin>
    </plugins>
  </build>
  ...
</project>

in your pom.xml [of project-1 for generating jar]

Now if you see u will be having two jars after maven build (one the original and the other jar with dependencies in pom)

if you extract the one which is having more size[the one with dependencies] u can find the jdbc jar here

now for project 2

when you include project-1 as a dependency [make sure the one which is bigger is included instead of smaller one(without dependencies) ] If you are using remote repository when u are pushing , the bigger one will get pushed so when u mention it in pom [project-1] then the bigger jar will be downloaded once after building it check the project-1 jar whether it is having its own dependencies

And finally build the war and deploy

Source : Worked on the same kind of Scenario, and it looks similar to the one I worked before

Let me know the result [Mostly it will work, if not mycontact :[email protected] will try to help]

Thanks :)

Cristionna

Few doubts : when u r testing in db tools , how u r testing whether u connected to db or not like how u run it as application, from main or through junit ? try to run by calling the class method after jar creation[like a seperate app] through cmd <Main-Class>com.yourpackage.YourClass</Main-Class> here you have to give ur class name that u r about to call [where u will call this in application-2] in this way even before including in project-2 u can ensure project-1 is working fine :) Thanks – Cristionna

Answer 2

First of all get familiar with auto-class loading for JDBC 4.0 here.

Now, see contents of META-INF/services/java.sql.Driver which is com.mysql.cj.jdbc.Driver. Thus you are getting the exception.

Please update the mysql connector dependency of project1 to below

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.41</version>
</dependency>

This will be automatically included in WEB-INF/lib of project2 thro' transitive dependency. Thus you do not need to include it in tomcat lib.

Remove the 6.0.6 mysql connector from the tomcat lib.

If still the issue persist, please try to register it explicitly before acquiring the connection

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

Mendicity