求助高手org.hibernate.exception.SQLGrammarException: could not execute query ？

Question

异常：
Stacktraces
org.hibernate.exception.SQLGrammarException: could not execute query 
    org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
    org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    org.hibernate.loader.Loader.doList(Loader.java:2235)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
    org.hibernate.loader.Loader.list(Loader.java:2124)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
    org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
   (DefaultWorkflowInterceptor.java:163)
    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:249)
    org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)
    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:122)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
   (StaticParametersInterceptor.java:148)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:93)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
   (ScopedModelDrivenInterceptor.java:128)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    org.apache.struts2.interceptor.ProfilingActivationInterceptor.intercept(ProfilingActivationInterceptor.java:104)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:126)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:138)
    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:148)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:128)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)
    org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:468)
    org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)
    org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:76)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:244)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96)
        org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:383)
    org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:284)
    org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:322)
    org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1684)
    java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    java.lang.Thread.run(Thread.java:619)
    
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 '10 user0_.id as id0_, user0_.username as username0_, user0_.password as password' at line 1 
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    org.hibernate.loader.Loader.doList(Loader.java:2232)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
    org.hibernate.loader.Loader.list(Loader.java:2124)
      org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
    org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    com.lgp.registration.dao.impl.UserDaoImpl.searchUsers(UserDaoImpl.java:73)
    com.lgp.registration.manager.impl.UserManagerImpl.searchUsers(UserManagerImpl.java:57)
    com.lgp.registration.action.UserAction.searchUsers(UserAction.java:140)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    java.lang.reflect.Method.invoke(Method.java:597)
    (DefaultWorkflowInterceptor.java:163)
    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:249)
编写的业务逻辑方法：自从写了下面的逻辑就出问题了，前面的业务逻辑也都是用ＨＱＬ语言public PagerModel searchUsers(int userId,int offSet,int pageSize)
{
String selectTotalHql = "select count(*) from User u where u.id = "+userId;
String selectHql = "from User";
Session session = this.getSessionFactory().openSession();
int total = ((Long)session.createQuery(selectTotalHql).uniqueResult()).intValue();
List datas = session.createQuery(selectHql).setFirstResult(offSet).setMaxResults(pageSize).list();
PagerModel pm = new PagerModel();
pm.setTotal(total);
pm.setUsers(datas);
session.close();
return pm;
}高手帮下帮吧！谢谢了！

Answer 1

你看一下你的sql语句的语法是不是有问题！

Answer 2

User类中是否有特殊的字段，为mysql关键字？
List datas = session.createQuery(selectHql).setFirstResult(offSet).setMaxResults(pageSize).list();
你看一下这句话执行时生成的sql是什么？你发的代码，中间缺了代码没？没看到什么问题？

Answer 3

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 '10 user0_.id as id0_, user0_.username as username0_, user0_.password as password' at line 1
可能是关键字的问题

Answer 4

执行上述逻辑之后后台输出：
Hibernate: select top ? user0_.id as id0_, user0_.username as username0_, user0_.password as password0_ from t_user user0_
[Hibernate]2011-06-24 10:57:43,953["http-apr-8080"-exec-2]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 '10 user0_.id as id0_, user0_.username as username0_, user0_.password as password' at line 1
[Tomcat]2011-06-24 10:57:43,953["http-apr-8080"-exec-2]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 '10 user0_.id as id0_, user0_.username as username0_, user0_.password as password' at line 1

Answer 5

完整异常：
Struts Problem ReportStruts has detected an unhandled exception:
Messages:     1. 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 '10 user0_.id as id0_, user0_.username as username0_, user0_.password as password' at line 1
   2. could not execute queryFile:  org/hibernate/exception/SQLStateConverter.java
Line number:  90
Stacktraces
org.hibernate.exception.SQLGrammarException: could not execute query    org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
    org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    org.hibernate.loader.Loader.doList(Loader.java:2235)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
    org.hibernate.loader.Loader.list(Loader.java:2124)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
    org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
    org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
    org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    com.lgp.registration.dao.impl.UserDaoImpl.searchUsers(UserDaoImpl.java:71)
    com.lgp.registration.manager.impl.UserManagerImpl.searchUsers(UserManagerImpl.java:58)
    com.lgp.registration.action.UserAction.searchUsers(UserAction.java:141)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    java.lang.reflect.Method.invoke(Method.java:597)
    com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:440)
    com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:279)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
    com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:163)
    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:249)
    org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)
    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:122)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)

Answer 6

 com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:148)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:93)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:235)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:89)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:128)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    org.apache.struts2.interceptor.ProfilingActivationInterceptor.intercept(ProfilingActivationInterceptor.java:104)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:126)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:138)
    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:148)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:128)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)
    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
    org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)
    org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:468)
    org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)
    org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:76)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:244)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:244)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
    org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161)
    org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
    org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
    org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:541)
    org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:383)
    org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:284)
    org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:322)
    org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1684)
    java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    java.lang.Thread.run(Thread.java:619)
    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 '10 user0_.id as id0_, user0_.username as username0_, user0_.password as password' at line 1

Answer 7

尝试一下给count(*)加个别名，或者把*换成具体的字段如u.id。感觉还是在hql语句上有问题。

Answer 8

应该是HQL语句的问题。给hibernate的配置文件加上showSql的属性。然后再执行的时候把sql语句复制下来，直接去数据库中测试。

Answer 9

HQL语句在后台打出
select user0_.id as id0_, user0_.username as username0_, user0_.password as password0_ from reg.t_user user0_ where user0_.username=? and user0_.password=?
这怎么测试啊？给count(*)加个别名，或者把*换成具体的字段如u.id，这个方法也不行啊！select count(*) 我都写了2个月都没出过问题，今天怎么就出问题了呢？百度谷歌一直都找不到解决的方案！高手们显显神通吧！