3 Replies Latest reply on Nov 23, 2009 7:03 PM by Walter White

    SubQuery using EntityQuery

    scott duke Novice

      I am trying to preform a lookup on a database table using EntityQuery where there is a subquery. Using MySQL, I am able to get the records I am wanting using the following SQL.




      select * from
      (SELECT o.* FROM artwork.owner o order by o.numberId, o.purchasedOn desc) as owner
      group by owner.numberId;
      



      When I place the code in my EntityQuery in java, it does not like the parenthesis.


      Questions:
      1. Do subqueries need to be in the where clause?
      2. Would it be better to not use EntityQuery, in order to use my own query?


      Here is a little more information about my database tables.


      Edition.java will have multiple numbers(Number.java) for the edition. Each number has an owner(Owner.java) list, which keeps a record of each owner for each number.


      For example: Say edition number 100 has had 3 owners: Smith, Jones and Thomas. Thomas is the current owner. When I view the owner Jones and his current listing of items he owns, there should not be anything. When I view Thomas and his current listing of items, I should see the item he owns.


      Edition.java




      @Entity
      public class Edition implements Serializable {
           
           private static final long serialVersionUID = -1742264939963952856L;
           
          // Field names which can be accessed from other programs.
          public static final String FIELD_ID = "id";
           public static final String FIELD_VERSION = "version";
          public static final String FIELD_TO_ITEM = "itemId";
          public static final String FIELD_TO_MEDIUMCODE = "mediumCodeId";
          public static final String FIELD_TO_EDITIONCODE = "editionCodeId";
          public static final String FIELD_TO_ENUM_LIMITED = "limited";
          public static final String FIELD_TOTAL = "total";
          public static final String FIELD_TO_RESERVE = "reserveId";
          //public static final String FIELD_REMAINING = "remaining";
          public static final String FIELD_SIGNATURE = "signature";
      
           //seam-gen attributes (you should probably edit these)
           private Long id;
           private Integer version;
          //add additional entity attributes
          private Item item;
          private Code mediumCode;
          private Code editionCode;
          private BooleanType limited;
          private Long total;
          private String signature;
          private List<Number> numbers = new ArrayList<Number>(0);
          // TODO: Add price listing.
          //private List<Price> prices = new ArrayList<Price>(0);
      
      
           
      
           public Edition() {
                // Do not predefine any members here or it will cause problems on listing screens. 
                // Define them in the Home class.
           }
           
           //seam-gen attribute getters/setters with annotations (you probably should edit)
                
           @Id @GeneratedValue
           @Column(name = FIELD_ID)
           public Long getId() {
                return id;
           }
      
           public void setId(Long id) {
                this.id = id;
           }
           
           @Version
           @Column(name = FIELD_VERSION)
           public Integer getVersion() {
                return version;
           }
      
           @SuppressWarnings("unused")
           private void setVersion(Integer version) {
                this.version = version;
           }
      
           /**
            * @return the item
            */
           @ManyToOne
          @JoinColumn(name = FIELD_TO_ITEM, referencedColumnName = "id", nullable = false)
           @org.hibernate.annotations.ForeignKey(name = "fk_Edition_to_ItemId")
           public Item getItem() {
                return item;
           }
      
           /**
            * @param item the item to set
            */
           public void setItem(Item item) {
                this.item = item;
           }
      
           /**
            * @return the medium
            */
          @ManyToOne
          @JoinColumn(name = FIELD_TO_MEDIUMCODE, referencedColumnName = "id", nullable = false)
          @org.hibernate.annotations.ForeignKey(name = "fk_MediumCode_to_CodeId")
           public Code getMediumCode() {
                return mediumCode;
           }
      
           /**
            * @param medium the medium to set
            */
           public void setMediumCode(Code mediumCode) {
                this.mediumCode = mediumCode;
           }
      
           /**
            * @return the edition
            */
          @ManyToOne
          @JoinColumn(name = FIELD_TO_EDITIONCODE, referencedColumnName = "id", nullable = false)
          @org.hibernate.annotations.ForeignKey(name = "fk_EditionCode_to_CodeId")
           public Code getEditionCode() {
                return editionCode;
           }
      
           /**
            * @param edition the edition to set
            */
           public void setEditionCode(Code editionCode) {
                this.editionCode = editionCode;
           }
      
           /**
            * This defines if this edition has an unlimited number of prints.
            * true - this edition is limited to the amount set in FIELD_TOTAL.
            * false - this edition is unlimited. Setting this to false will 
            * disable FIELD_TOTAL and will not display the print number.
            * 
            * @return the limited
            */
           @Enumerated
           @Column(name = FIELD_TO_ENUM_LIMITED)
           public BooleanType getLimited() {
                return limited;
           }
      
           /**
            * @param limited the limited to set
            */
           public void setLimited(BooleanType limited) {
                this.limited = limited;
           }
      
           /**
            * @return the size
            */
           @Min(value=0)
           @Column(name = FIELD_TOTAL)
           public Long getTotal() {
                return total;
           }
      
           /**
            * @param size the size to set
            */
           public void setTotal(Long total) {
                this.total = total;
           }
      
           /**
            * @return the signature
            */
           @Column(name = FIELD_SIGNATURE)
           public String getSignature() {
                return signature;
           }
      
           /**
            * @param signature the signature to set
            */
           public void setSignature(String signature) {
                this.signature = signature;
           }
      
           /**
            * @return the numbers
            */
          @OneToMany(cascade = CascadeType.ALL, mappedBy = "edition", fetch = FetchType.LAZY)
           public List<Number> getNumbers() {
                return numbers;
           }
      
           /**
            * @param numbers the numbers to set
            */
           public void setNumbers(List<Number> numbers) {
                this.numbers = numbers;
           }
           
           @Transient
           public Boolean getIsLimited() {
                return getLimited().equals(BooleanType.TRUE);
           }
      }
      



      Number.java




      @Entity
      public class Number implements Serializable {
      
           private static final long serialVersionUID = -1975151947717832408L;
           
           // Field names which can be accessed from other programs.
           public static final String FIELD_ID = "id";
           public static final String FIELD_VERSION = "version";
           public static final String FIELD_SEQUENCE = "sequence";
           public static final String FIELD_SUBSEQUENCE = "subSequence";          // Duplicate sequence number.
           // public static final String FIELD_TO_ITEM = "itemId";
           public static final String FIELD_TO_EDITION = "editionId";
           // TODO: Status: stolen, destroyed, reserved (when reserved numbers are assigned), available, owned and unknown.
           public static final String FIELD_STATUS = "status";
      
      
           // seam-gen attributes (you should probably edit these)
           private Long id;
           private Integer version;
           private Long sequence;
           private Integer subsequence;
           private Edition edition;
          private List<Owner> owners = new ArrayList<Owner>(0);
           private NumberStatusType status;
      
      
           @Id
           @GeneratedValue
           @Column(name = FIELD_ID)
           public Long getId() {
                return id;
           }
      
           public void setId(Long id) {
                this.id = id;
           }
      
           @Version
           @Column(name = FIELD_VERSION)
           public Integer getVersion() {
                return version;
           }
      
           @SuppressWarnings("unused")
           private void setVersion(Integer version) {
                this.version = version;
           }
           
           /**
            * @return the editionNumber. Every edition number starts with 1.
            */
           @Min(value=1)
           @Column(name = FIELD_SEQUENCE)
           public Long getSequence() {
                return sequence;
           }
      
           /**
            * @param editionNumber
            *            the editionNumber to set
            */
           public void setSequence(Long sequence) {
                this.sequence = sequence;
           }          
      
           /**
            * @return the subsequence. For each print this will be set to 1.
            * This number is incremented for each duplicate.
            */
           @Min(value=1)
           @Column(name = FIELD_SUBSEQUENCE)
           public Integer getSubSequence() {
                return subsequence;
           }
      
           /**
            * @param sequence the sequence to set
            */
           public void setSubSequence(Integer subsequence) {
                this.subsequence = subsequence;
           }
      
           /**
            * @return the edition
            */
           @ManyToOne
          @JoinColumn(name = FIELD_TO_EDITION, referencedColumnName = "id", nullable = false)
           @org.hibernate.annotations.ForeignKey(name = "fk_Number_to_EditionId")
           public Edition getEdition() {
                return edition;
           }
      
           /**
            * @param edition
            *            the edition to set
            */
           public void setEdition(Edition edition) {
                this.edition = edition;
           }
      
           /**
            * @return the owners
            */
          @OneToMany(cascade = CascadeType.ALL, mappedBy = "number", fetch = FetchType.LAZY)     
           public List<Owner> getOwners() {
                return owners;
           }
      
           /**
            * @param owners the owners to set
            */
           public void setOwners(List<Owner> owners) {
                this.owners = owners;
           }
      
           /**
            * @return the status
            */
           @Enumerated
           @Column(name = FIELD_STATUS)     
           public NumberStatusType getStatus() {
                return status;
           }
      
           /**
            * @param status the status to set
            */
           public void setStatus(NumberStatusType status) {
                this.status = status;
           }
      
           
      }
      



      Owner.java



      @Entity
      public class Owner implements Serializable {
      
           private static final long serialVersionUID = -8147201531690492865L;
           
           // Field names.
           public static final String FIELD_ID = "id";
           public static final String FIELD_VERSION = "version";
          public static final String FIELD_TO_NUMBER = "numberId";
           public static final String FIELD_TO_UZER = "uzerId";
          public static final String FIELD_PURCHASEDON = "purchasedOn";
      
           // seam-gen attributes (you should probably edit these)
           private Long id;
           private Integer version;
           private Number number;
           private Date purchasedOn;
           private Uzer uzer;
      
           // add additional entity attributes
      
           // seam-gen attribute getters/setters with annotations (you probably should
           // edit)
      
           @Id
           @GeneratedValue
           @Column(name = FIELD_ID)
           public Long getId() {
                return id;
           }
      
           public void setId(Long id) {
                this.id = id;
           }
      
           @Version
           @Column(name = FIELD_VERSION)
           public Integer getVersion() {
                return version;
           }
      
           @SuppressWarnings("unused")
           private void setVersion(Integer version) {
                this.version = version;
           }
      
           /**
            * @return the number
            */
           @ManyToOne
          @JoinColumn(name = FIELD_TO_NUMBER, referencedColumnName = "id", nullable = false)
           @org.hibernate.annotations.ForeignKey(name = "fk_Owner_to_NumberId")     
           public Number getNumber() {
                return number;
           }
      
           /**
            * @param number the number to set
            */
           public void setNumber(Number number) {
                this.number = number;
           }
      
           /**
            * @return the purchasedOn
            */
           @Column(name = FIELD_PURCHASEDON)
           public Date getPurchasedOn() {
                return purchasedOn;
           }
      
           /**
            * @param purchasedOn the purchasedOn to set
            */
           public void setPurchasedOn(Date purchasedOn) {
                this.purchasedOn = purchasedOn;
           }
      
           /**
            * @return the owner
            */
           @ManyToOne
           @JoinColumn(name = FIELD_TO_UZER, referencedColumnName = "id", nullable = false)
           @org.hibernate.annotations.ForeignKey(name = "fk_Owner_to_UzerId")     
           public Uzer getUzer() {
                return uzer;
           }
      
           /**
            * @param owner the owner to set
            */
           public void setUzer(Uzer uzer) {
                this.uzer = uzer;
           }
      
      
      }
      





        • 1. Re: SubQuery using EntityQuery
          scott duke Novice

          I did a little bit more testing and here is what I came up with. It seems EntityQuery is unable to handle nesting of subselects.


          MySQL - this works in MySQL.



          select owner.*
          from artwork.Owner owner
          where owner.uzerid = 1
          and owner.id in
          (select owner1.id from
          (SELECT o1.* FROM artwork.owner o1 order by o1.numberId, o1.purchasedOn desc)
          as owner1 where owner1.uzerId = 1 group by owner1.numberId);
          



          When I use the following SQL in EntityQuery



          select owner, uzer, number, edition, item from com.artwork.entity.Owner owner inner join owner.uzer uzer inner join owner.number number inner join number.edition edition inner join edition.item item inner join edition.editionCode editionCode inner join edition.mediumCode mediumCode  where owner.uzer.id = :el2 and owner.id in (select owner.id from (SELECT o1.* FROM owner o1 order by o1.numberId, o1.purchasedOn desc) where owner.uzerId = :el5 group by owner.numberId)
          



          I receive this:


          2009-09-29 20:39:14,125 DEBUG [org.hibernate.hql.ast.QueryTranslatorImpl] parse() - HQL: select owner, uzer, number, edition, item from com.artwork.entity.Owner owner inner join owner.uzer uzer inner join owner.number number inner join number.edition edition inner join edition.item item inner join edition.editionCode editionCode inner join edition.mediumCode mediumCode  where owner.uzer.id = :el2 and owner.id in (select owner.id from (SELECT o1.* FROM owner o1 order by o1.numberId, o1.purchasedOn desc) where owner.uzerId = :el5 group by owner.numberId)
          2009-09-29 20:39:14,140 ERROR [org.hibernate.hql.PARSER] line 1:350: unexpected token: (
          2009-09-29 20:39:14,140 DEBUG [org.hibernate.hql.ast.ErrorCounter] line 1:350: unexpected token: (
          line 1:350: unexpected token: (
               at org.hibernate.hql.antlr.HqlBaseParser.fromRange(HqlBaseParser.java:1584)
               at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1408)
               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.union(HqlBaseParser.java:1065)
               at org.hibernate.hql.antlr.HqlBaseParser.subQuery(HqlBaseParser.java:3683)
               at org.hibernate.hql.antlr.HqlBaseParser.compoundExpr(HqlBaseParser.java:3063)
               at org.hibernate.hql.antlr.HqlBaseParser.inList(HqlBaseParser.java:2874)
               at org.hibernate.hql.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2720)
               at org.hibernate.hql.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2449)
               at org.hibernate.hql.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2413)
               at org.hibernate.hql.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2341)
               at org.hibernate.hql.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2296)
               at org.hibernate.hql.antlr.HqlBaseParser.expression(HqlBaseParser.java:2082)
               at org.hibernate.hql.antlr.HqlBaseParser.logicalExpression(HqlBaseParser.java:1858)
               at org.hibernate.hql.antlr.HqlBaseParser.whereClause(HqlBaseParser.java:454)
               at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:708)
               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:248)
               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.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:92)
               at org.jboss.seam.persistence.EntityManagerProxy.createQuery(EntityManagerProxy.java:81)
               at org.jboss.seam.framework.EntityQuery.createQuery(EntityQuery.java:175)
               at org.jboss.seam.framework.EntityQuery.initResultList(EntityQuery.java:73)
               at org.jboss.seam.framework.EntityQuery.getResultList(EntityQuery.java:65)
               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:22)
               at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
               at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
               at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28)
               at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
               at org.jboss.seam.transaction.TransactionInterceptor$1.work(TransactionInterceptor.java:95)
               at org.jboss.seam.util.Work.workInTransaction(Work.java:47)
               at org.jboss.seam.transaction.TransactionInterceptor.aroundInvoke(TransactionInterceptor.java:89)
               at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
               at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44)
               at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
               at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
               at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:185)
               at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:103)
               at com.artwork.role.admin.store.OwnerList_$$_javassist_7.getResultList(OwnerList_$$_javassist_7.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 javax.el.BeanELResolver.getValue(BeanELResolver.java:62)
               at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:53)
               at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:72)
               at org.jboss.el.parser.AstPropertySuffix.getValue(AstPropertySuffix.java:53)
               at org.jboss.el.parser.AstValue.getValue(AstValue.java:67)
               at org.jboss.el.parser.AstEmpty.getValue(AstEmpty.java:29)
               at org.jboss.el.parser.AstNot.getValue(AstNot.java:26)
               at org.jboss.el.parser.AstChoice.getValue(AstChoice.java:29)
               at org.jboss.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
               at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71)
               at javax.faces.component.UIComponentBase.isRendered(UIComponentBase.java:390)
               at org.richfaces.component.RenderPhaseDataScrollerVisitor.isRendered(RenderPhaseDataScrollerVisitor.java:204)
               at org.richfaces.component.RenderPhaseDataScrollerVisitor.beforeComponent(RenderPhaseDataScrollerVisitor.java:100)
               at org.richfaces.event.RenderPhaseComponentListener.processComponents(RenderPhaseComponentListener.java:47)
               at org.richfaces.event.RenderPhaseComponentListener.processComponents(RenderPhaseComponentListener.java:51)
               at org.richfaces.event.RenderPhaseComponentListener.processComponents(RenderPhaseComponentListener.java:55)
               at org.richfaces.event.RenderPhaseComponentListener.processComponents(RenderPhaseComponentListener.java:55)
               at org.richfaces.event.RenderPhaseComponentListener.processComponents(RenderPhaseComponentListener.java:55)
               at org.richfaces.event.RenderPhaseComponentListener.processComponents(RenderPhaseComponentListener.java:55)
               at org.richfaces.event.RenderPhaseComponentListener.beforePhase(RenderPhaseComponentListener.java:71)
               at org.ajax4jsf.component.AjaxViewRoot.processPhaseListeners(AjaxViewRoot.java:185)
               at org.ajax4jsf.component.AjaxViewRoot.encodeBegin(AjaxViewRoot.java:502)
               at javax.faces.component.UIComponent.encodeAll(UIComponent.java:934)
               at com.sun.facelets.FaceletViewHandler.renderView(FaceletViewHandler.java:592)
               at org.ajax4jsf.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:100)
               at org.ajax4jsf.application.AjaxViewHandler.renderView(AjaxViewHandler.java:176)
               at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:109)
               at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:100)
               at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
               at javax.faces.webapp.FacesServlet.service(FacesServlet.java:266)
               at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
               at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
               at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:83)
               at org.jboss.seam.web.IdentityFilter.doFilter(IdentityFilter.java:40)
               at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
               at org.jboss.seam.web.MultipartFilter.doFilter(MultipartFilter.java:90)
               at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
               at org.jboss.seam.web.ExceptionFilter.doFilter(ExceptionFilter.java:64)
               at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
               at org.jboss.seam.web.RedirectFilter.doFilter(RedirectFilter.java:45)
               at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
               at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:178)
               at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
               at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:390)
               at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:517)
               at org.jboss.seam.web.Ajax4jsfFilter.doFilter(Ajax4jsfFilter.java:56)
               at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
               at org.jboss.seam.web.LoggingFilter.doFilter(LoggingFilter.java:58)
               at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
               at org.jboss.seam.web.HotDeployFilter.doFilter(HotDeployFilter.java:53)
               at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
               at org.jboss.seam.servlet.SeamFilter.doFilter(SeamFilter.java:158)
               at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
               at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
               at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
               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:230)
               at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
               at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:182)
               at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:432)
               at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
               at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
               at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
               at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
               at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
               at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
               at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
               at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
               at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
               at java.lang.Thread.run(Unknown Source)
          2009-09-29 20:39:14,140 ERROR [org.hibernate.hql.PARSER] line 1:420: unexpected token: where
          2009-09-29 20:39:14,140 DEBUG [org.hibernate.hql.ast.ErrorCounter] line 1:420: unexpected token: where
          line 1:420: unexpected token: where
          







          • 2. Re: SubQuery using EntityQuery
            The Empi Newbie

            Hi Scott,


            did you solve this issue?
            If so, I'd love to hear your solution, because I'm struggling with the same behaviour.


            Best Regards
            empi

            • 3. Re: SubQuery using EntityQuery
              Walter White Novice

              Scott,


              I believe anytime you do a groupBy, you no longer have a single entity anymore (but aggregate data) and that doesn't make sense.  However, this may be worth a try:


              <framework:entity-query ejbql="SELECT owner FROM artwork.owner owner" order="numberId, purchasedOn" order-direction="DESC" group-by="numberId"/>




              If you're trying to do reporting, have you tried a report engine like Jasper Reports or Eclipse BIRT?  It may be overkill for this type of thing, but I don't know how many other reports you will be generating.





              Walter