Spring Data JPA query with data manipulation fix

This post is about how to fix the error in Spring Data JPA – java.sql.SQLException: Can not issue data manipulation statements with executeQuery().


Problem

So, a code of custom Spring Data JPA repository might look like this:

public interface EmployeeRepository extends JpaRepository<Employee, String> {
    @Query(value = "delete from employees where disabled=1", nativeQuery = true)
    void deleteAllDisabled();
}

But, at runtime, calling the above-mentioned method will result in a stack trace of the following contents:

java.sql.SQLException: Can not issue data manipulation statements with executeQuery().
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
    com.mysql.jdbc.StatementImpl.checkForDml(StatementImpl.java:472)
    com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1976)
    org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
    org.hibernate.loader.Loader.getResultSet(Loader.java:2122)
    org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1905)
    org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1881)
    org.hibernate.loader.Loader.doQuery(Loader.java:925)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
    org.hibernate.loader.Loader.doList(Loader.java:2622)
    org.hibernate.loader.Loader.doList(Loader.java:2605)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2434)
    org.hibernate.loader.Loader.list(Loader.java:2429)
    org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336)
    org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1980)
    org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322)
    org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125)
    org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
    org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:529)
    org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:206)
    org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:78)
    org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:102)
    org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:92)
    org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:482)
    org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
    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:213)
    com.sun.proxy.$Proxy84.deleteAllDisabled(Unknown Source)
    ...

Solution

The fix is quite simple – just add @Modifying annotation to the method:

public interface EmployeeRepository extends JpaRepository<Employee, String> {
    @Modifying
    @Query(value = "delete from employees where disabled=1", nativeQuery = true)
    void deleteAllDisabled();
}

P.S. The example used Spring Data JPA 1.10.2.RELEASE, MySQL Connector/J (mysql-connector-java) 5.1.35, Hibernate 5.1.1 and MySQL 5.7.25.

P.P.S. Full name of the annotation is org.springframework.data.jpa.repository.Modifying and it has “clearAutomatically” attribute (false by default).