-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users (Deutsch)

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
bafz
 Post subject: wie Case when then end in HQL schreiben.
PostPosted: Mon Oct 08, 2007 8:10 am 
Newbie

Joined: Tue Jun 28, 2005 10:20 am
Posts: 15
Hallo,
ich habe folgende Abfrage (in annähernd korrekter SQL Syntax) in einem Programm:
Code:
           SELECT * FROM tabelle
   WHERE (geburtstag is not null) and
   (MONTH(geburtstag) BETWEEN startmonth AND endmonth) AND
            case
            when MONTH(Geburtstag) = startmonth
               then DAYOFMONTH(geburtstag) >= startday
            when MONTH(Geburtstag) = endmonth
               then DAYOFMONTH(geburtstag) <=  endday
            else  1 end;

Dabei sind die Terme startmonth,endmonth,startday ,endday
variable Parameter die geeignet eingesetzt werden.
Meine native SQL-Abfrage funktioniert.

Nun soll das ganze in einem Programm funktionieren, welches auf hibernate basiert.
mein bisheriger Versuch sieht so aus:
Code:
      query = database.createQuery(
            " select  kunde from Adressen  kunde where "+
              " (geburtstag is not null) and "+
              " month(geburtstag) between :startmonth and :endmonth and case" +
              " when month(geburtstag) = :startmonth " +
              "   then  (day(geburtstag) >= :startday ) " +
              " when month(geburtstag) = :endmonth " +
              "   then  (day(geburtstag) <= :endday ) " +
              " else 1 end"+
              " order by month(geburtstag)ASC, day(geburtstag)ASC"
              );

Doch leider klappt das nicht. Geht das überhaupt, und wenn ja wo ist mein Gedankenfehler?
Wenn es nicht geht, bleibt mir nur eine SQLQuery. Die möchte ich jedoch gerne vermeiden.
Beim ausführen der obigen Query erhalte ich folgende Exception:
Code:
14:06:53,640 ERROR [PARSER] <AST>:1:153: unexpected AST node: case

...
Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: case near line 1, column 153 [ select  kunde from de.katharinad.kv.model.Adressen  kunde where  (geburtstag is not null) and  month(geburtstag) between :startmonth and :endmonth and case when month(geburtstag) = :startmonth    then  (day(geburtstag) >= :startday )  when month(geburtstag) = :endmonth    then  (day(geburtstag) <= :endday )  else 1 end order by month(geburtstag)ASC, day(geburtstag)ASC]
   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.analyze(QueryTranslatorImpl.java:235)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
   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.jboss.seam.persistence.HibernateSessionProxy.createQuery(HibernateSessionProxy.java:102)
   at de.katharinad.kv.GeburtstagQuery.queryGeburtstag(GeburtstagQuery.java:59)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.jboss.seam.util.Reflections.invoke(Reflections.java:20)
   at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:57)
   at org.jboss.seam.interceptors.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:34)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.interceptors.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:47)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.interceptors.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:27)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:103)
   at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:151)
   at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:87)
   at de.katharinad.kv.GeburtstagQuery_$$_javassist_3.queryGeburtstag(GeburtstagQuery_$$_javassist_3.java)
   at de.katharinad.Geburtstagsliste.generate(Geburtstagsliste.java:43)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.jboss.seam.util.Reflections.invoke(Reflections.java:20)
   at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:57)
   at org.jboss.seam.interceptors.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:34)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.interceptors.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:47)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.interceptors.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:27)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:103)
   at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:151)
   at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:87)
   at de.katharinad.Geburtstagsliste_$$_javassist_2.generate(Geburtstagsliste_$$_javassist_2.java)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at com.sun.el.parser.AstValue.invoke(AstValue.java:174)
   at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:286)
   at com.sun.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:68)
   at com.sun.facelets.el.LegacyMethodBinding.invoke(LegacyMethodBinding.java:69)
   ... 38 more
14:06:53,734 ERROR [DebugPageHandler] redirecting to debug page
org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: case near line 1, column 153 [ select  kunde from de.katharinad.kv.model.Adressen  kunde where  (geburtstag is not null) and  month(geburtstag) between :startmonth and :endmonth and case when month(geburtstag) = :startmonth    then  (day(geburtstag) >= :startday )  when month(geburtstag) = :endmonth    then  (day(geburtstag) <= :endday )  else 1 end order by month(geburtstag)ASC, day(geburtstag)ASC]
   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.analyze(QueryTranslatorImpl.java:235)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
   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.jboss.seam.persistence.HibernateSessionProxy.createQuery(HibernateSessionProxy.java:102)
   at de.katharinad.kv.GeburtstagQuery.queryGeburtstag(GeburtstagQuery.java:59)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.jboss.seam.util.Reflections.invoke(Reflections.java:20)
   at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:57)
   at org.jboss.seam.interceptors.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:34)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.interceptors.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:47)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.interceptors.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:27)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:103)
   at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:151)
   at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:87)
   at de.katharinad.kv.GeburtstagQuery_$$_javassist_3.queryGeburtstag(GeburtstagQuery_$$_javassist_3.java)
   at de.katharinad.Geburtstagsliste.generate(Geburtstagsliste.java:43)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.jboss.seam.util.Reflections.invoke(Reflections.java:20)
   at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:57)
   at org.jboss.seam.interceptors.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:34)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.interceptors.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:47)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.interceptors.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:27)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:69)
   at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:103)
   at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:151)
   at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:87)
   at de.katharinad.Geburtstagsliste_$$_javassist_2.generate(Geburtstagsliste_$$_javassist_2.java)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at com.sun.el.parser.AstValue.invoke(AstValue.java:174)
   at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:286)
   at com.sun.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:68)
   at com.sun.facelets.el.LegacyMethodBinding.invoke(LegacyMethodBinding.java:69)
   at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:63)
   at javax.faces.component.UICommand.broadcast(UICommand.java:106)
   at org.ajax4jsf.component.AjaxViewRoot.processEvents(AjaxViewRoot.java:186)
   at org.ajax4jsf.component.AjaxViewRoot.broadcastEvents(AjaxViewRoot.java:164)
   at org.ajax4jsf.component.AjaxViewRoot.processApplication(AjaxViewRoot.java:352)
   at org.apache.myfaces.lifecycle.LifecycleImpl.invokeApplication(LifecycleImpl.java:343)
   at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:86)
   at javax.faces.webapp.FacesServlet.service(FacesServlet.java:137)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at org.jboss.seam.web.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:63)
   at org.jboss.seam.debug.hot.HotDeployFilter.doFilter(HotDeployFilter.java:60)
   at org.jboss.seam.web.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:49)
   at org.jboss.seam.web.RedirectFilter.doFilter(RedirectFilter.java:45)
   at org.jboss.seam.web.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:49)
   at org.jboss.seam.web.ExceptionFilter.doFilter(ExceptionFilter.java:57)
   at org.jboss.seam.web.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:49)
   at org.jboss.seam.web.MultipartFilter.doFilter(MultipartFilter.java:79)
   at org.jboss.seam.web.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:49)
   at org.jboss.seam.web.SeamFilter.doFilter(SeamFilter.java:84)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:141)
   at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:281)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
   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:869)
   at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
   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(Unknown Source)
Top
 Profile  
 
bafz
 Post subject:
PostPosted: Mon Oct 08, 2007 11:46 am 
Newbie

Joined: Tue Jun 28, 2005 10:20 am
Posts: 15
For now, i am using a sqlQuery in this case. but i do want to use HQL:-(

Code:
        String sqlstr =   "SELECT * FROM ADRESSEN "+
                        " WHERE (geburtstag is not null) and " +
                        "(MONTH(geburtstag) BETWEEN :startmonth AND :endmonth) AND  "+
                 "case "+
                 "when MONTH(Geburtstag) = :startmonth"+
                  " then DAYOFMONTH(geburtstag) >= :startday "+
                  " when MONTH(Geburtstag) = :endmonth "+
                  " then DAYOFMONTH(geburtstag) <= :endday "+
                  " else  1 end"+
                  " ORDER BY MONTH( geburtstag ) ASC, DAYOFMONTH( geburtstag ) ASC";
         SQLQuery sqlQuery = kdDatabase.createSQLQuery(sqlstr).addEntity(Adressen.class);

How can this code be properly converted into HQL? Or is this not possible?
Top
 Profile  
 
LaLiLuna
 Post subject:
PostPosted: Mon Oct 08, 2007 3:33 pm 
Expert
Expert

Joined: Tue Nov 23, 2004 7:00 pm
Posts: 570
Location: mostly Frankfurt Germany
Vielleicht kannst Du das case when in eine "Formula packen" oder alternativ das ganze auf einen View mappen, der das Case when durchführt.

_________________
Best Regards
Sebastian
---
Training for Hibernate and Java Persistence
Tutorials for Hibernate, Spring, EJB, JSF...
eBook: Hibernate 3 - DeveloperGuide
Paper book: Hibernate 3 - Das Praxisbuch
http://www.laliluna.de

Top
 Profile  
 
bafz
 Post subject:
PostPosted: Tue Oct 09, 2007 8:24 am 
Newbie

Joined: Tue Jun 28, 2005 10:20 am
Posts: 15
Danke für die Antwort.
Formula's hab ich bisher nur für berechnete Properties eingesetzt. Mir ist nicht klar wie ich das in einer Query nutzen kann.
Die Idee mit den Views hat mir auch nicht weiter geholfen, da ich ja erst zur Abfragezeit weiss, was zurückgeliefert werden soll.

Ich hatte schon über Filter nachgedacht,da diese sich ja parametrisieren lassen. Theoretisch kann ich ja für jede when clause einen Filter definieren. Aber praktisch weiss ich ja nicht welche Filter aktiviert werden sollen und welche nicht:-(

Immerhin wird jain Kapitel 14.9 Expressions der HibernateDoku 'case when' aufgeführt.
Nur leider funktioniert das bei mir nicht:-(
Ciao,
Carsten
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users (Deutsch)

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.