count() not working like it should

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

I'm reading through chap. 7 of Hibernate In Action and simply trying to count a field exactly like 7.4.2 describes, only it's not working correclty.

Here's my method:

Code:

  public Integer getCaseTotal(Order order, Date beginDate, Date endDate)
  {
    Session s = HibernateUtil.getCurrentSession();
      
      Integer count = (Integer)s.createQuery("select count(o.PickList.OrderItems) from Order o where o.Id = :id and o.SentDate between :bd and :ed")
         .setLong("id", order.getId())
         .setDate("bd", beginDate)
         .setDate("ed", endDate)
         .uniqueResult();
      
      return count;
  }

Here's the query that is returned:

Code:

select count(.) as col_0_0_ from sr_order order0_, sr_inv_pick_list picklist1_, sr_inv_pick_list_detail orderitems2_ where picklist1_.inv_pick_list_id=orderitems2_.inv_pick_list_id and order0_.order_id=picklist1_.inv_pick_list_id and order0_.order_id=? and (order0_.sent_date between ? and ?)

...which results in an exception being thrown that the query can't be parsed.

However, if I change it to count(*) it returns a query that is executable...but incorrect for my needs.

"PickList.OrderItems" is definitely a valid collection of OrderItem objects belonging to the PickList object and the PickList belongs to the Order object...and they are all thustly related in the mapping documents. I'm doing other selects with the same objects (without aggregates) w/o a problem.

Any ideas? This kills my report...I'm not sure how else to go forward w/ this.

Hibernate version: 3.1

Name and version of the database you are using: MSSQL 2000

LaLiLuna · **Posted:** Wed Jan 11, 2006 3:04 pm

what about
select count(item) from OrderItem item innerjoin Picklist p inner join Order o where
...

Regards Sebastian

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

LaLiLuna wrote:

what about
select count(item) from OrderItem item innerjoin Picklist p inner join Order o where
...

Regards Sebastian

Yes, that'd probably work...however I found a simple solution to what I already had...GOD I love Hibernate!!

I changed "o.PickList.OrderItems" to "o.PickList.OrderItems.Id" and all is good.

Thanks!

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

But why doesn't THIS work!?

Code:

select sum(c.Orders.PickList.OrderItems.Quantity) from Customer c where c.Id = :id and c.Orders.SentDate between :bd and :ed

The relationships are all there in the mapping files....Customer has an Order, Order has a PickList, PickList has a OrderItem...and OrderItem has a Quantity getter/setter....yet I get this exception:

Code:

java.lang.NullPointerException
   org.hibernate.hql.ast.HqlSqlWalker.lookupProperty(HqlSqlWalker.java:381)
   org.hibernate.hql.antlr.HqlSqlBaseWalker.addrExpr(HqlSqlBaseWalker.java:4306)
   org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1209)
   org.hibernate.hql.antlr.HqlSqlBaseWalker.aggregateExpr(HqlSqlBaseWalker.java:2881)
   org.hibernate.hql.antlr.HqlSqlBaseWalker.functionCall(HqlSqlBaseWalker.java:2340)
   org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:1957)
   org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:1819)
   org.hibernate.hql.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1390)
   org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:551)
   org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:279)
   org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:227)
   org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
   org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
   org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:105)
   org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:74)
   org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:53)
   org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
   org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:108)
   org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:88)
   org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1540)

...and in the console output I noticed this while it was parsing the queries:

Code:

ERROR PARSER:33 - illegal syntax near collection: PickList

I ended up refactoring the query to look like this...and it works:

Code:

select sum(o.PickList.OrderItems.Quantity) from Order o where o.Customer.Id = :id and o.SentDate between :bd and :ed

However, that's counter-intuitive since I am passing in a customer and would have preferred to be able to drill down from the customer level.

What am I not seeing?

Thanks again!