Random select rows via JPA

hibernate, java, jpa, mysql

Solution

Only the functions defined in the specification are guaranteed to be supported by all JPA providers and `RAND` or `RANDOM` aren't. So I don't think that you can do it in JPQL.

However, it would be possible in HQL (the order by clause in HQL is passed through to the database, so you can use any function):

String query = "SELECT o.id FROM Order o ORDER BY random()";
Query q = em.createQuery(query);
q.setMaxResults(5);

But, I repeat:

- This may not work with another database.

- This may not work with another JPA provider.

Problem

In Mysql, ``` SELECT id FROM table ORDER BY RANDOM() LIMIT 5 ``` this sql can select 5 random rows. How to do this via JPA Query (Hibernate as provider, Mysql database)? Thanks.

Original source