'last_insert_id‘不是一个可识别的函数名

Question

我知道这个错误：

last_insert_id不是一个可识别的函数名。

当我试图将数据发送到服务器以保存在SqlServer 2008数据库中时。一切都很顺利，但突然间，我遇到了这个问题。

下面是完整的异常跟踪：

12:38:48,867  WARN JDBCExceptionReporter:77 - SQL Error: 195, SQLState: 42000
12:38:48,867 ERROR JDBCExceptionReporter:78 - 'last_insert_id' is not a recognized built-in function name.
12:38:48,867  WARN JDBCExceptionReporter:54 - SQL Warning: 195, SQLState: 42000
12:38:48,883  WARN JDBCExceptionReporter:55 - Preparing the statement failed: 'last_insert_id' is not a recognized built-in function name.
12:38:48,883 ERROR SaveOrderAction:91 - Services level, can't save order - Order: (id=0, visitorId=2, status=A, customerId=528/nOrderDetail:(id=0, orderId=0, productId=124, amount=2)/n)
com.kaizen.gate.service.ServiseException: Services level, can't save order - Order: (id=0, visitorId=2, status=A, customerId=528/nOrderDetail:(id=0, orderId=0, productId=124, amount=2)/n)
    at com.kaizen.gate.service.impl.OrderServiceImpl.save(OrderServiceImpl.java:27)
    at com.kaizen.gate.http.action.SaveOrderAction.doSpecificAction(SaveOrderAction.java:82)
    at com.kaizen.gate.http.action.Action.execute(Action.java:39)
    at com.kaizen.gate.http.ActionServlet.doPost(ActionServlet.java:29)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at com.kaizen.gate.http.LoginFilter.doFilter(LoginFilter.java:40)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at com.kaizen.gate.http.UrlFilter.doFilter(UrlFilter.java:37)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:307)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:619)
Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not retrieve generated id after insert: [com.kaizen.gate.model.Order]; SQL [insert into MobileOrder (created, visitor_id, status, customer_id) values (?, ?, ?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not retrieve generated id after insert: [com.kaizen.gate.model.Order]
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:629)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
    at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
    at com.kaizen.gate.dao.impl.OrderDaoImpl.save(OrderDaoImpl.java:15)
    at com.kaizen.gate.service.impl.OrderServiceImpl.save(OrderServiceImpl.java:24)
    ... 27 more
Caused by: org.hibernate.exception.SQLGrammarException: could not retrieve generated id after insert: [com.kaizen.gate.model.Order]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:69)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2163)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2643)
    at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:51)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
    at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:298)
    at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:181)
    at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:107)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:94)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
    at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507)
    at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499)
    at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:495)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.orm.hibernate3.HibernateTemplate$CloseSuppressingInvocationHandler.invoke(HibernateTemplate.java:1282)
    at $Proxy4.saveOrUpdate(Unknown Source)
    at com.kaizen.gate.dao.impl.OrderDaoImpl$1.doInHibernate(OrderDaoImpl.java:19)
    at com.kaizen.gate.dao.impl.OrderDaoImpl$1.doInHibernate(OrderDaoImpl.java:15)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
    ... 30 more
Caused by: java.sql.SQLException: 'last_insert_id' is not a recognized built-in function name.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:55)
    ... 53 more

Answer 1

执行select @@identity以获取server中的最新值。这是文档

@@IDENTITY、SCOPE_IDENTITY和IDENT_CURRENT是类似的函数，因为它们返回插入表标识列的最后一个值。 @@IDENTITY和SCOPE_IDENTITY将返回当前会话中任何表中生成的最后一个标识值。但是，SCOPE_IDENTITY只返回当前范围内的值；@@IDENTITY并不仅限于特定的作用域。

Answer 2

如果您想要最后插入的记录的ID，可以在DAOImplementation中使用：

sessionFactory.getCurrentSession().persist(result); // result is object to save
sessionFactory.getCurrentSession().flush();
return result.getId(); //getter method from result object to get inserted records ID

Answer 3

我也收到了这个错误；我正在使用我的应用程序运行两个环境--一个使用Server，另一个使用MySQL。结果发现，当我切换环境时，我忘记切换方言了，正是这个环境导致了我的错误。看来您的数据库方言设置不正确。