Random select rows via JPA

Question

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.

Answer 1

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: 1. this may not work with another database 2. this may not work with another JPA provider.

Answer 2

Try calculating the random beforehand and construct your JPQL/HQL/native query with the pre-calculated random value.