java - Hibernate Criteria API: get n random rows

Question

I can't figure out how to fetch n random rows from a criteria instance:

Criteria criteria = session.createCriteria(Table.class);
criteria.add(Restrictions.eq('fieldVariable', anyValue));
...

Then what? I can't find any doc with Criteria API

Does it mean I should use HQL instead?

Thanx!

EDIT: I get the number of rows by:

int max = criteria.setProjecxtion(Projections.rowCount()).uniqueResult();

How do I fetch n random rows with indexes between 0 and max? Thx again!

Answer 1

Actually it is possible with Criteria and a little bit of tweaking. Here is how:

Criteria criteria = session.createCriteria(Table.class);
criteria.add(Restrictions.eq("fieldVariable", anyValue));
criteria.add(Restrictions.sqlRestriction("1=1 order by rand()"));
criteria.setMaxResults(5);
return criteria.list();

any Restrictions.sqlRestriction will add keyword 'and'; so to nullify its effect, we shall add a dummy condition and inject our rand() function.