Hibernate Criteria: Projecting Count with group by clause

Question

I want to execute the following SQL

select count(*) as myCount from user group by name;

I came up with the following criteria for the same

DetachedCriteria.ForClass(typeof(UserDTO))
    .setProjections(Projections.ProjectionList()
                        .Add(Projections.rowCount(),"myCount")
                        .Add(Projections.groupProperty("this.name"));

I get the result back as pair of the count and name,How can I get just the count from this.

What's the use of getting several numbers without any reference of what they correspond to? — Diego Mijelshon, Oct 13 '10 at 00:03
the application which I am working on has an architecture which uses repositories for data access. There are predefined repositories to fetch objects.the objects thus fetched need to implement certain interfaces. Under such architecture I cannot retrieve an object the one returned by this criteria. — frictionlesspulley, Oct 13 '10 at 00:28

score 0 · Answer 1 · answered Jul 22 '14 at 07:26

You can use count distinct if there is only one group by column.

HQL:

select count(distinct name) as myCount from user

Criteria:

DetachedCriteria.ForClass(typeof(UserDTO))
.setProjections(Projections.ProjectionList()
                    .Add(Projections.countDistinct("name"),"myCount"));

score 0 · Accepted Answer · answered Oct 13 '10 at 00:25

0

I don't think you can do it with Criteria, but it's easy with HQL. It's exactly the same string as your SQL query, but with entity/property names instead of table/column ones.

answered Oct 13 '10 at 00:25

Diego Mijelshon

52,548
16
116
154

Hibernate Criteria: Projecting Count with group by clause

2 Answers2

Linked