Get Random Rows Using JPQL

Question

Is it possible to use JPQL for getting random rows? For example in SQL Server I would use: select * from myTable where columnName = 4 order by newid()

Thanks, Rod

Answer 1

As of today (April 9th 2010), JPQL does not support random ordering

Answer 2

This is what I use. I first get the number of rows for the entity and I then limit the results of the fetch query to a random row. This involves two queries, so if this is a problem for you you might want to watch native queries. If not here is the code I use:

 public <T> T randomEntity(EntityManager em, Class<T> clazz) {
      Query countQuery = em.createQuery("select count(id) from "+clazz.getName());
      long count = (Long)countQuery.getSingleResult();

      Random random = new Random();
      int number = random.nextInt((int)count);

      Query selectQuery = em.createQuery("from "+clazz.getName());
      selectQuery.setFirstResult(number);
      selectQuery.setMaxResults(1);
      return (T)selectQuery.getSingleResult();
 }