mysqljpa-2.0datasourceeclipse-virgo

Eclipse Virgo Injected JPA EntityManager does not connect to Database

I am using Eclipse Virgo and I am trying to get annotation driven transaction management running but I keep running into the same issue.

Problem is that the EntityManager is injected and not null. But any query fails due to a connection error.

I used the setup according to GreenPages.

I have the following EntityManageFactory and TransactionManager configured

<bean id="emf"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceXmlLocation" value="classpath:META-INF/persistence.xml" />
    <property name="dataSource" ref="dataSource" />
    <property name="persistenceUnitName" value="ShiftManagement" />
    <property name="jpaVendorAdapter">
        <bean
            class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter">
            <property name="showSql" value="true" />
            <property name="generateDdl" value="true" />
            <property name="databasePlatform"
                value="org.eclipse.persistence.platform.database.MySQLPlatform" />
        </bean>
    </property>
    <property name="packagesToScan" value="....." />
    <property name="jpaDialect">
        <bean class="org.springframework.orm.jpa.vendor.EclipseLinkJpaDialect" />
    </property>
</bean>

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

<tx:annotation-driven transaction-manager="transactionManager" />

Also I have the following DataSources configured. Problem is, I tried them all, but when I try to connect to the database using a query, it doesn't work. Now, I am 100% sure that the credentials are OK, the DB is UP etc etc

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/shiftmanagement" />
    <property name="username" value="root" />
    <property name="password" value="rootroot" />
    <property name="initialSize" value="5" />
    <property name="maxActive" value="20" />
    <property name="maxIdle" value="5" />
    <!-- TRANSACTION_READ_COMMITTED = 2 -->
    <property name="defaultTransactionIsolation" value="2" />
    <!-- TRANSACTION_READ_UNCOMMITTED = 1 -->
    <!-- <property name="defaultTransactionIsolation" value="1" /> -->
    <property name="validationQuery" value="select 1 from dual" />
    <property name="testOnBorrow" value="true" />
</bean>

Error when using commons-dbcp

Caused by: java.lang.UnsupportedOperationException: Not supported by BasicDataSource
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:901)
at org.eclipse.persistence.sessions.JNDIConnector.connect(JNDIConnector.java:132)
at org.eclipse.persistence.sessions.DatasourceLogin.connectToDatasource(DatasourceLogin.java:162)

Using Spring Datasource 

<bean id="dataSource2"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/shiftmanagement" />
    <property name="username" value="root" />
    <property name="password" value="rootroot" />
</bean>



Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/shiftmanagement
at java.sql.DriverManager.getConnection(DriverManager.java:602)
at java.sql.DriverManager.getConnection(DriverManager.java:154)

OK so then I tried the promising tomcat-jdbc library

<bean id="dataSource3" class="org.apache.tomcat.jdbc.pool.DataSource"
    destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/shiftmanagement" />
    <property name="username" value="root" />
    <property name="password" value="rootroot" />
    <property name="initialSize" value="5" />
    <property name="maxActive" value="10" />
    <property name="maxIdle" value="5" />
    <property name="minIdle" value="2" />
</bean>

And the following similar exception pops up

Caused by: org.eclipse.virgo.kernel.osgi.framework.ExtendedClassNotFoundException: com.mysql.jdbc.Driver in KernelBundleClassLoader: [bundle=org.apache.tomcat.jdbc_1.1.0.1]
at org.eclipse.virgo.kernel.userregion.internal.equinox.KernelBundleClassLoader.loadClass(KernelBundleClassLoader.java:139)
at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:247)
at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:246)

I am using the following persistence.xml

<persistence version="1.0"
xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
<persistence-unit name="ShiftManagement" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    ....
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <properties>
        <!-- Database options -->
        <property name="eclipselink.target-database" value="MySQL"/>
        <property name="eclipselink.weaving" value="false"/>
        <property name="eclipselink.orm.throw.exceptions" value="true"/>
        <!-- 
        <property name="eclipselink.jdbc.read-connections.min" value="1"/>
        <property name="eclipselink.jdbc.write-connections.min" value="1"/>
         -->                        

        <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
        <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/shiftmanagement"/>
        <property name="javax.persistence.jdbc.user" value="root"/>
        <property name="javax.persistence.jdbc.password" value="rootroot"/>

        <!-- Logging -->
        <property name="eclipselink.logging.level" value="FINE" />
        <property name="eclipselink.logging.timestamp" value="false" />
        <property name="eclipselink.logging.session" value="false" />
        <property name="eclipselink.logging.thread" value="false" />
    </properties>

</persistence-unit>

I inject the EntityManager using

@PersistenceContext
private EntityManager em;

I read some posts to remove the username and password from the persistence.xml but then the system complains there are that user = [null].

Any help is greatly appreciated.

Note: mysqld is running and I can connect using mysql -uroot -prootroot

Solution

  • OK so I used a fourth datasource and all my problems dissapeared :-)

    <bean id="dataSource"
    class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/shiftmanagement" />
    <property name="username" value="${javax.persistence.jdbc.user}" />
    <property name="password" value="${javax.persistence.jdbc.password}" />
</bean>