CODEWITHSUNDEEP
javaspringjpaspring-data
Manu
Manu

Reputation: 1369

Why does a native delete query using aliases not work in Spring Data JPA?

Is there any known issue in having alias with DELETE in JPA Spring data? The same works like a charm on removing alias. I have a JPA Spring data native query as below:-

    @Modifying
    @Transactional
    @Query(value = "delete from Customer d where d.name = ?1", nativeQuery = true)
    public void removeOnName(String name);

This throws an exception as below:-

    01:11:13.616 [main] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 1064, SQLState: 42000
01:11:13.616 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd where d.name = 'kiran'' at line 1
01:11:13.621 [main] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Warning Code: 1064, SQLState: 42000
01:11:13.621 [main] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd where d.name = 'kiran'' at line 1
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:172)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:155)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy91.removeDevciesOnGuid(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)

The query executes fine when i remove the alias. The working query as below:-

 @Modifying
        @Transactional
        @Query(value = "delete from Customer where name = ?1", nativeQuery = true)
        public void removeOnName(String name);

Is there any known issue in having alias with DELETE in JPA Spring data? The same works like a charm on removing alias.

Reference Done The documentation also shows using alias http://docs.spring.io/spring-data/jpa/docs/1.4.2.RELEASE/reference/html/jpa.repositories.html

From Documentation:- 2.5.1 Transactional query methods

To allow your query methods to be transactional simply use @Transactional at the repository interface you define.

Example 2.20. Using @Transactional at query methods

@Transactional(readOnly = true)
public interface UserRepository extends JpaRepository<User, Long> {

  List<User> findByLastname(String lastname);

      @Modifying
  @Transactional
  @Query("delete from User u where u.active = false")
  void deleteInactiveUsers();
}

Typically you will want the readOnly flag set to true as most of the query methods will only read data. In contrast to that deleteInactiveUsers() makes use of the @Modifying annotation and overrides the transaction configuration. Thus the method will be executed with readOnly flag set to false.

Upvotes: 1

Views: 5149

Answers (2)

Nitin Arora
Nitin Arora

Reputation: 2668

Reason you get an error while executing the below query is because it's not a correct MySql syntax for the delete query. You are using nativeQuery and alias usage is incorrect.

Correct syntax is:

"delete d from Customer as d where d.name=?1"

@Modifying
@Transactional
@Query(value = "delete d from Customer as d where d.name=?1", nativeQuery = true)
public void removeOnName(String name);

Upvotes: 0

a1ex07
a1ex07

Reputation: 37382

You set nativeQuery flag of @Query to true, so query sent to the server (in your case Mysql) as it is. Mysql doesn't allow aliases in DELETE. Set nativeQuery to false or don't use aliases.

*Surely, there is also an option to use , for instance , SQLServer which would accept such syntax instead of Mysql :)

Upvotes: 5

Related Questions