Hibernate Criteria API - 如何按集合大小进行排序？ [英] Hibernate Criteria API - how to order by collection size?

问题描述

假设我有User和UserGroup类。有一个可选的一对多用户关联组，并且该关联从两侧映射（UserGroup一侧通过名为members的属性，这是一个HashSet，用户端通过属性group）。

使用Criteria API，我如何查询所有组，按组成员数量排序？

（Edit）I应该指出，当我问这是在SQL中执行分页时，所以如果可能的话，还应该在SQL中执行排序。

另一个选择是使用@Formula注解，它基本上相当于创建一个嵌套的select，所以你可以得到你的计数。

在你的数据库对象属性/成员问题（你应该创建），将注释添加到getter中，如：

@Formula（（SELECT COUNT（TABLE_NAME.ID）FROM TABLE WHERE whatever ...））
public long getNewProperty {
return newProperty;

然后直接指定成员以获得您的计数值...

注意：请注意，当在公式注释中指定sql时，必须在hibernate处理它本身时引用当前对象表（this）时直接指定字段名称。所以在你的WHERE子句中，当引用当前对象表时，我使用ID或者其他任何东西，我怀疑是UserGroup。

我必须为我的新属性设置一个setter为了让hibernate工作，并防止编译器抱怨。

可能还有其他聪明的方式来使用@Formula，但我很新，并且没有这似乎不是关于这个主题的非常多的文档......

如果你以前从未使用过嵌套的SELECTS，那么你可能只是想重新创建首先sql - 帮助了我。

希望这有助于

Assuming I have classes User and UserGroup. There is an optional 1-many group to user association and the association is mapped from both sides (the UserGroup side via a property called "members", which is a HashSet, the User side via property "group").

Using the Criteria API, how can I query for all groups, sorted by count of group members?

(Edit) I should have pointed out when I asked this that pagination was being performed in the SQL, so the ordering should also be performed in the SQL if possible.

解决方案

Another option would be to use the @Formula annotation which is basically the equivalent of creating a nested select so you can get at your counts.

On your DB objects property / member in question (which you should create), add the annotation to the getter like:

@Formula("(SELECT COUNT(TABLE_NAME.ID) FROM TABLE WHERE whatever...)") public long getNewProperty{ return newProperty; }

Then target the member directly to get hold of your count values...

Note: Be aware that when specifying the sql within the formula annotation, you must specify the field names directly when referencing the current objects table (this) as hibernate deals with this itself. So in your WHERE clause just use ID or whatever on its own when referencing the current objects table which I suspect is UserGroup.

I had to have a setter for my new property in order for hibernate to work and prevent the compiler from complaining.

There may be other clever ways of using @Formula, but I'm quite new to this and there doesn't appear to be very much documentation on the subject...

If you've never used nested SELECTS before, it might be an idea for you to just recreate in sql first - that helped me.

Hope this helps

这篇关于Hibernate Criteria API - 如何按集合大小进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！