选择“所有列”与“由...分组”在hibernate条件查询中 [英] select "all columns" with "group by" in hibernate criteria queries

问题描述

我想用group by编写一个标准查询并希望返回所有列。

Plane sql就是这样的：

  select * from tab group by client_name order by creation_time; 
  

我知道它会有 count（distinct client_name）行数。

我目前的查询似乎没有给出正确的结果，如下所示：

  Criteria criteria = getSession（requestType）.createCriteria（Tab.class）; 
 criteria.setProjection（Projections.projectionList（）。add（Projections.groupProperty（client_name）））; 
 criteria.addOrder（Order.asc（creationTime））; 
  

该查询仅返回client_name。我不想手动放置所有列名称。必须有某种方式，可以做些什么？ 我认为你误解了一些东西。如果您在SQL中使用 GROUP BY ，则需要按所有选定列进行分组。这同样适用于Hibernate - 如果你在 Projection 中 groupProperty ，你告诉Hibernate该列是一个组柱。如果没有其他列/字段被引用，Hibernate会认为你不需要它们，因为它们也需要分组。

退一步：你想要做什么？如果您在表中的所有列中有重复的数据，那么您的数据可能不正确，或者数据保存不正确。至少，你的钥匙会被弄乱。

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?

解决方案

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.

这篇关于选择“所有列”与“由...分组”在hibernate条件查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！