[FIXED] java.sql.SQLException: No suitable driver, when accessing the DB

Issue

I have created a basic Spring 3.1 - Hibernate 4 - Mysql 5.5 web app running on Tomcat 7. I'm pooling connections via spring + C3PO.

UPDATE: I'm posting my code as well.

My Spring application context:

<!-- Context -->
<context:component-scan base-package="com.yl.tomcat" />

<!-- AOP -->
<aop:aspectj-autoproxy />

<!-- Properties -->
<bean id="applicationProperties"
    class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="location" value="classpath:applicationProperties.properties" />
</bean>

<!-- Data source -->
<!-- Have spring manage connection management along with connection pooling -->
<bean id="dataSourceGlobal" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    destroy-method="close">
    <property name="driverClass" value="${driverClass}" />
    <property name="jdbcUrl" value="${jdbcUrl" />
    <property name="user" value="${user}" />
    <property name="password" value="${password}" />

    <!-- C3P0 Connection pool properties -->
    <property name="minPoolSize" value="${c3p0.min_pool_size}" />
    <property name="maxPoolSize" value="${c3p0.max_pool_size}" />
    <property name="unreturnedConnectionTimeout" value="${c3p0.timeout}" />
    <property name="idleConnectionTestPeriod" value="${c3p0.idle_test_period}"/>
    <property name="maxStatements" value="${c3p0.max_statements}"/>
    <property name="automaticTestTable" value="${c3p0.automatic_test_table}"/>
</bean>

<!-- JPA -->
<!-- Creates a EntityManagerFactory for use with the Hibernate JPA provider -->
<bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="myPU" />
    <property name="dataSource" ref="dataSourceGlobal" />
</bean>

<!-- In order to enable EntityManager injection -->
<bean id="persistenceAnnotation"
    class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor">
    <property name="persistenceUnits">
        <map>
            <entry key="myPU" value="persistence/myPU" />
        </map>
    </property>
</bean>

<!-- Transactions -->
<tx:annotation-driven />

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
    <property name="dataSource" ref="dataSourceGlobal" />
</bean>

As you can see I used a C3PO data source for the pooling, managed by spring container.

My properties file (referred from spring container):

driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/yl
user=root
password=pass

c3p0.min_pool_size=5
c3p0.max_pool_size=20
c3p0.timeout=5000
c3p0.idle_test_period=100
c3p0.max_statements=50
c3p0.automatic_test_table=test_table

Here are my POM dependencies:

    <properties>
    <java-version>1.6</java-version>
    <org.springframework-version>3.1.0.RELEASE</org.springframework-version>
    <hibernate.version>4.1.4.Final</hibernate.version>
    <org.aspectj-version>1.6.9</org.aspectj-version>
    <org.slf4j-version>1.5.10</org.slf4j-version>
</properties>
<dependencies>
    <!-- Spring -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>${org.springframework-version}</version>
        <exclusions>
            <!-- Exclude Commons Logging in favor of SLF4j -->
            <exclusion>
                <groupId>commons-logging</groupId>
                <artifactId>commons-logging</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-webmvc</artifactId>
        <version>${org.springframework-version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-beans</artifactId>
        <version>${org.springframework-version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>${org.springframework-version}</version>
    </dependency>
    <!--Transaction Management Abstraction (depends on spring-core, spring-beans, 
        spring-aop, spring-context) Define this if you use Spring Transactions or 
        DAO Exception Hierarchy(org.springframework.transaction.*/org.springframework.dao.*) -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>${org.springframework-version}</version>
    </dependency>
    <!--JDBC Data Access Library (depends on spring-core, spring-beans, spring-context, 
        spring-tx) Define this if you use Spring's JdbcTemplate API (org.springframework.jdbc.*) -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${org.springframework-version}</version>
    </dependency>
    <!--Object-to-Relation-Mapping (ORM) integration with Hibernate, JPA, and 
        iBatis.(depends on spring-core, spring-beans, spring-context, spring-tx) 
        Define this if you need ORM (org.springframework.orm.*) -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-orm</artifactId>
        <version>${org.springframework-version}</version>
    </dependency>

    <!-- DB Connection -->
    <dependency>
        <groupId>c3p0</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.1.2</version>
        <type>jar</type>
        <scope>compile</scope>
    </dependency>

    <!-- MySQL database driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.20</version>
    </dependency>

    <!-- Hibernate -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-validator</artifactId>
        <version>4.3.0.Final</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
        <version>${hibernate.version}</version>
    </dependency>
    <dependency>
        <groupId>javax.validation</groupId>
        <artifactId>validation-api</artifactId>
        <version>1.0.0.GA</version>
        <scope>provided</scope>
    </dependency>

    <!-- AspectJ -->
    <dependency>
        <groupId>org.aspectj</groupId>
        <artifactId>aspectjrt</artifactId>
        <version>${org.aspectj-version}</version>
    </dependency>

    <!-- Logging -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>${org.slf4j-version}</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>jcl-over-slf4j</artifactId>
        <version>${org.slf4j-version}</version>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>${org.slf4j-version}</version>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.15</version>
        <exclusions>
            <exclusion>
                <groupId>javax.mail</groupId>
                <artifactId>mail</artifactId>
            </exclusion>
            <exclusion>
                <groupId>javax.jms</groupId>
                <artifactId>jms</artifactId>
            </exclusion>
            <exclusion>
                <groupId>com.sun.jdmk</groupId>
                <artifactId>jmxtools</artifactId>
            </exclusion>
            <exclusion>
                <groupId>com.sun.jmx</groupId>
                <artifactId>jmxri</artifactId>
            </exclusion>
        </exclusions>
        <scope>runtime</scope>
    </dependency>

    <!-- @Inject -->
    <dependency>
        <groupId>javax.inject</groupId>
        <artifactId>javax.inject</artifactId>
        <version>1</version>
    </dependency>

    <!-- Misc -->
    <dependency>
        <groupId>commons-lang</groupId>
        <artifactId>commons-lang</artifactId>
        <version>2.6</version>
    </dependency>

    <!-- Servlet -->
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>servlet-api</artifactId>
        <version>2.5</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>javax.servlet.jsp</groupId>
        <artifactId>jsp-api</artifactId>
        <version>2.1</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>jstl</artifactId>
        <version>1.2</version>
    </dependency>

    <!-- Test -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.9</version>
        <scope>test</scope>
    </dependency>
</dependencies>

My web.xml is a standard Spring MVC web.xml with the addition of my persistence unit definition:

<persistence-unit-ref>
    <persistence-unit-ref-name>persistence/myPU</persistence-unit-ref-name>
    <persistence-unit-name>myPU</persistence-unit-name>
</persistence-unit-ref>

My DAO is injected with the Entity-manager:

@PersistenceContext
protected EntityManager entityManager;

And last, here is my persistence.xml which is located under my META-INF folder:

<persistence-unit name="myPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
        <property name="hibernate.transaction.flush_before_completion"
            value="true" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLInnoDBDialect" />
        <property name="hibernate.show_sql" value="true" />
    </properties>
</persistence-unit>

When trying to access to DB I'm getting java.sql.SQLException: No suitable driver:

java.sql.SQLException: No suitable driver java.sql.DriverManager.getDriver(DriverManager.java:289) com.mchange.v2.c3p0.DriverManagerDataSource.driver(DriverManagerDataSource.java:223) com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:119) com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:143) com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:132) com.mchange.v2.c3p0.impl.C3P0PooledConnectionPoolManager.initializeAutomaticTestTable(C3P0PooledConnectionPoolManager.java:772) com.mchange.v2.c3p0.impl.C3P0PooledConnectionPoolManager.createPooledConnectionPool(C3P0PooledConnectionPoolManager.java:696) com.mchange.v2.c3p0.impl.C3P0PooledConnectionPoolManager.getPool(C3P0PooledConnectionPoolManager.java:257) com.mchange.v2.c3p0.impl.C3P0PooledConnectionPoolManager.getPool(C3P0PooledConnectionPoolManager.java:271) com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128) org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:70) org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:281) org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:297) org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:169) org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.extractPhysicalConnection(ConnectionProxyHandler.java:82) org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138) org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) $Proxy45.prepareStatement(Unknown Source) org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147) org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166) org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145) org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1711) org.hibernate.loader.Loader.doQuery(Loader.java:828) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) org.hibernate.loader.Loader.loadEntity(Loader.java:2033) org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:82) org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:72) org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3719) org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:449) org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:418) org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:204) org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:251) org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:148) org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1079) org.hibernate.internal.SessionImpl.access$2200(SessionImpl.java:172) org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2425) org.hibernate.internal.SessionImpl.get(SessionImpl.java:975) org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:807) org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:781) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:601) org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:365) $Proxy44.find(Unknown Source) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:601) org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:240) $Proxy21.find(Unknown Source) com.yl.tomcat.dal.impl.BasicJpaDao.read(BasicJpaDao.java:48) com.yl.tomcat.app.bl.UserService.updateUserName(UserService.java:17) com.yl.tomcat.web.HomeController.String(HomeController.java:53) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:601) org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:212) org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126) org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578) org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882) org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789) javax.servlet.http.HttpServlet.service(HttpServlet.java:641) javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

In similar threads (this and this) the solution is provided by putting the mysql driver jar in the server lib folder. ($CATALINA_HOME/lib). Currently I have the jar only in my application classpath and I'm wondering if the above solution is the only one available (I don't like it, it couples my application to my tomcat server, which is not optimal in my opinion)?

Anyways - even when I copied the mysql jar to tomcat's lib library it didn't change anything :( Did I miss something along the way?

Any help is highly appreciated, Yogi

Solution

Ok, I got it.

as stated here: http://doc.51windows.net/mysql/?url=/mysql/ch23s03.html SQLException 'No Suitable Driver' means that Either the driver is not in your CLASSPATH or your URL format is incorrect.

Apparently I was missing a bracket there:

<property name="jdbcUrl" value="${jdbcUrl" />

I have went through many solution proposals such as:

• Copy the mysql jar to tomcat. This is not required since it is in my classpath already.
• Install a different mysql connector (5.1.2-bin instead of 5.1.2) which is also not required.
• Downdgrade Hibernate version from 4.1.4 to 4.1.1 - not requied as well.

So my URL format was incorrect. After fixing this I have encountered a few other issues.

For example, at some point I decided to add the persistence.xml location to my entitymanager definition:

<property name="persistenceXmlLocation" value="META-INF/persistence.xml"/>

But then got:

ContainerEntityManagerFactoryBean can't find persistence.xml in war file

The problem was that I had to add the classpath before my path (since META-INF is naturaly under the classpath in a standard maven project format):

<property name="persistenceXmlLocation" value="classpath:META-INF/persistence.xml"/>

Than for some reason I got:

java.lang.ClassNotFoundException: org.aspectj.util.PartialOrder$PartialComparable

Adding the aspectjweaver dependency to my pom.xml solved the issue:

  <dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
    <version>1.6.6</version>
  </dependency>

After fixing that I got:

ERROR: org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Access denied for user 'testUser '@'localhost' (using password: YES)

I even tried re-installing mysql but with no success so far. I gues I'll try to open a different thread for this one.

Answered By - forhas
Answer Checked By - Mary Flores (JavaFixing Volunteer)