81,114
社区成员
发帖
与我相关
我的任务
分享求助 hql的join连查，数据库里跑没问题，程序里就不行！
hql的值是select pd.personal_id,pd.name,pd.sex from personal_zywt pz left join personal_dossier pd on pz.personal_id=pd.personal_id where pz.flag=1 and pz.wtmc='高血压' and concat(pd.system_id) like '1101050100%'
08:57:00,390 ERROR PARSER:33 - line 1:90: unexpected token: on
08:57:00,562 WARN RequestProcessor:528 - Unhandled Exception thrown: class org.springframework.orm.hibernate3.HibernateQueryException
2010-6-9 8:57:00 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet action threw exception
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 90 [select pd.personal_id,pd.name,pd.sex from personal_zywt pz left join personal_dossier pd on pz.personal_id=pd.personal_id where pz.flag=1 and pz.wtmc='高血压' and concat(pd.system_id) like '1101050100%']
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
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:1623)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:842)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:840)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:832)
at com.ChinaCSM.CHSdeck.biz.impl.BaseManageImpl.queryAllPer(BaseManageImpl.java:214)
at com.ChinaCSM.CHSdeck.biz.impl.BaseManageImpl$$FastClassByCGLIB$$d0626597.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:696)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:631)
at com.ChinaCSM.CHSdeck.biz.impl.BaseManageImpl$$EnhancerByCGLIB$$2cb66fca.queryAllPer(<generated>)
at com.ChinaCSM.CHSdeck.web.actions.MxbGxyAction.queryByPropergr(MxbGxyAction.java:295)
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:589)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:270)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:187)
at org.springframework.web.struts.DelegatingActionProxy.execute(DelegatingActionProxy.java:110)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.ChinaCSM.CHSdeck.utils.PageFilter.doFilter(PageFilter.java:37)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:228)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:216)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:634)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:445)
at java.lang.Thread.run(Thread.java:619)
用hql语句也报一样的错，我就换成了sql语句结果还是报这个错，但sql语句放在数据库里跑就没问题，数据库是mysql的，查了半天也没查出来，请高手指教啊！
08:57:00,390 ERROR PARSER:33 - line 1:90: unexpected token: on
08:57:00,562 WARN RequestProcessor:528 - Unhandled Exception thrown: class org.springframework.orm.hibernate3.HibernateQueryException
2010-6-9 8:57:00 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet action threw exception
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 90 [select pd.personal_id,pd.name,pd.sex from personal_zywt pz left join personal_dossier pd on pz.personal_id=pd.personal_id where pz.flag=1 and pz.wtmc='高血压' and concat(pd.system_id) like '1101050100%']
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
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:1623)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:842)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:840)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:832)
at com.ChinaCSM.CHSdeck.biz.impl.BaseManageImpl.queryAllPer(BaseManageImpl.java:214)
at com.ChinaCSM.CHSdeck.biz.impl.BaseManageImpl$$FastClassByCGLIB$$d0626597.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:696)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:631)
at com.ChinaCSM.CHSdeck.biz.impl.BaseManageImpl$$EnhancerByCGLIB$$2cb66fca.queryAllPer(<generated>)
at com.ChinaCSM.CHSdeck.web.actions.MxbGxyAction.queryByPropergr(MxbGxyAction.java:295)
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:589)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:270)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:187)
at org.springframework.web.struts.DelegatingActionProxy.execute(DelegatingActionProxy.java:110)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.ChinaCSM.CHSdeck.utils.PageFilter.doFilter(PageFilter.java:37)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:228)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:216)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:634)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:445)
at java.lang.Thread.run(Thread.java:619)
用hql语句也报一样的错，我就换成了sql语句结果还是报这个错，但sql语句放在数据库里跑就没问题，数据库是mysql的，查了半天也没查出来，请高手指教啊！
...全文
请发表友善的回复…
发表回复
zhuxr2003 2010-06-10
- 打赏
- 举报
不需要用join,改为from personal_zywt pz，personal_dossier pd where pz.personal_id=pd.personal_id,关联映射关系也不需要建就可以用。
Leson_Yin 2010-06-10
- 打赏
- 举报
来晚了，jf
sunflowerpp 2010-06-10
- 打赏
- 举报
知道了，呵呵呵，拿到的是数组，忙完了给大家散分，谢谢大家了！
shanxmxj 2010-06-10
- 打赏
- 举报
[Quote=引用 12 楼 ronniegxq 的回复:]
引用 6 楼 shanxmxj 的回复:
Java code
就是要在hbm里建立2个表的关联映射关系。
比如说：
sql: select c.* from card c left join score s on s.card_id = c.id and where s.id is null
这里的关联是one-to-many, 在Card里面建一个Set scores，然后配置……
[/Quote]
不是建立关系以后才能用on ，是在hql里没有建立关系是没法用join的。
引用 6 楼 shanxmxj 的回复:
Java code
就是要在hbm里建立2个表的关联映射关系。
比如说：
sql: select c.* from card c left join score s on s.card_id = c.id and where s.id is null
这里的关联是one-to-many, 在Card里面建一个Set scores，然后配置……
[/Quote]
不是建立关系以后才能用on ，是在hql里没有建立关系是没法用join的。
ronniegxq 2010-06-10
- 打赏
- 举报
[Quote=引用 6 楼 shanxmxj 的回复:]
Java code
就是要在hbm里建立2个表的关联映射关系。
比如说：
sql: select c.* from card c left join score s on s.card_id = c.id and where s.id is null
这里的关联是one-to-many, 在Card里面建一个Set scores，然后配置好
<set name="scores……
[/Quote]
原来要建立关系才能on，hql太弱了吧
Java code
就是要在hbm里建立2个表的关联映射关系。
比如说：
sql: select c.* from card c left join score s on s.card_id = c.id and where s.id is null
这里的关联是one-to-many, 在Card里面建一个Set scores，然后配置好
<set name="scores……
[/Quote]
原来要建立关系才能on，hql太弱了吧
sunflowerpp 2010-06-10
- 打赏
- 举报
怎么拿到对象里的属性呢？
sunflowerpp 2010-06-10
- 打赏
- 举报
是改用sql语句了，可是得到的是object对象强转成实体对象时报错，该
怎么转呢
怎么转呢
逆风向前进 2010-06-10
- 打赏
- 举报
直接调用session.createSQLQuery(sql)。
focusforce 2010-06-10
- 打赏
- 举报
[Quote=引用 7 楼 sunflowerpp 的回复:]
是没有建立映射关系，可是程序都已经做完了，现在如果改成映射的话很多地方都要改
，原来是用in进行查询结果速度奇慢，现在是为了优化查询，谁有更好的解决办法吗？
[/Quote]
2楼的方法试过？
我遇到过同样的问题，最后就是用原始的sql解决，直接调用session.createSQLQuery(sql)。
是没有建立映射关系，可是程序都已经做完了，现在如果改成映射的话很多地方都要改
，原来是用in进行查询结果速度奇慢，现在是为了优化查询，谁有更好的解决办法吗？
[/Quote]
2楼的方法试过？
我遇到过同样的问题，最后就是用原始的sql解决，直接调用session.createSQLQuery(sql)。
sunflowerpp 2010-06-09
- 打赏
- 举报
是没有建立映射关系，可是程序都已经做完了，现在如果改成映射的话很多地方都要改
，原来是用in进行查询结果速度奇慢，现在是为了优化查询，谁有更好的解决办法吗？
，原来是用in进行查询结果速度奇慢，现在是为了优化查询，谁有更好的解决办法吗？
shanxmxj 2010-06-09
- 打赏
- 举报
就是要在hbm里建立2个表的关联映射关系。
比如说：
sql: select c.* from card c left join score s on s.card_id = c.id and where s.id is null
这里的关联是one-to-many, 在Card里面建一个Set scores，然后配置好
<set name="scores" cascade="none" where="valid_date >= curdate()">
<key column="CARD_ID"/>
<one-to-many class="Score" />
</set>
hql如下：select c from Card c left join c.scores s where s.id is nullshanxmxj 2010-06-09
- 打赏
- 举报
personal_zywt ,personal_dossier pd
这2个表有没有建立关系？没有建立关系的话是不能用join的
这2个表有没有建立关系？没有建立关系的话是不能用join的
网站推广优化yetaoaiueo 2010-06-09
- 打赏
- 举报
语句没啥问题
瞌睡不醒 2010-06-09
- 打赏
- 举报
08:57:00,390 ERROR PARSER:33 - line 1:90: unexpected token: on
第一句说的很明显了。。。
第一句说的很明显了。。。
focusforce 2010-06-09
- 打赏
- 举报
hql和sql确实有不少区别，可以用session.createSQLQuery(),直接执行sql语句而不用hql。
Java技术栈 2010-06-09
- 打赏
- 举报
严重: Servlet.service() for servlet action threw exception
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 90 [select pd.personal_id,pd.name,pd.sex from personal_zywt pz left join personal_dossier pd on pz.personal_id=pd.personal_id where pz.flag=1 and pz.wtmc='高血压' and concat(pd.system_id) like '1101050100%']
难道hql里面不能on吗
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 90 [select pd.personal_id,pd.name,pd.sex from personal_zywt pz left join personal_dossier pd on pz.personal_id=pd.personal_id where pz.flag=1 and pz.wtmc='高血压' and concat(pd.system_id) like '1101050100%']
难道hql里面不能on吗
