为什么使用别名的本机删除查询在Spring Data JPA中不起作用？

Question

在JPA Spring数据中使用带有DELETE的别名是否存在任何已知的问题？同样的工作就像一个魅力移除别名。我有一个JPA Spring数据原生查询，如下所示：-

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

    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)

当我删除别名时，查询执行得很好。工作查询如下：-

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

在JPA Spring数据中使用带有DELETE的别名是否存在任何已知的问题？同样的工作就像一个魅力移除别名。

文档还显示了使用别名http://docs.spring.io/spring-data/jpa/docs/1.4.2.release/Reference/html/jpa.repositories.html

@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();
}

通常情况下，您希望readOnly标志设置为true，因为大多数查询方法将只读取数据。与此相反，deleteInactiveUsers()使用@modifying注释并覆盖事务配置。因此，该方法将在readOnly标志设置为false的情况下执行。

Answer 1

您将@query的nativequery标志设置为true，以便将查询按原样发送到服务器（在您的示例中为Mysql）。Mysql不允许在delete中使用别名。将nativequery设置为false或不使用别名。

*当然，还有一个选项可以使用，例如SQLServer，它可以接受这样的语法，而不是Mysql：）