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! :)

Answer 1

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");

Answer 2

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 :)