Simple select with count .. howto ?

perlovmo · **Joined:** Fri May 21, 2010 8:37 am **Posts:** 14

Got a simple select (ex):

Code:

Select book.name as Bookname,
          author.name as Authorname,
          Count( Distinct(book.publisher) ) as Numberofdiffrentpublisher
From book, author
Where book.author_id = author.author_id,
         book.publishdate <= {somedate},
         book.publishdate >= {somedate}
 

How do I accomplish this in Hibernate ?

perlovmo · **Joined:** Fri May 21, 2010 8:37 am **Posts:** 14

Made the follwing criteria, but the result set is empty.

Code:

DetachedCriteria query = DetachedCriteria.forClass(Book.class) 
.add(Restrictions.between("publishDate", startDate, endDate)) 
.setProjection(Projections.countDistinct("publisher")); 
List retVal = hibernateTemplate.findByCriteria(query);

Can't seem to wrap my head around what's wrong. Anyone able to help ?

perlovmo · **Joined:** Fri May 21, 2010 8:37 am **Posts:** 14

Doh - forgot to define the annoted class in the hibernate config *smackshead*

Anyhow - Another problem now:

the countdistinct didn't act as I had expected. I thought it would run as "Count ( Distinct( "properety") )" but when I check the select generated, it only use "Count( "property" ).

Does Countdistinct only count the number of rows ? i thought the rowCount was for that (?)

Anyone ?

perlovmo · **Joined:** Fri May 21, 2010 8:37 am **Posts:** 14

So, after abit more search I've discovered it -IS- broken -> http://opensource.atlassian.com/project ... e/HHH-4957

sanne.grinovero · **Posted:** Wed Jun 02, 2010 5:41 am

the issues says "fixed in 3.5.2", you might want to update.

perlovmo · **Joined:** Fri May 21, 2010 8:37 am **Posts:** 14

Aye - did so :)

and now it works :)