3

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

Community
  • 1
  • 1
forhas
  • 11,551
  • 21
  • 77
  • 111
  • 1
    If the mysql jar is in your WEB-INF/lib folder, it should work – Serkan Arıkuşu Jul 01 '12 at 20:43
  • It must be under this specific location? I mean, currently it's under my maven dependencies, and all maven dependencies are in my classpath by default, so what is special with this location which you have suggested? – forhas Jul 01 '12 at 21:31
  • [Maven war plugin](http://maven.apache.org/plugins/maven-war-plugin/usage.html) will do the trick for you. Just define your packaging `war` in case you did not and package with `mvn package` – Serkan Arıkuşu Jul 01 '12 at 21:41
  • 1) Do you use JNDI to obtain the connection resource, or do you configure the connection within your application. 2) Please post the relevant part ("header" and driver dependency) of you POM. – Ralph Jul 02 '12 at 05:44
  • As per your request - I have posted my code as well. I'm not using JNDI but pooling a C3PO data source connection using Spring. – forhas Jul 03 '12 at 16:14

2 Answers2

11

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.

forhas
  • 11,551
  • 21
  • 77
  • 111
0

Add the following dependency to the pom or gradle file

groupId: org.springframework.boot artifactId: spring-boot-starter-data-jpa

and I had to downgrade the version for mariadb-java-client from 3.0 to 2.7.5

Jeroen Heijning
  • 11
  • 2