EclipseLink+Spring+ComboPooledDataSource+MySQL ORM not persisting

[babasholaking]

Help anybody as I have waisted over a week with this problem. Currently considering going to Hibernate or DataNucleus(JPOX) instead of EclipseLink.

My issue is that getJpaTemplate().persist(entity) is not persisting to database! Looking at the logs I can see that no Insert statement issued by Eclipselink. Reviewing some of the forums (here and eclipselink), seems to suggest transaction related issue but I can't seem to be able resolve this.

The following is my set-up;

datasourceContext.xml

<bean id="dataSource" parent="c3poAbstractDS"
         p:driverClass="${db.driverClassName}" p:jdbcUrl="${db.url}"
         p:user="${db.username}" p:password="${db.password}" lazy-init="false" />

<bean id="c3poAbstractDS" class="com.mchange.v2.c3p0.ComboPooledDataSource"
         destroy-method="close" abstract="true" p:initialPoolSize="${db.initialPoolSize}"
         p:minPoolSize="${db.minPoolSize}" p:maxPoolSize="${db.maxPoolSize}"
         p:acquireIncrement="2" p:acquireRetryAttempts="30"
         p:acquireRetryDelay="100" p:checkoutTimeout="3000"
         p:maxIdleTime ="1000" p:maxIdleTimeExcessConnections="300"
         p:maxStatements="500" p:maxStatementsPerConnection ="300"
         p:maxConnectionAge="3000"
         p:idleConnectionTestPeriod="30" p:testConnectionOnCheckin="true"
         p:testConnectionOnCheckout="false"
         p:numHelperThreads="5" p:breakAfterAcquireFailure="false"
         p:autoCommitOnClose="false"
         p:forceIgnoreUnresolvedTransactions="true"
         p:usesTraditionalReflectiveProxies="false"
         p:preferredTestQuery="select 1;" />

applicationContext.xml entries:

   <!-- Generic -->
   <context:component-scan base-package="com.xxx.leopersistence" />

   <context:annotation-config />
   <aop:aspectj-autoproxy />

   <!-- Configure LoadTimeWeaver. Be sure to start JVM with -javaagent=spring-agent.jar -->
   <context:load-time-weaver aspectj-weaving="on" />

   <!-- JPA annotations bean post processor. Will inject persistence related resources. -->
   <!-- Necessary to get the entity manager injected into the factory bean -->
   <bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />
   <!-- Adds transparent exception translation to the DAOs -->
   <bean class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />
   <!-- Adds dependency checks for setters annotated with @Required -->
   <bean class="org.springframework.beans.factory.annotation.RequiredAnnotationBeanPostProcessor" />

   <bean id="jpaDialect"
         class="org.springframework.orm.jpa.vendor.EclipseLinkJpaDialect" />
   <bean id="loadTimeWeaver"
         class="org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver" />
   <bean id="jpaVendorAdapter"
         class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter"
         p:databasePlatform="org.eclipse.persistence.platform.database.MySQLPlatform"
         p:generateDdl="false" p:showSql="true" />

<!-- classpath:META-INF/persistence.xml -->
   <bean id="entityManagerFactory"
         class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
         p:persistenceUnitName="leopersistencePU" p:dataSource-ref="dataSource"
         p:persistenceXmlLocation="classpath*:META-INF/persistence.xml"
         p:jpaDialect-ref="jpaDialect" p:jpaVendorAdapter-ref="jpaVendorAdapter"
         p:loadTimeWeaver-ref="loadTimeWeaver" />

   <bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager"
         p:entityManagerFactory-ref="entityManagerFactory" />
   <tx:annotation-driven transaction-manager="txManager" />

   <bean id="DBAuthPosDAO" class="com.xxx.leopersistence.DBAuthPosDAO"
         p:entityManagerFactory-ref="entityManagerFactory" />
   ...

persistence.xml entry;

   <persistence-unit name="leopersistencePU" transaction-type="RESOURCE_LOCAL">
      <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
      <class>com.leofund.leopersistence.entities.DBAuthorizedPosition</class>
       ...
      <exclude-unlisted-classes>false</exclude-unlisted-classes>

      <properties>
         <property name="eclipselink.weaving" value="false" />
         <property name="eclipselink.logging.level" value="FINEST"/>
         <property name="eclipselink.target-database" value="MYSQL"/>
         <property name="eclipselink.target-server" value="None"/>
         <property name="show-sql" value="true"/>

         <property name="eclipselink.cache.shared.default" value="false"/>

         <property name="eclipselink.jdbc.read-connections.min" value="1"/>
         <property name="eclipselink.jdbc.write-connections.min" value="1"/>
<!--
         <property name="eclipselink.logging.logger"
                   value="com.leofund.leoserver.utils.Log4JEclipseLinkLogger"/>

         <property name="eclipselink.cache.type.default" value="SoftWeak"/>
         <property name="eclipselink.cache.size.default" value="0"/>
 -->
         <property name="eclipselink.logging.thread" value="true"/>
         <property name="eclipselink.logging.session" value="true"/>
         <property name="eclipselink.logging.timestamp" value="true"/>
         <property name="eclipselink.logging.exceptions" value="true"/>
         <property name="eclipselink.logging.level.WEAVER" value="INFO"/>

         <property name="eclipselink.orm.throw.exceptions" value="true"/>

         <property name="eclipselink.jdbc.batch-writing" value="JDBC"/>
         <property name="eclipselink.jdbc.batch-writing.size" value="1000"/>
         <property name="eclipselink.jdbc.bind-parameters" value="true"/>

         <property name="eclipselink.jdbc.cache-statements" value="true"/>
         <property name="eclipselink.jdbc.cache-statements.size" value="10000"/>

         <property name="eclipselink.persistence-context.close-on-commit" value="true"/>
      </properties>
   </persistence-unit>

I have tried various combination and have scowled google to no resolve. All my finders works fine but the insert/update/delete does not issue the relevant SQL. Please help I am desperate as it's now eating into my estimates big time. I have also reviewed my ID generation and tried the various options TABLE/SEQUENCE/IDENTITY, the same result.

All my entity managers are not defined with any Transactions e.g;

public class DBEntityDAO extends JpaDaoSupport implements IDBEntityDAO {

   public void save(DBEntity entity) {
      try {
         getJpaTemplate().persist(entity);
      } catch (RuntimeException re) {
         throw re;
      }
   }

   public void delete(DBEntity entity) {
      try {
         entity = getJpaTemplate().getReference(DBEntity.class,
               entity.getEntityId());
         getJpaTemplate().remove(entity);
      } catch (RuntimeException re) {
         throw re;
      }
   }

   public DBEntity update(DBEntity entity) {
      logger.info("updating DBEntity instance");
      try {
         DBEntity result = getJpaTemplate().merge(entity);
         return result;
      } catch (RuntimeException re) {
         throw re;
      }
   }
   ...

I am using programmatic transactions at my service layer as follows;

   public int addJPAManager(String name, String shortName, boolean active, int displayOrderId)
      throws ServiceException {
      int newManagerId = -1, managerId = -1;
      TransactionStatus status = startDBTransaction();
      try {
         List<Object> dbEntities = jpaDelegateServices.findByProperty(DBEntity.class, "name", name);
         DBEntity dbEntity = null;
         if (dbEntities == null || dbEntities.size() == 0) {
            dbEntity = new DBEntity(name, shortName, SimpleUtils.booleanString(active));
            jpaDelegateServices.save(DBEntity.class, dbEntity);
         } else {
            if (dbEntities.size() > 1)
               throw new ServiceException("Error, non unique entity exists for the new manager!");
            dbEntity = (DBEntity)dbEntities.get(0);
         }
         managerId = dbEntity.getEntityId();

         DBManager dbManager = (DBManager)jpaDelegateServices.findById(DBManager.class, managerId);
         if (dbManager != null)
            throw new ServiceException("Error, new manager already exists!");

         dbManager = new DBManager(managerId, dbEntity, (short)displayOrderId, booleanString(active));
         jpaDelegateServices.save(DBManager.class, dbManager);
         newManagerId = dbManager.getManagerId();

         transactionManager.commit(status);
         messagingServices.broadcastDataElementChanged("Manager", newManagerId);
      } finally {
         if (!status.isCompleted()) transactionManager.rollback(status);
      }
      return newManagerId;
   }

   private TransactionStatus startDBTransaction() {
      DefaultTransactionDefinition def = new DefaultTransactionDefinition();
      def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
      return transactionManager.getTransaction(def);
   }

[Marten Deinum]

First off all I would like to say that your code seems overly complex, next to that your programmatic transaction handling is wrong. Why not use declarative transactions a lot simpler and saves you a lot of code.

Also there seems to be another service involded (jpaDelegateService) why and what does it do?! Why not simply use the dao directly.

Finally I would like to address the fact that using JpaTempate isn't the recommended approach, simply use a EntityManager(Factory) directly.

1) Fix your transactional code it really should have a try/catch/finally the catch should also rollback
1b) Preferably use declarative transactions instead of programmatic transactions
2) Your try/catch in dao layer is useless exception is never thrown
3) Your configuration contains a lot of duplication/unneeded material
4) Follow best practices (chapter 12.6.3 jpa and chapter 9 regarding transactions).

Configuration
Component-scan already registers the same stuff that annotation-config needs
- RequiredAnnotation
- PersistenceAnnotation
- CommonAnnotation
- EclipseJpaVendorAdapter already sets the dialect

  <!-- Generic -->
   <context:component-scan base-package="com.xxx.leopersistence" />

   <aop:aspectj-autoproxy />

   <!-- Configure LoadTimeWeaver. Be sure to start JVM with -javaagent=spring-agent.jar -->
   <context:load-time-weaver aspectj-weaving="on" />

   <!-- Adds transparent exception translation to the DAOs -->
   <bean class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />

   <bean id="loadTimeWeaver"
         class="org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver" />

   <bean id="jpaVendorAdapter"
         class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter"
         p:databasePlatform="org.eclipse.persistence.platform.database.MySQLPlatform"
         p:generateDdl="false" p:showSql="true" />

<!-- classpath:META-INF/persistence.xml -->
   <bean id="entityManagerFactory"
         class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
         p:persistenceUnitName="leopersistencePU" p:dataSource-ref="dataSource"
         p:persistenceXmlLocation="classpath*:META-INF/persistence.xml"
         p:jpaVendorAdapter-ref="jpaVendorAdapter"
         p:loadTimeWeaver-ref="loadTimeWeaver" />

   <bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager" p:entityManagerFactory-ref="entityManagerFactory" />
   <tx:annotation-driven transaction-manager="txManager" />

   <bean id="DBAuthPosDAO" class="com.xxx.leopersistence.DBAuthPosDAO"
         p:entityManagerFactory-ref="entityManagerFactory" />

In your configuration there is still a PersistenceExceptionTranslationPostProcessor however with your current setup that is useless. The JpaTemplate already does exception translation for you.

daos best practice and making use of PETPP. If you don't follow this best practice remove the PETPP.

public class DBEntityDAO implements IDBEntityDAO {

	@PersistenceContext
	private EntityManager em;

   public void save(DBEntity entity) {
	em.persist(entity);
   }

   public void delete(DBEntity entity) {
	em.getReferenec(DBEntity.class, entity.getEntityId());
	em.remove(entity);
   }

   public DBEntity update(DBEntity entity) {
      logger.info("updating DBEntity instance");
	  return (DBEntity) em.merge(entity);
   }

As you already have the @Transactional stuff registered your service should look something like this

   @Transactional
   public int addJPAManager(String name, String shortName, boolean active, int displayOrderId) throws ServiceException {
      int newManagerId = -1, managerId = -1
		 List<Object> dbEntities = jpaDelegateServices.findByProperty(DBEntity.class, "name", name);
		 DBEntity dbEntity = null;
		 if (dbEntities == null || dbEntities.size() == 0) {
			dbEntity = new DBEntity(name, shortName, SimpleUtils.booleanString(active));
			jpaDelegateServices.save(DBEntity.class, dbEntity);
		 } else {
			if (dbEntities.size() > 1)
			   throw new ServiceException("Error, non unique entity exists for the new manager!");
			dbEntity = (DBEntity)dbEntities.get(0);
		 }
		 managerId = dbEntity.getEntityId();

		 DBManager dbManager = (DBManager)jpaDelegateServices.findById(DBManager.class, managerId);
		 if (dbManager != null)
			throw new ServiceException("Error, new manager already exists!");

		 dbManager = new DBManager(managerId, dbEntity, (short)displayOrderId, booleanString(active));
		 jpaDelegateServices.save(DBManager.class, dbManager);
		 newManagerId = dbManager.getManagerId();

		 messagingServices.broadcastDataElementChanged("Manager", newManagerId);
      return newManagerId;
   }

You seem to be trying everything including the kitchen sink meanwhile mixing different strategies which (might) lead issues.

Please post your jpaDelegatingService because I guess the issue is there. Transactions aren't created/committed and thus nothing is going to change in the database.

[asavasamuel]

babasholaking


Here is an ORM that works with MySQL
https://www.kellermansoftware.com/p-...ess-layer.aspx