Vineet Vineet - 26 days ago 20
Java Question

Error while deleting DB records using spring data jpa

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

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.