当前位置： 代码迷 >> Java Web开发 >> [求助]这个hql语句对吗？（hibernate的联合查询）
  详细解决方案

[求助]这个hql语句对吗？（hibernate的联合查询）

热度：530   发布时间：2007-08-09 16:50:30.0
[求助]这个hql语句对吗？（hibernate的联合查询）
这是我的联合查询语句，难道写的有错？
String hql = "from KmsComments as c inner join "
+ "KmsAddresslist as a on c.kmsUser.userId=a.kmsUser.userId where c.findId='"
+ questionId
+ "' and a.isExpert=0 order by Convert(varchar,answerTime,111) desc ";

这是编译器报的异常：
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 70 [from cn.ks.hulu.bean.KmsComments as c inner join KmsAddresslist as a on c.kmsUser.userId=a.kmsUser.userId where c.findId='3' and a.isExpert=0 order by Convert(varchar,answerTime,111) desc ]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:244)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:155)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1583)
at org.ks.hulu.disdaoimpl.DisDaoImpl.listuserreply(DisDaoImpl.java:176)
at com.yourcompany.struts.action.QueryQuestionAction.execute(QueryQuestionAction.java:74)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
Caused by: line 1:70: unexpected token: on
at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1765)
at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1420)
at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1130)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:702)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:238)
... 30 more
java.lang.NullPointerException
at com.yourcompany.struts.action.QueryQuestionAction.execute(QueryQuestionAction.java:76)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
看到这些异常，找了好长时间都找不出错在哪儿，我真的快疯了。
搜索更多相关主题的帖子: hibernate  hql  语句  查询  

----------------解决方案--------------------------------------------------------

怎么没有人回答啊？那个我真是急的我无语？有没有人知道啊？老天啊，你怎么对我这么不公平！


----------------解决方案--------------------------------------------------------
inner join 就是你的问题了,老兄 不用那中啦,用那个的话要
1.配置好xml,
2.我建议把你的相对应的SQL到数据库中去跑下有什么问题,
3.我用的多表查询,不用配置XML很方便就那样查询用JOIN的话就配置很麻烦了
----------------解决方案--------------------------------------------------------
以下是引用xiao_2008在2007-8-12 19:11:51的发言：
inner join 就是你的问题了,老兄 不用那中啦,用那个的话要
1.配置好xml,
2.我建议把你的相对应的SQL到数据库中去跑下有什么问题,
3.我用的多表查询,不用配置XML很方便就那样查询用JOIN的话就配置很麻烦了

我把我的hql语句改成了你写格式，可是不知道为什么还是没有实质性的效果，结果没出来。

但是编译器换了另一个错误：

Hibernate: select kmscomment0_.CommentsId as CommentsId1_0_, kmsaddress1_.AddressListId as AddressL1_0_1_, kmscomment0_.UserId as UserId1_0_, kmscomment0_.CommentsType as Comments3_1_0_, kmscomment0_.FindId as FindId1_0_, kmscomment0_.Answer as Answer1_0_, kmscomment0_.AnswerTime as AnswerTime1_0_, kmsaddress1_.UserId as UserId0_1_, kmsaddress1_.Tel as Tel0_1_, kmsaddress1_.QQ as QQ0_1_, kmsaddress1_.Divsion as Divsion0_1_, kmsaddress1_.Address as Address0_1_, kmsaddress1_.PostNumber as PostNumber0_1_, kmsaddress1_.Email as Email0_1_, kmsaddress1_.IsExpert as IsExpert0_1_ from KnowledgeSystem.dbo.kms_comments kmscomment0_, KnowledgeSystem.dbo.kms_addresslist kmsaddress1_ where kmscomment0_.UserId=kmsaddress1_.UserId and kmscomment0_.FindId='3' and kmsaddress1_.IsExpert=0 order by Convert(varchar, answerTime, 111) desc

java.lang.ClassCastException: [Ljava.lang.Object;
at org.ks.hulu.disdaoimpl.DisDaoImpl.listuserreply(DisDaoImpl.java:186)
at com.yourcompany.struts.action.QueryQuestionAction.execute(QueryQuestionAction.java:64)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)

----------------解决方案--------------------------------------------------------
类型转换异常...看一下是不是有哪里类型错了.

建议:
把你的hql语句,先写成sql语句,到数据库里执行一下,看哪里出错.
----------------解决方案--------------------------------------------------------

谢谢 wildleopard 的提示：
我发现我查询的是两个实体对象一个是kmsComments,kmsAddresslist,可是我只要查kmsComments，又要用到kmsAddresslist，那我该怎么写这个hql语句啊？

list = this.session.createQuery(hql).list(）
for (java.util.Iterator iter = list.iterator(); iter.hasNext();) {
KmsComments commbean = (KmsComments) iter.next();
// 增加实体对象
userlist.add(commbean);
}
我想转换为KmsComments 对象，怎么办？
如果语句改为：
[CODE]hql = "select c.commentsId,c.kmsUser.userName,c.answer,c.answer,c.answerTime from KmsComments as c , "
+ "KmsAddresslist as a where c.kmsUser.userId=a.kmsUser.userId and c.findId='"
+ questionId
+ "' and a.isExpert=0 order by Convert(varchar,answerTime,111) desc ";[/CODE]

这是程序的相关信息：

/**
* 查询指定问题的全部回复(全员讨论)
*
*/
public List listuserreply(int questionId, int commentsType) {
List<KmsComments> userlist = new ArrayList<KmsComments>();
List list = null;
String hql = "";
try {
switch (commentsType) {
// 创建hql语句,条件:isExpert=0
case 0:
// 知识评论
hql = "";
break;
case 1:
// 全员回复

hql = "from KmsComments as c , "
+ "KmsAddresslist as a where c.kmsUser.userId=a.kmsUser.userId and c.findId='"
+ questionId
+ "' and a.isExpert=0 order by Convert(varchar,answerTime,111) desc ";

break;
case 2:
// 课题回复
hql = "";
break;
case 3:
// 我的学习心得
hql = "";
break;
default:
break;
}
// 执行：查询
list = this.session.createQuery(hql).list();

System.out.println("enter list");

for (java.util.Iterator iter = list.iterator(); iter.hasNext();) {
KmsComments commbean = (KmsComments) iter.next();
// 增加实体对象
userlist.add(commbean);
}
} catch (Exception ex) {
ex.printStackTrace();
}
return userlist;
}

[此贴子已经被作者于2007-8-13 17:59:04编辑过]


----------------解决方案--------------------------------------------------------
  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.