Java Hibernate Criteria API：获取 n 个随机行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2810693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate Criteria API: get n random rows
提问by Hadrien
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
然后呢？我找不到任何带有 Criteria 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 个随机行？再次感谢！
采纳答案by PSV Bhat
Actually it is possible with Criteria and a little bit of tweaking. Here is how:
实际上，通过 Criteria 和一些调整是可能的。方法如下：
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.
任何 Restrictions.sqlRestriction 都会添加关键字“and”；所以为了消除它的影响，我们将添加一个虚拟条件并注入我们的 rand() 函数。
回答by TomTom
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 所做的事情，而随机行提取根本不是我所知道的任何标准 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.
如果您有一个已知的带有升序数字的字段并且知道开始和结束，您可以在计算机上生成一个随机数并要求该行。
回答by BalusC
The Criteria API doesn't offer facilities for this. In MySQL however, you can use ORDER BY RAND() LIMIT nfor this where nrepresents the number of random rows you'd like to fetch.
Criteria API 没有为此提供工具。但是，在 MySQL 中，您可以使用ORDER BY RAND() LIMIT nwheren表示您想要获取的随机行数。
SELECT col1, col2, col3 FROM tbl ORDER BY RAND() LIMIT :n
You indeed need to execute it as HQL.
您确实需要将其作为 HQL 执行。
回答by Pascal Thivent
First of all, be aware that there is no standard way to do this in SQL, each database engine uses its own proprietary syntax1. With MySQL, the SQL statement to get 5 random rows would be:
首先，请注意在 SQL 中没有标准方法可以做到这一点，每个数据库引擎都使用自己的专有语法1。使用 MySQL，获取 5 个随机行的 SQL 语句将是：
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 databaseso 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 Orderclass to implement this feature. This is doable, refer to the Jira issue, but not available out of the box.
但是，与 HQL 不同，Criteria API 当前不支持ORDER BY Native 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。请记住，它不会是便携式的。
1Other readers might want to check the post SQL to Select a random row from a database tableto see how to implement this with MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle.
1其他读者可能想要查看从数据库表中选择随机行的 SQL帖子，了解如何使用 MySQL、PostgreSQL、Microsoft SQL Server、IBM DB2 和 Oracle 实现这一点。
回答by Esko Piirainen
The answer by @PSV Bhat is difficult if you are dynamically generating your Criteria. Here is a solution that extends hibernate Order class:
如果您动态生成标准，@PSV Bhat 的答案很困难。这是一个扩展休眠 Order 类的解决方案：
import org.hibernate.Criteria;
import org.hibernate.criterion.Order;
private void addOrderByToCriteria(Criteria criteria) {
criteria.addOrder(Order.asc("foobar"));
criteria.addOrder(ORDER_RANDOM);
}
private static final OrderRandom ORDER_RANDOM = new OrderRandom();
private static class OrderRandom extends Order {
public OrderRandom() {
super("", false);
}
@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) {
return "RANDOM()"; // or RAND() or whatever this is in your dialect
}
}
