How do I make a modifying query to delete a set of entities by their ids using Spring Data JPA?
I am trying to delete a very large number of records from my database. Each record has a foreign key cascade, so when deleting a record in one go via JPA, it takes about 5 seconds for each delete.
So, I decided to write a query like this in JPQL format:
@Repository
public interface MyTableRepository extends JpaRepository<MyTable, Long> {
@Query("delete from MyTable where id in :ids")
void deleteAllIds(@Param("ids") Long[] ids);
}
When I run this, or what is the same void deleteAllIds(@Param("ids") Set<Long> ids);
, I get this:
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [delete from com.lh.clte.domain.content.business.Hotel where id in (:ids_0_, :ids_1_, :ids_2_, :ids_3_, ... for the entire array size)]
org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:293)
org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:111)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
com.sun.proxy.$Proxy72.deleteAllId(Unknown Source)
How can I get to my goal?
source to share
To execute the modifying query, you need to annotate the method with @Modifying
as described in the reference documentation .
Note that issuing a delete request will not trigger the lifecycle callbacks of the affected objects, which will be called, since the statement is executed directly on the database. To get callbacks caused by problem a findAll(…)
, assuming the list of ad pipe ids passes the results to deleteAll(…)
.
If you are not managing transactions in any of the above levels, make sure you also use @Transactional
to make the query execute in a transaction as JPA requires.
source to share
You might want to write a separate service or class where you can write some kind of raw sql. JPA allows you to write custom sql, but if you're going to get this far, you should just write some raw sql and handle it yourself, making sure you delete the record and any related records in other tables that FK applies to.
source to share