Error while deleting DB records using spring data jpa

Question

I am getting the below exception while trying to delete some database records using my jpa repository interface

Caused by: javax.persistence.TransactionRequiredException: Executing an update/delete query
at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:71)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:234)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:99)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:90)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:415)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:393)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)

The method to delete the records is already annotated as @Modifying, @Transactional and the configuration class is annotated with the @EnableTransactionManagement annotation. Below is my pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>com.XXXXX</groupId>
<artifactId>xxxxxx</artifactId>
<name>xxxxxx</name>

<parent>
    <groupId>xxxxxx</groupId>
    <artifactId>xxxxxx</artifactId>
    <version>1.0</version>
</parent>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
        <version>1.2.5.RELEASE</version>
        <exclusions>
            <exclusion>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-entitymanager</artifactId>
            </exclusion>
            <exclusion>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-logging</artifactId>
            </exclusion>
            <exclusion>
                <groupId>org.apache.tomcat</groupId>
                <artifactId>tomcat-jdbc</artifactId>
            </exclusion>
            <exclusion>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
            </exclusion>
            <exclusion>
                <groupId>javax.transaction</groupId>
                <artifactId>javax.transaction-api</artifactId>
            </exclusion>
        </exclusions>
    </dependency>

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>4.1.7.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
        <version>4.3.7.Final</version>
    </dependency>

    <dependency>
        <groupId>com.jcraft</groupId>
        <artifactId>jsch</artifactId>
        <version>0.1.53</version>
    </dependency>

    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-slf4j-impl</artifactId>
        <version>2.2</version>
    </dependency>

    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-api</artifactId>
        <version>2.2</version>
    </dependency>

    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-core</artifactId>
        <version>2.2</version>
    </dependency>

    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>${oracle.version}</version>
    </dependency>
</dependencies>

<properties>
    <java.version>1.6</java.version>
    <oracle.version>11.2.0</oracle.version>
</properties>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
            <version>1.4.0.RELEASE</version>
            <executions>
                <execution>
                    <goals>
                        <goal>repackage</goal>
                    </goals>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

The java config is as below

import java.sql.SQLException;
import java.util.Properties;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import oracle.jdbc.pool.OracleDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.orm.jpa.EntityScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@EnableAutoConfiguration
@EnableJpaRepositories(basePackages = {"com.xxx.repository"})
@ComponentScan(value = "com.xxx.*")
@EntityScan(value = "com.xxxx.entity")
@EnableTransactionManagement
@PropertySource(value = "classpath:/properties/${app.env:dev}/config.properties", ignoreResourceNotFound = true)
public class XXXXXConfig {

private static final String PROPERTY_NAME_DATABASE_PASSWORD = "database.password";
private static final String PROPERTY_NAME_DATABASE_URL = "database.url";
private static final String PROPERTY_NAME_DATABASE_USERNAME = "database.username";

private static final String PROPERTY_NAME_HIBERNATE_DIALECT = "hibernate.dialect";
private static final String PROPERTY_NAME_HIBERNATE_SHOW_SQL = "hibernate.show_sql";
private static final String PROPERTY_NAME_HIBERNATE_HBM2DDL = "hibernate.hbm2ddl.auto";
private static final String PROPERTY_NAME_HIBERNATE_DEFAULT_SCHEMA = "hibernate.default_schema";
private static final String PROPERTY_NAME_HIBERNATE_ENTITY_MANAGER_FACTORY_NAME = "hibernate.ejb.entitymanager_factory_name";

@Autowired
Environment env;

@Bean
public DataSource dataSource() throws SQLException {

    OracleDataSource dataSource = new OracleDataSource();
    dataSource.setUser(env.getProperty(PROPERTY_NAME_DATABASE_USERNAME));
    dataSource
            .setPassword(env.getProperty(PROPERTY_NAME_DATABASE_PASSWORD));
    dataSource.setURL(env.getProperty(PROPERTY_NAME_DATABASE_URL));
    dataSource.setImplicitCachingEnabled(true);
    dataSource.setFastConnectionFailoverEnabled(true);
    return dataSource;
}

@Bean
public EntityManagerFactory entityManagerFactory() throws SQLException {

    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(true);

    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setJpaProperties(hibernateProperties());
    factory.setPackagesToScan("com.xxxx");
    factory.setDataSource(dataSource());
    factory.afterPropertiesSet();

    return factory.getObject();
}

private Properties hibernateProperties() {
    Properties properties = new Properties();
    properties.put(PROPERTY_NAME_HIBERNATE_DIALECT,
            env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_DIALECT));
    properties.put(PROPERTY_NAME_HIBERNATE_SHOW_SQL,
            env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_SHOW_SQL));
    properties.put(PROPERTY_NAME_HIBERNATE_HBM2DDL,
            env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_HBM2DDL));
    properties.put(PROPERTY_NAME_HIBERNATE_DEFAULT_SCHEMA,
            env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_DEFAULT_SCHEMA));
    return properties;
}

@Bean
public PlatformTransactionManager transactionManager() throws SQLException {
    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory());
    return txManager;
}

The jpa repository

import java.util.List;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.transaction.annotation.Transactional;

public interface XXXXRepository extends Repository<Entity, Long> {

@Query("select td from table td where td.modifiedOn >= TRUNC(SYSDATE) - 1")
List<Entity> find();

@Modifying
@Transactional
@Query("delete from table td where td.modifiedOn <= TRUNC(SYSDATE) - 90")
int deleteRecords();
}

Am I still missing some configuration?

Can anyone please help?

Answer 1

After searching for 2 days I found that I did a silly mistake - making the return type of the deleteRecords method as int. After changing it to void the error is gone. This may help someone so posting it here.