Vineet Vineet - 1 year ago 131
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download