JPA: select random row
java, jpa, jpql, named-query
Solution
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.
Problem
This is my JPA ENTITY ``` @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" I've tried also with RAND() instead of RANDOM and also NEWID(). Thanks.