时间戳

首页 > 解决方案 > Hibernate session.delete 发出错误的 SQL 命令

java - Hibernate session.delete 发出错误的 SQL 命令

问题描述

我正在尝试使用休眠的 session.delete(obj) 从数据库中删除一些东西。但它不起作用，显示不正确的 SQL 命令。

它发出 SQL 命令“delete Instructor where id=?”。但是hibernate发出的SQL命令中缺少“from”，我认为这就是导致错误的原因。

//Main class code
//77 is the id
 InstructorDetail tempID=session.get(InstructorDetail.class, 77);
 session.delete(tempID);

错误堆栈跟踪：

Aug 14, 2019 10:54:20 AM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {6.0.0.Alpha2}
Aug 14, 2019 10:54:20 AM org.hibernate.cfg.Environment <clinit>
INFO: HHH000206: hibernate.properties not found
Aug 14, 2019 10:54:21 AM org.hibernate.boot.jaxb.internal.stax.LocalXmlResourceResolver resolveEntity
WARN: HHH90000012: Recognized obsolete hibernate namespace http://hibernate.sourceforge.net/hibernate-configuration. Use namespace http://www.hibernate.org/dtd/hibernate-configuration instead.  Support for obsolete DTD/XSD namespaces may be removed at any time.
Aug 14, 2019 10:54:21 AM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
Aug 14, 2019 10:54:22 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH10001002: Using Hibernate built-in connection pool (not for production use!)
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Aug 14, 2019 10:54:22 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: using driver [com.mysql.jdbc.Driver] at URL [jdbc:mysql://localhost:3306/hibernateTutorial]
Aug 14, 2019 10:54:22 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001001: Connection properties: {user=root, password=****}
Aug 14, 2019 10:54:22 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001003: Autocommit mode: false
Aug 14, 2019 10:54:22 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl$PooledConnections <init>
INFO: HHH000115: Hibernate connection pool size: 5 (min=1)
Aug 14, 2019 10:54:22 AM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLInnoDBDialect
Aug 14, 2019 10:54:23 AM org.hibernate.envers.boot.internal.EnversServiceImpl configure
INFO: Envers integration enabled? : true
Aug 14, 2019 10:54:23 AM org.hibernate.envers.boot.internal.AdditionalJaxbMappingProducerImpl produceAdditionalMappings
INFO: Generating Envers XML mappings.
Aug 14, 2019 10:54:23 AM org.hibernate.validator.internal.util.Version <clinit>
INFO: HV000001: Hibernate Validator 6.1.0.Alpha6
Aug 14, 2019 10:54:23 AM org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl getIsolatedConnection
INFO: HHH10001501: Connection obtained from JdbcConnectionAccess [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess@2a9bc08f] for (non-JTA) DDL execution was not in auto-commit mode; the Connection 'local transaction' will be committed and the Connection will be set into auto-commit mode.
Hibernate: select i1_0.id, i1_0.hobby, i2_0.id, i2_0.name, i1_0.youtube_channel from instructor_detail as i1_0 left outer join (Instructor as i2_0) on i1_0.id=i2_0.instructor_detail_id where i1_0.id=?
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.RowReaderStandardImpl readRow
INFO: ---Processing Row---
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.domain.basic.BasicResultAssembler assemble
INFO: Extracted value [Position 2] - [76]
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.domain.basic.BasicResultAssembler assemble
INFO: Extracted value [Position 0] - [77]
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.domain.basic.BasicResultAssembler assemble
INFO: Extracted value [Position 3] - [Atul]
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.domain.basic.BasicResultAssembler assemble
INFO: Extracted value [Position 1] - [Cricket]
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.domain.basic.BasicResultAssembler assemble
INFO: Extracted value [Position 4] - [None]
Hibernate: delete Instructor where id=?
Aug 14, 2019 10:54:24 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 1064, SQLState: 42000
Aug 14, 2019 10:54:24 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: 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 'where id=76' at line 1
Aug 14, 2019 10:54:24 AM org.hibernate.internal.ExceptionMapperStandardImpl mapManagedFlushFailure
ERROR: HHH000346: Error during managed flush [org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [delete Instructor where id=?]]
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [delete Instructor where id=?]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1436)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:487)
    at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2786)
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:1932)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:456)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:272)
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:104)
    at com.aks.Main.main(Main.java:19)
Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [delete Instructor where id=?]
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.sql.exec.internal.JdbcMutationExecutorImpl.execute(JdbcMutationExecutorImpl.java:77)
    at org.hibernate.sql.exec.internal.JdbcMutationExecutorImpl.execute(JdbcMutationExecutorImpl.java:109)
    at org.hibernate.metamodel.model.domain.internal.entity.SingleTableEntityTypeDescriptor.executeOperation(SingleTableEntityTypeDescriptor.java:768)
    at org.hibernate.metamodel.model.domain.internal.entity.SingleTableEntityTypeDescriptor.executeDelete(SingleTableEntityTypeDescriptor.java:514)
    at org.hibernate.metamodel.model.domain.internal.entity.SingleTableEntityTypeDescriptor.deleteRootTable(SingleTableEntityTypeDescriptor.java:458)
    at org.hibernate.metamodel.model.domain.internal.entity.SingleTableEntityTypeDescriptor.delete(SingleTableEntityTypeDescriptor.java:429)
    at org.hibernate.metamodel.model.domain.internal.entity.SingleTableEntityTypeDescriptor.delete(SingleTableEntityTypeDescriptor.java:420)
    at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:96)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:602)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:474)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:358)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1430)
    ... 9 more
Caused by: java.sql.SQLSyntaxErrorException: 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 'where id=76' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1094)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1042)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1345)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1027)
    at org.hibernate.sql.exec.internal.JdbcMutationExecutorImpl.execute(JdbcMutationExecutorImpl.java:68)
    ... 21 more

标签： javahibernate

解决方案

事实证明，在某些 SQL 方言中from是可选的：请参阅在 SQL 语法中，如果您打算使用 'where'，'delete from' 中的 'from' 是可选的吗？delete from table where ...

因此，我怀疑您使用与您正在使用的数据库产品不兼容的“方言”配置了 Hibernate。 此页面说明如何配置方言并提供支持的Dialect类名列表。

更新- 所以看起来你正在使用适当的方言。但我也从日志中看到您正在使用 Hibernate 组件/JAR 的混合集合。

  • 休眠核心 {6.0.0.Alpha2}
  • Hibernate Commons 注释 {5.1.0.Final}
  • 休眠验证器 6.1.0.Alpha6

您应该（可能）使用一组一致的库。

此外，由于您使用的是“alpha”库，因此您应该期待错误......因为“alpha”意味着代码仍在开发中。

当您遇到错误时，您应该：

  • 做一些你自己的调试
  • 检查问题跟踪器以查看是否已报告相关问题，以及
  • 自己报告问题。

如果您不想麻烦，请使用最新的稳定版本（当前为 5.4.4）而不是开发（alpha）版本。

推荐阅读