Get random strings using JPQL

Question

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

Thanks Rod

Answer 1

This is what I use. First, I get the number of rows for the object, and then limit the results of the fetch request to a random string. This includes two queries, so if this is a problem for you, you may want to look at your own queries. If not here is the code I'm using:

 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();
 }

Answer 2

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