InvalidDataAccessApiUsageException: Executing an update/delete query Spring XML to Java config

Question

I'm trying to convert spring xml configuration to java configuration. This works perfectly through XML configuration. However, it throws the following exception if I use java config initializer. This happens when it tries to run JQL. The application starts properly though (with all JPA mapping initialized).

org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:410) [spring-orm-4.1.5.RELEASE.jar:4.1.5.RELEASE]
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:216) [spring-orm-4.1.5.RELEASE.jar:4.1.5.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417) [spring-orm-4.1.5.RELEASE.jar:4.1.5.RELEASE]
at org.springframework.dao.support.ChainedPersistenceExceptionTranslat

Following is my persistence initializer class. Bit of reading suggested me this is related to transactions are not being started properly. I've put debug points to each of these methods but transactionManager method never gets executed during server startup or any later time. I'm not sure what am I doing wrong :(. Same code based works perfectly when persistence is initialized through persistence.xml.

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "au.mypkg")
public class DatabaseConfig  {


    @Bean(name = "dataSource")
    @Primary
    public DataSource dataSource() throws Exception {
        Context ctx = new InitialContext();
        return (DataSource) ctx.lookup("java:jboss/datasources/mydb");
    }

    @PersistenceContext(unitName = "persistenceUnit")
    @Primary
    @Bean(name = "entityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() throws Exception {
    ..........

    @Primary
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
        final JtaTransactionManager transactionManager = new JtaTransactionManager();
        transactionManager.setTransactionManagerName(JBOSS_TRANSACTION_MANANGER);
        return transactionManager;
    }

Error occurs when accessing this method on Dao

   public void updateById(final Long id) {

        final String sqlQuery = "UPDATE testtable w SET w.LAST_ACCESSED = :date WHERE w.testtable_ID = :testid";
        final Query query = dao.createNativeQuery(sqlQuery);
        query.setParameter("date", new Date());
        query.setParameter("testid", id);
        query.executeUpdate();
    }

Answer 1

I had the some problem and I resolved it by just adding @Transactional annotation on the service method that perform delete or update. In my case it was a method that call a repository method which execute a delete by jpql like this by I think it can solve you problem too:

@Modifying    
@Query("delete from OtherPayment otherPayment " +
       "where otherPayment.otherPaymentParam.id = :otherPaymentParamId")    
void deleteByOtherPaymentParamId(@Param("otherPaymentParamId") Long otherPaymentParamId);

Answer 2

Finally figured out what was going on with it. The reason this wasn't hitting the debug point was, EnableTransactionManagement imposes to auto-configure transactions for you. So if your transaction manager configured with default name which is in my case, it wouldn't try to call my method to configure transactions. The only way to get around with this is to use a different name for your transaction manager and pass that ref as a parameter on enableJPARepositories annotation.If you use one of the default names, it wouldn't make this call.

@Primary
@Bean(name = "myRealTransactionManager")
public PlatformTransactionManager transactionManager() {
    final JtaTransactionManager transactionManager = new JtaTransactionManager();
    transactionManager.setTransactionManagerName(JBOSS_TRANSACTION_MANANGER);
    return transactionManager;
}

.. and then

@EnableJpaRepositories(basePackages = "au.mypkg", transactionManagerRef = "myRealTransactionManager"

The other problem was I have had used setdatasource as opposed to setJtaDataSource on LocalContainerEntityManagerFatoryBean.