JPA: select random row

Question

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.

Answer 1

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.