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

java mysql hibernate group-by hibernate-criteria
instanceOfObject picture instanceOfObject · Dec 21, 2012 · Viewed 8.6k times · Source

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

atrain picture atrain · Dec 23, 2012

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.

Related questions

Hibernate: Automatically creating/updating the db tables based on entity classes

I have the following entity class (in Groovy): import javax.persistence.Entity import javax.persistence.Id import javax.persistence.GeneratedValue import javax.persistence.GenerationType @Entity public class ServerNode { @Id @GeneratedValue(strategy = GenerationType.AUTO) Long id String firstName String lastName } and …

Read More
Unable to get spring boot to automatically create database schema

I'm unable to get spring boot to automatically load my database schema when I start it up. Here is my application.properties: spring.datasource.url=jdbc:mysql://localhost:3306/test spring.datasource.username=test spring.datasource.password= spring.datasource.driverClassName = com.…

Read More
Hibernate Criteria Query to get specific columns

I am using Criteria Query in my code. It always fires select * from ... Instead I want to neglect one column(field) from my query as that field have large number of data stored in bytes. And that causing performance issue. …

Read More