@Repository count query - Method executeUpdate cannot be used for query

Question

I need to get the count of total rows inserted for the today. I am using IBM DB2 for database along with springboot 2 and using @Repository to perform CRUD operations.

However, I am stuck at the count.

@Transactional
@Modifying
@Query(value = "SELECT COUNT(1) FROM temp.MY_CALL WHERE DATE(INSERT_TS) = CURRENT_DATE ", nativeQuery = true)
int countByCurrentDa();

and Getting the following exception

org.hibernate.engine.jdbc.spi.SqlExceptionHelper[0;39m: [jcc][10104][10942][4.9.78] Method executeUpdate cannot be used for query. ERRORCODE=-4476, SQLSTATE=null
org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:353)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)

Answer 1

Remove the @Modifying annotation - you do not modify anything with this query.

As per documentation:

Indicates a query method should be considered as modifying query as that changes the way it needs to be executed.

Queries that require a @Modifying annotation include INSERT, UPDATE, DELETE, and DDL statements.