Java JPA：选择随机行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21432078/
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
JPA: select random row
提问by user3168286
This is my JPA ENTITY
这是我的 JPA 实体
@Entity
@NamedQueries({
@NamedQuery(name = "Question.randQuestion", query = "SELECT q FROM Question AS q ORDER BY RANDOM")
})
@Table(name = "questions")
public class Question implements Serializable {
.....
}
The problem is:
eclipse gives me an error for this namedQuery. It says:
"The identification variable 'RANDOM' is not defined in the FROM clause"
问题是：
eclipse 给了我这个namedQuery 的错误。它说：“在 FROM 子句中没有定义标识变量‘RANDOM’”
I've tried also with RAND() instead of RANDOM and also NEWID().
我也试过用 RAND() 而不是 RANDOM 和 NEWID()。
Thanks.
谢谢。
回答by Alex K.
As far as I understand you want to select random question from the table. You'd rather use WHERE clause, with providing some parameter from your code, like:
据我了解，您想从表中选择随机问题。您宁愿使用 WHERE 子句，并从您的代码中提供一些参数，例如：
SELECT q FROM Question AS q WHERE id = :id
Then in the code, which creates your query you must generate random id to be selected:
然后在创建查询的代码中，您必须生成要选择的随机 ID：
query.setParam("id", getRandomId());
And to get random id, you may want to query number of rows from DB and use java.util.Random.nextInt(rowsCount)(if all ids are there, of course).
为了获得随机 id，您可能想要从 DB 查询行数并使用java.util.Random.nextInt(rowsCount)（当然，如果所有 id 都在那里）。
Btw, something similar is described here: http://www.shredzone.de/cilla/page/53/how-to-fetch-a-random-entry-with-hibernate.html
顺便说一句，这里描述了类似的东西：http: //www.shredzone.de/cilla/page/53/how-to-fetch-a-random-entry-with-hibernate.html
回答by vels4j
To get a Random Row, first get list of total question and get any one.
要获得随机行，首先获得总问题列表并获得任何一个。
public Question getRandomQuestion(EntityManager em) {
Query countQuery = em.createNativeQuery("select count(*) from Question");
long count = (Long)countQuery.getSingleResult();
Random random = new Random();
int number = random.nextInt((int)count);
Query selectQuery = em.createQuery("select q from Question q");
selectQuery.setFirstResult(number);
selectQuery.setMaxResults(1);
return (Question)selectQuery.getSingleResult();
}
Note: You may need to implement a logic to avoid duplicates while calling method more than once.
注意：当多次调用方法时，您可能需要实现一个逻辑以避免重复。
回答by Nishant Trivedi
I had to solve a specific case of this problem where I had to select random records from a set of records matching a certain input criteria. The solution also had to support limit. I describe my solution below starting with assumptions.
我必须解决这个问题的一个特定情况，我必须从一组符合特定输入条件的记录中选择随机记录。该解决方案还必须支持限制。我在下面从假设开始描述我的解决方案。
Assumptions:
假设：
Given the set of criteria as input, it is possible to count number of records that match a selection criteria, as supported by the
org.springframework.data.querydsl.QueryDslPredicateExecutor<T>.count(Predicate predicate)method.Pages are zero indexed.
It is possible to request specific page as supported by the
org.springframework.data.domain.PageRequest(int page, int size)method.
给定一组标准作为输入，可以计算与该
org.springframework.data.querydsl.QueryDslPredicateExecutor<T>.count(Predicate predicate)方法支持的选择标准匹配的记录数。页面是零索引的。
可以请求该
org.springframework.data.domain.PageRequest(int page, int size)方法支持的特定页面。
Algorithm
算法
Count all records matching the input criteria.
Calculate total number of pages based on the count and specified limit.
Generate a random page index in range [0, total pages).
Request the page with index generated in previous step.
Shuffle elements in the returned page.
计算符合输入条件的所有记录。
根据计数和指定的限制计算总页数。
生成范围 [0，总页数) 内的随机页索引。
请求带有在上一步中生成的索引的页面。
在返回的页面中随机播放元素。
Code
代码
Long totalRecords = someRepository.count(somePredicate);
Long totalPages =
(totalRecords % someLimit == 0)
? (totalRecords / someLimit)
: ((totalRecords / someLimit) + 1);
int pageIndex = (int) (Math.random() * totalPages);
PageRequest pageRequest = new PageRequest(pageIndex, someLimit);
Page<T> somePage = someRepository.findAll(somePredicate, pageRequest);
List<T> someList;
if (somePage.getTotalElements() > 0) {
someList = new ArrayList<>(somePage.getContent());
} else {
someList = new ArrayList<>();
}
Collections.shuffle(someList);
The second shuffle is to ensure records within the page are also randomized. The general case of this solution is that there is no criteria and so the count()has to be invoked with no predicatethus getting a count of all rows in the table.
第二次洗牌是为了确保页面内的记录也是随机的。此解决方案的一般情况是没有条件，因此count()必须在没有条件的情况下调用 ，predicate从而获取表中所有行的计数。
