-->
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 » Projects » Search, Validator, Shards

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
  [ 9 posts ] 
  Previous topic | Next topic 
Author Message
maitscha
 Post subject: Hibernate Having Clause
PostPosted: Fri Jul 21, 2006 1:32 pm 
Regular
Regular

Joined: Sat May 20, 2006 3:49 am
Posts: 78
Hello,

I am using the latest Hibernate EJB 3.0 Implementation from trunk svn tree, this means Hibernate Core with Hibernate Annotations and Hibernate EntityManager. I have a problem with the following query:

Code:
        //get the items which has a quanity ordered of >50
        query = manager.createQuery("select items " +
                                    "from Itemtable items " +
                                    "join items.salespositions sales " +
                                    "having sum(sales.quantity) > 60");
        items = (ArrayList<Itemtable>)query.getResultList();
       
        if (items.size() > 0) {
            System.out.println("Items which has a quanitity ordered of >50");
            for (Itemtable item : items) {
                System.out.println("Id: " + item.getId() + " ,Name: " + item.getName());
            }
        } else {
            System.out.println("Nothing found.");
        }


I always get the following error:
Code:
Exception in thread "main" java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: having near line 1, column 67 [select items from Itemtable items join items.salespositions sales having sum(sales.quantity) > 60]
   at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:596)
   at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:94)
   at HibernateTest.select(HibernateTest.java:67)
   at HibernateTest.main(HibernateTest.java:22)
Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: having near line 1, column 67 [select items from Itemtable items join items.salespositions sales having sum(sales.quantity) > 60]
   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.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:91)

what could that be? isn't the having clause a regular hql syntax?

Regards,

Christopher
Top
 Profile  
 
alesj
 Post subject:
PostPosted: Fri Jul 21, 2006 2:24 pm 
Expert
Expert

Joined: Sat Oct 25, 2003 8:49 am
Posts: 490
Location: Vrhnika, Slovenia
Missing some 'group by' infront having?
Top
 Profile  
 
maitscha
 Post subject:
PostPosted: Fri Jul 21, 2006 2:31 pm 
Regular
Regular

Joined: Sat May 20, 2006 3:49 am
Posts: 78
no.

the same error with the following query:
Code:
        query = manager.createQuery("select items " +
                                    "from Itemtable items " +
                                    "join items.salespositions sales " +
                                    "where having sum(sales.quantity) > 60");
Top
 Profile  
 
alesj
 Post subject:
PostPosted: Fri Jul 21, 2006 2:32 pm 
Expert
Expert

Joined: Sat Oct 25, 2003 8:49 am
Posts: 490
Location: Vrhnika, Slovenia
Uf ... no idea :-( :
- http://dev.mysql.com/doc/maxdb/en/0f/486fae2f9611d3a98100a0c9449261/content.htm
Top
 Profile  
 
maitscha
 Post subject:
PostPosted: Fri Jul 21, 2006 4:13 pm 
Regular
Regular

Joined: Sat May 20, 2006 3:49 am
Posts: 78
and its an oracle database. it's no sql-syntax-problem, but an hql-syntax-problem.
Top
 Profile  
 
maitscha
 Post subject:
PostPosted: Sat Jul 22, 2006 3:05 am 
Regular
Regular

Joined: Sat May 20, 2006 3:49 am
Posts: 78
ok, just found the solution:
Code:
        //get the items which has a quanity ordered of >50
        query = manager.createQuery("select i.id " +
                "from Itemtable i " +
                "join i.salespositions s " +
                "group by i.id " +
                "having sum(s.quantity) > 60");
        ArrayList<BigDecimal> ids = (ArrayList<BigDecimal>)query.getResultList();
       
        if (allItems.size() > 0) {
            System.out.println("\nItems which has a quanitity ordered of >50:");
            for (BigDecimal id : ids) {
                System.out.print("Id: " + id);
                Itemtable i = manager.find(Itemtable.class, id);
                System.out.print(" Name: " + i.getName());
            }
        } else {
            System.out.println("Nothing found.");
        }
Top
 Profile  
 
alesj
 Post subject:
PostPosted: Sat Jul 22, 2006 3:15 am 
Expert
Expert

Joined: Sat Oct 25, 2003 8:49 am
Posts: 490
Location: Vrhnika, Slovenia
Code:
ArrayList<BigDecimal> ids = (ArrayList<BigDecimal>)query.getResultList()


Better use plain List interface.
Top
 Profile  
 
maitscha
 Post subject:
PostPosted: Sat Jul 22, 2006 3:21 am 
Regular
Regular

Joined: Sat May 20, 2006 3:49 am
Posts: 78
alesj wrote:
Code:
ArrayList<BigDecimal> ids = (ArrayList<BigDecimal>)query.getResultList()


Better use plain List interface.


for what reason, why?
Top
 Profile  
 
alesj
 Post subject:
PostPosted: Sat Jul 22, 2006 4:31 am 
Expert
Expert

Joined: Sat Oct 25, 2003 8:49 am
Posts: 490
Location: Vrhnika, Slovenia
Where does it say that it will always return ArrayList as List implementation - API says that it will be List - maybe they change something and use their own implementaion of List.
And it is similar in all other cases - Set, Map, <interface here>, ...

Just as a small advice ...
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
  [ 9 posts ] 

Board index » Projects » Search, Validator, Shards

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.