How to do a random search using JPQL script in JPA2

Question

The idea is basically to search for an X number of entities persisted randomly, and these entities cannot be repeated. The question is, is there any way to perform this type of search by creating and running a JPQL script? , or is it necessary to implement a JAVA logic ?

Example

public Jogo gerarJogo(EntityManager em)
{
   Jogo jogo = new Jogo();
   public String query = //query 
   List<Pergunta> perguntas =  em..createQuery(query).getResultList();
   jogo.setPerguntas(perguntas);
   return jogo;
}

if you need to implement java logic, you do not need the algorithm, but I need more technical information, such as, if necessary, searching for the record with less or greater id, among others.

Answer 1

I will respond as if you were working mysql.

The problem is that RANDOM is not covered by the JPA specification. My suggestion is that you do a Native query +/- like this:

SELECT id FROM tabela ORDER BY RAND() LIMIT quantidadeDeRegistros

And then, with these records, make a jpaQL with IN:

SELECT p FROM Pergunta p WHERE p.id IN :ids