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.

Answer 2

First of all, be aware that there is no standard way to do this in SQL, each database engine uses its own proprietary syntax¹. With MySQL, the SQL statement to get 5 random rows would be:

SELECT column FROM table
ORDER BY RAND()
LIMIT 5

And you could write this query in HQL because the order by clause in HQL is passed through to the database so you can use any function.

String query = "SELECT e.attribute FROM MyEntity e ORDER BY RAND()";
Query q = em.createQuery(query);
q.setMaxResults(5);

However, unlike HQL, the Criteria API currently doesn't support ORDER BY Native SQL (see HHH-2381) and in the current state, you would have to subclass the Order class to implement this feature. This is doable, refer to the Jira issue, but not available out of the box.

So, if really you need this query, my recommendation would be to use HQL. Just keep in mind it won't be portable.

¹ Other readers might want to check the post SQL to Select a random row from a database table to see how to implement this with MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle.

Answer 3

The Criteria API doesn't offer facilities for this. In MySQL however, you can use ORDER BY RAND() LIMIT n for this where n represents the number of random rows you'd like to fetch.

SELECT col1, col2, col3 FROM tbl ORDER BY RAND() LIMIT :n

You indeed need to execute it as HQL.

Answer 4

You can not fetch random rows efficiently, sorry. Hibernate can only do what SQL does, and random row fetch simply is not part of any standard SQL implementation I know - actually it is to my knowledge not part of ANY SQL that I am aware of (anyone please enlight me).

And as Hibernate is an O/R mapper, and not a wonder machine, it can only do what the underlying database supports.

If you have a known filed with ascending numbers and know start and end, you can generate a random number on the computer and ask for that row.

Answer 5

The answer by @PSV Bhat is difficult if you are dynamically generating your Criteria. Here is a solution that extends hibernate Order class:

import org.hibernate.Criteria;
import org.hibernate.criterion.Order;

private void addOrderByToCriteria(Criteria criteria) {
    criteria.addOrder(Order.asc("foobar"));
    criteria.addOrder(ORDER_RANDOM);
}

private static final OrderRandom ORDER_RANDOM = new OrderRandom();

private static class OrderRandom extends Order {
    public OrderRandom() {
        super("", false);
    }
    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) {
        return "RANDOM()"; // or RAND() or whatever this is in your dialect
    }
}