hibernate with mySql generating top function_问答_开发者

I have the following method:

@Transactional
public List<T> findByRange(Integer firstItem, Integer count){
    Criteria criteria = getSession().createCriteria(getEntityClass());
    criteria.setFirstResult(firstItem).setMaxResults(count).addOrder(Order.asc("id"));
    return criteria.list();
}

But this generates:

select top 5 this_.id as id0_0_, this_.f_name as f2_0_0_, this_.l_name as l3_0_0_, this_.phone as phone0_0_ from person this_ order by this_.id asc

What am I doing wrong? I'm using hibernate 3.6.0.Final, and spring 3.0.0.RELEASE The exception:

Hibernate: select top ? this_.id as id0_0_, this_.f_name as f2_0_0_, this_.l_name as
l3_0_0_, this_.phone as phone0_0_ from person this_ order by this_.id asc
38976 [http-8080-exec-3] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error:      
1064, SQLState: 42000
38976 [http-8080-exec-3] ERROR org.hibernate.util.JDBCExceptionReporter - 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 '5 this_.id as id0_0_, this_.f_name as 
f2_0_0_, this_.l_name as l3_0_0_, this_.ph' at line 1
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2536)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
at com.maxheapsize.dao.BaseDao.findByRange(BaseDao.java:36)
at com.maxheapsize.controllers.PersonController$1.createPageDataModel(PersonController.java:56)
at com.maxheapsize.controllers.PersonController.getPersonDataModel(PersonController.java:67)
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 javax.el.BeanELResolver.getValue(BeanELResolver.java:87)
at com.sun.faces.el.DemuxCompositeELResolver._getValue(DemuxCompositeELResolver.java:173)
at com.sun.faces.el.DemuxCompositeELResolver.getValue(DemuxCompositeELResolver.java:200)
at org.apache.el.parser.AstValue.getValue(AstValue.java:169)
at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:189)
at com.sun.faces.facelets.el.TagValueExpression.getValue(TagValueExpression.java:106)
at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:190)
at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:178)
at javax.faces.component.UIData.getValue(UIData.java:553)
at javax.faces.component.UIData.getDataModel(UIData.java:1293)
at javax.faces.component.UIData.setRowIndex(UIData.java:446)
at com.sun.faces.renderkit.html_basic.TableRenderer.encodeBegin(TableRenderer.java:77)
at javax.faces.component.UIComponentBase.encodeBegin(UIComponentBase.java:816)
at javax.faces.component.UIData.encodeBegin(UIData.java:936)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1674)
at javax.faces.render.Renderer.encodeChildren(Renderer.java:164)
at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:841)
at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeRecursive(HtmlBasicRenderer.java:300)
at com.sun.faces.renderkit.html_basic.GridRenderer.renderRow(GridRenderer.java:181)
at com.sun.faces.renderkit.html_basic.GridRenderer.encodeChildren(GridRenderer.java:125)
at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:841)
at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeRecu开发者_Python百科rsive(HtmlBasicRenderer.java:300)
at com.sun.faces.renderkit.html_basic.GridRenderer.renderRow(GridRenderer.java:181)
at com.sun.faces.renderkit.html_basic.GridRenderer.encodeChildren(GridRenderer.java:125)
at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:841)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1676)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1679)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1679)
at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:395)
at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:127)
at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:269)
at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:117)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:97)
at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:135)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:335)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:306)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:558)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:379)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:243)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:259)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:237)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:281)
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: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '5 this_.id as id0_0_, this_.f_name as f2_0_0_, this_.l_name as l3_0_0_, this_.ph' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 63 more

Have you configured Hibernate with the appropriate dialect?

See http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#configuration-optional-dialects for the list of dialects, and http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#configuration-xmlconfig for an exemple XML configuration where the dialect is configured.