Hibernate Criteria API:获取n个随机的行。

I can't figure out how to fetch n random rows from a criteria instance:

我不知道如何从一个标准实例中获取n个随机的行:

Criteria criteria = session.createCriteria(Table.class);
criteria.add(Restrictions.eq('fieldVariable', anyValue));
...

Then what? I can't find any doc with Criteria API

然后呢?我找不到任何符合标准API的文档。

Does it mean I should use HQL instead?

这是否意味着我应该使用HQL ?

Thanx!

谢谢!

EDIT: I get the number of rows by:

编辑:我得到的行数:

int max = criteria.setProjecxtion(Projections.rowCount()).uniqueResult();

How do I fetch n random rows with indexes between 0 and max? Thx again!

如何在0和最大值之间取n个随机的行?再次谢谢!

4 个解决方案

#1

Actually it is possible with Criteria and a little bit of tweaking. Here is how:

实际上，有标准和稍作调整是可能的。这里是:

Criteria criteria = session.createCriteria(Table.class);
criteria.add(Restrictions.eq('fieldVariable', anyValue));
criteria.add(Restrictions.sqlRestriction("1=1 order by rand()"));
criteria.setMaxResults(5);
return criteria.list();

any Restrictions.sqlRestriction will add keyword 'and'; so to nullify its effect, we shall add a dummy condition and inject our rand() function.

任何限制。sqllimit将添加关键字'和';为了消除它的影响，我们将添加一个虚拟条件并注入我们的rand()函数。

#2

First of all, be aware that there is no standard way to do this in SQL, each database engine uses its own proprietary syntax¹. With MySQL, the SQL statement to get 5 random rows would be:

首先，要知道在SQL中没有标准的方法来实现这一点，每个数据库引擎都使用它自己的专有syntax1。使用MySQL, SQL语句得到5个随机的行:

SELECT column FROM table
ORDER BY RAND()
LIMIT 5

And you could write this query in HQL because the order by clause in HQL is passed through to the database so you can use any function.

您可以在HQL中编写这个查询，因为HQL中的order by子句被传递到数据库，以便您可以使用任何函数。

String query = "SELECT e.attribute FROM MyEntity e ORDER BY RAND()";
Query q = em.createQuery(query);
q.setMaxResults(5);

However, unlike HQL, the Criteria API currently doesn't support ORDER BY Native SQL (see HHH-2381) and in the current state, you would have to subclass the Order class to implement this feature. This is doable, refer to the Jira issue, but not available out of the box.

但是，与HQL不同的是，Criteria API目前不支持本地SQL(参见HHH-2381)的顺序，在当前状态下，您必须子类化ORDER类来实现这个特性。这是可行的，参考Jira的问题，但是没有现成的。

So, if really you need this query, my recommendation would be to use HQL. Just keep in mind it won't be portable.

因此，如果您真的需要这个查询，我的建议是使用HQL。只是要记住，它是不能移植的。

¹ Other readers might want to check the post SQL to Select a random row from a database table to see how to implement this with MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle.

其他读者可能想要检查post SQL从数据库表中选择一个随机的行，看看如何用MySQL、PostgreSQL、Microsoft SQL Server、IBM DB2和Oracle实现这一功能。

#3

The Criteria API doesn't offer facilities for this. In MySQL however, you can use ORDER BY RAND() LIMIT n for this where n represents the number of random rows you'd like to fetch.

Criteria API不提供此功能。但是，在MySQL中，可以使用RAND()限制n的顺序，在这里n表示想要获取的随机行数。

SELECT col1, col2, col3 FROM tbl ORDER BY RAND() LIMIT :n

You indeed need to execute it as HQL.

您确实需要将其作为HQL执行。

#4

You can not fetch random rows efficiently, sorry. Hibernate can only do what SQL does, and random row fetch simply is not part of any standard SQL implementation I know - actually it is to my knowledge not part of ANY SQL that I am aware of (anyone please enlight me).

你不能有效地获取随机的行，抱歉。Hibernate只能做SQL做的事情，而random row fetch并不是我所知道的任何标准SQL实现的一部分——实际上，它是我所知道的任何SQL的一部分(任何人都可以让我感到轻松)。

And as Hibernate is an O/R mapper, and not a wonder machine, it can only do what the underlying database supports.

由于Hibernate是一个O/R映射器，而不是一个神奇的机器，它只能执行底层数据库所支持的功能。

If you have a known filed with ascending numbers and know start and end, you can generate a random number on the computer and ask for that row.

如果你有一个已知的上升数字，并且知道开始和结束，你可以在计算机上产生一个随机数，并要求这一行。

#1