select "all columns" with "group by" in hibernate criteria queries

Question

I want to write a criteria query using "group by" and want to return all the columns.

Plane sql is like this:

select * from Tab group by client_name order by creation_time;

I understand that it will have count(distinct client_name) number of rows.

My current query which doesn't seem to give proper result is as follows:

Criteria criteria = getSession(requestType).createCriteria(Tab.class);
        criteria.setProjection(Projections.projectionList().add(Projections.groupProperty("client_name")));
        criteria.addOrder(Order.asc("creationTime"));

This query returns "client_name" only. I don't want to manually put all column names. There must be some way, what could be done?

Answer 1

I think you're misunderstanding something. If you GROUP BY in SQL, then you need to group by all selected columns. The same applies to Hibernate - if you groupProperty in a Projection, you're telling Hibernate that that column is a group column. If no other columns/fields are referenced, Hibernate will assume you don't want them, as they would also need to be grouped.

To take a step back: what are you trying to do? If you have duplicate data across all columns in a table, you might have bad data, or be persisting data incorrectly. At the very least, your key would be messed up.

Answer 2

In hibernate for projections, all columns required needs to be added to projection list. To get the result in entity we have to use setResultTransformer. Check below example to get group by in hibernate:

 ProjectionList projectionList = Projections.projectionList();
    projectionList.add(Projections.groupProperty("column1"));
    projectionList.add(Projections.property("column2"));
    projectionList.add(Projections.property("column3"));
    criteria.setProjection(projectionList);
    criteria.setResultTransformer(Transformers.aliasToBean(Table.class));