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?

Check out this link: http://stackoverflow.com/questions/6678216/hibernate-select-all-projections-group-by — maxhuang, Dec 28 '12 at 03:42
in mysql is not permitted, click here for explanation https://es.stackoverflow.com/questions/243344/group-by-de-mysql-obliga-a-meter-todos-los-campos-del-select — Erick Mejias Alvarez, May 12 '22 at 18:47

score 0 · Answer 1 · answered Dec 23 '12 at 15:00

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.

score 0 · Answer 2 · answered Apr 16 '15 at 14:43

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));

score 0 · Answer 3 · answered Apr 17 '15 at 04:40

0

when using hibernate projection you should add all columns which are needed in projection list. you have only used Projections.projectionList().add(Projections.groupProperty("client_name"))

this . so it is clear that return only client_name.

answered Apr 17 '15 at 04:40

Dulip Chandana

22
1
3

score 0 · Answer 4 · answered Jun 02 '20 at 13:44

It is not possible to get all columns of the database by using a group based on a single column. Instead, you can use all columns in the group by clause and in the select as well which you want. To get the result in hibernate like below

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

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

4 Answers4

Linked