Hi there,
how do I do the following SQL in Hibernate 3.2 using Criteria and ProjectionList? I keep getting the wrong rowCount, I'm expecting 5 and I get 21. Here is the SQL I want to emulate in Hibernate:

SELECT count(distinct order_id, tower_no, pallet_layer_no) from stackedproduct;

My Hibernate code looks like this:


stackedProductsCriteria.setProjection(Projections.projectionList()
.add(Projections.rowCount(), "noOfPallets")
.add(Projections.countDistinct("sortedOrder"))
.add(Projections.countDistinct("towerNo"))
.add(Projections.countDistinct("palletLayerNo")));

List <PalletStatsBean> list = stackedProductsCriteria.setResultTransformer(Transformers.aliasToBean(PalletStatsBean.class)).list();

I think the problem is that the rowCount() is happening at the same time as the countDistinct()s, but I need it to be done afterwards on the result of the countDistinct()s. but I can't seem to be able to do that. This is the generated Hibernate SQL (very different from the above SQL):

select count(*) as y0_, count(distinct this_.order_id) as y1_, count(distinct this_.tower_no) as y2_, count(distinct this_.pallet_layer_no) as y3_ from stackedproduct this_ inner join sortedorder sortedorde1_ on this_.order_id=sortedorde1_.id inner join destination destinatio2_ on sortedorde1_.destination_id=destinatio2_.id

I'm using MySQL 5.

Another quick question (not sure if this is the right place) - I want to buy the Java Persistence with Hibernate book by Gavin King and Christian Bauer but Amazon.com sells a 880 page version and Amazon.co.uk sells a 700 page version as is not out until Dec 31st. Does anyone know if they are the same, and if not, which one should I buy?

I think this example might help you:

Criteria c = session.createCriteria(User.class)
.createAlias("roles", "r")
.add(Expression.or(Expression.eq("r.roleNr", new Long(410)), Expression.eq("r.roleNr", new Long(414))))
.setProjection(Projections.distinct(Projections.projectionList().add(Projections.property("userId"), "userId").add(Projections.property("userName"), "userName")))
.setResultTransformer(new AliasToBeanResultTransformer(User.class));
List users = c.list();
((User)users.get(0)).getUserName();

The key is the use of Projections.distinct().

Hi andresgr,
thanks for your response, it has helped me, now I have this:

stackedProductsCriteria.setProjection(Projections.distinct(Projections.projectionList()
.add(Projections.property("sortedOrder"))
.add(Projections.property("towerNo"))
.add(Projections.property("palletLayerNo"))));

and it gives me back 5 rows. The last trick now is how can I do a Projections.Count() or something like that on these 5 rows and set the result to "noOfPallets" so I can do an aliasToBean transform. I don't want to use a list.size() as I will be adding grouping criteria later on.

Anyway, thank you or anyone else for the help.

I think something like this could work:


stackedProductsCriteria.setProjection(Projections.projectionList().add(
Projections.distinct(Projections.projectionList()
.add(Projections.property("sortedOrder"))
.add(Projections.property("towerNo"))
.add(Projections.property("palletLayerNo")))
.add(Projections.rowCount(), "noOfPallets") )
);

Hi andresgr,
thanks again, your post almost works! Hibernate tells me now that rowCount() needs to have a Grouping which I don't have yet. I'll add that in when I add that column to the table. I'll just keep going for now and fix this later. Thanks for your help.

Could you please rate my last post? So i'll get the 25 credits and maybe a Hibernate book O:)

sorry about that, i forgot! here you go, thanks again for your help. you're from galicia? i walked the camino de santiago 2 years ago, very much like ireland (where i'm from) around there.

Really? So ireland must be really pretty then :)
If you came back to Galicia some day give me a ping. We have good beer here too O:)

Hi andresgr or anyone else?,
I swapped some emails with you about 2 months ago how to do "select count distincts" in hibernate 3.2 using Criteria and ProjectionLists. You gave me the following sample code to try:



This doesn't work as rowCount() always returns the total number of rows in the table, ie it doesn't do it on the number of distinct rows and I can't seem to get it to do it on the distinct rows ProjectionList.

Here is the ordinary SQL which works that I want to specify in Hibernate:



Here is the Hibernate-generated SQL that doesn't work:



as you can see, it puts in "count(*) instead of "select count(distinct ....)".

I can't use Projections.count(String) either as even if I give the distinct Projection an alias, eg"distinct_rows", the count("distinct_rows") fails because it can't find "distinct_rows" anywhere.

This is really annoying, grrrrr. any ideas?
Thanks

Has anyone ever got this to work using Criteria api?