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);
int count = ((Number) crit.uniqueResult()).intValue();
if (0 != count) {
  int index = new Random().nextInt(count);
  crit = session.createCriteria(Picture.class);
  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.

Written by Shred in Java5 comments

Tags: Hibernate

Show 5 comments...

Oct 21, 2010 5:36:31 AM

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

iteezy Nov 20, 2010 2:59:56 PM

Apparently this is now the "canonical" way to order by rand that works cross DB. Disappointing hibernate...

roger Dec 2, 2014 1:53:09 AM

Please send me the complete code after result

Anu Jul 23, 2015 7:51:06 AM

Thanks so much, I got it.

Anu Jul 23, 2015 10:16:53 AM
For the Gravatar icon. Will not be shown.
No HTML. No spam. Please be polite.
  Please click on the 'X' for submission: