How to fetch a random entry with Hibernate

Friday, August 27, 2010 9:32:12 PM

I recently found myself in the situation where I needed Hibernate to query a single, random entry from a table of Picture entities.

There is a simple way. Some DBMS allow to shuffle the result set by bringing the rows into a random order. For instance, in MySQL it is possible to use a query like this:

SELECT id FROM picture ORDER BY rand() LIMIT 1;

Since Hibernate delegates unknown function calls to the underlying DBMS, rand() could be used in a HQL query as well:

q.createQuery("FROM picture ORDER BY rand()").setMaxResults(1);

However this query would require MySQL, so we would sacrifice the benefit of Hibernate acting as an abstraction layer to the underlying database. HQL on the other hand does not offer a similar function.

A solution is to use the pagination technique. First we count the number of entries, and then select a random entry using setFirstResult(). With Hibernate Criteria, it would look something like this:

Criterion restriction = yourRestrictions;
Object result = null;  // will later contain a random entity
Criteria crit = session.createCriteria(Picture.class);
crit.add(restriction);
crit.setProjection(Projections.rowCount());
int count = ((Number) crit.uniqueResult()).intValue();
if (0 != count) {
  int index = new Random().nextInt(count);
  crit = session.createCriteria(Picture.class);
  crit.add(restriction);
  result = crit.setFirstResult(index).setMaxResults(1).uniqueResult();
}

restriction contains further restrictions to the result set (like only pictures that have been published). At the end, result contains a random single entry from the Picture entity, or null if the result set was empty.

identicatwittergooglePlusdeliciousdigggooglegoogleReaderlinkarenamrWongredditwebnewsyigg

Comments

Gravatar

Oct 21, 2010 5:36:31 AM
Gravatar

Thanks so much this is what I have been looking for!

iteezy Nov 20, 2010 2:59:56 PM
Gravatar

Apparently this is now the "canonical" way to order by rand that works cross DB. Disappointing hibernate...http://stackoverflow.com/questions/4232934/get-random-object-from-sql-database-via-hibernate

roger Dec 2, 2014 1:53:09 AM
For the Gravatar icon. Will not be shown.
No HTML. No spam. Please be polite.
  Please click on the 'X' for submission: